The bigger question
The bigger question is this: what's the best place for the SQL statements that insert, update, and delete data, especially when using object views? Assuming 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 21-3 summarizes some of the major considerations of the three techniques. Note that this table is not meant to compare these approaches for general-purpose use, but only as they apply to localizing DML on object views.
|Table 21-3. Assessment of techniques for encapsulating DML on object views|
|Consideration||PL/SQL package||Object method||INSTEAD OF trigger|
|Consistency with object-oriented approach||Potentially very good||Excellent||Potentially very good|
|Ability to modify when underlying schema changes||Excellent; can be easily altered and recompiled independently||Excellent, as of Oracle9i||Excellent|
|Risk of unexpected interactions||Low||Low||High; triggers may have unpredictable interactions with each other|
|Ease of use with client tool default functionality (specifically Oracle Developer)||Acceptable; programmer must add code for all client-side transactional triggers||Acceptable; programmer must add code for all client-side transactional triggers||Excellent for top-level types (however, there is no INSTEAD OF LOCK server-side trigger)|
|Can be turned on and off at will||No||No||Yes (by disabling and enabling the trigger)|
As you can see, there is no clear "winner." Each technique has benefits that may be of more or less importance to your application.
One important point about using INSTEAD OF triggers in view hierarchies is that you will need a separate trigger for each level of the hierarchy. When you perform DML through a subview, the subview's trigger will fire; when you perform DML through the superview, the superview's trigger will fire.
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_insertINSTEAD OF INSERT ON images_vFOR EACH ROWBEGIN /* Call a packaged procedure to perform the insert. */ manage_image.create_one(:NEW.image_id, :NEW.file_type, :NEW.file_name, :NEW.bytes, :NEW.keywords);END;
In an ideal world, developers would select an overall architecture and design approach before hurling every Oracle feature at their application. Use a feature only if it make sense for your design. I agree with Oracle's advice that if you do use triggers, you should use them in moderation.