1

Table in MySQL have 1 problem column: creation_date.

During inserting a new row through PHP, I thought that there would be correct to insert the date directly in the query, MySQL has to do it himself.

Do I need to do the trigger, or it would be better to use PHP for this, as intended? How would you have done to?

PS: MySQL: How to create trigger for setting creation date for new rows

Cœur
  • 37,241
  • 25
  • 195
  • 267
ajile
  • 666
  • 1
  • 10
  • 18
  • Conclusion: 1. TIMESTAMP column with default value CURRENT_TIMESTAMP can be only one. 2. If you're want to auto insert date on create row action you may use: ALTER TABLE `jv-temp`.store_order MODIFY creation_date TIMESTAMP NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP; – ajile Aug 03 '10 at 11:00

2 Answers2

3

Use TIMESTAMP DEFAULT CURRENT_TIMESTAMP field

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

Mchl
  • 61,444
  • 9
  • 118
  • 120
  • Take care with this, as the timestamp column will get set on an UPDATE. Your updates will have to specify TIMESTAMP_COLUMN = TIMESTAMP_COLUMN as one of the things SET, otherwise the creation date will be lost. – Brian Hooper Aug 03 '10 at 10:37
  • Only if it will have ON UPDATE CURRENT_TIMESTAMP in CREATE TABLE definition. – Mchl Aug 03 '10 at 10:45
  • It does't work. On CREATE do work, but on UPDATE it set to NULL. – ajile Aug 03 '10 at 11:10
1

If you create a trigger for your table (or adopt the suggestion above) you won't have to remember to do it in your PHP. This advantage will show when someone else creates another PHP to insert into the same table.

Brian Hooper
  • 21,544
  • 24
  • 88
  • 139