Triggers and SPL Routines

You cannot define a trigger in an SPL routine that is called inside a DML (data manipulation language) statement, as listed in Data Manipulation Language Statements. Thus, the following statement returns an error if the sp_items procedure includes the CREATE TRIGGER statement:

INSERT INTO items EXECUTE PROCEDURE sp_items;

You can use the CREATE FUNCTION or CREATE PROCEDURE statement of SQL with the REFERENCING clause to define trigger routines that include the FOR table or FOR view specification. These UDRs must include the REFERENCING clause that declares correlation names for OLD or NEW column values in the specified table or view. Triggers on the table or view can invoke the trigger routine from the FOR EACH ROW section of the Triggered Action list. Triggers can also invoke non-trigger routines from the BEFORE and AFTER sections of the Triggered Action list, but these UDRs cannot use correlation names to reference the NEW or OLD column values. The REFERENCING clause in a trigger routine supports the same syntax as in the CREATE TRIGGER statement, as described in the section REFERENCING Clauses.

Multiple triggers that the same triggering event executes can invoke more than one trigger routine, and these trigger routines can access the same NEW or OLD column values by using SPL variables that have the same names or different names. When a single triggering event executes multiple triggers, the order of execution is not guaranteed, but all of the BEFORE triggered actions execute before any of the FOR EACH ROW triggered actions, and all of the AFTER triggered actions execute after all of the FOR EACH ROW triggered actions.

For UDRs that are not trigger routines, SPL variables are not valid in CREATE TRIGGER statements. An SPL routine cannot perform INSERT, DELETE, or UPDATE operations on any table or view that is not local to the current database. See also Rules for SPL Routines for additional restrictions on SPL routines that are invoked in triggered actions.