Accessing Column Values in Row Triggers

Within a trigger body of a row trigger, the PL/SQL code and SQL statements have access to the old and new column values of the current row affected by the triggering statement. Two correlation names exist for every column of the table being modified. There is one for the old column value and one for the new column value. These columns in the table are identified by :OLD.colum_name and :NEW.column_name. The use of :NEW and :OLD is shown in Example: Creating a Database Trigger WIth the AFTER Option and Example: Creating a Database Trigger With the BEFORE Option.

Depending on the type of triggering statement, certain correlation names might not have any meaning:

A trigger fired by an INSERT statement has meaningful access to new column values only. Because the row is being created by the INSERT operation, the old values are null.
A trigger fired by an UPDATE statement has access to both old and new column values for both BEFORE and AFTER row triggers.
A trigger fired by a DELETE statement has meaningful access to old (:OLD) column values only. Because the row no longer exists after the row is deleted, the new (:NEW) values are NULL and cannot be modified.

Old and new values are available in both BEFORE and AFTER row triggers. A new column value can be assigned in a BEFORE row trigger, but not in an AFTER row trigger (because the triggering statement takes effect before an AFTER row trigger is fired). If a BEFORE row trigger changes the value of NEW.column, then an AFTER row trigger fired by the same statement sees the change assigned by the BEFORE row trigger.

Correlation names can also be used in the Boolean expression of a WHEN clause. A colon (:) must precede the OLD and NEW qualifiers when they are used in a trigger body, but a colon is not allowed when using the qualifiers in the WHEN clause.