SQL update trigger only when column is modified

You have two way for your question :

1- Use Update Command in your Trigger.

ALTER TRIGGER [dbo].[tr_SCHEDULE_Modified]   ON [dbo].[SCHEDULE]   AFTER UPDATEAS 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

ALTER TRIGGER [dbo].[tr_SCHEDULE_Modified]   ON [dbo].[SCHEDULE]   AFTER UPDATEAS 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.QtyToRepairEND

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.

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.QtyToRepairend

One should check if QtyToRepair is updated at first.

ALTER TRIGGER [dbo].[tr_SCHEDULE_Modified]   ON [dbo].[SCHEDULE]   AFTER UPDATEAS BEGINSET 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    ENDEND