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 rollback
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
rollback 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.
- Write a basic CREATE TRIGGER statement specifying the desired
trigger attributes. See the CREATE TRIGGER statement.
- 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.
- 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
- 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.
- Having completed writing your CREATE TRIGGER statement
you can now execute it statically (from within an application) or
dynamically (from the CLP, or from the Control Center) to formally
create the trigger in the database.
- Invoke your trigger. Do this by executing against the
appropriate data change statement that corresponds to your trigger
event.
- 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
- Run-time errors
- 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 SQL
below creates 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'@