Invoking a stored procedure or user-defined function from a trigger

A trigger body can include only SQL statements. To perform actions or use logic that is not available in SQL statements, create user-defined functions or stored procedures. Then invoke them from within the trigger body.

To invoke a stored procedure or user-defined function from a trigger:

1. Ensure that the stored procedure or user-defined function is defined before the trigger is defined.
   • Define procedures by using the CREATE PROCEDURE statement.
   • Define triggers by using the CREATE FUNCTION statement.
2. Invoke the user-defined function or stored procedure by performing one of the following actions:
   • To invoke a user-defined function, include the user-defined function in one of the following statements in the trigger:

     SELECT statement

     Use a SELECT statement to execute the function conditionally. The number of times that the user-defined function executes depends on the number of rows in the result table of the SELECT statement. For example, in the following trigger, the SELECT statement invokes user-defined function LARGE_ORDER_ALERT. This function executes once for each row in transition table N_TABLE with an order price of more than 10000:

     CREATE TRIGGER LRG_ORDR
       AFTER INSERT ON INVOICE
       REFERENCING NEW TABLE AS N_TABLE
       FOR EACH STATEMENT MODE DB2SQL
       BEGIN ATOMIC
         SELECT LARGE_ORDER_ALERT(CUST_NO, TOTAL_PRICE, DELIVERY_DATE)
           FROM N_TABLE WHERE TOTAL_PRICE > 10000;
       END

     VALUES statement

     Use the VALUES statement to execute a function unconditionally. The function executes once for each execution of a statement trigger or once for each row in a row trigger. In the following example, user-defined function PAYROLL_LOG executes every time the trigger PAYROLL1 is activated. This trigger is activated when an update operation occurs.

     CREATE TRIGGER PAYROLL1
       AFTER UPDATE ON PAYROLL
       FOR EACH STATEMENT MODE DB2SQL
       BEGIN ATOMIC
         VALUES(PAYROLL_LOG(USER, 'UPDATE',
           CURRENT TIME, CURRENT DATE));
       END

     ]
   • To invoke a stored procedure, include a CALL statement in the trigger. The parameters of this stored procedure call must be constants, transition variables, table locators, or expressions.

     If the parameter is a transition variable or table locator, and the CALL statement is in a BEFORE or AFTER trigger, Db2 returns a warning.

3. To pass transition tables from the trigger to the user-defined function or stored procedure, use table locators.

   When you call a user-defined function or stored procedure from a trigger, you might want to give the function or procedure access to the entire set of modified rows. In this case, use table locators to pass a pointer to the old or new transition table.

   Most of the code for using a table locator is in the function or stored procedure that receives the locator.

   To pass the transition table from a trigger, specify the parameter TABLE transition-table-name when you invoke the function or stored procedure. This parameter causes Db2 to pass a table locator for the transition table to the user-defined function or stored procedure. For example, the following trigger passes a table locator for a transition table NEWEMPS to stored procedure CHECKEMP:

   CREATE TRIGGER EMPRAISE
     AFTER UPDATE ON EMP
     REFERENCING NEW TABLE AS NEWEMPS
     FOR EACH STATEMENT MODE DB2SQL
     BEGIN ATOMIC
       CALL CHECKEMP(TABLE NEWEMPS);
     END