SQL Server logging with triggers

Selective Logging with the UPDATE() Function

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
(
  ProductID	INT IDENTITY(1,1),
  Name		NVARCHAR(100),
  Price		DECIMAL(19, 4)
);
GO
CREATE TABLE ProductPriceLog
(
  ProductID		INT,
  ChangeDateTime	DATETIME2,
  OldPrice		DECIMAL(19, 4),
  NewPrice		DECIMAL(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
      INSERT		ProductPriceLog
      SELECT		i.ProductID,
            SYSDATETIME(),
            d.Price,
            i.Price
      FROM		inserted i
      LEFT JOIN	deleted 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.

Trigger logs insert

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.

Trigger logs update to specific column

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.

About the author

James Anderson

I'm a freelance SQL Server DBA in Southampton, UK. Performance tuning code and improving development processes are what I love to do for work. Outside of work, I live to travel to as many places as possible with my wife Sarah.

3 thoughts on “Selective Logging with the UPDATE() Function”

  1. You should be aware, tha the UPDATE() function returns TRUE, regardless, if the columns value was really changed (it checks only, if the column was part of the SET list in the UPDATE statement, particularly because you could update multiple rows at once where a few change while other rows remain unchanged).

    So if you execute an
    [sql]
    UPDATE Products SET price = price
    [/sql]
    or
    [sql]
    UPDATE Products SET price = 29.99 WHERE price between 29.98 and 30
    [/sql]

    it would create an log entry. If you want to prevent this, you have to compare both columns explicit in the INSERT-Statement.

Leave a Reply

Your email address will not be published. Required fields are marked *

Sign Up

James Anderson SQL Server DBA

Hi, I'm James Anderson. When I'm not blogging about SQL Server, I do this.

The SQL People

DBA Stackexchange Profile