VALUES

The VALUES statement provides a method for invoking a user-defined function from a trigger. Transition variables and transition tables can be passed to the user-defined function.

Invocation

This statement can only be used in the triggered action of a trigger.

Authorization

EXECUTE authority is needed on any user-defined function that is referenced in the VALUES clause.

Syntax

VALUES {expression | (expression[,...])}

Description

Specifies one or more expressions. If more than one expression is specified, the expressions must be enclosed within parentheses.

The expressions are evaluated, but the resulting values are discarded and are not assigned to any output variables.

If a user-defined function is specified as part of an expression, the user-defined function is invoked. If a negative SQLCODE is returned when the function is invoked, DB2 stops executing the trigger and rolls back any triggered actions that were performed.

Example:

Create an after trigger EMPISRT1 that invokes user-defined function NEWEMP when the trigger is activated. An insert operation on table EMP activates the trigger. Pass transition variables for the new employee number, last name, and first name to the user-defined function.

CREATE TRIGGER EMPISRT1
    AFTER INSERT ON EMP
    REFERENCING NEW AS N
    FOR EACH ROW
    MODE DB2SQL
    BEGIN ATOMIC
        VALUES(NEWEMP(N.EMPNO, N.LASTNAME, N.FIRSTNAME));
    END