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
   IF UPDATE (progress_percentage)
   SET last_progress_date = GETDATE()

1 Answer 1


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 
   --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) 
       SET NEW.last_progress_date = GETDATE();
   END IF;
  • Thank you very much! I had no idea I was confusing sql and mysql code =S My DB is a mysql one, but I've always used sql with php to get data until now. I was able to drop the trigger but the last part after delimiter didn't get parsed, so I pasted solely that part, but it has the following error: "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6 " → SET NEW.last_progress_date = GETDATE();
    – mesqueeb
    Commented Nov 5, 2015 at 0:36
  • By the way I added the table names in the first row. CREATE TRIGGER wp_task_mgr.last_progress_date BEFORE UPDATE ON wp_task_mgr.wp_task_mgr
    – mesqueeb
    Commented Nov 5, 2015 at 0:38
  • I'm not sure if dot can be part of trigger name in mysql. I'd first make it work with simple name.
    – a1ex07
    Commented Nov 5, 2015 at 15:53
  • How would the trigger know then which table to look at?
    – mesqueeb
    Commented Nov 5, 2015 at 16:18
  • ON clause specifies table. I wanted to meant wp_task_mgr.last_progress_date may be invalid name for mysql trigger (not 100% sure, I haven't worked with mysql for quite a while). I'd name it , for instance, BU_wp_task_mgr ...
    – a1ex07
    Commented Nov 5, 2015 at 17:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.