Advertisement

Mysql update date relative to specific column update

I have a users_settings table on my db in which I store some data about the user. For some of that data I need to keep track of last modification, for example:

+--------------+-------------+
| Field        | Type        |
+--------------+-------------+
| feel         | varchar(10) |
| feel_last    | timestamp   |
| other        | varchar(10) |
| other_last   | timestamp   |
+--------------+-------------+

When I update the row with a new feel value I want to automatically write the current timestamp, is it possible to achieve this directly from mysql or I need to set the timestamp directly in the update query from my backend?

Update 1

As suggested I need to use a trigger, I written this trigger but there is some syntax error, can you help me to identify the error?

CREATE TRIGGER users_feel_last BEFORE UPDATE ON users
FOR EACH ROW BEGIN
  IF NEW.feel <> OLD.feel THEN
    SET NEW.feel_last := now();
  END IF;
END;

UPDATE 2

For who use AWS RDS: the parameter “log_bin_trust_function_creators” isn’t enabled by default, to create triggers it’s necessary, here a small guide to set it: https://aws.amazon.com/it/premiumsupport/knowledge-center/rds-mysql-functions/

Now, thanks to James answer I created the trigger, it works well

DELIMITER $$
CREATE TRIGGER before_feel_last_update 
    BEFORE UPDATE ON users
    FOR EACH ROW 
BEGIN

if(old.feel<>new.feel)
then
 set new.feel_last=current_timestamp;
elseif( old.feel = NULL )
then 
 set new.feel_last=current_timestamp;
end if;


END$$
DELIMITER ;

Advertisement

Answer

You can use the below trigger on BEFORE UPDATE

BEGIN

if(old.feel<>new.feel)
then

 set new.feel_last=current_timestamp;

end if;

END

Advertisement