NEW Context Variables
Go Up to Procedures and Triggers
Indicates a new column value in an
UPDATE operation. Available only in triggers.
Name of a column in the affected row
Description: Triggers support two context variables:
NEW context variable refers to the new value of a column in an
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
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
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 ;
For more information on creating triggers, see CREATE TRIGGER.