Oracle 9i Database Triggers Page - 2

Handling multiple situations

A trigger can be used to handle multiple situations as shown in the following example. By using conditional predicates UPDATING, INSERTING, or DELETING we can handle each situation.

CORRELATION NAMES

While using row triggers, the trigger action statement can access column values of the row that is being processed currently. This is done using correlation names. There exist two correlation names for every column of the table, one for the column old value and the other for its new value. We use qualifier NEW with column name for new values and qualifier OLD is used to refer old value of the column.

Example:

IF :new.sal < :old.sal THEN
……

The REFERENCING option is used to avoid name conflicts between correlation names and table names. For example if you are using a table by name new or old with field names say SNO, NAME (though it is a very rare situation) then the ambiguity arises. To avoid this we use REFERENCING option.

Mutating Table: is a table that is presently under modification by INSERT, UPDATE, or DELETE statement, or a table that has referential integrity constraint with DELETE CASCADE OPTION.

Constraining Table: is a table, which is to be read by triggering statement either directly or indirectly.

In the above example when we tried to delete a row using SQL statement (Mutating Table) , which fires AFTER DELETE trigger. The body of this trigger is having a select statement that tries to read the table. This operation is not allowed by oracle. Hence we received a runtime error and total action is rolled back by Oracle. (The row is not deleted)

Download Sample Code 1

Download Sample Code 2

Download Sample Code 3