In sweet memories of my ever loving brother "kutty thambi " ARUN KUMAR

Monday, August 3, 2009

Performing DML operation in join views(UPDATE)

How to update a join view ?

In order to be able to update a view which joins two or more tables, you need to use an instead of trigger.
The following example shows a view being created over the EMPLOYEES and DEPARTMENTS tables in the HR schema.

SQL> CREATE OR REPLACE VIEW empdept_view
2 (empno,ename,deptno,dname,loc)
3 AS SELECT e.employee_id,e.last_name, d.department_id,
4 d.department_name,d.location_id
5 FROM employees e, departments d
6 WHERE e.department_id=d.department_id;

View created.
We can see which columns we are able to update by looking at USER_UPDATABALE_COLUMNS for this view :
SQL> col owner format a5
SQL> col table_name format a15
SQL> col column_name format a15

SQL> SELECT * FROM user_updatable_columns
2 WHERE table_name='EMPDEPT_VIEW';

OWNER TABLE_NAME COLUMN_NAME UPD INS DEL
----- --------------- --------------- --- --- ---
HR EMPDEPT_VIEW EMPNO YES YES YES
HR EMPDEPT_VIEW ENAME YES YES YES
HR EMPDEPT_VIEW DEPTNO NO NO NO
HR EMPDEPT_VIEW DNAME NO NO NO
HR EMPDEPT_VIEW LOC NO NO NO

So this view will allow the EMPNO, ENAME columns to be updated, inserted or deleted, but not the DEPTNO, DNAME or LOC columns. The following shows an example of DML operation being carried out on the view :

First select the current data :

SQL> select * from empdept_view
2 where empno=206;

EMPNO ENAME DEPTNO DNAME LOC
----- ---------- ------ --------------- ----------
206 Gietz 110 Accounting 1700

Then update the ENAME column :

SQL> UPDATE empdept_view
2 set ename='JOHN'
3 WHERE empno=206;
1 row updated.

SQL> select * from empdept_view

2 where empno=206;

EMPNO ENAME DEPTNO DNAME LOC
----- ---------- ------ --------------- ----------
206 JOHN 110 Accounting 1700

So this view will allow the EMPNO, ENAME columns to be updated, inserted or deleted, but not the DEPTNO, DNAME or LOC columns. The following shows an example of DML operation being carried out on the view :

Then try to update the DNAME column :
SQL> update empdept_view
2 set dname = 'SOFTWARE'
3 WHERE empno=206;
set dname = 'SOFTWARE'
*
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table

So it is plain to see that we are not permitted to update the DNAME column even though we can update ENAME. We need to create a trigger.

SQL>CREATE OR REPLACE TRIGGER empdept_view_tr_up
2 INSTEAD OF UPDATE
3 ON empdept_view
4 FOR EACH ROW
5 BEGIN
6 IF (:NEW.deptno <> :OLD.deptno) THEN
7 RAISE_APPLICATION_ERROR(-20001, 'Updates are prohibited to the primary key of the DEPT table');
8 END IF;
9 IF (:NEW.dname <> :OLD.dname) OR
10 (:NEW.loc <> :OLD.loc) THEN
11 UPDATE departments
12 SET department_name = :NEW.dname,
13 location_id = :NEW.loc
14 WHERE department_id = :OLD.deptno;
15 END IF;
16 END;
17 /

Trigger created.

SQL> update empdept_view
2 set dname= 'SOFTWARE'
3 WHERE empno=206;

1 row updated.

SQL> select * from empdept_view
2 where empno=206;

EMPNO ENAME DEPTNO DNAME LOC
----- ---------- ------ --------------- ----------
206 JOHN 110 SOFTWARE 1700

And finally, having a instead of trigger for a specific DML statement on a view causes changes to USER_UPDATABLE_COLUMNS as can be seen below, however, note that it seems to imply that the DEPTNO column can be updated - you'll need to know what's inside your instead of triggers to believe everything you see in USER_UPDATABLE_COLUMNS
SQL> select * from user_updatable_columns
2 where table_name='EMPDEPT_VIEW';

OWNER TABLE_NAME COLUMN_NAME UPD INS DEL
----- --------------- --------------- --- --- ---
HR EMPDEPT_VIEW EMPNO YES YES YES
HR EMPDEPT_VIEW ENAME YES YES YES
HR EMPDEPT_VIEW DEPTNO YES NO NO
HR EMPDEPT_VIEW DNAME YES NO NO
HR EMPDEPT_VIEW LOC YES NO NO
the following final example demonstrates :

SQL> UPDATE empdept_view SET deptno=50
2 WHERE deptno=60;
UPDATE empdept_view SET deptno=50
*
ERROR at line 1:
ORA-20001: Updates are prohibited to the primary key of the DEPT table
ORA-06512: at "HR.EMPDEPT_VIEW_TR_UP", line 3
ORA-04088: error during execution of trigger 'HR.EMPDEPT_VIEW_TR_UP'

The error stack clearly shows the trigger's own error code ORA-20001 and message prohibiting you form changing the DEPARTMENTS table's primary key via the empdept_view view.

No comments:

 
Share/Bookmark