SQL Server如何通过更新前触发器调用存储过程将表记录插入历史表

前提说明 #

SQL Server没有原生的BEFORE UPDATE触发器，你可以用INSTEAD OF UPDATE触发器实现更新前触发的逻辑：触发器会优先于原定的更新操作执行，你可以先完成历史记录插入，再手动执行原更新操作即可。

步骤1：创建UserHistory历史表 #

先确保历史表存在，推荐额外加变更时间字段便于追溯：

CREATE TABLE UserHistory (
    HistoryID INT IDENTITY(1,1) PRIMARY KEY,
    ID INT,
    Name NVARCHAR(100),
    Address NVARCHAR(255),
    Contact NVARCHAR(50),
    Email NVARCHAR(100),
    Password NVARCHAR(255),
    UpdateTime DATETIME DEFAULT GETDATE()
);

步骤2：创建插入历史记录的存储过程 #

接收旧记录的字段作为入参，写入历史表：

CREATE PROCEDURE InsertUserHistory
    @ID INT,
    @Name NVARCHAR(100),
    @Address NVARCHAR(255),
    @Contact NVARCHAR(50),
    @Email NVARCHAR(100),
    @Password NVARCHAR(255)
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO UserHistory (ID, Name, Address, Contact, Email, Password)
    VALUES (@ID, @Name, @Address, @Contact, @Email, @Password);
END;

步骤3：创建INSTEAD OF UPDATE触发器 #

从系统临时表deleted中取更新前的旧数据，调用存储过程写入历史，再用inserted表中的新数据完成原表的更新操作：

CREATE TRIGGER trg_UserDetails_BeforeUpdate
ON UserDetails
INSTEAD OF UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- 兼容批量更新场景，遍历所有待更新的旧记录
    DECLARE @ID INT, @Name NVARCHAR(100), @Address NVARCHAR(255), 
            @Contact NVARCHAR(50), @Email NVARCHAR(100), @Password NVARCHAR(255);
    
    DECLARE history_cursor CURSOR FOR
    SELECT ID, Name, Address, Contact, Email, Password FROM deleted;

    OPEN history_cursor;
    FETCH NEXT FROM history_cursor INTO @ID, @Name, @Address, @Contact, @Email, @Password;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 调用存储过程插入旧记录
        EXEC InsertUserHistory @ID, @Name, @Address, @Contact, @Email, @Password;
        FETCH NEXT FROM history_cursor INTO @ID, @Name, @Address, @Contact, @Email, @Password;
    END;

    CLOSE history_cursor;
    DEALLOCATE history_cursor;

    -- 执行实际的更新操作
    UPDATE u
    SET 
        u.Name = i.Name,
        u.Address = i.Address,
        u.Contact = i.Contact,
        u.Email = i.Email,
        u.Password = i.Password
    FROM UserDetails u
    INNER JOIN inserted i ON u.ID = i.ID;
END;

注意：如果你的业务场景会修改UserDetails表的ID字段，需要调整最后更新步骤的关联逻辑，避免更新匹配错误。如果不需要支持批量更新，也可以去掉游标简化代码，不会影响单条更新的使用。

内容的提问来源于stack exchange，提问作者Ashutosh Kumar