CODEWITHSUNDEEP
mysqltimestampauto-update
Dev Angie
Dev Angie

Reputation: 3

MySQL automatic update for specific column instead of whole record

I have a table APPLEVARIETY. It has the following columns:

For the latter column I would like to use 'ON UPDATE CURRENT_TIMESTAMP'. However, this will cause the timestamp to be updated if at least one of the other columns are updated (id, family, color).

Instead I would like to specify that the description_last_update_time will get the CURRENT_TIMESTAMP if only the description column is updated. It should ignore updates on the rest of the columns.

I have looked through the MySQL manual, other questions on stackoverflow and of course used google extensively but found nothing.

So in other words: is there some way to specify a certain column with ON UPDATE? Is it even possible or am I moving in the wrong direction?

Upvotes: 0

Views: 1077

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

No thats not possible using the ON UPDATE CURRENT_TIMESTAMP this will get updated when any column is updated. You may however achieve the same using a trigger. For that you will first need to remove ON UPDATE CURRENT_TIMESTAMP from the table definition. The trigger will look like

delimiter //

create trigger APPLEVARIETY_update before update on APPLEVARIETY
for each row 
begin
  if new.description <> old.description then
    set new.description_last_update_time = now();
  end if;
end;//

delimiter ;

Upvotes: 2

Related Questions