在 SQL Server 中创建修改日期列

引言

本文档解释了如何向表中添加“修改时间”列。SQL Server 本身并不原生支持此功能，而且具有误导性的 Timestamp 字段类型并不能达到我们想要的效果。我们的解决方案将完全在 SQL Server 中实现，这样我们就不需要更改现有代码，也不用担心在每次更新时写入当前日期。

背景

在 SQL Server 中，触发器用于在对特定表进行更新、删除或插入时执行 SQL 命令。有关更多信息，请参阅 http://msdn.microsoft.com/msdnmag/issues/03/12/DataPoints/。

使用代码

下面是一个简单的表，它有一个用于存储数据的列

CREATE TABLE [my_table] (
    [id] [int] IDENTITY (1, 1) NOT NULL,
    [my_data] [nvarchar] (50) NULL, 
    [created_on] [datetime] NOT NULL,
    [modified_on] [datetime] NOT NULL,
) ON [PRIMARY] 

ALTER TABLE [my_table] WITH NOCHECK ADD 
    CONSTRAINT [PK_my_table] PRIMARY KEY  CLUSTERED ( [Id] )  ON [PRIMARY] 
GO 

ALTER TABLE [my_table] ADD 
    CONSTRAINT [DF_my_table_created_on] DEFAULT (getdate()) FOR [created_on]
GO 

ALTER TABLE [my_table] ADD 
    CONSTRAINT [DF_my_table_modified_on] DEFAULT (getdate()) FOR [modified_on]
GO

getdate() 方法返回当前日期时间。created_on 和 modified_on 在插入行时自动设置为 getdate()。

创建触发器

我们的触发器非常简单

CREATE TRIGGER trg_update_my_table on my_table FOR UPDATE AS            
BEGIN
    UPDATE my_table
    SET modified_on=getdate()
    FROM my_table INNER JOIN deleted d
    on my_table.id = d.id
END
GO

SQL Server 为每个事务维护两个元表，inserted 和 deleted。deleted 记录旧行，而 inserted 记录新行。在我们的例子中，使用哪个表并不重要。请记住，单个命令可以更新多行或零行。使用连接将处理这些情况。使用触发器的优点是 modified_on 将始终被设置；即使更新来自另一个触发器，触发器也会运行。

允许覆盖 modified_on

触发器会覆盖对 modified_on 所做的任何更改。我认为这是好事，但你可以通过将触发器创建为以下方式来阻止任何覆盖：

CREATE TRIGGER trg_update_my_table on my_table FOR UPDATE AS            
BEGIN
    if not update(modified_on)
    begin
        UPDATE my_table
        SET modified_on=getdate()
        FROM my_table INNER JOIN deleted d
        on my_table.id = d.id
    end
END
GO

update() 函数如果指定列被修改则返回 true。

禁止覆盖 created_on

你也可以通过将触发器创建为以下方式来阻止命令意外覆盖 created_on：

CREATE TRIGGER trg_update_my_table on my_table FOR UPDATE AS            
BEGIN
    UPDATE my_table
    SET modified_on=getdate(), created_on=d.created_on
    FROM my_table INNER JOIN deleted d
    on my_table.id = d.id
END
GO

这将 created_on 设置为更新行之前的行值。