Update TimeStamp on Single Column Update

Question

I have one table called number_list which have columns like:

id, name, number, server, status, last_act, user_id, created_at, disable, notify,fcm

I want update last_act when there any changes in status column only. Currently its updating last_act whenever any changes in any column. Let me know if its possible with MySQL. Thanks

Yes it is possible; have you tried resolving this yourself first or researched it? — Funk Forty Niner, Jul 18 '18 at 12:27
what are the possible values for status? is the update query will work on the basis on id column?what you have tried ? have you tried trigger? — Ankit Agrawal, Jul 18 '18 at 12:36
Create `ON UPDATE` trigger where you could check whenever the status has been changed. More here: https://stackoverflow.com/questions/6296313/mysql-trigger-after-update-only-if-row-has-changed — mitkosoft, Jul 18 '18 at 12:38

score 0 · Answer 1 · answered Jul 18 '18 at 12:49

Setting up a trigger like the following should accomplish what you have asked.

DELIMITER $$
        CREATE TRIGGER number_list_update_trigger 
        BEFORE UPDATE 
        ON number_list
        FOR EACH ROW
        BEGIN
          if (NEW.status = OLD.status) THEN 
             set NEW.last_act = OLD.last_act;
          END IF;
        END $$
  DELIMITER ;

Update TimeStamp on Single Column Update

1 Answers1