Trigger routines

You can define specialized SPL routines, called trigger routines, that can be invoked only from the FOR EACH ROW section of the triggered action. Unlike ordinary UDRs that EXECUTE FUNCTION or EXECUTE PROCEDURE routines can call from the triggered action list, trigger routines include their own REFERENCING clause that defines correlation names for the old and new column values in rows that the triggered action modifies. These correlation names can be referenced in SPL statements within the trigger routine, providing greater flexibility in how the triggered action can modify data in the table or view.

Trigger routines can also use trigger-type Boolean operators, called DELETING, INSERTING, SELECTING, and UPDATING, to identify what type of trigger has called the trigger routine. Trigger routines can also invoke the mi_trigger* routines, which are sometimes called trigger introspection routines, to obtain information about the context in which the trigger routine has been called.

Trigger routines are invoked by EXECUTE FUNCTION or EXECUTE PROCEDURE statements that include the WITH TRIGGER REFERENCES keywords. These statements must call the trigger routine from the FOR EACH ROW section of the triggered action, rather than from the BEFORE or AFTER sections.

For information about syntax features that the CREATE FUNCTION, CREATE PROCEDURE, EXECUTE FUNCTION, and EXECUTE PROCEDURE statements of SQL support for defining and executing trigger routines, see your HCL OneDB™ Guide to SQL: Syntax. For more information about the mi_trigger* routines, see your HCL OneDB DataBlade® API Programmer's Guide.