The Oracle (tm) Users' Co-Operative FAQ

How do I update a join view ?


Author's name: Norman Dunbar

Addendum: John McCabe

Author's Email: Oracle@bountifulsolutions.co.uk

Date written: 03/03/2003

Updated written: 22/08/2005

Oracle version(s): 9.2.0

I have a view which joins two tables - how can I update rows in that view ?

Back to index of questions


In order to be able to update a view which joins two or more tables, you need to use an instead of trigger. According to Tom Kyte, instead of triggers have been available since Oracle release 8.1.5 but only in Enterprise Edition. From 8.1.6 onwards, they are available in all versions. If you try to create an instead of trigger on a version of Oracle which doesn't support them, you'll simply get the error message ORA-04071: missing BEFORE or AFTER keyword. Please note that the documentation for 8.1.7 still says that Enterprise Edition is required, however, this is not the case.

The following example shows a view being created over the EMP and DEPT tables in the scott schema on a 9.2.0 database.

 
    SQL> CREATE OR REPLACE VIEW empdept_vu AS 
      2  SELECT emp.empno, emp.ename, emp.job, dept.deptno, dept.dname, dept.loc
      3  FROM emp, dept
      4  WHERE emp.deptno = dept.deptno;
    
    View created.

We can see which columns we are able to update by looking at USER_UPDATABALE_COLUMNS for this view :

 
    SQL> column owner format a15
    SQL> column table_name format a10
    SQL> column updatabale format a3
    SQL> column insertable format a3
    SQL> column deleteable format a3
    SQL> select * from user_updatable_columns
      2  where table_name = 'EMPDEPT_VU';
      
    OWNER           TABLE_NAME COLUMN_NAME                    UPD INS DEL
    --------------- ---------- ------------------------------ --- --- ---
    SCOTT           EMPDEPT_VU EMPNO                          YES YES YES
    SCOTT           EMPDEPT_VU ENAME                          YES YES YES
    SCOTT           EMPDEPT_VU JOB                            YES YES YES
    SCOTT           EMPDEPT_VU DEPTNO                         NO  NO  NO
    SCOTT           EMPDEPT_VU DNAME                          NO  NO  NO
    SCOTT           EMPDEPT_VU LOC                            NO  NO  NO

So this view will allow the EMPNO, ENAME and JOB columns to be updated, inserted or deleted, but not the DPTNO, 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_vu
      2  where empno = 7499;
    
         EMPNO ENAME      JOB           DEPTNO DNAME          LOC
    ---------- ---------- --------- ---------- -------------- -------------
          7499 ALLEN      SALESMAN          30 SALES          CHICAGO

Then update the ENAME column :

 
    SQL> update empdept_vu
      2  set ename = 'WAS_ALLEN'
      3  where empno = 7499
    
    1 row updated.
    
    SQL> select * from empdept_vu
      2  where empno = 7499;

Then try to update the DNAME column :

 
    SQL> update empdept_vu
      2  set dname = 'WAS_SALES'
      3  where empno = 7499
    set dname = 'WAS_SALES'
        *
    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 which will do the proper updating for us in the event of a requirement to update any columns of the view which USER_UPDATABLE_COLUMNS shows as being non-updatable. The following brief example allows the view's columns taken from the DEPT table to be updated, however, it explicitly prohibits updates to the primary key - this would not be a good thing to do especially as there is a foreign key constraint on the EMP table which references the DEPTNO column of the DEPT table.

 
    SQL> CREATE OR REPLACE TRIGGER empdept_vu_u
      2  INSTEAD OF UPDATE
      3  ON empdept_vu
      4  FOR EACH ROW
      5  BEGIN
      6      -- Prohibit updates to the primary key
      7      IF (:NEW.deptno <> :OLD.deptno) THEN
      8          RAISE_APPLICATION_ERROR(-20001, 'Updates are prohibited to the primary key of the DEPT table');
      9      END IF;
     10       
     11      -- We can update DNAME and LOC however
     12      IF (:NEW.dname <> :OLD.dname) OR
     13         (:NEW.loc <> :OLD.loc) THEN
     14          UPDATE dept  
     15          SET    dname = :NEW.dname,
     16                 loc = :NEW.loc
     17          WHERE deptno = :OLD.deptno;
     18      END IF;
     19  END;
     20  /
    
    Trigger created.

If we now try to run our failing UPDATE DML on the view again, we should see a different result :

 
    SQL> update empdept_vu
      2  set dname = 'WAS_SALES'
      3  where empno = 7499
      4  ;
    
    1 row updated.
    
    SQL> select * from empdept_vu
      2  where empno = 7499;
    
         EMPNO ENAME      JOB           DEPTNO DNAME          LOC
    ---------- ---------- --------- ---------- -------------- -------------
          7499 WAS_ALLEN  SALESMAN          30 WAS_SALES      CHICAGO

It is a simple matter to create INSERT and/or DELETE instead of triggers if these are required. I shall leave this as an 'exercise for the reader' as they say.

Bear in mind when writing these triggers, that you might not want the users to update all the fields. If so, you will have to explicitly check for changes to that field and disallow them - either by raising an error as I have done, or simply by assigning the :OLD_column_name to :NEW_column_name inside the trigger. I prefer to raise an error as I then don't have to try to track down 'missing' updates at some point in the future.

Another point to bear in mind when creating instead of triggers is what exactly do you want to do in the event of an insert or delete or update? It might not always be obvious. In the above example, I'm allowing the DNAME and LOC columns to be updated but not the DEPTNO column. This makes sense for an update, but what would really be required when inserting a new row into the view ? Should I insert into the EMP table only, or into both tables if the user supplies a new DNAME and DEPTNO etc etc.

It can be quite tricky to think through what is required, so take care when using instead of triggers and make sure you know exactly what should be happening for the individual DML statements.

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_VU';
    
    OWNER           TABLE_NAME COLUMN_NAME                    UPD INS DEL
    --------------- ---------- ------------------------------ --- --- ---
    SCOTT           EMPDEPT_VU EMPNO                          YES YES YES
    SCOTT           EMPDEPT_VU ENAME                          YES YES YES
    SCOTT           EMPDEPT_VU JOB                            YES YES YES
    SCOTT           EMPDEPT_VU DEPTNO                         YES NO  NO
    SCOTT           EMPDEPT_VU DNAME                          YES NO  NO
    SCOTT           EMPDEPT_VU LOC                            YES NO  NO
    
    6 rows selected.

Putting an instead of trigger on a view for an INSERT changes the USER_UPDATABALE_COLUMNS to YES for all columns, even if the trigger refuses to allow updates to one or more of those columns, as the following final example demonstrates :

 
    SQL> update empdept_vu
      2  set deptno = 66
      3  where empno = 7499
      4*
    update empdept_vu
           *
    ERROR at line 1:
    ORA-20001: Updates are prohibited to the primary key of the DEPT table
    ORA-06512: at "SCOTT.EMPDEPT_VU_U", line 4
    ORA-04088: error during execution of trigger 'SCOTT.EMPDEPT_VU_U'

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

 


Addendum July 2005 – John McCabe

Having just implemented a pair of instead of triggers it may be worth noting on your site that a CREATE OR REPLACE VIEW even when unchanged will cause oracle to drop the instead of trigger (silently).

It is handy therefore to
a) Put the trigger functionality in elsewhere.
b) Have the instead of trigger call that external code.
c) Have a quick and easy way of reinstating the trigger.

Simply recompiling the view is OK

In a production environment this should not normally be a problem - but in development it was a royal pain!


Further reading:

Oracle9i Application Developer's Guide - Fundamentals. Chapter 15 - Triggers.

Beginning Oracle Programming - Dillon, Beck & Kyte. Chapter 15 - Triggers.


Back to top

Back to index of questions