Updating DATETIME with trigger, get error 1442 in MySQL

Question

I am having trouble with a trigger in MySQL. I have a column named "last_modified" that I want to be automatically updated with the current date and time when it is the table is edited. Using a trigger, this is my SQL query:

delimiter //
CREATE TRIGGER trg_update_responders BEFORE UPDATE ON survey_responders
FOR EACH ROW
BEGIN
UPDATE survey_responders
SET NEW.last_modified = CURRENT_DATETIME();
END;//

However, when I update the table, such as with this query:

UPDATE survey_responders SET first_name = "bob" WHERE id = "1";

MySQL Workbench displays error 1442: "Can't update table 'table_name' in stored function/trigger because it is already used by statement which invoked this stored function/trigger"

I have looked at similar questions with the same error but still have not fixed it. Help is appreciated.

** UPDATE **

This did the trick:

delimiter //
CREATE TRIGGER trg_update_responders BEFORE UPDATE ON survey_responders
FOR EACH ROW
BEGIN
SET NEW.last_modified = CURRENT_TIMESTAMP();
END;//

Seems like I simply did not need to repeat the

UPDATE survey_responders

and CURRENT_DATETIME() did not exist, I had to use CURRENT_TIMESTAMP().

Any reason for not updating last_modified column in update statement itself? — rs., Apr 02 '13 at 03:09
This is part of an assignment; it requires me to use a trigger. — Richard, Apr 02 '13 at 03:13

score 1 · Accepted Answer · answered Apr 02 '13 at 13:27

This did the trick:

delimiter //
CREATE TRIGGER trg_update_responders BEFORE UPDATE ON survey_responders
FOR EACH ROW
BEGIN
SET NEW.last_modified = CURRENT_TIMESTAMP();
END;//

Seems like I simply did not need to repeat the

UPDATE survey_responders

and CURRENT_DATETIME() did not exist, I had to use CURRENT_TIMESTAMP().

score 0 · Answer 2 · edited May 23 '17 at 10:32

What you are trying to do is not possible using a trigger.

Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

Source

You need to do this some other way.

See here: MySQL - Trigger for updating same table after insert

The typical way to do that, is to create a stored procedure, that inserts into/Updates the target table, then updates the other row(s), all in a transaction.

Updating DATETIME with trigger, get error 1442 in MySQL

2 Answers2