Temporarily disabling trigger operations

You can set triggers so that their operations are disabled when users perform actions (that fire the trigger) on column data. The trigger can still be fired, and its operations executed, using a procedure that contains a predefined connection variable. Users can then INSERT, ALTER or DELETE columns without the trigger operations being executed even though the trigger fires.

Disable the operations of a single trigger temporarily

Note

If you are using a row level trigger, use a WHEN clause to specify when you want the trigger to fire.

This example disables the operations of a trigger based on whether a connection variable exists.

Create an after insert trigger that checks the state of a connection variable to determine if the trigger logic is enabled. If the variable does not exist, the trigger's operations are enabled:

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-logic
   END IF;
END;

Add the following code to your statement to call the trigger you created in step 1. The statement uses a connection variable to control when the trigger is disabled, and must surround the code you want to disable.

...
  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-logic-will-do-its-work

Example: temporarily disable operations for multiple triggers

This example uses the connection variable technique from Example 1 to control the operations of multiple triggers. It creates two procedures that can be called to enable and disable multiple triggers. It also creates a function that can be used to check whether trigger operations are enabled.

Create a procedure that can be called to disable trigger operations. Its behavior is based on the value of a connection variable.

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 a procedure that can be called to enable trigger operations. Its behavior is based on the value of a connection variable.

CREATE PROCEDURE sp_enable_triggers()
BEGIN
        IF VAREXISTS ('enable_trigger_logic') = 0 THEN
          CREATE VARIABLE enable_trigger_logic INT;
        END IF;
      SET enable_trigger_logic = 1;
END;

Create a function that can be called to determine whether or not your trigger operations are enabled:

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;

Add an IF clause to the triggers whose operations you want to control:

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

Call the procedure you created in Step 2 to enable trigger operations:

CALL sp_enable_triggers();
... execute-code-where-trigger-logic-runs

Call the procedure you created in Step 1 to disable trigger operations:

CALL sp_disable_triggers();
... execute-your-code-where-trigger-logic-is-disabled