Triggers can be very useful when you want to log changes to your data, but what if you’re only interested in logging changes to certain columns? This is where the UPDATE function comes in. This function can only be used in DML triggers and it allows us to discover if a particular column was updated in the statement that caused the trigger to fire. This post demonstrates how to use this function to selectively log changes to our data.

First of all, let’s setup a test schema consisting of a Products table and a ProductPriceLog table to record every change to the price of every product.

CREATE TABLE Products
(
  ProductIDINT IDENTITY(1,1),
  NameNVARCHAR(100),
  PriceDECIMAL(19, 4)
);
GO
CREATE TABLE ProductPriceLog
(
  ProductIDINT,
  ChangeDateTimeDATETIME2,
  OldPriceDECIMAL(19, 4),
  NewPriceDECIMAL(19, 4)
);
GO

With use of the UPDATE function, this trigger will only log changes to the Price column in the Products table. These specific changes will be logged to the ProductPriceLog table.

CREATE TRIGGER trg_LogProductPriceChange ON Products
AFTER INSERT, UPDATE
AS
BEGIN
  IF(UPDATE(Price))
  BEGIN
    BEGIN TRY
      INSERTProductPriceLog
      SELECTi.ProductID,
            SYSDATETIME(),
            d.Price,
            i.Price
      FROMinserted i
      LEFT JOINdeleted d 
      ON i.ProductID = d.ProductID;
    END TRY
    BEGIN CATCH
      IF @@TRANCOUNT > 0
        ROLLBACK;
      RAISERROR('Failed to log price change. Transaction rolled back!', 16, 1);
    END CATCH
  END
END;
GO

The code to log the changes is inside the IF statement that uses the UPDATE function to check if the Price column was updated. If it was, then the function returns TRUE and the change is logged. If not, then the trigger doesn’t log the change. Next, we’ll INSERT some products.

INSERT Products (Name, Price)
SELECT*
FROM(VALUES('USB Stick', 14.99),
        ('100x DVD-R', 12.99),
        ('Remote Control', 29.99)
    ) AS Products(Name, Price);

The INSERT fires the trigger which logs to the ProductPriceLog table. INSERTs cause the UPDATE function to return TRUE.

Now, we make some minor changes to the product names. We don’t want to log these changes.

UPDATE Products
SET Name += Name + ' GB';

When any changes are made to the cost of our products we want to log them. So lets add 5% onto the price of our products.

UPDATE Products
SET Price += ((Price/100) * 5);

Let’s take another look at our log table to see what was logged.

The last three rows of our log table show the changes in price to our products.

This use of the UPDATE function for selective logging can be very useful when used on tables with columns such as: LastOrderDate, LastLoginDate, etc as these columns are often updated but those changes are probably not required to be logged.

One interesting point is that even if our trigger was configured to fire on DELETEs, the UPDATE function would not return true and therefore the change would not be logged. This makes sense as a DELETE affects all columns, so checking for a particular column is not required. If we wanted to log DELETEs to our ProductPriceLog table, we would use a trigger that fired on DELETEs.

Multiple columns can be checked in the same way with the COLUMNS_UPDATED function.

UPDATE

Thomas Franz makes a good point in the comments below; The UPDATE function returns TRUE even if the value of the columns it’s checking doesn’t change. If we wanted to log only when the price changes, we would need to use the inserted and deleted tables to compare the value before and after the change.

The post Selective Logging with the UPDATE() Function appeared first on The Database Avenger.