CODEWITHSUNDEEP
mysqltriggerssql-update
Grec Mihai
Grec Mihai

Reputation: 45

MySQL update automatically a column after update

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?

Upvotes: 3

Views: 1423

Answers (1)

juergen d
juergen d

Reputation: 204756

It would be easier to change the column definition to

last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

If you really want to do it in a trigger (refer to the current record with NEW):

Replace

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

with

set NEW.last_update = CURDATE();

Upvotes: 4

Related Questions