SQL Reference

SET transition-variable

The SET transition-variable statement assigns values to new transition variables. It is under transaction control.

Invocation

This statement can only be used as a triggered SQL statement in the triggered action of a BEFORE trigger whose granularity is FOR EACH ROW (see CREATE TRIGGER).

Authorization

The privileges held by the authorization ID of the creator of the trigger must include at least one of the following:

UPDATE of the columns referenced on the left hand side of the assignment and SELECT for any columns referenced on the right hand side.
CONTROL privilege on the table (subject table of the trigger)
SYSADM or DBADM authority.

To execute this statement with a row-fullselect as the right hand side of the assignment, the privileges held by the authorization ID of the creator of the trigger must also include at least one of the following for each table or view referenced:

SELECT privilege
CONTROL privilege
SYSADM or DBADM.

Syntax

>>-SET---------------------------------------------------------->
 
      .-,-------------------------------------------------------------------------------------.
      V                                                                                       |
>--------+-| transition-variable |--=--+-expression-+--------------------------------------+--+>
         |                             +-NULL-------+                                      |
         |                             '-DEFAULT----'                                      |
         |    .-,--------------------------.               .-,--------------------.        |
         |    V                            |               V              (1)     |        |
         '-(-----| transition-variable |---+---)--=--(--+----+-expression------+--+-+---)--'
                                                        |    +-NULL------------+    |
                                                        |    '-DEFAULT---------'    |
                                                        |                (2)        |
                                                        '-row-fullselect------------'
 
>--------------------------------------------------------------><
 
transition-variable
 
|---+-------------------+--column-name-------------------------->
    '-correlation-name.-'
 
>-----+--------------------------+------------------------------|
      |  .--------------------.  |
      |  V                    |  |
      '----..attribute-name---+--'

Notes:

The number of expressions, NULLs and DEFAULTs must match the number of transition-variables.
The number of columns in the select list must match the number of transition-variables.

Description

transition-variable

Identifies a column in the set of affected rows for the trigger.

correlation-name

The correlation-name given for referencing the NEW transition variables. This correlation-name must match the correlation name specified following NEW in the REFERENCING clause of the CREATE TRIGGER.

If OLD is not specified in the REFERENCING clause, the correlation-name will default to the correlation-name specified following NEW. If both NEW and OLD are specified in the REFERENCING clause, then a correlation-name is required with each column-name (SQLSTATE 42702).

column-name

Identifies the column to be updated. The column-name must identify a column of the subject table of the trigger (SQLSTATE 42703). A column must not be specified more than once (SQLSTATE 42701).

..attribute name

Specifies the attribute of a structured type that is set (referred to as an attribute assignment). The column-name specified must be defined with a user-defined structured type (SQLSTATE 428DP). The attribute-name must be an attribute of the structured type of column-name (SQLSTATE 42703). An assignment that does not involve the ..attribute name clause is referred to as a conventional assignment.

expression

Indicates the new value of the column. The expression is any expression of the type described in Expressions. The expression can not include a column function except when it occurs within a scalar fullselect (SQLSTATE 42903). An expression may contain references to OLD and NEW transition variables and must be qualified by the correlation-name to specify which transition variable (SQLSTATE 42702).

NULL

Specifies the null value and can only be specified for nullable columns (SQLSTATE 23502). NULL cannot be the value in an attribute assignment (SQLSTATE 429B9), unless it was specifically cast to the data type of the attribute.

DEFAULT

Specifies that the default value should be used based on how the corresponding column is defined in the table. The value that is inserted depends on how the column was defined.

If the column was defined using the WITH DEFAULT clause, then the value is set to the default defined for the column (see default-clause in ALTER TABLE).
If the column was defined using the IDENTITY clause, the value is generated by the database manager.
If the column was defined without specifying the WITH DEFAULT clause, the IDENTITY clause, or the NOT NULL clause, then the value is NULL.
If the column was defined using the NOT NULL clause and the IDENTITY clause is not used, or the WITH DEFAULT clause was not used or DEFAULT NULL was used, the DEFAULT keyword cannot be specified for that column (SQLSTATE 23502).

row-fullselect

A fullselect that returns a single row with the number of columns corresponding to the number of column-names specified for assignment. The values are assigned to each corresponding column-name. If the result of the row-fullselect is no rows, then null values are assigned. A row-fullselect may contain references to OLD and NEW transition variables which must be qualified by the correlation-name to specify which transition variable to use. (SQLSTATE 42702). An error is returned if there is more than one row in the result (SQLSTATE 21000).

Rules

The number of values to be assigned from expressions, NULLs and DEFAULTs or the row-fullselect must match the number of columns specified for assignment (SQLSTATE 42802).
If the statement is used in a BEFORE UPDATE trigger, the column-name specified as a transition-variable cannot be a partitioning key column (SQLSTATE 42997).

Notes

If more than one assignment is included, all the expressions and row-fullselects are evaluated before the assignments are performed. Thus references to columns in an expression or row fullselect are always the value of the transition variable prior to any assignment in the single SET transition-variable statement.
When an identity column defined as a distinct type is updated, the entire computation is done in the source type, and the result is cast to the distinct type before the value is actually assigned to the column. ¹⁰⁶
To have DB2 generate a value on a SET statement for an identity column, use the DEFAULT keyword:
```
   SET NEW.EMPNO = DEFAULT
```
In this example, NEW.EMPNO is defined as an identity column, and the value used to update this column is generated by DB2.
See INSERT for more information on consuming values of a generated sequence for an identity column.
See INSERT for more information on exceeding the maximum value for an identity column.

Examples

Example 1: Set the salary column of the row for which the trigger action is currently executing to 50000.

  SET NEW_VAR.SALARY = 50000;
 or
  SET (NEW_VAR.SALARY) = (50000);

Example 2: Set the salary and the commission column of the row for which the trigger action is currently executing to 50000 and 8000 respectively.

  SET NEW_VAR.SALARY = 50000, NEW_VAR.COMM = 8000;
 or
  SET (NEW_VAR.SALARY, NEW_VAR.COMM) = (50000, 8000);

Example 3: Set the salary and the commission column of the row for which the trigger action is currently executing to the average of the salary and of the commission of the employees of the updated row's department respectively.

  SET (NEW_VAR.SALARY, NEW_VAR.COMM)
     = (SELECT AVG(SALARY), AVG(COMM)
          FROM EMPLOYEE E
          WHERE E.WORKDEPT = NEW_VAR.WORKDEPT);

Example 4: Set the salary and the commission column of the row for which the trigger action is currently executing to 10000 and the original value of salary respectively (i.e., before the SET statement was executed).

  SET NEW_VAR.SALARY = 10000, NEW_VAR.COMM = NEW_VAR.SALARY;
 or
  SET (NEW_VAR.SALARY, NEW_VAR.COMM) = (10000, NEW_VAR.SALARY);

Footnotes:

¹⁰⁶: There is no casting of the previous value to the source type prior to the computation.

[ Top of Page | Previous Page | Next Page ]