there are 2 columns in my mysql table: created_on and modified_on.I have set the default value for created_on column to CURRENT_TIMESTAMP. thus on every insert operation in the table the created_on column correctly sets the value to current timestamp.however i want that when i update the same row the 'modified_on' column updates the value to the timestamp when it is updated.Let me know if i use any function/trigger to do so or is there any in-build mysql feature for the same
Asked
Active
Viewed 1,181 times
4
-
1use trigger to update automatically – Bhavik Shah Oct 23 '12 at 06:23
-
Are you using any ORM framework? – user1516873 Oct 23 '12 at 06:48
-
1i am using hibernate 3.0 – user1767274 Oct 23 '12 at 09:03
-
@user1767274 maybe you find this question useful http://stackoverflow.com/questions/221611/creation-timestamp-and-last-update-timestamp-with-hibernate-and-mysql – user1516873 Oct 23 '12 at 10:23
-
and i suggest do not use triggers, because it always adds some 'magic'. – user1516873 Oct 23 '12 at 10:26
2 Answers
4
Try alter your table with ON UPDATE keyword like:
ALTER TABLE `tableName`
CHANGE `modified_on` `modified_on` TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP;
Or you can add default value when inserted like:
ALTER TABLE `tableName`
CHANGE `modified_on` `modified_on` TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP
NOT NULL DEFAULT CURRENT_TIMESTAMP ;
You also can define ON UPDATE to a timestamp data type when you create a table.
Habibillah
- 27,347
- 5
- 36
- 56
