How to disable triggers in MySQL?

Question

In my MySQL database I have some triggers ON DELETE and ON INSERT. Sometimes I need to disable some triggers, and I have to DROP e.g.

DROP TRIGGER IF EXISTS hostgroup_before_insert //   

and reinstall. Is there any shortcut to SET triggers hostgroup_before_insert = 0 like we have for foreign keys:

mysql> SELECT version();
+-------------------------+
| version()               |
+-------------------------+
| 5.1.61-0ubuntu0.10.10.1 |
+-------------------------+
1 row in set (0.00 sec)

EDIT Answer There is no built-in server system variable TRIGGER_CHECKS in MySQL.
A simple workaround is to instead use a user-defined session variable.

#FALSE value overrides trigger type settings
SET @TRIGGER_CHECKS = [TRUE|FALSE]; 

SET @TRIGGER_BEFORE_INSERT_CHECKS = [TRUE|FALSE];
SET @TRIGGER_AFTER_INSERT_CHECKS = [TRUE|FALSE];

DELIMITER $$
DROP TRIGGER IF EXISTS `yearCheck_beforeInsert` $$
CREATE DEFINER=`root`@`localhost` TRIGGER `yearCheck_beforeInsert`
BEFORE INSERT ON `movies` FOR EACH ROW 

#Patch starts here
thisTrigger: BEGIN
  IF ((@TRIGGER_CHECKS = FALSE)
      OR (@TRIGGER_BEFORE_INSERT_CHECKS = FALSE))
    AND (USER() = 'root@localhost') 

This TRICK is Explained here.

Toxoplasmosis

What happens when the code is executed in for example PHP where the user defined session variable does not exist? Will it treat at false "FALSE" or will it error with varaible does not exists type message. – Fluid

I found the answer posted to [#3578028 to be helpful, regarding SET @disable_trigger = NULL; and the corresponding conditional statement. – Adverb

Answer 1

Its not possible to temporarly disable triggers. Do one thing, use one global variable. Trigger will first check value of global variable first. In this case you can change value of global variable to prevent working of trigger.

Chairman

How do you create a global variable? – Tricky

Why disabling triggers temporarily not possible? – Schwing

Answer 2

@Fathah's answer pointed me at a slightly different solution (more convenient/flexible for me).

1. create a table that stores names of triggers to be disabled.
2. then put a trigger name in that table to disable it.
3. have a trigger check for its name in that table before doing anything.

For example:

1. create table Disabled_Triggers (TriggerName varchar(500) not null);

2. Insert into Disabled_Triggers(TriggerName) values ('trg_example');

CREATE TRIGGER trg_example AFTER UPDATE on Example_Table
     FOR EACH ROW BEGIN
      if not exists (select 1 from Disabled_Triggers where TriggerName = 'trg_example') THEN
       ...
      END IF;
     END;

Now, when the trigger name is in the Disabled_Triggers table the trigger won't do anything when Example_Table is updated. This solved my problem that brought me here.

Galcha