advice please anyone,
i know you can disable triggers from within stored procedures like:
DISABLE TRIGGER Audit_P_Ops_Update ON P_Ops;
but i want to know if there is any way of doing this but having it disabled just for the instance of the stored procedure.
what i mean is if i run the stored procedure which disables it, if someone else ran an update query on the table where the trigger is held, to have the trigger enabled for them, as there is a chance that these things could happen in sync.
If your trigger code doesn't apply to every update then it doesn't belong in a trigger. I suggest you put it in a stored procedure instead. There is no selective disabling of triggers. They are either enabled or disabled for a table.
Poorly thought out triggers seem to be a common problem unfortunately.
The trigger was added for auditing purposes, but there is one stored procedure that does quite alot of updates to a table, and this is creating about 5 records in the audit table.
i was just wanting a way to stop that happening for this specific stored procedure...
What we once did, was to add a column to the table with the name "disable_trigger".
If any part of the application wanted to insert or update a row without firing the trigger, that column was simply set to 1.
In the trigger this was checked. If it was 1 it was reset to 0 and then the trigger finished, otherwise it would proceed normally.