How do I add a “last updated” column in a SQL Server 2008 R2 table?

Question

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

In modern versions you can trick SQL Server into doing this using temporal tables:

Maintaining LastModified Without Triggers

But this is full of caveats and limitations and was really only making light of multiple other similar posts:

A System-Maintained LastModifiedDate Column
Tracking Row Changes With Temporal
Columns
How to add “created” and “updated” timestamps without triggers
Need a datetime column that automatically updates

Leave a Comment Cancel reply