Rob Kraft's Software Development Blog

Software Development Insights

The Correct Way to Write A SQL Server Trigger That Modifies the Record Just Inserted

Posted by robkraft on October 31, 2014

It is easy to write a trigger that has unintended consequences, which is why I recommend you always test your triggers for two things:

  1. Did the trigger update the records I expected it to correctly?
  2. Did the trigger update records I did not expect it to?

Here is an example of a trigger we had that was incorrect.  We discovered it because it was taking so long to run the insert query that the query was timing out.  And it was taking so long because it was updating all the records in the table, not just the one we wanted it to modify.

In this example, we want to update the OrderDetails date to be equal to the OrderDate from the parent table, but only for orders with type=200 and only when new order details are inserted.  Run this SQL to create the tables and add some test data.

drop Table Orders
go
drop table Orderdetails
go
Create Table Orders (OrderID int, OrderDate datetime, OrderType int)
Create Table OrderDetails (DetailID int, parentOrderID int, DetailDate datetime)
insert into orders values (1,’10/25/2014′,100)
insert into orders values (2,’10/27/2014′,200)
insert into orders values (3,’10/28/2014′,100)
insert into orders values (4,’10/29/2014′,200)
insert into OrderDetails values (10, 1, ’10/25/2015′)
insert into OrderDetails values (11, 1, ’10/26/2015′)
insert into OrderDetails values (14, 2, ’10/25/2015′)
insert into OrderDetails values (15, 2, ’10/26/2015′)
insert into OrderDetails values (16, 3, ’10/25/2015′)
insert into OrderDetails values (17, 3, ’10/26/2015′)
insert into OrderDetails values (18, 4, ’10/25/2015′)
insert into OrderDetails values (19, 4, ’10/26/2015′)

Then run this script to create the incorrect stored procedure:

GO
CREATE TRIGGER dbo.MyFirstTriggerThatDoesntWork
ON OrderDetails
FOR INSERT AS
BEGIN
UPDATE OrderDetails
SET DetailDate=Orders.OrderDate
FROM Orders INNER JOIN
INSERTED I ON Orders.OrderID = I.parentOrderID
WHERE (Orders.OrderType = 200)
END

Now let’s run an INSERT and check the results:

insert into OrderDetails (DetailID, parentOrderID) values (20, 4)

The messages displayed look like the following, and that message with “9 row(s) affected” is a good indicator that something went wrong:

(1 row(s) affected)
(1 row(s) affected)
(9 row(s) affected)
(1 row(s) affected)

Looking at all of our data, we can see that every record got updated to the 10/29 date:

select * from OrderDetails
10 1 2014-10-29 00:00:00.000
11 1 2014-10-29 00:00:00.000
14 2 2014-10-29 00:00:00.000
15 2 2014-10-29 00:00:00.000
16 3 2014-10-29 00:00:00.000
17 3 2014-10-29 00:00:00.000
18 4 2014-10-29 00:00:00.000
19 4 2014-10-29 00:00:00.000
20 4 2014-10-29 00:00:00.000

That is not what we wanted.  Here is the correct way to write this trigger. Do this after running the script above to drop and recreate the tables.

CREATE TRIGGER dbo.MySecondTriggerThatDoesWork
ON OrderDetails
FOR INSERT AS
BEGIN
UPDATE OrderDetails
SET DetailDate=Orders.OrderDate
FROM Orders INNER JOIN
INSERTED I ON Orders.OrderID = I.parentOrderID INNER JOIN OrderDetails d on d.DetailID = I.DetailID
WHERE (Orders.OrderType = 200)
END

If we run the insert again and look at the output messages we will see that only 1 record was affected and we can see the correct output in our results:

insert into OrderDetails (DetailID, parentOrderID) values (20, 4)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
select * from OrderDetails
10 1 2015-10-25 00:00:00.000
11 1 2015-10-26 00:00:00.000
14 2 2015-10-25 00:00:00.000
15 2 2015-10-26 00:00:00.000
16 3 2015-10-25 00:00:00.000
17 3 2015-10-26 00:00:00.000
18 4 2015-10-25 00:00:00.000
19 4 2015-10-26 00:00:00.000
20 4 2014-10-29 00:00:00.000

I emboldened the line of code in the trigger that made the difference.  In the first trigger, we are updating the OrderDetails table with the result of a join between the Inserted table and the Orders table.  So basically, that says “run a query that joins the Orders table with the Inserted table, and apply the results of that join to EVERY record in OrderDetails”.  It is applied to EVERY record in OrderDetails because we did not apply any criteria to limit which records on OrderDetails are affected.

UPDATE OrderDetails
SET DetailDate=Orders.OrderDate
FROM Orders INNER JOIN INSERTED I ON Orders.OrderID = I.parentOrderID

The fix was include a join to the table we are updating (OrderDetails) and limit which records it is going to affect by joining the OrderDetails table to the Inserted table.

UPDATE OrderDetails
SET DetailDate=Orders.OrderDate
FROM Orders INNER JOIN INSERTED I ON Orders.OrderID = I.parentOrderID INNER JOIN OrderDetails d on d.DetailID = I.DetailID

In the example above, the Inserted table would have 1 record in it, with the values of DetailID=20 and parentOrderID=4.  Our trigger would update a record in OrderDetails that had the same DetailID of 20 and set the DetailDate equal to the OrderDate of the Order we were joined to.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: