Rules for SPL Routines

In addition to the rules listed in Re-Entrancy of Triggers, the following guidelines apply to an SPL routine that is specified as a trigger action:
  • The SPL routine cannot be a cursor function (one that returns more than one row) in a context where only one row is expected.
  • You cannot use the old or new correlation name inside the SPL routine unless the CREATE FUNCTION or CREATE PROCEDURE statement includes the REFERENCING clause that defines the UDR as a trigger routine. If you need to use the corresponding values in a routine that is not a trigger routine, you must pass them as parameters. In this case, the routine should be independent of triggers, and the old or new correlation name does not have any meaning outside the trigger.
  • A trigger routine must include the REFERENCING clause that can declare a correlation name for OLD or NEW column values that SPL statements in the trigger routine can reference.
  • A trigger routine must include the FOR table_object clause that specifies the name of the table or view in the local database whose triggers can invoke this routine. The triggered action cannot call a trigger routine that does not specify the triggering table or view.
  • Only trigger routines invoked in the FOR EACH ROW section of the Triggered Action list can operate directly on old or new correlation names that are defined in the REFERENCING clause of the trigger or of the trigger routine.
  • Trigger routines can be invoked only in the FOR EACH ROW section of the Triggered Action list in the trigger definition.
  • Correlated variables for OLD or NEW values can appear in the IF statement of SPL and in CASE expressions.
  • Only correlated variables for NEW values can be on the left-hand side of a LET expression that references correlated variables. In this case, the FOR clause of the SPL routine must specify a table, rather than a view, and the trigger whose action invokes the SPL routine cannot be an INSTEAD OF trigger.
  • Both OLD and NEW values can be on the right-hand side of a LET expression.
  • Only trigger routines that are invoked in the FOR EACH ROW clause can use the Boolean operators SELECTING, INSERTING, DELETING, and UPDATING. These operators return TRUE ('t') if the triggering event matches the DML operation referenced by the name of the operator, and they return FALSE ('f') otherwise.
  • The IF statement of SPL and CASE expressions of SQL can specify these operators as the condition in a trigger routine.
  • Trigger routines must be written in the SPL language. They cannot be written in an external language, such as the C or Java™ language, but the trigger routine can include calls to external language routines, such as the mi_trigger application programming interface for trigger introspection.
  • Trigger routines cannot reference savepoints. Any changes to the data values or to the schema of the database by a triggered action must be committed or rolled back in their entirety. HCL OneDB™ does not support the ROLLBACK TO SAVEPOINT statement in a trigger routine for the partial rollback of a triggered action.

For more information about the mi_trigger API, refer to the HCL OneDB DataBlade® API Programmer's Guide and to the HCL OneDB DataBlade API Function Reference.

When you use an SPL routine as a trigger action, the database objects that the routine references are not checked until the routine is executed.

See also the SPL restrictions in Triggers and SPL Routines.