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.

asked 03 Feb '10, 19:50

Ismael's gravatar image

Ismael
2026612
accept rate: 50%


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

permanent link

answered 03 Feb '10, 20:35

Mark%20Culp's gravatar image

Mark Culp
22.5k9129265
accept rate: 40%

edited 04 Feb '10, 12:25

1

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

(04 Feb '10, 08:06) Martin
1

Perfect explanation.

(04 Feb '10, 11:35) Ismael
2

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

(06 Feb '10, 19:33) Breck Carter

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

permanent link

answered 04 Feb '10, 11:10

Thomas%20Duemesnil's gravatar image

Thomas Dueme...
2.5k213460
accept rate: 15%

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.

(04 Feb '10, 12:02) Mark Culp

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.

permanent link

answered 30 Aug '12, 09:45

Robert2's gravatar image

Robert2
91348
accept rate: 0%

edited 30 Aug '12, 10:22

Breck%20Carter's gravatar image

Breck Carter
26.9k424581829

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

"handle with care", they say:

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×58
×4

question asked: 03 Feb '10, 19:50

question was seen: 5,670 times

last updated: 31 Aug '12, 08:17