16.7 How do I…Issue DML commands on object views and use INSTEAD OF triggers?
Problem
I have created an object view to enable object operations on some existing relational tables. How do I insert, select, and update data in the relational tables using an object view?
Technique
The SELECT statement syntax used to extract data from an object view is not particularly noteworthy. INSERT and UPDATE statements are fairly straight-forward as well, unless the end result of the insert or update operation is to modify data residing in more than one base table. Oracle8 will not allow these operations by default; INSTEAD OF triggers provide a way for developers to overcome these limitations.
The name of this trigger type is indicative of its function. When an update state-ment fires an update INSTEAD OF trigger, for example, the trigger is executed instead of the originally issued update statement. INSTEAD OF triggers operate similarly for delete and insert operations. This type of trigger serves to resolve the ambiguity common to update operations on join views.
Steps
1. If you have not executed the scripts in the previous How-To, do so now by starting SQL*Plus, connecting as the WAITE user, and using the START command to load and execute the scripts CHP16_17.SQL, CHP16_18.SQL, CHP16_19.SQL, and CHP16_20.SQL. These scripts create two relational tables, two object types, and an object view. They are explained fully in the previous How-To.
2. Object views precipitate some changes to standard data manipulation language (DML). To see how, use the START command to load and execute the CHP16_21.SQL script. This script and its results appear in Listing 16.4.
Listing 16.4 A sample of DML statements issued against an object view
SQL> start chp16_21
SQL>
SQL> SELECT * -- 1
2 FROM empdept16;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ------------ ------- -----------
DEPT(DEPTNO, DNAME, LOC)
---------------------------------------------------------------------------
1 FRANK CEO 25-OCT-97 72500 6
DEPT_TYPE16(1, ‘IS’, ‘RALEIGH’)
SQL>
SQL> SELECT empno, ename, dept.deptno, dept.dname -- 2
2 FROM empdept16
3 WHERE dept.deptno = 1;
EMPNO
ENAME
DEPT.DEPTNO
DEPT.DNAME
----------
----------
----------
----------
1
FRANK
1
IS
SQL>
SQL> UPDATE empdept16 -- 3
2 SET ename = ‘BARBARA’
3 WHERE ename = ‘FRANK’;
1 row updated.
SQL>
SQL> INSERT INTO empdept16 (empno, ename) -- 4
2 VALUES (2, ‘JOE’);
1 row created.
SQL>
SQL> INSERT INTO empdept16 (empno, ename, dept) -- 5
(THIS WON’T WORK!)
2 VALUES (2, ‘STEVE’, dept_type16(1, null, null));
VALUES (2, ‘STEVE’, dept_type16(1, null, null))
ERROR at line 2:
ORA-01776: cannot modify more than one base table through a join view
The SQL statements are numbered in inline comments on the right side of the script. Statement 1 uses the familiar asterisk to select all columns from the object view. Statement 2 selectively extracts columns from the view, using dot notation where appropriate for the DEPT object. Statements 3 and 4 insert and update data via the view, but note that they only modify one of the underlying base tables. Statement 5 fails because it attempts to modify more than one of the underlying relational base tables. The base tables comprise a master-detail relationship and Oracle8 needs to know how to resolve any ambiguity in the update statement.
3. Use the START command to load and execute the script CHP16_22.SQL. This script creates a trigger that fires whenever a user or application issues an INSERT statement against the EMPDEPT16 object view. The script appears in Listing 16.5.
Listing 16.5 Script CHP16_22.SQL creates an insert INSTEAD OF trigger on the EMPDEPT16 object view
SQL> start chp16_22
SQL>
SQL> CREATE OR REPLACE TRIGGER insert_empdept16
2 INSTEAD OF INSERT ON empdept16
3 FOR EACH ROW
4 DECLARE
5 thecount NUMBER(4);
6 BEGIN
7
7 SELECT count(*) into thecount
8 FROM dept_table16
9 WHERE deptno = :new.dept.deptno;
10
10 if thecount = 0 then
11 INSERT INTO dept_table16
12 (deptno, dname, loc)
13 VALUES
14 (:new.dept.deptno,
15 :new.dept.dname,
16 :new.dept.loc);
17 end if;
18
18 INSERT INTO emp_table16
19 (empno, ename, job, mgr, hiredate, sal, comm, deptno)
20 VALUES
21 (:new.empno,
22 :new.ename,
23 :new.job,
24 :new.mgr,
25 :new.hiredate,
26 :new.sal,
27 :new.comm,
28 :new.dept.deptno);
29 END;
30 Input truncated to 1 characters
/ Trigger created.
All INSERT operations issued against the EMPDEPT16 object view will fire the trigger, and Oracle8 will execute the code in the trigger instead of the original INSERT statement. The SELECT statement in trigger lines 7 through 9 checks to see if the value of DEPTNO in the INSERT statement already exists in the DEPT_TABLE16 table. If it does, the trigger will not execute the INSERT statement in lines 11 through 16 (doing so would generate a constraint violation because DEPTNO is the primary key of the underlying relational table DEPT_TABLE16). The INSERT statement in lines 18 through 28 inserts the new row into the EMP_TABLE16 table.
4. Use the START command to load and execute the script CHP16_23.SQL. This script and its output appear in Figure 16.21.
Notice that the INSERT statement invokes the system-defined DEPT_TYPE16 constructor to instantiate an object of this type. The simple SELECT statement and its output indicate that the insert operation is successful.
How It Works
Step 1 creates the objects first used in How-To 16.6. Step 2 shows that some familiar DML statements are still valid against Oracle8 object views and that others, particularly INSERT statements that modify more than one underlying relational table, are not. The script in Step 3 generates an INSTEAD OF trigger for INSERT statements that users or applications issue against the EMPDEPT16 object view. The INSERT statement in Step 4 succeeds because the INSTEAD OF trigger resolves any ambiguity for the Oracle8 server.
Comments
This is only a simple example. A production application would have additional INSTEAD OF triggers to accommodate UPDATE and DELETE operations against the EMPDEPT16 object view. The existence of Oracle7-style referential integrity checking mechanisms, as in the example, can complicate INSTEAD OF trigger creation because the trigger must operate in concert with these existing constraints.