Everybody knows what is a trigger, so I won't insist on this subject. My goal is to use a little example and show a mistake that I have seen too many times. Article is a little bit long, but easy to follow. So, let's suppose we are selling books, so the minimal definition of a Product table look like this:
CREATE TABLE Products
(ProductID INT IDENTITY(1,1) PRIMARY KEY,
CategoryId INT,
ProductName VARCHAR(50),
Price NUMERIC(10,4)
)
Business requirement is to log all price changes into ProductPriceChanges table. Definition of the table follows.
CREATE TABLE ProductPriceChanges
(ProductPriceChange INT IDENTITY(1,1) PRIMARY KEY,
ProductID INT,
OldPrice NUMERIC(10,4),
NewPrice NUMERIC(10,4),
DateChanged DATETIME DEFAULT GETDATE()
)
Let's put some test data, suppose Category Id 1 means 'Bestsellers' and Category Id 2 means 'Comic Books'.
INSERT INTO Products (CategoryId, ProductName, Price)
SELECT 1, 'Theodore Boone, Kid Lawyer', '16.99'
UNION ALL
SELECT 1, 'The Red Pyramid', 17.99
UNION ALL
SELECT 2, 'Rodrick Rules', 13.95
We have tables, test data, let's create the trigger - this is the example of 'Don't do like this!'
CREATE TRIGGER PrUpdate ON dbo.Products AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ProductID INT
DECLARE @OldPrice NUMERIC(10,4)
DECLARE @NewPrice NUMERIC(10,4)
SELECT @ProductID=ProductID, @OldPrice=Price FROM deleted
SELECT @NewPrice=Price FROM inserted
INSERT INTO ProductPriceChanges
(ProductID, OldPrice, NewPrice)
VALUES (@ProductID, @OldPrice, @NewPrice)
END
Let's test our trigger by issuing the following command:
UPDATE Products SET Price = 15.99 WHERE ProductId = 1
Excellent, it worked! See the picture below.
But, next day company management decide to reduce the price of all books from 'Bestsellers' category with 5%. Remember that in our test scenario there are 2 records. So, issue the following SQL statement:
UPDATE Products SET Price = Price * .95 WHERE CategoryId = 1
See the next image. Price change for product id 2 was not logged. That's because the way trigger is written (in Oracle there is clause - FOR EACH ROW that run the code for each individual row. In SQL Server there is no such option). So, we should rewrite the trigger in such a way that ALL the records affected by the update statement will be logged into ProductPriceChanges table.
Modified version of the trigger:
ALTER TRIGGER PrUpdate ON dbo.Products AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO ProductPriceChanges
(ProductID, OldPrice, NewPrice)
SELECT d.ProductID, d.Price, i.Price
FROM deleted d, inserted i
WHERE d.ProductID = i.ProductID
END
Run the last update again and you'll notice that all price changes are reflected. Hope it helps.