In this section and in sections that follow, references to nonlogging databases do not apply to Extended Parallel Server. (In Extended Parallel Server, all databases support transaction logging.)
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.
When you use logging, the database server defers constraint checking on the triggering statement until after the statements in the triggered-action list execute. This is equivalent to executing a SET CONSTRAINTS ALL DEFERRED statement before executing the triggering statement. After the trigger action is completed, the database server effectively executes a SET CONSTRAINTS constraint IMMEDIATE statement to check the constraints that were deferred. This action allows you to write triggers so that the trigger action can resolve any constraint violations that the triggering statement creates. For more information, see SET Database Object Mode.
Consider the following example, in which the table child has constraint r1, which references the table parent. You define trigger trig1 and activate it with an INSERT statement. In the trigger action, trig1 checks to see if parent has a row with the value of the current cola in child; if not, it inserts it.
CREATE TABLE parent (cola INT PRIMARY KEY);
CREATE TABLE child (cola INT REFERENCES parent CONSTRAINT r1);
CREATE TRIGGER trig1 INSERT ON child
REFERENCING NEW AS new
FOR EACH ROW
WHEN((SELECT COUNT (*) FROM parent
WHERE cola = new.cola) = 0)
-- parent row does not exist
(INSERT INTO parent VALUES (new.cola));
When you insert a row into a table that is the child table in a referential constraint, the row might not exist in the parent table. The database server does not immediately return this error on a triggering statement. Instead, it allows the trigger action to resolve the constraint violation by inserting the corresponding row into the parent table. As the previous example shows, you can check within the trigger action to see whether the parent row exists, and if so, you can provide logic to bypass the INSERT action.
For a database without logging, the database server does not defer constraint checking on the triggering statement. In this case, the database server immediately returns an error if the triggering statement violates a constraint.
You cannot use the SET Transaction Mode statement in a trigger action. The database server checks this restriction when you activate a trigger, because the statement could occur inside a UDR.
In Extended Parallel Server, rows that cause constraint violations might appear in the violations table, even if a subsequent trigger action corrects the violation.
When you activate multiple triggers with an UPDATE statement, a trigger can possibly override the changes that an earlier trigger made. If you do not want the trigger actions to interact, you can split the UPDATE statement into multiple UPDATE statements, each of which updates an individual column.
As another alternative, you can create a single update trigger for all columns that require a trigger action. Then, inside the trigger action, you can test for the column being updated and apply the actions in the desired order. This approach, however, is different from having the database server apply the actions of individual triggers, and it has the following disadvantages: