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

User · Answer

Since MySQL 5 6 X you can do this   ALTER TABLE  schema   users   CHANGE COLUMN  created   created  DATETIME NOT NULL DEFAULT CURRENT TIMESTAMP ON UPDATE CURRENT TIMESTAMP     That way your column will be updated with the current timestamp when a new row is inserted  or updated   If you re using MySQL Workbench  you can just put CURRENT TIMESTAMP ON UPDATE CURRENT TIMESTAMP in the DEFAULT value field  like so     http   dev mysql com doc relnotes mysql 5 6 en news-5-6-5 html

User · Answer

DELIMITER    CREATE TRIGGER  my table bi  BEFORE INSERT ON  my table  FOR EACH ROW BEGIN     SET NEW created date   NOW    END   DELIMITER

User · Answer

Using Now   is not a good idea  It only save the current time and date  It will not update the the current date and time  when you update your data  If you want to add the time once  The default value  Now   is best option  If you want to use timestamp  and want to update the this value  each time that row is updated  Then  trigger is best option to use     http   www mysqltutorial org sql-triggers aspx http   www tutorialspoint com plsql plsql triggers htm   These two toturial will help to implement the trigger

User · Answer

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   See doc http   dev mysql com doc refman 5 7 en timestamp-initialization html

[mysql] MySQL set current date in a DATETIME field on insert

Examples related to mysql

Examples related to triggers