SQLSERVER 触发器记录表某个字段更新记录

墨着染霜华

于 2024-08-01 17:10:09 发布

阅读量188

点赞数 1

文章标签： sqlserver 数据库

本文链接：https://blog.csdn.net/chengmin123456789/article/details/140852290

版权

想要记录该字段的原值和现有值，触发器写法：

CREATE TRIGGER tr_UpdateEmployeeDepartment
ON Employees
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON; -- 避免多余的计数消息

    IF UPDATE(Department) -- 检查是否更新了 Department 字段
    BEGIN
        INSERT INTO UpdateLog (EmployeeID, FieldName, OldValue, NewValue)
        SELECT
            inserted.EmployeeID,
            'Department',
            deleted.Department,
            inserted.Department
        FROM
            inserted
        INNER JOIN
            deleted ON inserted.EmployeeID = deleted.EmployeeID;
    END
END;