ORACLE DBA: 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

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.