SQL Server：创建触发器强制维护创建与更新列值

Hey there, I've implemented exactly this kind of audit column maintenance countless times in SQL Server. Here's a robust trigger that will enforce the behavior you need—automatically setting the create/update timestamps and user values without relying on application code:

CREATE TRIGGER trg_MaintainAuditColumns
ON YourTableName
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- 处理插入操作：强制设置所有审计字段
    IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted)
    BEGIN
        UPDATE t
        SET 
            create_time = CURRENT_TIMESTAMP,
            create_user = CURRENT_USER,
            last_update_time = CURRENT_TIMESTAMP,
            last_update_user = CURRENT_USER
        FROM YourTableName t
        INNER JOIN inserted i ON t.PrimaryKeyColumn = i.PrimaryKeyColumn; -- 替换为你的实际主键列
    END

    -- 处理更新操作：保留创建字段，仅更新最后修改字段
    IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
    BEGIN
        UPDATE t
        SET 
            last_update_time = CURRENT_TIMESTAMP,
            last_update_user = CURRENT_USER
            -- 重要：此处不要修改create_time或create_user，以保留原始值
        FROM YourTableName t
        INNER JOIN inserted i ON t.PrimaryKeyColumn = i.PrimaryKeyColumn; -- 替换为你的实际主键列
    END
END
GO

关键细节与注意事项： #

• 触发器时机：我们使用AFTER INSERT, UPDATE，确保在初始插入/更新完成后修正字段值，这样可以覆盖用户可能尝试输入的任何手动值。
• 区分插入与更新：通过inserted和deleted系统表判断操作类型：只有inserted有数据是插入操作，两者都有数据则是更新操作。
• 主键关联：务必将PrimaryKeyColumn替换为表的实际主键（或唯一标识列），确保精准定位要更新的行。
• 强制一致性：插入时触发器会覆盖用户提供的这些审计字段值，不用担心应用程序错误导致的数据不一致。
• 保护创建值：更新时我们特意不修改create_time和create_user，保证它们永远保留第一次插入时的原始值。

额外强化（可选）： #

如果你想完全阻止用户手动修改这些字段，可以添加列级权限来禁止更新：

DENY UPDATE ON YourTableName(create_time, create_user, last_update_time, last_update_user) TO Public;

这能确保只有触发器可以设置这些值，为数据完整性增加额外保障。

内容的提问来源于stack exchange，提问作者Sean C