database:mysql:ts_on_ins_and_upd [Eureka Moment]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.

====== Refresh timestamp field on insert and update ======
When working with databases, it's a good practice to do as much data manipulation inside the database server, instead of usercode. So, using triggers, stored procedures and functions is an excellent way to modify linked data across tables (or calculate certain values).

One such example is updating the TIMESTAMP field.

When creating tables using various GUI tools, you will not be presented with all options that are available. One such example is SQLyog. When creating a table, you can only set the default value, meaning thet you can set the default value on insert - not on update. But perhaps you want to know when was the last time the record was updated and you don't want to add another update field in your query.

So you need to alter the table and add ON UPDATE parameter to the timestamp field. Let's assume that you have a table called 'articles' and a TIMESTAMP field called 'last_change'

<code sql>
ALTER TABLE `articles` 
CHANGE `last_change` `last_change` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
</code>