Is it possible to listen to updates and change value?

Question

Assume I have a mysql table with columns Status, Timestamp and others. Is it possible to make Timestamp update whenever Status is updated?

take a look at this answer: https://stackoverflow.com/questions/18962757/when-is-a-timestamp-auto-updated — Ofer Skulsky, Apr 12 '18 at 08:06
They only want the Timestamp updated when the Status Column is changed — neildt, Apr 12 '18 at 08:07

score 1 · Answer 1 · answered Apr 12 '18 at 08:01

Yeah, you've got 3 different approaches:

1) Add the timestamp update to your queries explicitly.

2) Implement triggers on your database. Triggers are used to take actions when events occur. The only bad thing with using triggers is it's not guaranteed the timestamp with be the same moment the update has occurred; hence the reason why updating it with the query is better.

3) Make your updates using stored procedures which make multiple changes by a single database call.

To be honest: 1 is the simplest approach and is what I'd recommend as this also isn't really what triggers are necessarily intended for.

score 1 · Answer 2 · answered Apr 12 '18 at 08:04

You can use a AFTER UPDATE ON table Trigger, that will check if the Status Column value has been changed. Trigger Syntax and Examples

For example

CREATE TRIGGER `your_trigger_name` 
AFTER INSERT ON `TableName` 
FOR EACH ROW  

IF NOT NEW.StatusColumnName <=> OLD.StatusColumnName THEN
    UPDATE TableName SET TimeStampColumnName = NOW() WHERE id = OLD.id; 
END IF;

Is it possible to listen to updates and change value?

2 Answers2