Oracle PL/SQL Programming, Third Edition

INSTEAD OF Triggers

INSTEAD OF triggers control insert, update, and delete operations on views, not tables. They can be used to make non-updateable views updateable and to override the default behavior of views that are updateable.

Creating an INSTEAD OF Trigger

To create (or replace) an INSTEAD OF trigger, use the syntax shown here:

 1  CREATE [OR REPLACE TRIGGER] trigger_name
 2  INTEAD OF operation
 3  ON view name
 4  FOR EACH ROW
 5  BEGIN
 6    ... code goes here ...
 7  END;

The table contains an explanation of this code:

Line(s)	Description
1	States that a trigger is to be created with the unique name supplied. Specifying OR REPLACE is optional. If the trigger exists and REPLACE is not specified, then your attempt to create the trigger anew will result in an ORA-4081 error.
2	This is where we see differences between INSTEAD OF triggers and other types of triggers. Because INSTEAD OF triggers aren’t really triggered by an event, we don’t need to specify AFTER or BEFORE or provide an event name. What we do specify is the operation that the trigger is to fire in place of (or instead of). Stating INSTEAD OF followed by one of INSERT, UPDATE, or DELETE accomplishes this.
3	This line is somewhat like the corresponding line for DDL and database event triggers in that the keyword ON is specified. The similarities end there: instead of specifying DATABASE or SCHEMA, we provide the name of the view to which the trigger is to apply.
4-7	Contains standard PL/SQL code.

INSTEAD OF triggers ...