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.-'

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).

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).

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 without specifying the WITH DEFAULT clause or the NOT NULL clause, then the value inserted is NULL.
If the column was defined using the NOT NULL clause and 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.

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);

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]