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.
[Edit: Please read the first comment for a description on updating more than one row]
