Re-Entrancy and Cascading Triggers

The cases when a trigger cannot be re-entrant apply recursively to all cascading triggers, which are considered part of the initial trigger. In particular, this rule means that a cascading trigger cannot update any columns in the triggering table that were updated by the original triggering statement, including any nontriggering columns affected by that statement. For example, assume this UPDATE statement is the triggering statement:

UPDATE tab1 SET (a, b) = (a + 1, b + 1);

In the cascading triggers of the next example, trig2 fails at runtime because it references column b, which the triggering UPDATE statement updates:

CREATE TRIGGER trig1 UPDATE OF a ON tab1-- Valid
   AFTER (UPDATE tab2 SET e = e + 1);

CREATE TRIGGER trig2 UPDATE OF e ON tab2-- Invalid
   AFTER (UPDATE tab1 SET b = b + 1);

Now consider the following SQL statements. When the final UPDATE statement is executed, column a is updated and the trigger trig1 is activated.

The trigger action again updates column a with an EXECUTE PROCEDURE INTO statement.

CREATE TABLE temp1 (a INT, b INT, e INT);
INSERT INTO temp1 VALUES (10, 20, 30);

CREATE PROCEDURE proc(val iINT) RETURNING INT,INT;
    RETURN val+10, val+20;
END PROCEDURE;

CREATE TRIGGER trig1 UPDATE OF a ON temp1
    FOR EACH ROW (EXECUTE PROCEDURE proc(50) INTO a, e);

CREATE TRIGGER trig2 UPDATE OF e ON temp1
    FOR EACH ROW (EXECUTE PROCEDURE proc(100) INTO a, e);

UPDATE temp1 SET (a,b) = (40,50);

Several questions arise from this example of cascading triggers. First, should the update of column a activate trigger trig1 again? The answer is no. Because the trigger was activated, it is not activated a second time. If the trigger action is an EXECUTE PROCEDURE INTO or EXECUTE FUNCTION INTO statement, the only triggers that are activated are those that are defined on columns that are mutually exclusive from the columns updated until then (in the cascade of triggers) in that table. Other triggers are ignored.

Another question that arises from the example is whether trigger trig2 should be activated. The answer is yes. The trigger trig2 is defined on column e. Until now, column e in table temp1 has not been modified. Trigger trig2 is activated.

A final question that arises from the example is whether triggers trig1 and trig2 should be activated after the trigger action in trig2 is performed. The answer is no. Neither trigger is activated. By this time columns a and e have been updated once, and triggers trig1 and trig2 have been executed once. The database server ignores and does not activate these triggers. For more about cascading triggers, see Cascading Triggers.

As noted earlier, an INSTEAD OF trigger on a view cannot include the EXECUTE PROCEDURE INTO statement among its trigger actions. In addition, an error results if two views each have INSERT INSTEAD OF triggers with actions defined to perform INSERT operations on the other view.