Click here to Skip to main content
Click here to Skip to main content

Create a Modified On Column in SQL Server

, 23 Dec 2007
Rate this:
Please Sign up or sign in to vote.
How to add a modified on column in SQL Server using triggers.

Screenshot -

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

james.wren

United States United States
No Biography provided

Comments and Discussions

 
Questionupdate trigger for specific time PinmemberMember 1014361115-Aug-13 2:13 
GeneralCorrupt download PinmemberDewey26-Dec-07 10:03 
GeneralRe: Corrupt download Pinmemberjames.wren2-Jan-08 14:59 
GeneralRe: Corrupt download PinmemberMember 16057524-Jan-08 5:06 
I receive and error saying it is an SFX file not a ZIP. Would not open.
AnswerRe: Corrupt download PinmemberG James4-Jan-08 13:42 
GeneralWhy not use inserted PinmemberChristianFalk12-Dec-07 22:14 
AnswerRe: Why not use inserted Pinmemberssturgis13-Dec-07 5:09 
GeneralRe: Why not use inserted PinmemberChristianFalk13-Dec-07 7:53 
GeneralRe: Why not use inserted Pinmemberjames.wren16-Dec-07 17:56 
GeneralRe: Why not use inserted PinmemberApocwhen23-Dec-07 22:54 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 24 Dec 2007
Article Copyright 2007 by james.wren
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid