Re-Entrancy of Triggers

In some cases a trigger can be re-entrant. In these cases the triggered action can reference the triggering table. In other words, both the trigger event and the triggered action can operate on the same table. The following list summarizes the situations in which triggers can be re-entrant and the situations in which triggers cannot be re-entrant:

• The trigger action of an Update trigger cannot be an INSERT or DELETE of the table that the trigger event updated.
• Similarly, the trigger action of an Update trigger cannot be an UPDATE of a column that the trigger event updated. (But the trigger action of an Update trigger can update a column that was not updated by the trigger event.)

  For example, assume that the following UPDATE statement, which updates columns a and b of tab1, is the triggering statement:

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

  Now consider the trigger actions in the following example. The first UPDATE statement is a valid trigger action, but the second one is not, because it updates column b again.

  UPDATE tab1 SET c = c + 1;    -- OK
  UPDATE tab1 SET b = b + 1;    -- INVALID

• If the trigger has an UPDATE event, the trigger action can be an EXECUTE PROCEDURE or EXECUTE FUNCTION statement with an INTO clause that references a column that was updated by the trigger event or any other column in the triggering table.

  When an EXECUTE PROCEDURE or EXECUTE FUNCTION statement is the trigger action, the INTO clause for an UPDATE trigger is valid only in FOR EACH ROW trigger actions, and column names that appear in the INTO clause must be from the triggering table.

  The following statement illustrates the appropriate use of the INTO clause:

  CREATE TRIGGER upd_totpr UPDATE OF quantity ON items
     REFERENCING OLD AS pre_upd NEW AS post_upd
     FOR EACH ROW(EXECUTE PROCEDURE 
        calc_totpr(pre_upd.quantity,post_upd.quantity,
        pre_upd.total_price) INTO total_price)

  The column that follows the INTO keyword must be in the triggering table, but need not have been updated by the trigger event.

  When the INTO clause appears in the EXECUTE PROCEDURE or EXECUTE FUNCTION statement, the database server updates the specified columns with values returned from the UDR, immediately upon returning from the UDR.

• If the trigger has an INSERT event, the trigger action cannot be an INSERT or DELETE statement that references the triggering table.
• If the trigger has an INSERT event, the trigger action can be an UPDATE statement that references a column in the triggering table, but this column cannot be a column for which a value was supplied by the trigger event.

  If the trigger has an INSERT event, and the trigger action updates the triggering table, the columns in both statements must be mutually exclusive. For example, assume that the triggering statement inserts values for columns cola and colb of table tab1:

  INSERT INTO tab1 (cola, colb) VALUES (1,10)

  Now consider the following trigger actions. The first UPDATE is valid, but the second one is not, because it updates column colb even though the trigger event already supplied a value for column colb:

  UPDATE tab1 SET colc=100; --OK
  UPDATE tab1 SET colb=100; --INVALID

• If the trigger has an INSERT event, the trigger action can be an EXECUTE PROCEDURE or EXECUTE FUNCTION statement with an INTO clause that references a column that was supplied by the trigger event or a column that was not supplied by the trigger event.

  When an EXECUTE PROCEDURE or EXECUTE FUNCTION statement is the trigger action, you can specify the INTO clause for an INSERT trigger only when the trigger action occurs in the FOR EACH ROW list. In this case, the INTO clause can contain only column names from the triggering table.

  The following statement illustrates the valid use of the INTO clause:

  CREATE TRIGGER ins_totpr INSERT ON items
     REFERENCING NEW AS new_ins
     FOR EACH ROW (EXECUTE PROCEDURE calc_totpr
        (0, new_ins.quantity, 0) INTO total_price).

  The column that follows the INTO keyword can be a column in the triggering table that was supplied by the trigger event, or a column in the triggering table that was not supplied by the trigger event.

  When the INTO clause appears in the EXECUTE PROCEDURE or (for Dynamic Server only) the EXECUTE FUNCTION statement, the database server immediately updates the specified columns with values returned from the UDR.

• If the trigger action is a SELECT statement, the SELECT statement can reference the triggering table. The SELECT statement can be a trigger action in the following instances:
  • The SELECT statement appears in a subquery in the WHEN clause or in a trigger-action statement.
  • The trigger action is a UDR, and the SELECT statement appears inside the UDR.

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 int) 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);

In Extended Parallel Server, to re-create this example, use the CREATE PROCEDURE statement instead of the CREATE FUNCTION statement.

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.

In Dynamic Server, 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.