If you can come up with the logic of how you want Oracle to interpret a particular operation on a view, you can implement the behavior with INSTEAD OF triggers. This type of trigger is available to all Oracle users; it is not a part of the Oracle objects option.

Conceptually, INSTEAD OF triggers are very simple. You write code that the Oracle server will execute when a program performs a DML operation on the view. Unlike a conventional BEFORE or AFTER trigger, an INSTEAD OF trigger takes the place of, rather than supplements, Oracle's usual DML behavior. The INSTEAD OF trigger is a great way to make a view update-able, even if it violates the normal rules for update-able triggers. Note that you cannot use BEFORE/AFTER triggers on any type of view, even if you have defined an INSTEAD OF trigger on the view.

In the following example, applications INSERT into the images_v view view:

   /* This will fail with DUP_VAL_ON_INDEX if the images table
   || already contains a record with the new image_id.
   INSERT INTO images
      VALUES (:NEW.image_id, :NEW.file_name, :NEW.file_type,

   IF :NEW.keywords IS NOT NULL THEN
         /* Note: apparent bug prevents use of :NEW.keywords.LAST. 
         || The workaround is to store :NEW.keywords as a local 
         || variable (in this case keywords_holder.)
         keywords_holder Keyword_tab_t := :NEW.keywords;
         FOR the_keyword IN 1..keywords_holder.LAST
            INSERT INTO keywords
            VALUES (:NEW.image_id, keywords_holder(the_keyword));
         END LOOP;
   END IF;

Once we've created this INSTEAD OF trigger, we can insert a record into this object view (and hence into both underlying tables) quite easily using:

INSERT INTO images_v
VALUES (Image_t(41265, 'pigpic.jpg', 'JPG', 824, 
        Keyword_tab_t('PIG', 'BOVINE', 'FARM ANIMAL')));

This statement causes the INSTEAD OF trigger to fire, and as long as the primary key value (image_id = 41265) does not already exist, the trigger will insert the data into the appropriate tables.

Similarly, additional triggers can be written to handle updates and deletes. These triggers use the clauses INSTEAD OF UPDATE and INSTEAD OF DELETE.

When to use INSTEAD OF Triggers

One of the most important architectural decisions you will make for your object views is where to put SQL statements that insert, update, and delete data. Going on the assumption that you want to localize these operations on the server side, you have at least three choices where to put SQL statements:
  • PL/SQL packages
  • object methods
  • INSTEAD OF triggers.

The table below summarizes some of the major considerations of the three techniques as they apply to localizing DML on object views.

DML Consideration PL/SQL Package Object Method INSTEAD OF Trigger
Ability to adapt to schema changes Excellent; can be easily altered and recompiled independantly Poor, especially if object types are responsible for their own persistence. Good, but still some areas where Oracle does not automatically recompile dependant structures.
Risk of unexpected interatcions. Low Low High; triggers may have unpredictable interactions with each other.
Ease of use with client tool default functionality (specifically, Developer/2000) Acceptable; programmer must add code for all client-side transactional triggers. Acceptable; programmer must add code for all client-side transactional triggers. Excellent (however, there is no INSTEAD OF LOCK server-side trigger.)
Ability to use technique on transient objects. Very good, but not a "natural" use of packages. Excellent Theoretically possible, but why bother?
Can be turned on and off at will No No Yes (by enabling and disabling the trigger)

There is no clear "winner." Each technique has benefits that may be of more or less importance to your own particular application.