Wednesday, March 4, 2009

Disable Triggers in MySQL 5.0

Information about triggers in MySQL 5.0 you can find here:
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:

  3. 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:

7 comments:

james jackson said...

so it seems like you would have to set that global variable up in your my.conf and then restart the
db... then you could set it to 1/0

you can't just create global variables from the CLI ... or
is there something i'm missing?

0v34c10ck said...
This comment has been removed by the author.
0v34c10ck said...

@james jackson:

It's not based on the my.conf file. Its a per connection change. By default, the variable will be NULL. On a user's connection who is doing the INSERTs, its SET to 1. After a disconnect it will be NULL again.

0v34c10ck said...

The code would allow any user to disable triggers. That may not be desirable.

Also, when altering existing triggers you'll need to wrap the entire trigger body in the IF-THEN construct.

Here's a different way to do it:

http://mysql-0v34c10ck.blogspot.com/2011/06/how-to-disableenable-triggers-on-demand.html

This might be better/worse. Let me know what you think. :)

Kevin Nelson said...

Thanks for the post. I've got two tables whose triggers will update each other and I need to avoid an infinite loop. So, I'll be trying this out. However, I think some of the confusion with the other users is that you are calling it a "GLOBAL" variable instead of a "SESSION" variable. The @ indicates SESSION scope:

http://www.java2s.com/Tutorial/MySQL/0201__Procedure-Function/LOCALSESSIONANDGLOBALVARIABLESINMYSQL.htm

fahri said...

Thanks, very enlighting...

Trương Chương Dương said...

Thank You,

I just created a script which can detect,disable and re-enable all trigger. It will more flexible when we want to temporary disable all trigger, you can see the code here

http://www.chuongduong.net/page/9/Temporary%20disable%20triggers%20in%20MySQL

DuongTC