3

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
6

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 ;
  • 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 Nov 5 '15 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 Nov 5 '15 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 Nov 5 '15 at 15:53
  • How would the trigger know then which table to look at? – mesqueeb Nov 5 '15 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 Nov 5 '15 at 17:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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