MySQL update automatically a column after update

Refresh

December 2018

Views

421 time

1

As the title says, I have a table with a column named last_update of type DATE. I want to make a trigger that set it to CURDATE() every time I update a row(set it for this particular row). I tried this:

delimiter $$
CREATE TRIGGER before_customer_update before update on customer for each row
begin
update customer set last_update = CURDATE() where id = old.id;
end $$
delimiter ;

but a get an error

can't update table in stored function/trigger because it is already used by statement

How can I resolve this?

1 answers

3

Было бы проще изменить определение столбца в

last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Если вы действительно хотите сделать это в качестве триггера (см текущей записи с NEW):

замещать

update customer set last_update = CURDATE() where id = old.id;

с

set NEW.last_update = CURDATE();