NEW Context Variables
Go Up to Procedures and Triggers
Indicates a new column value in an INSERT or UPDATE operation. Available only in triggers.
NEW.column
| Argument | Description |
|---|---|
|
<column> |
Name of a column in the affected row |
Description: Triggers support two context variables: OLD and NEW. A NEW context variable refers to the new value of a column in an INSERT or UPDATE operation.
Context variables are often used to compare the values of a column before and after it is modified. Context variables can be used anywhere a regular variable can be used.
New values for a row can only be altered before actions. A trigger that fires after INSERT and tries to assign a value to NEW.column will have no effect. However, the actual column values are not altered until after the action, so triggers that reference values from their target tables will not see a newly inserted or updated value unless they fire after UPDATE or INSERT.
Example: The following script is a trigger that fires after the EMPLOYEE table is updated, and compares an employee’s old and new salary. If there is a change in salary, the trigger inserts an entry in the SALARY_HISTORY table.
CREATE TRIGGER SAVE_SALARY_CHANGE FOR EMPLOYEE
AFTER UPDATE AS
BEGIN
IF (OLD.SALARY <> NEW.SALARY) THEN
INSERT INTO SALARY_HISTORY
(EMP_NO, CHANGE_DATE, UPDATER_ID, OLD_SALARY,
PERCENT_CHANGE)
VALUES (OLD.EMP_NO, 'NOW', USER, OLD.SALARY,
(NEW.SALARY - OLD.SALARY) * 100 / OLD.SALARY);
END ;
See Also
For more information on creating triggers, see CREATE TRIGGER.