How do I add a “last updated” column in a SQL Serv

问题:

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?

回答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

回答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 shuold have that feature.....