Making a Trigger Fire On Column Change

I was working with some SQL Server triggers today at work, the triggers were used to track changes to a Price column on a table. Whenever the Price column changed, we wanted to track that in a separate table so we could have a price history. So I setup the trigger on the Price column and set it to fire on INSERTs and UPDATEs. Here was the trigger at this point:

CREATE TRIGGER trg_SavePriceHistory ON myTable
FOR INSERT, UPDATE
AS
IF UPDATE(Price)
BEGIN
DECLARE @newPrice decimal(18,2)
DECLARE @itemId int
SET @newPrice = (SELECT Price FROM Inserted)
SET @itemId = (SELECT ItemID FROM Inserted)
INSERT INTO PriceHistory (NewPrice, ItemID) VALUES (@newPrice, @itemId)
END

Its pretty straightforward, check to see if the Price column was updated and if it was then make a new entry in the PriceHistory table with the new price and the item�s id. After a little while, I realized that every update statement that included the Price column was setting off the trigger. It made sense, I guess I assumed that it would only fire when the value of the Price column actually changed, not if it simply got written with the same value. For instance, if I ran the following INSERT statement:

INSERT INTO myTable (Price) VALUES (10);

Then the trigger would fire and the price would get logged. Now suppose I update the record that I just inserted with the same value for price (assume the id = 1):

UPDATE myTable SET Price = 10 WHERE ItemID = 1;

Now the trigger will fire again, which is what I don�t want. I only wanted the trigger to fire if the value had changed. I was kind of scratching my head, being new to triggers and all, about how I could get that to work. I started writing an email to an internal mailing list, when it hit me in one of those �Aha!� moments. Inside each trigger are 2 special tables called �Inserted� and �Deleted�. The Deleted table holds the values of the record before its state was changed by my UPDATE statement and the Inserted table holds the values of record after my UPDATE statement. All I had to do was compare the Price columns from each table and see if they were different; if they were the same, then I could just exit my trigger. So heres what the trigger looked like after I modified it (modifications in red):

CREATE TRIGGER trg_SavePriceHistory ON myTable
FOR INSERT, UPDATE
AS
IF UPDATE(Price)
BEGIN
DECLARE @newPrice decimal(18,2)
DECLARE @oldPrice decimal(18,2)
DEClARE @itemId int
SET @newPrice = (SELECT Price FROM Inserted)
SET @oldPrice = (SELECT Price FROM Deleted)
IF @newPrice != @oldPrice
BEGIN
SET @itemId = (SELECT ItemID FROM Inserted)
INSERT INTO PriceHistory (NewPrice, ItemID) VALUES (@newPrice, @itemId)
END
END

This is all probably very obvious to someone familiar with triggers, just thought I would help someone else out if they were looking for this.
-----------------------------------------------------------------------
COMMENT:
Hi Ben,

I may be wrong but I don’t think your code will work correctly when updating sets: the inserted and deleted tables are what thet are: tables and not rows.
You are logging the changes to one row, not a set.

The following code works for an update trigger.
You can add similar code for delete and insert statements (or you can change this one to cover the three possibilities)

INSERT INTO PriceHistory(ItemId, OldPrice, NewPrice)
SELECT I.ItemId, D.Price, I.Price
FROM INSERTED I INNER JOIN DELETED D ON I.ItemId = D.ItemId
WHERE I.Price != D.Price

Kind regards,

Karel Vandenhove

http://benreichelt.net/blog/2005/12/13/making-a-trigger-fire-on-column-change