Description:
Trying to run a trigger that may invoke itself is prohibited by MySQL.
How to repeat:
CREATE TABLE test (
id INT NOT NULL AUTO_INCREMENT,
data INT NOT NULL,
updated DATETIME DEFAULT NULL,
PRIMARY KEY (id)
) Engine=InnoDB;
SET @nestlevel = 0;
DELIMITER //
CREATE TRIGGER test_update AFTER UPDATE ON test
FOR EACH ROW
BEGIN
IF @nestlevel = 0 THEN
SET @nestlevel = 1; -- this would prevent the trigger from executing infinitely
UPDATE test SET updated = NOW() WHERE id = NEW.id;
ELSE
SET @nestlevel = 0;
END IF;
END; //
DELIMITER ;
INSERT INTO test (data) VALUES(123);
UPDATE test SET data = 234 WHERE id = 1;
ERROR 1442 (HY000): Can't update table 'test' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
Suggested fix:
Add option IGNORE TRIGGER for INSERT/UPDATE/DELETE queries or allow triggers to nest up to a maximum number of iterations (specifiable as a server variable).
Description: Trying to run a trigger that may invoke itself is prohibited by MySQL. How to repeat: CREATE TABLE test ( id INT NOT NULL AUTO_INCREMENT, data INT NOT NULL, updated DATETIME DEFAULT NULL, PRIMARY KEY (id) ) Engine=InnoDB; SET @nestlevel = 0; DELIMITER // CREATE TRIGGER test_update AFTER UPDATE ON test FOR EACH ROW BEGIN IF @nestlevel = 0 THEN SET @nestlevel = 1; -- this would prevent the trigger from executing infinitely UPDATE test SET updated = NOW() WHERE id = NEW.id; ELSE SET @nestlevel = 0; END IF; END; // DELIMITER ; INSERT INTO test (data) VALUES(123); UPDATE test SET data = 234 WHERE id = 1; ERROR 1442 (HY000): Can't update table 'test' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. Suggested fix: Add option IGNORE TRIGGER for INSERT/UPDATE/DELETE queries or allow triggers to nest up to a maximum number of iterations (specifiable as a server variable).