How to temporary disable a trigger inside a procedure and enable again?

[Former Member]

I don't know even if is it possible to do this.

But I didn't succeed looking in the manual.

I'm using SQL ANYWHERE 11.

[Breck_Carter]

Sigh... Mark posted the better answer while I was working on this one.

[MarkCulp · Accepted solution]

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:

CREATE TRIGGER myTrig AFTER INSERT
REFERENCING NEW as new_name
FOR EACH STATEMENT
BEGIN
   DECLARE @execute_trigger integer;
   IF varexists('enable_trigger_logic') = 1 THEN
      set @execute_trigger = enable_trigger_logic;
   ELSE
      set @execute_trigger = 1;
   END IF;
   IF @execute_trigger = 1 THEN
      ... <your-trigger-code-goes-here>
   END IF;
END;

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 varexists('enable_trigger_logic') = 0 THEN
      create variable enable_trigger_logic int;
   END IF;
   set enable_trigger_logic = 0;
   ... < execute-your-code-that-you-do-not-want-triggers-to-run >
   set enable_trigger_logic = 1;
   ... < now-your-trigger-code-will-do-its-work >

If you were going to do this a lot, you could create two procedures and one function:

CREATE PROCEDURE sp_disable_triggers ()
BEGIN
       IF varexists('enable_trigger_logic') = 0 THEN
          create variable enable_trigger_logic int;
       END IF;
       set enable_trigger_logic = 0;
END;

CREATE PROCEDURE sp_enable_triggers ()
BEGIN
       set enable_trigger_logic = 1;
END;

CREATE FUNCTION f_are_triggers_enabled ()
RETURNS INT
BEGIN
       IF varexists('enable_trigger_logic') = 1 THEN
          return enable_trigger_logic;
       ELSE
          return 1;
       END IF;
END;

Now you can use:

call sp_disable_triggers();
... <execute-your-code-where-trigger-logic-is-disabled>
call sp_enable_triggers();
... <execute-code-where-trigger-logic-will-run>

and within your trigger use:

IF f_are_triggers_enabled() = 1 THEN
     ... your trigger logic
END IF;

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! )

[MCMartin · Accepted solution]

This is great, maybe you should add this to the official documentation

[Former Member · Accepted solution]

Perfect explanation.

[Breck_Carter · Accepted solution]

IMO sp_enable_triggers should include the IF VAREXISTS ... CREATE VARIABLE logic as well, to make the solution more robust.

[thomas_duemesnil]

In extension to Mark if you do not want to clutter your trigger logic you could extend the trigger definition to test the condition

CREATE TRIGGER myTrig AFTER INSERT
REFERENCING NEW as new_name
FOR EACH STATEMENT
WHEN ( f_are_triggers_enabled() = 1 )
BEGIN
...
<Trigger Logic Unchanged>
...
END

HTH Thomas

[MarkCulp]

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.

[Former Member]

I know this is an old post but I thought it was worth adding that you can disable all the triggers in code using:
SET OPTION FIRE_TRIGGERS = OFF;

And if i'm not mistaken using the temporary option the change is connection specific:

SET TEMPORARY OPTION FIRE_TRIGGERS = OFF;

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.

[Breck_Carter]

You're not mistaken, even though the Help doesn't mention that fact.

[VolkerBarth]

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:)

[VolkerBarth]

"handle with care", they say:

Yes, as it requires DBA authority, the "careful handling" is given by nature:)

[regdomaratzki]

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.

[Former Member]

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.