CODEWITHSUNDEEP
mysqlsqldatedatabase-trigger
Pat
Pat

Reputation: 509

MySQL Trigger update a 'modifiedDate' column not working properly

I have a special requirement where I need to automatically update a 'modifiedDate' column's datetime value to NOW(), IF:

  1. Value provided is null
  2. Value provided is the same as what it was before the update

BUT

If value provided is any other date string, then update column with value.

So, I've setup this trigger, but something's now quite right with it:

CREATE TRIGGER `tr_users_updateModDateOnUpdate` BEFORE UPDATE ON `tbl_users`
 FOR EACH ROW IF (OLD.date_modified <> NEW.date_modified) THEN
  SET NEW.date_modified = IFNULL(NEW.date_modified, NOW());
ELSE
  SET NEW.date_modified = NOW();
END IF

I can't see what's wrong with it... , but I'm getting strange behavior when testing with my web application.. So trying to see if the problem is with my trigger, or my php code...

Can anyone tell me if my Trigger code seems okay for my requirements above? Thanks a million!

Pat

Upvotes: 0

Views: 38

Answers (1)

GMB
GMB

Reputation: 222582

Your trigger contains an IF statement, so it needs at BEGIN/END block. Also, the conditions do not seem to do what you want. Finally, you need to set the DELIMITER.

I think that this does what you want:

delimiter //

create trigger tr_users_update_mod_date_on_update 
before update on tbl_users
for each row 
begin
    if new.date_modified is null or old.date_modified = new.date_modified then
        set new.date_modified = now();
    end if;
end;
//

delimiter ;

Upvotes: 1

Related Questions