How can triggers be temporarily disabled in mysql?
is it even possible?
in version 5 there's no
built-in mechanism for it
but you can introduce a special flag variable that controls whether the trigger works or not
Schema:
CREATE TABLE `users` (
`id` int auto_increment,
`name` varchar(15) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `actions` (
`userid` int unsigned NOT NULL,
`updated` varchar(45) NOT NULL,
PRIMARY KEY (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
TRIGGER:
DROP TRIGGER IF EXISTS AddUsers;
CREATE TRIGGER AddUsers AFTER INSERT ON users
FOR EACH ROW BEGIN
IF (@DISABLE_TRIGGERS IS NULL) then
# main body
INSERT INTO actions VALUES(NEW.id, NEW.name);
END IF;
END;
normal operation of the trigger:
INSERT INTO users(name) VALUES('Masha');
working with the trigger disabled:
BEGIN;
SET @DISABLE_TRIGGERS=1;
INSERT INTO users(name) VALUES('bbb');
SET @DISABLE_TRIGGERS=NULL;
COMMIT;
thanks for the info
Comments