仅在修改列时触发 SQL 更新

2022-01-01 00:00:00 sql sql-server triggers

通过查看其他示例，我想出了以下内容，但它似乎并没有像我希望的那样工作:如果 QtyToRepair 值具有，我希望它只更新修改后的信息已更新......但它并没有这样做.

By looking at other examples I've come up with the following but it doesn't seem to work as I would like: I want it to only update the modified information if the QtyToRepair value has been updated... but it doesn't do that.

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

If I comment out the where then the modified information is updated in every case. As I said other examples led me to be optimistic. Any clues appreciated. Thanks.

沃尔特

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

1- Use Update Command in your Trigger.

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- 在插入表和删除表之间使用连接

2- Use Join between Inserted table and deleted table

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 Column 设置为新值时，如果在一行或多行中新值等于旧值，则解决方案1 ​​更新所有更新了调度表中的行，但解决方案 2 只更新了旧值不等于新值的调度行.

When you use update command for table SCHEDULE and Set QtyToRepair Column to new value, if new value equal to old value in one or multi row, solution 1 update all updated row in Schedule table but solution 2 update only schedule rows that old value not equal to new value.

相关文章