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:
x
+--------------+-------------+
| 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