1

I am trying to set up a trigger to get it so if a new entry is either null or set to '000-00-00 00:00:00', that it will set the current datetime (NOW()).

I can get the trigger to set the datetime if I don't check for null or other value, but if I do, it doesn't fire.

I have tried the following and they are not working:

delimiter // CREATE TRIGGER applicationsolution_OnInsert BEFORE UPDATE ON applicationsolution FOR EACH ROW BEGIN IF NEW.created = '0000-00-00 00:00:00' THEN SET NEW.created = NOW(); END IF; END;// delimiter ;


CREATE TRIGGER applicationsolution_OnInsert BEFORE INSERT ON applicationsolution FOR EACH ROW SET NEW.created = IFNULL(NEW.created, NOW());
1

Try this code:

DROP TABLE applicationsolution;
CREATE TABLE applicationsolution (myid INTEGER AUTO_INCREMENT PRIMARY KEY,
                                  created DATETIME);

Sample table.

delimiter //    
CREATE TRIGGER applicationsolution_OnInsert
BEFORE INSERT ON applicationsolution 
FOR EACH ROW
BEGIN
  IF NEW.created IS NULL
    THEN SET NEW.created = CURRENT_TIMESTAMP();
  END IF;
END //
delimiter ;

Trigger - which works - see below.

INSERT INTO applicationsolution (created) VALUES (NULL);
INSERT INTO applicationsolution (created) VALUES (NULL);

Sample data insert - will produce two records.

SELECT * FROM applicationsolution;

myid,created
==== =======
1,"2016-06-24 21:50:40"
2,"2016-06-24 21:50:40"

So far, so good for NULLs. Now for the UPDATE trigger:

delimiter // 
CREATE TRIGGER applicationsolution_OnUpdate 
BEFORE UPDATE ON applicationsolution 
FOR EACH ROW 
BEGIN 
  IF NEW.created = '0000-00-00 00:00:00' 
    THEN SET NEW.created = current_timestamp(); 
  END IF; 
END//
delimiter ;

Now do an update.

UPDATE applicationsolution  SET created = '0000-00-00 00:00:00'
WHERE myid = 2;


SELECT * FROM applicationsolution;

And the results:

myid,created
1,"2016-06-24 21:50:40"
2,"2016-06-24 21:54:30"

Note the timestamp change in record 2 which shows that if you try to INSERT a created of '0000-00-00 00:00:00', you get a CURRENT_TIMESTAMP.

Does this do what you want? I think there are two problems with your code. It's not formatted properly - this made it very difficult to follow the logic and difficult to see that the second trigger was incomplete. You also put the INSERT code in the ON UPDATE trigger and vice-versa.

Furthermore, CURRENT_TIMESTAMP seems to work better than NOW().

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.