SET Transitional Variable

Restriction: This topic applies to Windows environments only.

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 transition-variable = 
{   {expression | NULL}
    | (transition-variable[,...]) = ({{expression | NULL} | VALUES {{expression | NULL} | ({expression | NULL}[,...])})
}

Description

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

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 triggering table. The name can identify an identity column that is defined as GENERATED BY DEFAULT but not one defined as GENERATED ALWAYS. 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.

Specifies the value to be assigned to the corresponding transition-variable. A reference to a local special register is the value of that special register at the server when the trigger body is activated.

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.

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.

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

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)

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 statement that assigns the default values to the SALARY and COMMISSION columns.

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