SQL update trigger only when column is modified

by

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

More Related Contents:

  1. Create Trigger to log SQL that affected table?
  2. Solution for: Store update, insert, or delete statement affected an unexpected number of rows (0) [closed]
  3. Need to list all triggers in SQL Server database with table name and table’s schema
  4. How can I do a BEFORE UPDATED trigger with sql server?
  5. SQL Server Update Trigger, Get Only modified fields
  6. How do I prevent a database trigger from recursing?
  7. Sql Server trigger insert values from new row into another table
  8. Simulating group_concat MySQL function in Microsoft SQL Server 2005?
  9. How can I do an UPDATE statement with JOIN in SQL Server?
  10. Why does NULL = NULL evaluate to false in SQL server
  11. The multi-part identifier could not be bound
  12. How to insert a line break in a SQL Server VARCHAR/NVARCHAR string
  13. SQL – How to store and navigate hierarchies?
  14. T-SQL – Insert Data into Parent and Child Tables
  15. SQL Query to get aggregated result in comma separators along with group by column in SQL Server
  16. Fastest way to update 120 Million records
  17. how can I Update top 100 records in sql server
  18. How to find current transaction level?
  19. Count Number of Consecutive Occurrence of values in Table
  20. Is there a SELECT … INTO OUTFILE equivalent in SQL Server Management Studio?
  21. How can I compare time in SQL Server?
  22. SQL – Difference between COALESCE and ISNULL? [duplicate]
  23. Drop all tables whose names begin with a certain string
  24. SQL: capitalize first letter only [duplicate]
  25. UPDATE and REPLACE part of a string
  26. Getting the Id of a row I updated in Sql Server
  27. Zero SQL deadlock by design – any coding patterns?
  28. Backup a single table with its data from a database in sql server 2008
  29. Try_Convert for SQL Server 2008 R2
  30. SQL Server variable scope in a stored procedure

Leave a Comment