仅在修改列时触发 SQL 更新

发布于08月27日

通过查看其他示例，我得出了以下结论，但它似乎并不像我希望的那样工作:我希望它只在QtyToRepair值已更新的情况下更新修改后的信息...但事实并非如此.

如果我把where注释掉，那么修改后的信息在任何情况下都会更新.正如我所说，其他例子让我感到乐观.谢谢你提供任何线索.谢谢

沃尔特

ALTER TRIGGER [dbo].[tr_SCHEDULE_Modified]
   ON [dbo].[SCHEDULE]
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    UPDATE SCHEDULE SET modified = GETDATE()
        , ModifiedUser = SUSER_NAME()
        , ModifiedHost = HOST_NAME()
    FROM SCHEDULE S
    INNER JOIN Inserted I on S.OrderNo = I.OrderNo and S.PartNumber = I.PartNumber
    WHERE S.QtyToRepair <> I.QtyToRepair
END

推荐答案

你有两种方式来回答你的问题:

1-在触发器中使用Update命令.

ALTER TRIGGER [dbo].[tr_SCHEDULE_Modified]
   ON [dbo].[SCHEDULE]
   AFTER UPDATE
AS BEGIN
    SET NOCOUNT ON;
    IF UPDATE (QtyToRepair) 
    BEGIN
        UPDATE SCHEDULE 
        SET modified = GETDATE()
           , ModifiedUser = SUSER_NAME()
           , ModifiedHost = HOST_NAME()
        FROM SCHEDULE S INNER JOIN Inserted I 
        ON S.OrderNo = I.OrderNo and S.PartNumber = I.PartNumber
        WHERE S.QtyToRepair <> I.QtyToRepair
    END 
END

2-在插入的表和删除的表之间使用联接

ALTER TRIGGER [dbo].[tr_SCHEDULE_Modified]
   ON [dbo].[SCHEDULE]
   AFTER UPDATE
AS BEGIN
    SET NOCOUNT ON;    

    UPDATE SCHEDULE 
    SET modified = GETDATE()
       , ModifiedUser = SUSER_NAME()
       , ModifiedHost = HOST_NAME()
    FROM SCHEDULE S 
    INNER JOIN Inserted I ON S.OrderNo = I.OrderNo and S.PartNumber = I.PartNumber
    INNER JOIN Deleted D ON S.OrderNo = D.OrderNo and S.PartNumber = D.PartNumber                  
    WHERE S.QtyToRepair <> I.QtyToRepair
    AND D.QtyToRepair <> I.QtyToRepair
END

当您对表SCHEDULE使用update命令并将QtyToRepair列设置为新值时，如果一行或多行中的新值等于旧值，解决方案1将更新计划表中所有更新的行，但解决方案2仅更新旧值不等于新值的计划行.