Trigger variables (PL/SQL)

NEW and OLD are special variables that you can use with PL/SQL triggers without explicitly defining them.

• NEW is a pseudo-record name that refers to the new table row for insert and update operations in row-level triggers. Its usage is :NEW.column, where column is the name of a column in the table on which the trigger is defined.
  • When used in a before row-level trigger, the initial content of :NEW.column is the column value in the new row that is to be inserted or in the row that is to replace the old row.
  • When used in an after row-level trigger, the new column value has already been stored in the table.
  • When a trigger is activated by a DELETE operation, the :NEW.column used in that trigger is null.

  In the trigger code block, :NEW.column can be used like any other variable. If a value is assigned to :NEW.column in the code block of a before row-level trigger, the assigned value is used in the inserted or updated row.

• OLD is a pseudo-record name that refers to the old table row for update and delete operations in row-level triggers. Its usage is :OLD.column, where column is the name of a column in the table on which the trigger is defined.
  • When used in a before row-level trigger, the initial content of :OLD.column is the column value in the row that is to be deleted or in the old row that is to be replaced by the new row.
  • When used in an after row-level trigger, the old column value is no longer stored in the table.
  • When a trigger is activated by an INSERT operation, the :OLD.column used in that trigger is null.

  In the trigger code block, :OLD.column can be used like any other variable. If a value is assigned to :OLD.column in the code block of a before row-level trigger, the assigned value has no affect on the action of the trigger.