Stored procedures called from within a triggered action statement can be one or more of the SQL statements summarized in the following table:
| Trigger Type | Valid Statement |
|---|---|
| AFTER |
|
| BEFORE |
|
Triggered statements must run within the same transaction as the statement that fires their trigger. Stored procedures can change the state of a transaction, but the statements within the body of a trigger cannot change the transaction state.
Executing the SQL CALL statement does not initiate a transaction: it is the execution of the first SQL request or expression inside the stored procedure that initiates a transaction. The system then assigns a request number and transaction number to it if a transaction number has not already been assigned. The system then increments the request number for each SQL request inside the stored procedure. However, the transaction number of the SQL requests depends on the session mode and the status of the transaction in which the CALL request was submitted.
For this reason, the following statements are not permitted inside the body of any stored procedure that is called from a trigger:
- All DDL statements
- All DCL statements
- The BEGIN TRANSACTION and END TRANSACTION statements
- All exception handling statements
This is not strictly true. Although a stored procedure called by a trigger can validly contain condition handlers, most of them can never be executed when the procedure is called because the first failure on a triggered statement causes the entire request to abort and roll back.
In general, stored procedures support the following three types of parameters:
- IN
- OUT
- INOUT
However, because triggered actions cannot return output to the requestor, the OUT and INOUT parameters are not allowed in any stored procedure that is called from a trigger.
You can pass the old-values correlation name and the new-values correlation name as parameters, but not the old-values-table alias, new-values-table alias, or old-new-table alias.