Preventing Recursive Trigger Conditions
In the past, I have written on some of the possibilities of using triggers in SQL. SQL triggers can be very useful, but there are conditions that you have to watch out for when writing them. There may be times when you want to update one column of a table, when a row of that table is updated. If you are not careful you can end up with a recursive condition, where the trigger keeps firing over and over.
If, for example, let’s assume that you have TABLEA, with ITEM_NO, QTY_ON_HAND, COST, and INVENTORY_VALUE. While good programming practices dictate that computed values are not normally stored, for our purposes assume that INVENTORY_VALUE is COST multiplied by QTY_ON_HAND. It is very tempting to write an UPDATE trigger on TABLEA, so that when the QTY_ON_HAND or COST change, the INVENTORY_VALUE is updated.
The simple approach to this (ignoring the details of cursor loops, etc.) would be add an UPDATE trigger on TABLEA, that declares the local variable @ItemNo, and a cursor for:
select ITEM_NO
from INSERTED
then do a cursor loop into @ItemNo. Then, inside your cursor loop, do:
UPDATE TABLEA set INVENTORY_VALUE=QTY_ON_HAND*COST where ITEM_NO=@ItemNo
The problem here, is that when a record is updated, the trigger will fire, which will update the record and fire the trigger, again. In other words, the trigger will fire repeatedly, until it aborts when the maximum recursion depth is reached.
The trick, is to add conditions to the cursor declaration, so that records that do not need updated are not selected. In this case, if the QTY_ON_HAND and COST have not changed, the record does not need to be updated:
select ITEM_NO
from INSERTED I
join DELETED D on D.ITEM_NO=I.ITEM_NO
where D.QTY_ON_HANDI.QTY_ON_HAND or
D.COSTI.COST
The result is that only the records are selected where either the quantity, or cost, have changed. So, if the quantity changes, the INVENTORY_VALUE is recalculated, which fires the trigger again. However, the second time it fires, neither the quantity nor cost have changed, and it does not update the record again.