The Toad World Community URL is now community.toadworld.com. Don't worry -- you'll still find the same great content here. We updated our new Toad World site at www.toadworld.com with additional information for Toad products.
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:
CREATE OR REPLACE TRIGGER images_v_insert
INSTEAD OF INSERT ON images_v
FOR EACH ROW
/* 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));
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.
The table below summarizes some of the major considerations of the three techniques as they apply to localizing DML on object views.
There is no clear "winner." Each technique has benefits that may be of more or less importance to your own particular application.