INSTEAD-OF Trigger Views
to Support Application Development
Overview
Views are an important part of application development. Since Oracle 7.3, we quickly recognized the importance of using Oracle’s updateable view feature. An updateable view allows you to join several tables and perform updates against the driving table. For example, if you join EMP and DEPT in the traditional way and display columns from both tables, DML operations are possible against EMP but not DEPT.
For traditional relational database designs, this is enough functionality. For example, in a typical Forms application, when you are basing a block on a table, the additional columns that you want to display are lookups from other tables and can therefore be easily supported using traditional updateable views. These views are built using a combination of joins and outer joins or, in extreme cases, looking up additional information through functions embedded in the views. Under no circumstances should post query triggers be used to support this functionality. Post query triggers cause unnecessary network traffic and also embed the logic in the application rather than in the database or somewhere else where it can easily be reused.
What happens in a situation where the information you want to display in the block requires a query that is so complex that your ability to maintain (insert, update, delete) that information using a simple updateable view is eliminated? The updateable views are relatively restrictive. Only a single table can be updated. Joins must be created carefully and based on Foreign Key constraints in the database. No set operators such as UNION or MINUS can be used. For these reasons, it is common to end up with a block that cannot be updated as required. How do most developers handle this situation?
a) By placing complex logic in the form (WHEN-VALIDATE-ITEM triggers)
b) By writing procedures that access Forms’ ability to replace the Insert, Update, Delete routines and place that logic in the form
These practices are just as undesirable as using POST-QUERY triggers. The logic is in the wrong place and is not reusable.
The INSTEAD-OF trigger views feature was introduced by Oracle in version 8.15. This feature enables developers to create views on single or multiple tables or any other view imaginable by writing INSTEAD-OF triggers that tell the view how to behave when Inserts, Updates or Deletes are issued. Peter Koletzke and I first wrote about this feature in our Oracle Press book Oracle Developer: Advanced Forms & Reports (2000). At the time, we gave the feature relatively brief mention because we believed that most of the systems we were building included blocks based on traditional updateable views, which allow updates to a single table. Now, there is a good reason to look more closely at INSTEAD-OF trigger views.
Keeping up with a changing development environment
As an industry, we are currently in a state of flux with respect to application development. No development team should be complacent in believing that their application development environment is totally stable. Every prudent developer should be encapsulating as much of the logic as possible and placing it in the database rather than in application code. This approach has two benefits:
1. It is a sounder from a theoretical perspective, as well as helping to create a better-organized development environment. Structures are more easily reused and the applications themselves are smaller and easier to maintain.
2. It protects against the contingency of changing products and allows multiple database interfaces (such as adding a JSP application for web access) to be built very quickly since the structural logic is built into the database rather than the applications.
The world of the Internet is still in flux and it is not clear what the emerging winner will be with respect to application development. It is therefore a prudent strategy to place as little program logic as possible in your applications against the eventuality of a change in the future development environment. INSTEAD-OF triggers fit perfectly into such a strategy.
Generic Modeling Techniques
Using generic modeling techniques makes the database much more flexible and adaptable to changing business requirements. However, there are disadvantages to this strategy. One of the problems with flexible data models is that they are harder for developers to understand. A generic data model may include a recursive, typed Org. Unit structure but most developers would rather see tables labeled “Company,” “Division” and “Department.”
By using INSTEAD-OF trigger views, we can have the best of both worlds: a generic Org. Unit structure that senior developers can write flexible applications against and views that look more like traditional tables. Where appropriate, the work that would normally require several applications can be replaced with a single, flexible one. However, such flexible applications are usually difficult to write and may actually involve poor design practices since much of the logic ends up in the application, which defines the structure of the logical tables. For example, if the attributes associated with departments were significantly different from the attributes associated with the corporation, the flexible applications would have to display different attributes depending upon the type of organization unit.
Traditionally, this logic is written in the application. The alternative approach is to use INSTEAD-OF triggers views for each logical table. Using this strategy, developers have two options:
1. Write applications directly against the generic structure
2. Take advantage of specific views where appropriate.
The INSTEAD-OF trigger view feature can now be very useful in systems based upon generic data models.
Using INSTEAD-OF Triggers
For traditionally built systems using standard data modeling methods, INSTEAD-OF triggers should still be a rarely invoked feature. However, for the last several years, we have been building systems based on increasingly abstract data models. In such abstract structures, the logical table that the developer wants to build against is actually stored in two or more physical tables. Coding against such abstract structures is not only difficult but presents the user with a non-intuitive user interface. By using INSTEAD-OF trigger views, we can give the developer a view that looks exactly like the table they wished they had in the database while simultaneously allowing them to build applications very quickly.
From a conceptual viewpoint, this is a more powerful approach. Complex display and update logic is encapsulated into a database object where it logically belongs that can then be accessed by a relatively thin application. Given the current state of flux with respect to our development environment, even if you are committed to staying within the Oracle product environment, choices must be made between Developer, JDeveloper and Web DB (now called Oracle Portal) for lighter applications.
How do INSTEAD-OF trigger views work?
An INSTEAD-OF trigger is nothing more than a PL/SQL procedure that uses the columns of the view as the input parameters. You write a straightforward PL/SQL procedure referencing columns of views as input parameters using :NEW, :OLD prefixes common to any triggers. Since you are writing PL/SQL procedures, you can make your procedures do anything. You can fire off Insert, Update and Delete statements to any table in the database. This is true whether you are writing an Insert, Update or Delete trigger. There are no restrictions. You can write an INSTEAD-OF trigger view in a simple text editor and run it through SQL*Plus, or instead, build it in a product such as SQL Navigator.
The syntax for an INSTEAD-OF trigger view is quite simple. The first step is to build the underlying query. As mentioned above, there are no restrictions in the construction of the view. Set operations (union, intersect), embedded functions, outer joins, even Cartesian products are all possible. Once you are satisfied that your view returns the correct information, you need to build the INSTEAD-OF trigger.
NOTE: Be careful using third party products. In SQL navigator, once you have built an INSTEAD-OF trigger view, if you add a column to the view and save it, the product will drop all INSTEAD-OF triggers associated with that view.
To edit INSTEAD-OF trigger views, first extract the DDL using SQL Navigator, edit the extracted code and rerun it. This method is not too time consuming and works reliably.
Example of a complex INSTEAD-OF Trigger View
At Dulcian, INSTEAD-OF trigger views are being used in conjunction with our object-oriented business rules approach. Rather than updating attributes directly, these views call procedures that command our UML engine to actually perform the DML operations.
From the generated code below, it is evident that requiring developers to build applications using this kind of logic would be quite difficult. By using INSTEAD-OF trigger views, simple views that hide the complexity required by our generic system are provided to traditional developers so that they can interact with the systems we design without necessarily understanding or even being aware of the underlying code.
-- Start of DDL script for GLBOOKDTL
-- Generated 14-Apr-01 2:22:09 pm
-- from java16mat-UML5:1
-- Drop the old instance of GLBOOKDTL
DROP VIEW glbookdtl
/
-- View GLBOOKDTL
CREATE OR REPLACE VIEW glbookdtl (
glbookdtl_oid,
glbookdtl_dsp,
activ_yn,
glbookdtlsdate,
glbookdtledate,
glorder,
drcrind,
drcrind_dsp,
glaccttype,
glaccttype_dsp,
description,
caption,
note,
name,
basedonfnclbook,
basedonfnclbook_dsp,
glbudgbook_oid,
glbudgbook_dsp,
glfnclbook_oid,
glfnclbook_dsp,
glrptbook_oid,
glrptbook_dsp,
glacct_oid,
glacct_dsp,
glbookdtl_rfk,
glbookdtl_dsr,
scccorp_oid,
scccorp_dsp,
scccorpgroup_oid,
scccorpgroup_dsp,
sccdept_oid,
sccdept_dsp,
sccjob_oid,
sccjob_dsp )
AS
select
GlBookDtl_OID
, uml.GetObjectDisplay('GlBookDtl',GlBookDtl_OID) GlBookDtl_DSP
, decode(nvl(to_char(GlBookDtlEDate,'YYYY'),'Y'),'Y','Y','N') activ_yn
, GlBookDtlSDate
, GlBookDtlEDate
, GlOrder
, uml_rep.GetRefCode(DrCrInd) DrCrInd
, uml_rep.GetRefValue(DrCrInd) DrCrInd_DSP
, uml_rep.GetRefCode(GlAcctType) GlAcctType
, uml_rep.GetRefValue(GlAcctType) GlAcctType_DSP
, Description
, Caption
, Note
, Name
, BASEDONFNCLBOOK
--foreign key lookups
, uml.GetObjectDisplay('GlFnclBook',BASEDONFNCLBOOK) BASEDONFNCLBOOK_DSP
, GlBudgBook_OID
, uml.GetObjectDisplay('GlBudgBook',GlBudgBook_OID) GlBudgBook_DSP
, GlFnclBook_OID
, uml.GetObjectDisplay('GlFnclBook',GlFnclBook_OID) GlFnclBook_DSP
, GlRptBook_OID
, uml.GetObjectDisplay('GlRptBook',GlRptBook_OID) GlRptBook_DSP
, GlAcct_OID
, uml.GetObjectDisplay('GlAcct',GlAcct_OID) GlAcct_DSP
, GlBookDtl_RFK
, uml.GetObjectDisplay('GlBookDtl',GlBookDtl_RFK) GlBookDtl_DSR
, SccCorp_OID
, uml.GetObjectDisplay('SccCorp',SccCorp_OID) SccCorp_DSP
, SccCorpGroup_OID
, uml.GetObjectDisplay('SccCorpGroup',SccCorpGroup_OID) SccCorpGroup_DSP
, SccDept_OID
, uml.GetObjectDisplay('SccDept',SccDept_OID) SccDept_DSP
, SccJob_OID
, uml.GetObjectDisplay('SccJob',SccJob_OID) SccJob_DSP
from t_GlBookDtl
/
-- Triggers for GLBOOKDTL
CREATE OR REPLACE TRIGGER glbookdtl_del
INSTEAD OF DELETE
ON glbookdtl
REFERENCING NEW AS NEW OLD AS OLD
declare
v1 number;
begin
uml.delete_object('GlBookDtl', :old.GlBookDtl_OID, uml.remove);
uml_view.auto_post;
end;
/
CREATE OR REPLACE TRIGGER glbookdtl_ins
INSTEAD OF INSERT
ON glbookdtl
REFERENCING NEW AS NEW OLD AS OLD
declare
v1 number;
v2 number;
class_ref2 uml_rule_class.class_cd%type;
v3 number;
class_ref3 uml_rule_class.class_cd%type;
end_oid number;
head_oid number;
e_no_create_inactive_object exception;
e_no_update_display_col exception;
begin
if :new.GlBookDtl_DSP is not null then
raise e_no_update_display_col;
end if;
if :new.GlBookDtl_OID is not null then
uml.create_object('GlBookDtl', v1, :new.GlBookDtl_OID);
uml_view.add_new_object(:new.GlBookDtl_OID,'GlBookDtl', v1);
else
uml.create_object('GlBookDtl', v1);
end if;
if :new.activ_yn = 'N' or :new.GlBookDtlEDate is not null then
raise e_no_create_inactive_object;
end if;
uml.create_assoc('OBJECTS', v1, 'GlBookElemElem', class_ref2, v2);
end if;
--... other attributes handled similarly
--...
--...
uml_view.auto_post;
exception
when e_no_create_inactive_object then
uml.manually_delete_bundle;
raise_application_error(-20900, 'Cannot create an inactive object');
when e_no_update_display_col then
uml.manually_delete_bundle;
raise_application_error(-20905, 'Cannot specify value for display column');
when others then
uml.manually_delete_bundle;
raise;
end;
/
CREATE OR REPLACE TRIGGER glbookdtl_upd
INSTEAD OF UPDATE
ON glbookdtl
REFERENCING NEW AS NEW OLD AS OLD
declare
v1 number;
class_ref uml_rule_class.class_cd%type;
v2 number;
class_ref2 uml_rule_class.class_cd%type;
e_no_update_object_id exception;
e_no_update_assoc_class exception;
e_no_update_display_col exception;
e_continue exception;
begin
---similar to insert trigger
end;
/
-- End of DDL script for GLBOOKDTL
Issues to Consider When Using INSTEAD-OF Trigger Views
Writing INSTEAD-OF triggers is not as complex as it may seem. In traditional systems, using these triggers does not involve locking at all. An example is an INSTEAD-OF trigger view that is a union of a few tables with very similar structures such as separate tables for Contractor and Full-Time Employees. If you are only manipulating a well-defined record in one table, there will usually be no locking problems when using INSTEAD-OF trigger views with this type of structure.
Locking
Since an INSTEAD-OF trigger view is simple a view with three procedures with special names added to it, all of the normal locking that Oracle does is disabled by these procedures. You must be very careful when writing these triggers so that you do not cause deadlock or other similar problems. If you are working in Oracle Forms, you should handle locking prior to firing Update or Delete procedures by writing locking code in the ON-LOCK trigger.
Depending upon the type of INSTEAD-OF trigger you are building, locking may or may not be an issue; however, when it is relevant, it will be a significant factor to take into consideration. For example, if you are working with a generic structure, you can create a view to hide this structure from the developers as shown in the following data model.
Figure 1: Example generic structure
The accompanying code is as follows:
SELECT
attribval.value Type,
Project_ID,
Project_Name
FROM
Project Proj,
Attribute Attrib,
Attribute_value Attribvalue,
WHERE
Proj.Project_ID =
Attribvalue.Project_ID
AND Attrib.Attribute_ID =
Attribvalue.Attribute_ID
AND Attrib.Name = ‘Type’
In this example, the model designers have abstracted the Type column out of the PROJECT table into an Attribute/Attribute Value table. (This is not necessarily a good data model but is taken from a real data model against which I was asked to build an application).
If you are trying to write the INSTEAD-OF trigger and you are requested to change the Project Type, two interesting issues arise:
1. What is the algorithm for changing the Type? Do we delete the record from the Attribute Value table and create a new one? Or do we first detect whether there ever was a value for the type attribute and then modify it?
2. In either case, what record(s) should be locked while this operation is being executed?
When using models like the one shown in Figure 1, you should not think in terms of locking records. Think about locking the logical object. If you choose the algorithm involving deleting the record from the Attribute Value table and reinserting a new one, what will prevent someone from modifying the project record while you are performing this operation? This would make it impossible for you to modify the project record simultaneously. For the course of this operation, the project must be locked even though you are not touching the Project table.
NOTE: When dealing with generic structures, before modifying any objects, the safest method is to lock the whole object. This is frequently not necessary but after careful consideration of attribute-level locking (very difficult), this seems to be the easiest solution. You do not need to lock all of the records. You merely need to use “select for update” on the project record in the Project table. As long as you hold the lock on the project, you may manipulate any portion of it.
Using INSTEAD-OF trigger views to avoid mutating tables
Mutating tables are problematic for anyone who has tried to write any type of complex trigger in an Oracle database. A mutating table occurs whenever you try to query the table that you are modifying. The only place where you will not encounter mutating tables is with a BEFORE-INSERT trigger. Since most triggers also need to deal with updates, mutating tables are a common and vexing problem.
The traditional solutions to the problem of mutating tables are not very elegant. Often, it entails moving the trigger logic into the applications to avoid the issue altogether. However, INSTEAD-OF trigger views provide wonderful functionality and allow developers to easily avoid mutating tables.
CAUTION: Be aware that the mutating table error arises, not because of Oracle’s inability to allow you to write reasonable code, but, instead, serves as a message that you are doing something that could potentially corrupt the data in your database and also does not make logical sense. Moving the logic causing this type of error into an INSTEAD-OF trigger view removes the mutating table error but puts the entire database at risk. Use care when moving code into INSTEAD-OF trigger views.
Another problem associated with using INSTEAD-OF trigger views to avoid mutating tables is that there is no guarantee that the view rather than the underlying table will actually be used. If the table is used, the trigger will not fire at all. This problem can be avoided by limiting access to the physical tables. However, most developers do not operate in a sufficiently controlled environment to guarantee that no inserts, modifications or deletions are ever made to the tables in question. The best solution in most environments involves the following:
· Renaming the underlying physical table using a precise naming convention that flags relevant tables as unsuitable for Insert/Modify/Delete operations. For example, use a Q_ prefix to indicate a query-only table with an associated view to handle all data modifying DML operations.
· Moving the table into a new schema
While these measures do not guarantee that some developer won’t build an inappropriate application, they do create sufficient impediments to decrease the likelihood of the applications accessing the actual tables upon which the views are based.
Querying INSTEAD-OF Trigger Views
Querying INSTEAD-OF trigger views is no different from querying regular views. However, care must be taken when joining views. If your views involve multi-table joins or any set operator (UNION - *) and you join one view to another view, the cost-based optimizer will decide to perform full table scans.
Conventional wisdom therefore dictates that you should not write queries that join views. This is not always true. If each of the views being joined is based on a single table, joins will usually work as expected. For example, using a view based on EMP, a view based on DEPT, and a query joining EMP_VIEW to DEPT_VIEW, the index on DEPTNO will usually be used. However, this does not always occur. If you use an outer join on the views in the query, you would expect the index to be used and the query to run without problems; but, it doesn’t work properly. Full table scans and poor performance result. If you run the exact same query with a join on the tables rather than the views, it will work as expected.
One would think that by restricting the view to querying a single table, the cost-based optimizer could intelligently process queries using views. Unfortunately, this is not the case.
Conclusion
INSTEAD-OF trigger views should probably not be used frequently, if at all, in most traditional systems. For systems not built using generic data modeling principles, the use of INSTEAD-OF trigger views should remain quite rare. (1-3% of the views in the system.) However, INSTEAD-OF trigger views are useful tools for encapsulating Insert/Update/Delete operation complexity in the database. This provides, not only a powerful way of managing this logic but also places the logic where overall system performance is likely to be maximized.
About the Author
Dr. Paul Dorsey is the founder and President of Dulcian, Inc. (www.dulcian.com), an Oracle consulting firm specializing in client/server and web custom application development. Paul is co-author with Peter Koletzke of The JDeveloper3 Handbook (2001), Oracle Developer: Advanced Forms & Reports (2000), and The Oracle Designer Handbook (1999), and with Joseph R. Hudicka of Oracle8 Design Using UML Object Modeling (1999), all from Oracle Press. Paul is the Executive Editor of SELECT Magazine. He is the President of the New York Oracle Users’ Group and a frequent speaker at Oracle conferences and user group meetings.