How to force SQL to update my modified timestamp record correctly?

Question

I am trying to create a sql table that records when the account was created and when it was the last modification. I would like sql to handle this, so I don't need to do this in my php files.

I have two columns in my user table (both are of type timestamp ):

created

modified

I want the “created” time to never change and always contain the date when it was created, and the “changed” one, which needs to be changed every time the user row changes. I have a table configured so that the "created" work is as I expect, but when I try to update the changed:

 ALTER TABLE `users` CHANGE `modified` `modified` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP 

I get the following error:

1293 - incorrect definition of the table; there can only be one TIMESTAMP column with CURRENT_TIMESTAMP in the DEFAULT or ON UPDATE section

Can someone help me on what I need to do in order to accomplish this correctly?

Answer 1

This is not possible in mysql. You can set them to the actual time only on INSERTs , only on UPDATEs or both. However, you could not have more than one of these auto-TIMESTAMP columns in the same table. what can now use TRIGGERs when using Mysql 5.x

See the article This will help you:

Two auto-TIMESTAMP columns in the same table as MySQL 5.0