只有在修改列时才触发SQL更新

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.

通过查看其他例子，我已经找到了下面的例子，但它似乎没有我想要的效果:我希望它只更新修改后的信息，如果QtyToRepair值已经更新……但它并没有这样做。

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.

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

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

5 个解决方案

#1

You have two way for your question :

你的问题有两种回答:

1- Use Update Command in your Trigger.

在触发器中使用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- Use Join between Inserted table and deleted table

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

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.

当你使用更新命令表时间表和QtyToRepair列设置为新值,如果新值等于旧值在一个或多行,解决方案1更新所有行进度更新表但解决方案2只更新时间表行旧值不等于新值。

#2

fyi The code I ended up with:

供大家参考的代码:

IF UPDATE (QtyToRepair)
    begin
        INSERT INTO tmpQtyToRepairChanges (OrderNo, PartNumber, ModifiedDate, ModifiedUser, ModifiedHost, QtyToRepairOld, QtyToRepairNew)
        SELECT S.OrderNo, S.PartNumber, GETDATE(), SUSER_NAME(), HOST_NAME(), D.QtyToRepair, I.QtyToRepair 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 I.QtyToRepair <> D.QtyToRepair
end

#3

One should check if QtyToRepair is updated at first.

首先应该检查QtyToRepair是否更新。

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

#4

You want to do the following:

你想做以下事情:

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

Please note that this trigger will fire each time you update the column no matter if the value is the same or not.

请注意，无论值是否相同，每次更新列时都会触发此触发器。

#5

I got the simplest thinking concept is whenever a record has updated a record is deleted.

我得到的最简单的想法是每当一个记录更新了一个记录就被删除。

here is my example

ALTER TRIGGER [dbo].[UpdatePhyDate] ON [dbo].[M_ContractDT1] AFTER UPDATE AS BEGIN -- on ContarctDT1 PhyQty is updated -- I want system date in Phytate automatically saved SET NOCOUNT ON;

改变触发[dbo]。[UpdatePhyDate][dbo]。[M_ContractDT1]更新开始后——在ContarctDT1 PhyQty上更新——我想要Phytate中的系统日期自动保存SET NOCOUNT on;

declare @dt1ky as int   

if(update(Phyqty))
begin
    select @dt1ky = dt1ky from deleted

    update M_ContractDT1 set PhyDate=GETDATE() where Dt1Ky=  @dt1ky     

end

END

It works fine

它工作好