http://www.sqlservercentral.com/blogs/steve_jones/2010/09/23/common-sql-server-mistakes-_1320_-multi-row-dml-triggers/

Printed 2014/09/16 01:46AM

Common SQL Server Mistakes – Multi Row DML Triggers

By Steve Jones, 2010/09/23

How often have you seen someone write a DML trigger like this:

create trigger mytrigger on Mytable for insert as

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.

Update: I changed the above paragraph after the comment below.

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:

orders_1

and his in my inventory table.

trigger_2

If I now write this trigger:

alter trigger orders_update_inventory on orders
  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:

update orders
  set qty = 2
   where orderid = '59CD85CE-984C-4D33-9E23-5F6159848277'

I will find that my inventory table looks like this:

trigger_3

That appears to work, but what happens if we execute this?

update orders
  set qty = qty+1
  where[CustomerID int] = 2


Then we find that the inventory is

trigger_4

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


alter trigger orders_update_inventory on orders
  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.


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.