Sunday, November 8, 2009

Solution for "Can't update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger"

I try to create a mysql trigger for testing:

DELIMITER $$

DROP TRIGGER IF EXISTS change_password $$

CREATE TRIGGER change_password
AFTER INSERT ON user
FOR EACH ROW
BEGIN
UPDATE user SET password = 'newpassword' WHERE id = NEW.id;
END
$$

DELIMITER ;

And then i do an insertion: "INSERT INTO user SET username='alice', password='changeme'"; I got this error:
"Can't update table user in stored function/trigger because it is already used by statement which invoked this stored function/trigger".

I search online and found a solution here: http://crazytoon.com/2008/03/03/mysql-error-1442-hy000-cant-update-table-t1-in-stored-functiontrigger-because-it-is-already-used-by-statement-which-invoked-this-stored-functiontrigger/

The point is, if you want to create a trigger on the table which will update itself, you must use the NEW.column_name to refer to the row. So i modify my trigger as:

DELIMITER $$

DROP TRIGGER IF EXISTS change_password $$

CREATE TRIGGER change_password
AFTER INSERT ON user
FOR EACH ROW
BEGIN
SET NEW.password = 'newpassword';
END
$$

DELIMITER ;

This time i got another error when i create this trigger: "Updating of NEW row is not allowed in after trigger". So i change the trigger to:
DELIMITER $$

DROP TRIGGER IF EXISTS change_password $$

CREATE TRIGGER change_password
BEFORE INSERT ON user
FOR EACH ROW
BEGIN
SET NEW.password = 'newpassword';
END
$$

DELIMITER ;

The trigger is created successfully and being executed when i run the insert sql statement again.