mysqltriggersmysql-workbenchmysql-error-1442

Updating DATETIME with trigger, get error 1442 in MySQL

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().

Solution

  • 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().