Mysql – Update one thesql column when another is edited

MySQLtrigger

I'm trying to make a datetime field that automatically gets updated with the current time only if there was a change to a certain field.

It seems I have a syntax error.
I try to have last_progress_date that gets the date when the progress_percentage is updated/edited:

CREATE OR ALTER TRIGGER last_progress_date
ON wp_task_mgr
AFTER UPDATE  
AS BEGIN
   IF UPDATE (progress_percentage)
   SET last_progress_date = GETDATE()
END

Best Answer

The code doesn't look like valid Mysql code:

  1. ALTER is not a valid option
  2. No AS in Mysql
  3. IF UPDATE(column)
  4. Finally, you can change value of the record which is being modified in BEFORE trigger, not in AFTER.

You need something like 

drop trigger if exists last_progress_date;
delimiter // 
CREATE TRIGGER last_progress_date BEFORE UPDATE ON wp_task_mgr 
FOR EACH ROW     
BEGIN
   --assuming progress_percentage is not nullable; if it is, the condition 
   -- needs to be modified to properly handle NULL values
   IF (NEW.progress_percentage != OLD.progress_percentage) 
   THEN
       SET NEW.last_progress_date = GETDATE();
   END IF;
END;
//
DELIMITER ;
Related Question