Timestamp for creating strings and last modification

Question

I need to track the time at which a row was inserted into the database, and the time of the last change.

I tried to create two separate columns and use CURRENT_TIMESTAMP :

 create table def ( id int, creation timestamp default CURRENT_TIMESTAMP, modification timestamp on update CURRENT_TIMESTAMP ); 

However, this caused an error:

ERROR 1293 (HY000): Incorrect table definition; there can only be one TIMESTAMP column with CURRENT_TIMESTAMP in the DEFAULT or ON UPDATE section

What is the best way to do this?

I am thinking of a stored procedure, but am looking for a standard solution. I am also interested in access privileges - since several programs / things should be able to touch timestamps as much as possible.

---

Although I would prefer answers to MySQL, solutions for other RDBMSs are also welcome!

Answer 1

I. This is a limitation on MySQL. If you are viewing the application, you can add a time () call to the created_at column, and let the updated_at column use CURRENT_TIMESTAMP.

 $sql = "INSERT INTO my_table SET name = 'Mike', created_at = " . time(); 

I would prefer to do this in the created_at column, since it probably won't be affected as often as the updated_at column.

- Change -

Better yet, use MySQL built into the now() function. Thus, you only need to consider the time zone of the mysql server and not the time zones of the application server and mysql server.

 $sql = "INSERT INTO my_table SET name = 'Mike', created_at = NOW()"; 

Answer 2

You can use a trigger. An application can also set a value, but if so, it will be overwritten by the database.

 delimiter // CREATE TRIGGER def_bef_update BEFORE UPDATE ON def FOR EACH ROW BEGIN SET NEW.modification = CURRENT_TIMESTAMP; END// delimiter ; 

You can also use it to verify data and update the modification date only in case of important changes.