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
	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.
	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.
	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 are best explained with an example. Let's use one of my favorite topics: pizza delivery! Before we can start pounding the dough, we have to put a system in place to monitor our deliveries. We will need three tables: one to track actual deliveries, one to track delivery areas, and one to track our massive fleet of drivers (remember the first rule of business— always think big!).

/* File on web: pizza_tables.sql */CREATE TABLE delivery(delivery_id NUMBER, delivery_start DATE, delivery_end DATE, area_id NUMBER, driver_id NUMBER); CREATE TABLE area (area_id NUMBER, area_desc VARCHAR2(30)); CREATE TABLE driver (driver_id NUMBER, driver_name VARCHAR2(30));

For the sake of brevity I will not create any primary or foreign keys.

We will also need three sequences to provide unique identifiers for our tables.

CREATE SEQUENCE delivery_id_seq;CREATE SEQUENCE area_id_seq;CREATE SEQUENCE driver_id_seq;

To avoid having to explain relational database design and normalization to our employees, we will simplify deliveries into a single view displaying delivery, area, and driver information.

CREATE OR REPLACE VIEW delivery_info ASSELECT d.delivery_id, d.delivery_start, d.delivery_end, a.area_desc, dr.driver_name FROM delivery d, area a, driver dr WHERE a.area_id = d.area_id AND dr.driver_id = d.driver_id;

Because our system relies heavily on this view for query functionality, why not make it available for insert, update, and delete as well? This is where INSTEAD OF triggers come into play. We need to tell the database what to do when an insert, update or delete operation occurs against the delivery_info view; in other words, we need to tell it what to do instead of trying to insert, update, or delete. Let's start with the INSERT trigger.