MySQL set current date in a DATETIME field on insert

Question

I have a 'created_date' DATETIME field which I would like to be populated with the current date upon insert. What syntax should I use for the trigger for this? This is what I have so far but it is incorrect.

CREATE TRIGGER set_created_date
BEFORE INSERT ON product
FOR EACH ROW BEGIN
SET NEW.created_date = now();

Answer 1

Your best bet is to change that column to a timestamp. MySQL will automatically use the first timestamp in a row as a 'last modified' value and update it for you. This is configurable if you just want to save creation time.

http://dev.mysql.com/doc/refman/5.4/en/timestamp.html

Answer 2

It will be easyer to set a default value for your created_date field like this:

CREATE TABLE mytable
(
created_date datetime NOT NULL DEFAULT NOW()
)