How to get SQL to update my record Modified Timestamp correctly?

I am trying to set up an sql table which records when an account was created and when it was last modified. I would like sql to handle this so I don't have to do it in my php files.

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

created

modified

I want the "created" time to never change and always contain the date it was created, and the "modified" to be changed each time the users row is modified. I have the table set up so "created" works as I expect, but when I try to update modified:

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 table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

Can someone assist me on what I need to do to accomplish this correctly?

Solution

It's stil not possible in mysql. You can have them set to the actual time only on INSERTs, only on UPDATEs or on both. However you couldn't have more than one of these auto-TIMESTAMP columns in one table. that's now possible using TRIGGERs if using Mysql 5.x

Refer this article It will help you lot :

Two auto-TIMESTAMP columns in one table with MySQL 5.0