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:
- System variables.
- Functions with arguments that are scalar subexpressions.
- Scalar, but not aggregate, user-defined functions.
- Expressions referencing NEW or OLD row columns.
- Constants.
- 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 applied.
- 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.