Professional SQL Server™ 2005 Programming

13.6. IF UPDATE() and COLUMNS_UPDATED

In an UPDATE trigger, we can often limit the amount of code that actually executes within the trigger by checking to see whether the column(s) we are interested in are the ones that have been changed. To do this, we make use of the UPDATE() or COLUMNS_UPDATED() functions. Let's look at each.

13.6.1. The UPDATE() Function

The UPDATE() function has relevance only within the scope of a trigger. Its sole purpose in life is to provide a Boolean response (true/false) to whether a particular column has been updated or not. You can use this function to decide whether a particular block of code needs to run or not — for example, if that code is only relevant when a particular column is updated.

Let's run a quick example of this by modifying one of our earlier triggers:

ALTER TRIGGER Production.ProductAudit
   ON Production.ProductInventory
   FOR INSERT, UPDATE, DELETE
AS
IF UPDATE(Quantity)
BEGIN
    INSERT INTO Production.InventoryAudit
    (ProductID, NetAdjustment)
           SELECT COALESCE(i.ProductID, d.ProductID),
                ISNULL(i.Quantity, 0) - ISNULL(d.Quantity, 0) AS NetAdjustment
           FROM Inserted i
           FULL JOIN Deleted d
              ON i.ProductID = d.ProductID
             AND i.LocationID = d.LocationID
           WHERE ISNULL(i.Quantity, 0) - ISNULL(d.Quantity, 0) != 0
END

With this change, we will now limit the rest of the code to run only when the Quantity column (the one we care about) has been changed. The user can change the value of any other column, and we don't care. This means that we'll be executing ...