Re-Entrancy of Triggers

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

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, 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

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 the EXECUTE FUNCTION statement, the database server immediately updates the specified columns with values returned from the UDR.

• 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.