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.

13 comments:

Anonymous said...

Thank!!!!!! excelent!!!

chumemo said...

Excelente Muchas gracias !!!

Luis V said...

Dios te bendiga!

Anonymous said...

Hello,
Do you know how I could do to insert another record in the table when inserting a new one?

Anonymous said...

Sorry, it is not possible. http://dev.mysql.com/doc/refman/5.5/en/faqs-triggers.html#qandaitem-B-5-1-9

Hugo Aguirre said...

AMAZIIIIIIIIIIIIING!!! THANK YOU!!

Hugo Aguirre said...

OMG!! you saved me!! thank you dude!

Ferran Gonzalez Alonso said...
This comment has been removed by the author.
Ferran Gonzalez Alonso said...
This comment has been removed by the author.
Ferran Gonzalez Alonso said...
This comment has been removed by the author.
Ferran Gonzalez Alonso said...
This comment has been removed by the author.
Ferran Gonzalez Alonso said...

Good men, thanks!

Amit Garg said...

what can I do if I need to update the value of a column on the basis of ID which is auto_increment?