14.4. Trigger Variables

In the trigger code block, several special variables are available for use.

NEW

NEW is a pseudo-record name that refers to the new table row for insert and update operations in row-level triggers. This variable is not applicable in statement-level triggers and in delete operations of 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.

The initial content of :NEW.column is the value in the named column of the new row to be inserted or of the new row that is to replace the old one when used in a before row-level trigger. When used in an after row-level trigger, this value has already been stored in the table since the action has already occurred on the affected row.

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 will be used in the new inserted or updated row.

OLD

OLD is a pseudo-record name that refers to the old table row for update and delete operations in row-level triggers. This variable is not applicable in statement-level triggers and in insert operations of 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.

The initial content of :OLD.column is the value in the named column of the row to be deleted or of the old row that is to replaced by the new one when used in a before row-level trigger. When used in an after row-level trigger, this value is no longer stored in the table since the action has already occurred on the affected row.

In the trigger code block, :OLD.column can be used like any other variable. Assigning a value to :OLD.column, has no affect on the action of the trigger.

INSERTING

INSERTING is a conditional expression that returns true if an insert operation fired the trigger, otherwise it returns false.

UPDATING

UPDATING is a conditional expression that returns true if an update operation fired the trigger, otherwise it returns false.

DELETING

DELETING is a conditional expression that returns true if a delete operation fired the trigger, otherwise it returns false.