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 <> I.QtyToRepair

END

Answer 1

You can try using the following ways:

1: Try Update Command in your Trigger this way:

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: Try using Join between the Inserted table and deleted table like this:

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 the new value, if a new value equal to an 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 a new value.

You can learn in-depth about SQL statements, queries and become proficient in SQL queries by enrolling in our industry-recognized Microsoft SQL Certification.