There is no way to programmatically disable a trigger temporarily. Note that you can disable triggers on the server command line using the -gf command line option (but this is not going to help you here). One method that may solve your problem is to let the trigger fire but programmatically make the operation a no-op. One method to doing this would be to wrap the logic within your trigger code with, say, a test of a connection variable. For example:
Note that my example checks that the global connection variable exists before attempting to use the variables value. The non-existing variable implies that triggers are enabled. Then when you want to disable the triggers temporarily, you would do:
If you were going to do this a lot, you could create two procedures and one function:
Now you can use:
and within your trigger use:
Note: If you are using a row level trigger then you can add a WHEN clause containing the condition and leave the trigger code as is. ( Thanks to TDuemesnil for making this suggestion! ) answered 03 Feb '10, 20:35 Mark Culp 2
IMO sp_enable_triggers should include the IF VAREXISTS ... CREATE VARIABLE logic as well, to make the solution more robust. |
In extension to Mark if you do not want to clutter your trigger logic you could extend the trigger definition to test the condition
HTH Thomas answered 04 Feb '10, 11:10 Thomas Dueme... 2
Nice! This solution keeps the trigger code clean. Note however that the WHEN clause can only be used for row level triggers but not in INSTEAD OF triggers. |
I know this is an old post but I thought it was worth adding that you can disable all the triggers in code using: And
You can find the option details in the docs for v11: http://dcx.sybase.com/1100/en/dbadmin_en11/fire-triggers-option.html Also for what it is worth i found the same in the v7 and v9 docs as well. answered 30 Aug '12, 09:45 Robert2 Breck Carter You're not mistaken, even though the Help doesn't mention that fact.
(30 Aug '12, 10:23)
Breck Carter
Replies hidden
1
As this may also influence RI logic (i.e. cascading deletes and the like), I'm really surprised that this option can be used connection-specific - "handle with care", they say:)
(30 Aug '12, 12:07)
Volker Barth
Yes, as it requires DBA authority, the "careful handling" is given by nature:)
(30 Aug '12, 12:09)
Volker Barth
2
The anal-retentive part of me wants to highlight that the original question asked about disabling "a" trigger, but setting the fire_triggers database option will disable "ALL" triggers in the database, including system generated triggers used for referential integrity on foreign keys. I think the original answers from Mark and Thomas are better suited to disabling a single trigger.
(30 Aug '12, 12:37)
Reg Domaratzki
Replies hidden
I agree with you Reg, this post was only intended as an addition. I would only use it in a rare situation when I had little choice.
(31 Aug '12, 08:17)
Robert2
|