Recursion

As with stored procedures, we can set up recursive triggers. Although rare, there can be scenarios when this can be acceptable. We saw a direct recursion with the Factorial stored procedure in Chapter 1, it is possible to have recursion on triggers as well.

There are two types of trigger recursion:

• direct – A trigger updates the same table that the trigger has been fired on and therefore fires itself again.

• indirect – A trigger is fired on one table, which updates a different table with a trigger on it, which then updates the original table again.

To clarify the indirect trigger, one example would be when a new order comes in, and a trigger fires which updates the stock levels. This fires a trigger on the Stock table to reduce the stock level. If the stock falls below a certain level, then this might update the order table with an indicator that we are out of stock. This will re-fire the trigger on the order table. In this scenario, we have to be careful not to end up in an endless loop, and fire the update on the Stock table only if there is a stock movement. The second time the order trigger is fired this will not be the case, and therefore we will test this, and just let the trigger recursion stop.

To enable direct recursion to take place, then we need to set the RECURSIVE_TRIGGERS SQL option to ON.

     ALTER DATABASE Northwind     SET RECURSIVE_TRIGGERS ON 

Note

As soon as we place a trigger on a database, whenever another trigger is added, there is always the danger that recursion could exist. Keep this in mind, it is better to have RECURSIVE_TRIGGERS set to OFF, and ensure that we do not code a ‘cover all’ and bolt down what the trigger is actually there for, while building it.

If we have a trigger that updates the same table that caused its firing, then the same trigger will fire again if RECURSIVE_TRIGGERS was ON. If we do have this option on, and we are updating the same table, perhaps with summary info, then we have to consider the fact that recursion will occur and code defensively for it, otherwise we may get into an infinite loop.