Mimic timestamp behavior of other database platforms to store last modified date

By: Murali Krishnan   |   Updated: 2011-03-10   |   Comments (6)   |   Related: > Dates

Problem

Recently I did a migration for SQL Server 2008 from another platform. When I converted the database schema to SQL Server, I faced a problem converting TIMESTAMP to SQL Server. In MySQL/DB2, Timestamp is a datetime field. It is used to track changes to a record and updated every time the record is changed. In the migration process, we needed to store the datetime in the 'ModifiedDate' column and it should get updated every time the row is changed. But TIMESTAMP in SQL Server is a unique binary number within a database and used as a mechanism for version-stamping table rows, not for showing when a record was last updated. In this tip, we show how this behavior can be duplicated in SQL Server.

Solution

The solution to store the date and time when a record was last updated can be done in two ways:

1. Implementing a few necessary changes at the application level (could be with DML commands)
2. Defining Timestamp column as a datetime column and creating INSERT and UPDATE triggers

From a performance point of view, application level changes may be preferred, but it requires code changes and subsequent effort to distribute the application changes. Where as the second option is simple and quick to implement, which I will explain in this tip.

In this tip, we will do a migration of a timestamp table with the following steps; first create the table by defining the timestamp column as datetime column and then create an INSERT and UPDATE trigger to update the 'ModifedDate' with GETDATE().

First let's examine TIMESTAMP in MySQL to understand the Timestamp functionality.

The following SQL scripts are used in MySQL to create the 'address' table and to insert a few rows.

CREATE TABLE ‘address'
(
  ‘AddressID' bigint(20) unsigned NOT NULL,
  ‘AddressLine1' varchar(60) DEFAULT NULL,
  ‘AddressLine2' varchar(60) NOT NULL,
  ‘City' varchar(30) DEFAULT NULL,
  ‘StateProvinceID' int(10) unsigned DEFAULT NULL,
  ‘ModifedDate' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (‘AddressID');
)
INSERT INTO address (AddressID, AddressLine1, AddressLine2, City, StateProvinceId)
VALUES (1,'196 Ellis St','Block1','Seattle',79);
INSERT INTO address (AddressID, AddressLine1, AddressLine2, City,StateProvinceId)
VALUES (2,'200 Rakha St','Block2','Seattle',79);
INSERT INTO address (AddressID, AddressLine1, AddressLine2, City, StateProvinceId)
VALUES (3,'370 Vil Avenue','Block3','Seattle',79);

The inserted rows' ModifiedDate columns are updated with the current date and time as shown below.

Let's update a row to understand the TIMESTAMP functionality.

UPDATE address 
SET    AddressLine2 = 'Block A' 
WHERE  AddressID = 1; 

When the row is changed the Modified column automatically is updated with the current date and time since it is a timestamp datatype.

Let's see what we can do to mimic this process in SQL Server.

The following script is used to create a table.

CREATE TABLE [address]
(
 [AddressID] [bigint]  NOT NULL,
 [AddressLine1] [varchar](60) NULL,
 [AddressLine2] [varchar](60) NOT NULL,
 [City] [varchar](30) NULL,
 [StateProvinceID] [bigint] NULL,
 [ModifedDate] [datetime] DEFAULT getdate() NOT NULL,
 PRIMARY KEY ([AddressID] )
)

Then we need to create a trigger on this table for the timestamp. Whenever a row is inserted or updated this trigger will fire and update the ModifiedDate column with the current date and time.

CREATE TRIGGER UpdateModifiedDate
ON [address]  AFTER INSERT, UPDATE
AS 
SET NOCOUNT ON;
UPDATE [address] 
SET [ModifedDate] = GETDATE() 
WHERE [AddressID] in (SELECT [AddressID] FROM INSERTED);
GO

Here we insert some rows and the inserted sample rows are shown in the below image.

INSERT INTO address (AddressID, AddressLine1, AddressLine2, City, StateProvinceId)
VALUES (1,'196 Ellis St','Block1','Seattle',79);
INSERT INTO address (AddressID, AddressLine1, AddressLine2, City,StateProvinceId)
VALUES (2,'200 Rakha St','Block2','Seattle',79);
INSERT INTO address (AddressID, AddressLine1, AddressLine2, City, StateProvinceId)
VALUES (3,'370 Vil Avenue','Block3','Seattle',79);

Let's update a row. When we update it, as per the TIMESTAMP feature, we get the updated date and time in the modifiedDate column.

UPDATE address 
SET    AddressLine2 = 'Block A' 
WHERE  AddressID = 1; 

That's all there is to it.

Next Steps

• Microsoft has announced that the Timestamp syntax has been deprecated. A future release of SQL Server will not have the Timestamp datatype. The Timestamp datatype is being replaced with the rowversion data type.
• Learn more about creating triggers
• Related Tips
  • Optimistic Locking in SQL Server using the ROWVERSION Data Type
  • Tips about triggers

About the author

Murali Krishnan is a Lead Consultant with vast experience in Database/BI Design, Development and Administration.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips