Calling procedures from triggers or SQL routines

Calling a procedure from an SQL routine, a trigger, or dynamic compound statement is essentially the same. The same steps are used to implement this call. This topic explains the steps using a trigger scenario. Any prerequisites or steps that differ when calling a procedure from a routine or dynamic compound statement are stated.

Before you begin

  • The procedure must have been created in the database by executing the CREATE PROCEDURE statement.
  • For external procedures, the library or class files must be in the location specified by the EXTERNAL clause of the CREATE PROCEDURE statement.
  • The creator of a trigger that contains a CALL statement must have the privilege to execute the CALL statement. At runtime when a trigger is activated it is the authorization of the creator of the trigger that is checked for the privilege to execute the CALL statement. A user that executes a dynamic compound statement that contains a CALL statement, must have the privilege to execute the CALL statement for that procedure.
  • To invoke a trigger, a user must have the privilege to execute the data change statement associated with the trigger event. Similarly, to successfully invoke an SQL routine or dynamic compound statement a user must have the EXECUTE privilege on the routine.


Restrictions

When invoking a procedure from within an SQL trigger, an SQL routine, or a dynamic compound statement the following restrictions apply:
  • In partitioned database environments procedures cannot be invoked from triggers or SQL UDFs.
  • On symmetric multi-processor (SMP) machines, procedure calls from triggers are executed on a single processor.
  • A procedure that is to be called from a trigger must not contain a COMMIT statement or a ROLLBACK statement that attempts to roll back the unit of work. The ROLLBACK TO SAVEPOINT statement is supported within the procedure however the specified savepoint must be in the procedure.
  • A rollback of a CALL statement from within a trigger will not roll back any external actions effected by the procedures, such as writing to the file system.
  • The procedure must not modify any federated table. This means that the procedure must not contain a searched UPDATE of a nickname, a searched DELETE from a nickname or an INSERT to a nickname.
  • Result sets specified for the procedure will not be accessible from inline SQL PL statements.
  • If a cursor defined as WITH RETURN TO CLIENT is opened during the execution of a compiled trigger, result sets from the cursor will be discarded.
BEFORE triggers can not be created if they contain a CALL statement that references a procedure created with an access level of MODIFIES SQL DATA. The execution of a CREATE TRIGGER statement for such a trigger will fail with error (SQLSTATE 42987). For more about SQL access levels in routines see:

Procedure

This procedure section explains how to create and invoke a trigger that contains a CALL statement. The SQL required to call a procedure from a trigger is the same SQL required to call a procedure from an SQL routine or dynamic compound statement.

  1. Write a basic CREATE TRIGGER statement specifying the desired trigger attributes. See the CREATE TRIGGER statement.
  2. In the trigger action portion of the trigger you can declare SQL variables for any IN, INOUT, OUT parameters that the procedure specifies. See the DECLARE statement. To see how to initialize or set these variables see the assignment statement. Trigger transition variables can also be used as parameters to a procedure.
  3. In the trigger action portion of the trigger add a CALL statement for the procedure. Specify a value or expression for each of the procedure's IN, INOUT, and OUT parameters
  4. For SQL procedures you can optionally capture the return status of the procedure by using the GET DIAGNOSTICS statement. To do this you will need to use an integer type variable to hold the return status. Immediately after the CALL statement, simply add a GET DIAGNOSTICS statement that assigns RETURN_STATUS to your local trigger return status variable.
  5. Having completed writing your CREATE TRIGGER statement you can now execute it statically (from within an application) or dynamically (from the CLP) to formally create the trigger in the database.
  6. Invoke your trigger. Do this by executing against the appropriate data change statement that corresponds to your trigger event.
  7. When the data change statement is executed against the table, the appropriate triggers defined for that table are fired. When the trigger action is executed, the SQL statements contained within it, including the CALL statement, are executed.

Results

Runtime errors might occur if the procedure attempts to read or write to a table that the trigger also reads or writes to, an error might be raised if a read or write conflict is detected. The set of tables that the trigger modifies, including the table for which the trigger was defined must be exclusive from the tables modified by the procedure.

Example: Calling an SQL procedure from a trigger

This example illustrates how you can embed a CALL statement to invoke a procedure within a trigger and how to capture the return status of the procedure call using the GET DIAGNOSTICS statement. The following SQL statements create the necessary tables, an SQL PL language procedure, and an after trigger.

CREATE TABLE T1 (c1 INT, c2 CHAR(2))@
CREATE TABLE T2 (c1 INT, c2 CHAR(2))@

CREATE or replace PROCEDURE proc(IN val INT, IN name CHAR(2))
LANGUAGE SQL 
DYNAMIC RESULT SETS 0
MODIFIES SQL DATA
BEGIN
  DECLARE rc INT DEFAULT 0;
  INSERT INTO T2 VALUES (val, name);
  GET DIAGNOSTICS rc = ROW_COUNT;
  IF ( rc > 0 ) THEN
      RETURN 0;
  ELSE
      RETURN -200;
  END IF;
END@

CREATE or replace TRIGGER trig1 AFTER UPDATE ON t1
REFERENCING NEW AS n
FOR EACH ROW 
WHEN (n.c1 > 100)
BEGIN ATOMIC
   DECLARE rc INTEGER DEFAULT 0;
   CALL proc(n.c1, n.c2);
   GET DIAGNOSTICS rc = RETURN_STATUS;
   VALUES(CASE WHEN rc < 0 THEN CAST(RAISE_ERROR('70001', 'PROC CALL failed')
      as varchar(70))END);
END@

Issuing the following SQL statement will cause the trigger to fire and the procedure will be invoked.

UPDATE T1 SET c1 = c1+1 WHERE c2 = 'CA'@