Using Mysql Trigger BEFORE INSERT for Default Value

Question

I have created a BEFORE INSERT trigger on a Mysql table. I want to create a default value behavior for a date field in mySQL 5.4. This means I want to use the value from the trigger if no value is provided for this field at the insert.

CREATE TRIGGER `be_table1` 
BEFORE INSERT ON `table1` FOR EACH ROW SET NEW.`date_joined` = NOW();

This works so far. But if I now insert data into my table like this ...

INSERT INTO table1 (date_joined, productname) VALUES ('2001-02-10', 'hello world');

... not '2001-02-10' is inserted, but the current date.

As far as I understood the BEFORE INSERT trigger, it pre-populates the field and should be overwritten by the insert statement. Am I wrong?

MySQL 5.4 is downright ancient at this point. If you can upgrade to 5.7 or 8.0 you'll see considerable performance benefits. — tadman
– tadman, Commented Feb 10, 2021 at 22:40
Thanks. Where can I add this clause? I get a syntax error if I put it right behind Now() — ESP32
– ESP32, Commented Feb 10, 2021 at 22:43

Shadow · Accepted Answer · 2021-02-10 22:44:22Z

4

You can modify the values to be inserted in the before insert trigger, not pre-populate it!

You can use coalesce() function to override the NEW value only if it is null:

...
SET NEW.`date_joined` = COALESCE(NEW.`date_joined`, NOW());

answered Feb 10, 2021 at 22:44

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tadman Over a year ago

In MySQL this is also known as IFNULL(), though COALESCE() is more in line with the SQL standard.

Collectives™ on Stack Overflow

Using Mysql Trigger BEFORE INSERT for Default Value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related