- MySQL 5.0 New Features: Triggers
- MySQL 5.0 Reference Manual :: 18 Stored Programs and Views :: 18.3 Using Triggers
But there are several tricks:
- drop/create triggers
- drop triggers
- do anything you need (import, update, etc)
- create triggers
- drop triggers
- 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:
7 comments:
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?
@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.
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. :)
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
Thanks, very enlighting...
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
Post a Comment