Trigger not setting DATETIME correctly 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
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);