0

I have the following table:

CREATE TABLE assignment (
  id INT NOT NULL AUTO_INCREMENT,
  course_id INT NOT NULL,
  title VARCHAR(128),
  assigned DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  due DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
)

with the following trigger:

DELIMITER ;;

DROP TRIGGER IF EXISTS assignment_due_date;

CREATE DEFINER=`root`@`localhost` TRIGGER assignment_due_date
    BEFORE INSERT ON `assignment`
    FOR EACH ROW BEGIN
        IF (NEW.due IS NULL) THEN
            SET NEW.due=DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 7 DAY);
        END IF;
    END;;

DELIMITER ;

The intent is to set a default due date of 1 week (7 days), unless the INSERT already specifies a due date. However, whenever I insert a row, the due date ends up the same as the assigned date, without any error messages. Why would the trigger not update the due column?

Note that this behaves as expected when the INSERT statement does specify a value for the due column, and only misbehaves when a value is not provided. INSERT INTO assignment (course_id, title, due) VALUES (12, 'Hemingway Essay', '2019-06-12') populates the assigned and due columns with the current time and 2019-06-12, as expected, but INSERT INTO assignment (course_id, title) VALUES (12, 'Hemingway Essay') populates both columns with the current time.


UPDATE

I've removed the DEFAULT value of the due column, so the table now looks like this:

CREATE TABLE assignment (
  id INT NOT NULL AUTO_INCREMENT,
  course_id INT NOT NULL,
  title VARCHAR(128),
  assigned DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  due DATETIME NOT NULL,
  PRIMARY KEY (id)
)

With no trigger in place, the following INSERT fails (due to the lack of default value):

 INSERT INTO assignment (course_id, title) VALUES (12, 'Hemingway Essay')

which is expected. With the following three variations of the trigger in place, I would expect the due date to be set to one week out, but it always ends up with the current time:

IF (NEW.due IS NULL) THEN
    SET NEW.due=DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 7 DAY);
END IF;
SET NEW.due=COALESCE(NEW.due, CURRENT_TIMESTAMP + INTERVAL 7 DAY);
SET NEW.due=(CURRENT_TIMESTAMP + INTERVAL 7 DAY); -- forget conditions, just always set
11
  • However, whenever I insert a row, the due date ends up the same as the assigned date, without any error messages. Very strange... see fiddle (trigger text was simplified).
    – Akina
    May 14, 2019 at 13:45
  • @Akina I've created a fork of that fiddle with a new insert statement that reproduces the issue. It seems if the column is explicitly inserted as NULL, it works as expected, but if it is omitted, the trigger doesn't work.
    – yoozer8
    May 14, 2019 at 13:50
  • 1
    If some field is NOT listed in the fields list, then DEFAULT is applied before any trigger firing.
    – Akina
    May 14, 2019 at 13:57
  • So the best solution is likely to remove the DEFAULT value of the column
    – yoozer8
    May 14, 2019 at 15:11
  • remove the DEFAULT value of the column That's right.
    – Akina
    May 14, 2019 at 19:08

1 Answer 1

0

You are testing for NULL, yet you are not letting it be NULL -- either by having a DEFAULT or NOT NULL.

So change to

due DATETIME NULL,

And, for less confusion between "timestamps" and "datetimes", use

SET NEW.due = COALESCE(NEW.due, NOW() + INTERVAL 7 DAY);
3
  • The IF and COALESCE approach check for null, but my 3rd variation on the trigger (always set to +7 days) exhibits the same behavior (setting it to current time).
    – yoozer8
    May 15, 2019 at 16:36
  • @yoozer8 - are you implying the + INTERVAL 7 DAY is being ignored?
    – Rick James
    May 15, 2019 at 16:55
  • Yes, that appears to be the case.
    – yoozer8
    May 15, 2019 at 17:07

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.