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


By:   |   Updated: 2011-03-10   |   Comments (6)   |   Related: More > 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.

looking at timestamp in my sql

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.

the sql scrpits above were used in mysql to create the address table


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);

mimic the timestamp process in sql server

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; 

updated with the timestamp feature

That's all there is to it.

Next Steps


Last Updated: 2011-03-10


get scripts

next tip button



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

View all my tips





Comments For This Article




Monday, February 27, 2012 - 6:11:44 PM - Murali Back To Top (16179)

For the straight logic we need Primary Key.

If you do not have PK then use the same logic what you do for the update statements.

 


Monday, February 27, 2012 - 11:38:52 AM - Kalyan Back To Top (16173)

Murali,

 

What if update happened other than Address ID or What is the way to mimic same scenario on non-primary key tables.

 

Kalyan.


Monday, July 11, 2011 - 9:50:49 AM - Jagadeep Back To Top (14149)

Murali, The Solution is perfect this solved my problem.

but this is the major backdrop of SQL Server in Oracle the updatetimestamp column gets updated automatically when a record is updated.  Is this fixed in Denali any idea..


Friday, March 11, 2011 - 9:42:25 AM - Murali Krishnan Back To Top (13179)

Hi Sineetha,

As per the functionality requirement, when ever a record gets updated, the modified date field also should get the updated date and time.

From your code, it gets only when you insert first time it gets date and time. How about when a user updates any info in the row later. (That is why we need to have the trigger).


 


Friday, March 11, 2011 - 5:56:17 AM - Sineetha Back To Top (13176)

But when we give ' [ModifedDate] [datetime] DEFAULT getdate() NOT NULL' during table creation, It will automatically insert current datetime to the 'ModifiedDate' filed.

So i think trigger is not necessary. Please reply me if it is wrong.

Thursday, March 10, 2011 - 10:40:01 AM - Linda Leslie Back To Top (13172)

We do something very similar but use a default for the field instead of using an insert trigger and the update trigger uses a join instead of a IN clause... like this :

 

 

 

 

 

/* lsl version 1/25/2008 */

 

CREATE  TRIGGER [dbo].[Upd_tablename] ON [dbo].[tablename]
FOR UPDATE AS

 

 

UPDATE  tablename SET LastUpdate = GETDATE()
FROM  tablename s INNER JOIN inserted i ON i.PK = s.PK

 

GO



download


Recommended Reading

Date and Time Conversions Using SQL Server

Format SQL Server Dates with FORMAT Function

Add and Subtract Dates using DATEADD in SQL Server

Creating a date dimension or calendar table in SQL Server

SQL Server Date and Time Functions with Examples





get free sql tips
agree to terms


Learn more about SQL Server tools