Create a Modified On Column in SQL Server

Introduction

This article explains how to add a Modified On column to a table. SQL Server does not support this natively, and the misleading Timestamp field type does not do what we want. Our solution will be implemented entirely in SQL Server so that we don’t have to change the existing code or worry about writing the current date on every update.

Background

In SQL Server, triggers are used to execute SQL commands on an update, delete, or insert to a specific table. For more information, look at http://msdn.microsoft.com/msdnmag/issues/03/12/DataPoints/.

Using the code

Shown below is a simple table that has a column to store data:

CREATE TABLE [my_table] (
    [id] [int] IDENTITY (1, 1) NOT NULL,
    [my_data] [nvarchar] (50) NULL, 
    [created_on] [datetime] NOT NULL,
    [modified_on] [datetime] NOT NULL,
) ON [PRIMARY] 

ALTER TABLE [my_table] WITH NOCHECK ADD 
    CONSTRAINT [PK_my_table] PRIMARY KEY  CLUSTERED ( [Id] )  ON [PRIMARY] 
GO 

ALTER TABLE [my_table] ADD 
    CONSTRAINT [DF_my_table_created_on] DEFAULT (getdate()) FOR [created_on]
GO 

ALTER TABLE [my_table] ADD 
    CONSTRAINT [DF_my_table_modified_on] DEFAULT (getdate()) FOR [modified_on]
GO

The method getdate() returns the current date-time. created_on and modified_on are automatically set to getdate() when a row is inserted.

Creating the trigger

Our trigger is fairly simple:

CREATE TRIGGER trg_update_my_table on my_table FOR UPDATE AS            
BEGIN
    UPDATE my_table
    SET modified_on=getdate()
    FROM my_table INNER JOIN deleted d
    on my_table.id = d.id
END
GO

SQL Server maintains two meta-tables for each transaction, inserted and deleted. deleted records the old rows, and inserted records the new rows. In our case, it doesn’t matter which we use. Keep in mind that multiple, or zero, rows can be updated on a single command. Using a join will handle those cases. The great part about using a trigger is modified_on will always be set; the trigger will run even if the updates come from, for instance, another trigger.

Allow overwriting of modified_on

The trigger overwrites any changes made to modified_on. I think this is a good thing, but you could stop any overwrites by creating the trigger as:

CREATE TRIGGER trg_update_my_table on my_table FOR UPDATE AS            
BEGIN
    if not update(modified_on)
    begin
        UPDATE my_table
        SET modified_on=getdate()
        FROM my_table INNER JOIN deleted d
        on my_table.id = d.id
    end
END
GO

The update() function returns true if the specified the column was modified.

Disallow overwriting of created_on

You could also stop a command from accidently overwriting created_on by creating the trigger as:

CREATE TRIGGER trg_update_my_table on my_table FOR UPDATE AS            
BEGIN
    UPDATE my_table
    SET modified_on=getdate(), created_on=d.created_on
    FROM my_table INNER JOIN deleted d
    on my_table.id = d.id
END
GO

This sets created_on to be the value of the row before it was updated.