SQL update trigger only when column is modified

Question

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    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   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  lt  gt  I QtyToRepair END

User · Answer

One should check if QtyToRepair is updated at first    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  lt  gt  I QtyToRepair     END END

User · Answer

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  lt  gt  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

User · Answer

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  lt  gt  D QtyToRepair end

User · Answer

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

User · Answer

You have two way for your question    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  lt  gt  I QtyToRepair     END  END   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  lt  gt  I QtyToRepair     AND D QtyToRepair  lt  gt  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

[sql] SQL update trigger only when column is modified

Examples related to sql

Examples related to sql-server

Examples related to triggers