How often have you seen someone write a DML trigger like this:
declare @id int
select @id = id from inserted
update xx set yyy = zz
where id = @id
return
There seems to be this common misconception that a trigger fires for each change in a row (insert/update/delete), and that’s not true. As noted in Books Online, triggers fire once for the insert/update/delete. Typically this is an implicit transaction for the statement. If you have multiple statements inside an explicit transaction, the trigger fires once for each insert/update/delete statement in the transaction.
That means that if I change two rows and I have the trigger above, I won’t get the behavior I expect from the trigger. Let’s say that I want to update my inventory table each time I change an order. Imagine that I have this in my orders table:
and this in my inventory table.
If I now write this trigger:
for update
as
declare @qty int
, @product int
select @qty = a.qty – b.qty
from inserted a
inner join deleted b
on a.orderid = b.orderid
select @product = productid from inserted
update inventory
set onhand = onhand – @qty
where productid = @product
return
and execute this:
set qty = 2
where orderid = ’59CD85CE-984C-4D33-9E23-5F6159848277′
I will find that my inventory table looks like this:
That appears to work, but what happens if we execute this?
set qty = qty+1
where[CustomerID int] = 2
Then we find that the inventory is
In this case productID 1 has had its inventory reduced by 1, but not product ID 2. Why not?
When the trigger fires, there are actually this data in the tables:
Inserted
OrderID OrderDate CustomerID int qty productid
——– ———- ————– ———– ———–
59CD… 2010-09-22 3 3 1
01B6… 2010-09-22 2 2 2
and deleted
OrderID OrderDate CustomerID int qty productid
——– ———- ————– ———– ———–
59CD85… 2010-09-22 2 2 1
01B66E… 2010-09-22 2 1 2
However the trigger, in setting the variables to the result of a query could have picked either of the rows, but only one row. SQL Server doesn’t guarantee order without an ORDER BY, so either product ID could have been chosen. As a result, only one of the products had the inventory updated.
A proper trigger would look like this
for update
as
select @qty = a.qty – b.qty
from inserted a
inner join deleted b
on a.orderid = b.orderid
select @product = productid from inserted
update inventory
set onhand = onhand – ( a.qty – b.qty)
from inserted a
inner join deleted b
on a.orderid = b.orderid
where inventory.productid = i.productid
return
Triggers should always be written to handle multiple rows, using the inserted and deleted tables for joins instead of variables. Even if you always just update single rows, coding this way will prevent issues if there is a multiple row change.
Voice of the DBA 