Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


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


next webcast button


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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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

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

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

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

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

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


Learn more about SQL Server tools