hindol_saha

 Profile Answers by hindol_saha Questions by hindol_saha

• Apr 9th, 2007

 

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. (And in case you're wondering, you cannot use BEFORE/AFTER triggers on any type of view, even if you have defined an INSTEAD OF trigger on the view.)
CREATE OR REPLACE TRIGGER images_v_insert
INSTEAD OF INSERT ON images_v
FOR EACH ROW
BEGIN
   /* 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,
              :NEW.bytes);

   IF :NEW.keywords IS NOT NULL THEN
      DECLARE
         /* 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;
      BEGIN
         FOR the_keyword IN 1..keywords_holder.LAST
         LOOP
            INSERT INTO keywords
            VALUES (:NEW.image_id, keywords_holder(the_keyword));
         END LOOP;
       END;
   END IF;
END;

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, we can write additional triggers that handle updates and deletes. These triggers use the predictable clauses INSTEAD OF UPDATE and INSTEAD OF DELETE.

berly.george

 Profile Answers by berly.george 

• Dec 1st, 2009

 

Instead of triggers are used:
1) To control the DML operations on Views(Insert/Update/Delete)
2) Makes the non updatable views updatable