SQL更新仅在列被修改时触发

通过查看其他示例，我想出了以下内容，但它似乎没有按照我想要的方式工作：我希望它仅在以下情况下更新修改后的信息：QtyToRepair值已更新...但它并没有这样做。

如果我注释掉其中，则修改后的信息在每种情况下都会更新。正如我所说，其他例子让我变得乐观。任何线索表示赞赏。谢谢。

Walter

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- 在触发器中使用更新命令。

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并设置QtyToRepair列到新值，如果一行或多行中新值等于旧值，解决方案 1 更新 Schedule 表中所有已更新的行，但解决方案 2 仅更新旧值不等于新值的计划行。