triggers - How do I add a "last updated" column in a SQL Server 2008 R2 table?

Question

I have a table in my SQL Server 2008 R2 database, and would like to add a column called LastUpdated, that will automatically be changed every time the row is updated. That way, I can see when each individual row was last updated.

It seems that SQL Server 2008 R2 doesn't have a data type to handle this like earlier versions did, so I'm not sure of the best way to do it. I wondered about using a trigger, but what would happen when the trigger updated the row? Will that fire the trigger again, etc?

Answer 1

To know which row was last updated, you need to create a new column of type DATETIME/DATETIME2 and update it with a trigger. There is no data type that automatically updates itself with date/time information every time the row is updated.

To avoid recursion you can use the UPDATE() clause inside the trigger, e.g.

ALTER TRIGGER dbo.SetLastUpdatedBusiness 
ON dbo.Businesses 
AFTER UPDATE -- not insert!
AS
BEGIN
    IF NOT UPDATE(LastUpdated)
    BEGIN
        UPDATE t
            SET t.LastUpdated = CURRENT_TIMESTAMP -- not dbo.LastUpdated!
            FROM dbo.Businesses AS t -- not b!
            INNER JOIN inserted AS i 
            ON t.ID = i.ID;
    END
END
GO