[Chapter 20] 20.2 INSTEAD OF Triggers

20.2 INSTEAD OF Triggers

Some conventional views are "inherently modifiable." For example, even Oracle Version 6 allowed updates through a view of a single table which uses no aggregation clauses such as GROUP BY. While Oracle7 added to the family of modifiable views, even in Oracle8 there is still a class of views which are "inherently unmodifiable" if you limit yourself to standard SQL.

However, in Oracle8, if you can come up with the logic of how you want Oracle to interpret a particular operation on a view -- however wacky that view might be -- you can implement the behavior with INSTEAD OF triggers. Happily, this new type of trigger is available to all Oracle8 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. (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.)

For example, to allow applications to INSERT into our images_v view, we could write the following trigger:

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.

20.2.1 INSTEAD OF Triggers: To Use or Not to Use?

Before launching headlong into the business of updating complex views with triggers, let's step back and look at the bigger picture. Do we really want to use INSTEAD OF triggers in an Oracle environment? Particularly if we are migrating toward an object approach, isn't this new feature just a relational "throwback" which facilitates a free-for-all in which any application can perform DML?

Yes and no.

Let's consider arguments on both sides, and come up with some considerations so you can decide what's best for your application.

20.2.1.1 The "don't use" argument

On the one hand, you could use tools such as packages and methods to provide a more comprehensive technique than triggers for encapsulating DML. It is nearly trivial to take the logic from our INSTEAD OF trigger and put it into an alternate PL/SQL construct which has more universal application. In other words, if you standardize on some combination of packages and object methods as the means of performing DML, you could keep your environment consistent without using view triggers. You might conclude that view triggers are just another variable in an increasingly complex standards equation.

Moreover, even Oracle cautions against the "excessive use" of triggers, because they can cause "complex interdependencies." Imagine if your INSTEAD OF triggers performed DML on tables which had other triggers, which performed DML on still other tables...it's easy to see how this could get confusing.

20.2.1.2 The "use" argument

On the other hand, you can put much of the necessary logic into an INSTEAD OF trigger that you would normally put into a package or method body. Doing so in combination with a proper set of privilege restrictions could protect your data just as well as, or even better than, methods or packages.

What's more, if you use a client tool such as Oracle Forms, INSTEAD OF triggers allow you to use much more of the product's default functionality when you create a Forms "block" against a view rather than a table. This fact alone could make object views wildly popular.

Finally, if you use OCI, a more significant factor is that INSTEAD OF triggers are required if the object view is not inherently modifiable and you want to be able to easily "flush" cached object view data back to the server.

20.2.1.3 What to do?

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: PL/SQL packages, object methods, and INSTEAD OF triggers.

Table 20.1 summarizes some of the major considerations of the three techniques. This table is not meant to compare these approaches "in general" but only as they apply to localizing DML on object views.

Table 20.1: Assessment of Techniques for Encapsulating 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 independently	Poor, especially if object types are responsible for their own persistence	Good, but still some areas where Oracle does not automatically recompile dependent structures
Risk of unexpected interactions	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 disabling and enabling the trigger)

Chapter 18 discussed at some length architectural considerations of packages versus methods. While those considerations also apply to object views, we now need to compare packages and methods with INSTEAD OF triggers.

As you can see, there is no clear "winner." Each technique has benefits that may be of more or less importance to your own particular application.

And of course, you may decide that INSTEAD OF triggers make sense in combination with PL/SQL packages and/or object methods to provide layers of encapsulation. For example:

CREATE OR REPLACE TRIGGER images_v_insert
INSTEAD OF INSERT ON images_v
FOR EACH ROW
BEGIN
   /* Call a packaged procedure to perform the insertion.
   || (The called procedure is not presented in the text.)
   */
   manage_image.create_one(:NEW.image_id, :NEW.file_type,
      :NEW.file_name, :NEW.bytes, :NEW.keywords);
END;

In an ideal world, you will select an architecture and design approach before hurling every Oracle feature at your application. Use a feature if it make sense for your architectural approach. I tend to agree with Oracle's advice that if you do use triggers, you should use them in moderation.