SET transition-variable assignment

The SET transition-variable assignment statement assigns values, either of expressions or NULL values, to transition variables.

Invocation

This statement can be used as a triggered SQL statement in the triggered action of a before trigger whose granularity is FOR EACH ROW.

Authorization

The privileges that are held by the current authorization ID must include those required to execute any of the expressions or assignments to transition variables.

Syntax

>>-SET---------------------------------------------------------->

   .-,-------------------------------------------------------------------------------.   
   V                                                                                 |   
>----+-transition-variable=-+-expression-+-----------------------------------------+-+-><
     |                      +-DEFAULT----+                                         |     
     |                      '-NULL-------'                                         |     
     |   .-,-------------------.       .-,--------------.                          |     
     |   V                     |       V                |                      (1) |     
     '-(---transition-variable-+-)=(-+---+-expression-+-+------------------+-)-----'     
                                     |   +-DEFAULT----+                    |             
                                     |   '-NULL-------'                    |             
                                     '-VALUES-+-+-expression-+-----------+-'             
                                              | +-DEFAULT----+           |               
                                              | '-NULL-------'           |               
                                              |    .-,--------------.    |               
                                              |    V                |    |               
                                              '-(----+-expression-+-+--)-'               
                                                     +-DEFAULT----+                      
                                                     '-NULL-------'

Notes:

The number of expressions, DEFAULT, and NULL keywords must match the number of transition-variables.

transition-variable:


>>-+-------------------+--column-name--------------------------><
   '-correlation-name.-'

Description

transition-variable

Identifies a column in the set of affected rows for the trigger that is used to used to receive the corresponding expression, default value, or NULL value on the right side of the statement.

Start of change transition-variable must not correspond to a begin column or end column of a BUSINESS_TIME period. End of change

The value to be assigned to each transition-variable can be specified immediately following the transition variable, for example, transition-variable = expression, transition-variable=expression. Or, sets of parentheses can be used to specify all the transition-variables and then all the values, for example, (transition-variable, transition-variable) = (expression, expression).

correlation-name: Identifies the correlation name given for referencing the NEW transition variables. The name must match the correlation name specified following NEW in the REFERENCING clause of the CREATE TRIGGER statement.
If OLD is not specified in the REFERENCING clause, correlation-name defaults to the correlation name following NEW.
column-name: Identifies the column to be updated. The name must identify a column of the subject table. The name can identify a column that is defined as GENERATED BY DEFAULT but not one defined as GENERATED ALWAYS, unless the DEFAULT keyword is specified on the right side of the statement. You must not specify the same column more than once.

The effect of a SET transition-variable statement is equivalent to the effect of an SQL UPDATE statement.

expression

Specifies the value to be assigned to the corresponding transition-variable. The expression is any expression of the type described in Expressions. A reference to a local special register is the value of that special register at the server when the trigger body is activated. If the expression contains a scalar fullselect, the scalar fullselect cannot reference columns of the triggering table. The expression cannot include a aggregate function except when it occurs within a scalar fullselect.

An expression can contain references to OLD and NEW transition variables that are qualified with a correlation name.

All expressions are evaluated before any result is assigned to a transition variable. If an expression refers to a transition variable that is used in the list of transition variables, the value of the variable in the expression is the value of the variable prior to any assignments.

Each assignment to a transition variable column is made according to the assignment rules described in Assignment and comparison. Assignments are made in sequence through the list. When the transition-variables are enclosed within parentheses, for example, (transition-variable, transition-variable, …) = (expression, expression, …), the first value is assigned to the first transition variable in the list, the second value to the second transition variable in the list, and so on.

DEFAULT

Specifies that the default value is used based on how the corresponding column is defined in the table. The value that is assigned depends on how the column is defined.

If the column is defined using the IDENTITY clause, the column is generated by the DB2® system.
If the column is defined as a row change timestamp column, the column value is generated by the DB2 system.
If the column is defined using the WITH DEFAULT clause, the value is set to the default that is defined for the column.
If the column is defined without specifying the WITH DEFAULT clause, the GENERATED clause, or the NOT NULL clause, the value is NULL
If the column is specified in the INCLUDE column list, the column value is set to null.

A ROWID column must not be set to the DEFAULT keyword.

An identity column or a row change timestamp column that is defined as GENERATED ALWAYS can be set only to the DEFAULT keyword.

If the column is defined using the NOT NULL clause and the GENERATED clause is not used, or the WITH DEFAULT clause is not used, the DEFAULT keyword cannot be specified for that column.

NULL

Specifies the null value and can only be specified for nullable transition variables.

VALUES

Specifies the value to be assigned to the corresponding transition variable. When more than one value is specified, the values must be enclosed in parentheses. Each value can be an expression or NULL, as described above. The following syntax is equivalent:

(transition-variable, transition-variable) = (VALUES(expression, NULL))
(transition-variable, transition-variable) = (expression, NULL)

Examples

Example 1: Assume that you want to create a before trigger that sets the salary and commission columns to default values for newly inserted rows in the EMPLOYEE table and that you will define the trigger only with NEW in the REFERENCING clause. Write the SET transition-variable statement that assigns the default values to the SALARY and COMMISSION columns.

   SET (SALARY, COMMISSION) = (50000, 8000);

Example 2: Assume that you want to create a before trigger that detects any commission increases greater than 10% for updated rows in the EMPLOYEE table and limits the commission increase to 10%. You will define the trigger with both OLD and NEW in the REFERENCING clause. Write the SET transition-variable statement that limits an increase to the COMMISSION column to 10%.

   SET NEWROW.COMMISSION = 1.1 * OLDROW.COMMISSION;