SET as a Triggered Action Statement
A SET clause assigns a value to a column, variable, or parameter.
You can use the SET clause in a trigger definition as a triggered SQL statement if
the triggering action retains the row in the target table after its action is completed.
For example, you can specify a SET clause in UPDATE and INSERT BEFORE row triggers,
but not in a DELETE BEFORE row trigger because the DELETE statement removes a row
set from the target table.
The following rules apply to the use of the SET clause:
SET can modify values of a new transition table row, referred to as NEW ROW, but it
cannot modify the OLD ROW.
SET can use values from both the NEW ROW and OLD ROW.
Each SET clause can assign a value to only one column.
The scalar expression specified with a SET expression can contain any combination
of the following elements:
Functions with arguments that are scalar subexpressions.
Scalar, but not aggregate, user-defined functions.
Expressions referencing NEW or OLD row columns.
If a column name in the SET clause is not qualified explicitly, then the system qualifies
that column with the correlation name for NEW row.
If there is a CHECK column or referential integrity constraint on the column to which
the SET assignment clause is applied, then that constraint is enforced only on the
final value of the column after all the SET clauses of the BEFORE row trigger are
You can specify multiple SET clauses that update the same column.
When the same column is updated more than once in multiple SET clauses, the final
value assigned to the column is the value assigned by the last SET clause in the sequence,
and the earlier updates are lost.
You can assign a UDT expression to a UDT column. However, the mutator SET clause syntax
is not supported.
To work around this, use the standard non-mutator SET clause syntax with a column
reference on one side of the equal sign and a UDT expression that contains mutators
on the other side.