Enable/Disable triggers in mysql

Disable Triggers in MySQL 5.0

Information about triggers in MySQL 5.0 you can find here:

• MySQL 5.0 New Features: Triggers
• MySQL 5.0 Reference Manual :: 18 Stored Programs and Views :: 18.3 Using Triggers

It is not possible to temporary disable triggers in MySQL 5.0.
But there are several tricks:

1. drop/create triggers
   • drop triggers
   • do anything you need (import, update, etc)
   • create triggers


2. Use global variable
   
   • each trigger should check this variable in its code beginning
   • set global variable to 1 (or other NOT NULL value) to disable triggers
   • do anything you need (import, update, etc)
   • set global variable to NULL to enable triggers
   • global variable IS NULL by default
   
   
   • see sample below:

schema:

script:

CREATE TABLE  `users` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
CREATE TABLE  `properties` (
  `userid` int(10) unsigned NOT NULL,
  `updated` varchar(45) NOT NULL,
  PRIMARY KEY  (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


trigger:

DROP TRIGGER IF EXISTS tUsersInsert;
  
DELIMITER $$
CREATE TRIGGER tUsersInsert AFTER INSERT ON users
FOR EACH ROW BEGIN
  IF (@DISABLE_TRIGGERS IS NULL) then
    # main trigger part
    INSERT INTO properties VALUES(NEW.id, NEW.name);
  END IF;
END; $$
DELIMITER ;


trigger is enabled during insert:

INSERT INTO users(name) VALUES('aaa');


disable trigger during insert:

BEGIN;
SET @DISABLE_TRIGGERS=1;
INSERT INTO users(name) VALUES('bbb');
SET @DISABLE_TRIGGERS=NULL;
COMMIT;


result: