CODEWITHSUNDEEP
mysqltriggers
ESP32
ESP32

Reputation: 8728

Using Mysql Trigger BEFORE INSERT for Default Value

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?

Upvotes: 0

Views: 1594

Answers (1)

Shadow
Shadow

Reputation: 34285

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());

Upvotes: 4

Related Questions