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

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?

Solution 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

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

Solution 2:

It's not that easy, unfortunately.

You can add a new DATETIME (or DATETIME2) field to your table, and you can give it a default constraint of GETDATE() - that will set the value when a new row is inserted.

Unfortunately, other than creating an AFTER UPDATE trigger, there is no "out of the box" way to keep it updated all the time. The trigger per se isn't hard to write, but you'll have to write it for each and every single table that should have that feature.....