如何添加“最近更新”的SQL Server 2008 R2表中的列？ [英] How do I add a "last updated" column in a SQL Server 2008 R2 table?

问题描述

我在SQL Server 2008 R2数据库中有一个表，并且想添加一个名为LastUpdated的列，该列在每次更新行时都会自动更改。这样，我可以看到每行的最新更新时间。

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.

似乎SQL Server 2008 R2没有像早期版本那样处理数据的方法，所以我不确定做到这一点的最佳方法。我想知道如何使用触发器，但是当触发器更新行时会发生什么？

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?

推荐答案

要知道最后更新哪一行，您需要创建一个新列，是否会再次触发该触发器？键入 DATETIME / DATETIME2 并使用触发器进行更新。没有数据类型会在每次更新行时自动使用日期/时间信息自动更新。

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.

为避免递归，可以使用 UPDATE （）子句，例如

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

这篇关于如何添加“最近更新”的SQL Server 2008 R2表中的列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！