Using SPL Routines as Triggered Actions

Probably the most powerful feature of triggers is the ability to call an SPL routine as a triggered action. The EXECUTE PROCEDURE statement, which calls an SPL routine, lets you pass data from the triggering table to the SPL routine and also to update the triggering table with data returned by the SPL routine. SPL also lets you define variables, assign data to them, make comparisons, and use procedural statements to accomplish complex tasks within a triggered action.

Passing Data to an SPL Routine

You can pass data to an SPL routine in the argument list of the EXECUTE PROCEDURE statement. The EXECUTE PROCEDURE statement in the following trigger example passes values from the quantity and total_price columns of the items table to the SPL routine calc_totpr:

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)

Passing data to an SPL routine lets you use it in the operations that the procedure performs.

Using SPL

The EXECUTE PROCEDURE statement in the preceding trigger calls the SPL routine that the following example shows. The procedure uses SPL to calculate the change that needs to be made to the total_price column when quantity is updated in the items table. The procedure receives both the old and new values of quantity and the old value of total_price. It divides the old total price by the old quantity to derive the unit price. It then multiplies the unit price by the new quantity to obtain the new total price.

CREATE PROCEDURE calc_totpr(old_qty SMALLINT, new_qty SMALLINT,
   total MONEY(8)) RETURNING MONEY(8);
   DEFINE u_price LIKE items.total_price;
   DEFINE n_total LIKE items.total_price;
   LET u_price = total / old_qty;
   LET n_total = new_qty * u_price;
   RETURN n_total;
END PROCEDURE;

In this example, SPL lets the trigger derive data that is not directly available from the triggering table.

Updating Nontriggering Columns with Data from an SPL Routine

Within a triggered action, the INTO clause of the EXECUTE PROCEDURE statement lets you update nontriggering columns in the triggering table. The EXECUTE PROCEDURE statement in the following example calls the calc_totpr SPL procedure that contains an INTO clause, which references the column total_price:

FOR EACH ROW(EXECUTE PROCEDURE calc_totpr(pre_upd.quantity,
   post_upd.quantity, pre_upd.total_price) INTO total_price);

The value that is updated into total_price is returned by the RETURN statement at the conclusion of the SPL procedure. The total_price column is updated for each row that the triggering statement affects.