Cascading Triggers

The database server allows triggers other than Select triggers to cascade, meaning that the trigger actions of one trigger can activate another trigger. (For further information on the restriction against cascading Select triggers, see Circumstances When a Select Trigger Is Activated.)

The maximum number of triggers in a cascading series is 61: the initial trigger plus a maximum of 60 cascading triggers. When the number of cascading triggers in a series exceeds the maximum, the database server returns error number -748, with the following message:

Exceeded limit on maximum number of cascaded triggers.

The next example illustrates a series of cascading triggers that enforce referential integrity on the manufact, stock, and items tables in the stores_demo database. When a manufacturer is deleted from the manufact table, the first trigger, del_manu, deletes all the items of that manufacturer from the stock table. Each DELETE in the stock table activates a second trigger, del_items, that deletes all items of that manufacturer from the items table. Finally, each DELETE in the items table triggers SPL routine log_order, creating a record of any orders in the orders table that can no longer be filled.

CREATE TRIGGER del_manu
   DELETE ON manufact REFERENCING OLD AS pre_del
   FOR EACH ROW(DELETE FROM stock WHERE manu_code = pre_del.manu_code);
CREATE TRIGGER del_stock
   DELETE ON stock REFERENCING OLD AS pre_del
   FOR EACH ROW(DELETE FROM items WHERE manu_code = pre_del.manu_code);
CREATE TRIGGER del_items
   DELETE ON items REFERENCING OLD AS pre_del
   FOR EACH ROW(EXECUTE PROCEDURE log_order(pre_del.order_num));

When you are not using logging, referential integrity constraints on both the manufact and stock tables prohibit the triggers in this example from executing. When you use logging, however, the triggers execute successfully because constraint checking is deferred until all the trigger actions are complete, including the actions of cascading triggers. For more information about how constraints are handled when triggers execute, see Constraint Checking.

The database server prevents loops of cascading triggers by not allowing you to modify the triggering table in any cascading trigger action, except with an UPDATE statement that does not modify any column that the triggering UPDATE statement updated, or with an INSERT statement. An INSERT trigger can define UPDATE trigger actions on the same table.