SQL Created and Last Updated Times

By defining appropriate timestamp or datetime fields in our database tables we can have those tables record when each row was created and when each row was last updated within the database itself without needing to have anything in the actual code that accesses the database. All we need to do is to define the fields in the create statement for the table and then not reference the fields again in any insert or update statement.

Here's how to define the two fields in the create table statement.

CREATE TABLE t1 (
  updated DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  created DATETIME DEFAULT CURRENT_TIMESTAMP
);

Having created the fields you just need to make sure that none of the SQL statements that these two fields are never referenced from any further SQL calls. Any reference to either of these fields in an insert or update call will break the desired processing and potentially invalidate all of the content of those fields in the table.

The only situation where those fields should ever be referenced after the create would be in a select call that is retrieving the values.

 

This article written by Stephen Chapman, Felgall Pty Ltd.

go to top

FaceBook Follow
Twitter Follow
Donate