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.

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

2wheretable_name = 'EMPDEPT_VU';

OWNERTABLE_NAME COLUMN_NAMEUPD INS DEL

--------------- ---------- ------------------------------ --- --- ---

SCOTTEMPDEPT_VU EMPNOYES YESYES

SCOTTEMPDEPT_VU ENAMEYES YESYES

SCOTTEMPDEPT_VU JOBYES YESYES

SCOTTEMPDEPT_VU DEPTNONONONO

SCOTTEMPDEPT_VU DNAMENONONO

SCOTTEMPDEPT_VU LOCNONONO

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
:

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

2INSTEAD OF UPDATE

3ONempdept_vu

4FOR EACH ROW

5BEGIN

6-- Prohibit updates to the primary key

7IF (:NEW.deptno <> :OLD.deptno) THEN

8RAISE_APPLICATION_ERROR(-20001, 'Updates are prohibited to the primary key of the DEPT table');

9END IF;

10

11-- We can update DNAME and LOC however

12IF (:NEW.dname <> :OLD.dname) OR

13(:NEW.loc <> :OLD.loc) THEN

14UPDATE dept

15SETdname= :NEW.dname,

16loc = :NEW.loc

17WHERE deptno= :OLD.deptno;

18END IF;

19END;

20/

Trigger created.

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

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 wheretable_name = 'EMPDEPT_VU';

OWNERTABLE_NAME COLUMN_NAMEUPD INS DEL

--------------- ---------- ------------------------------ --- --- ---

SCOTTEMPDEPT_VU EMPNOYES YESYES

SCOTTEMPDEPT_VU ENAMEYES YESYES

SCOTTEMPDEPT_VU JOBYES YESYES

SCOTTEMPDEPT_VU DEPTNOYES NONO

SCOTTEMPDEPT_VU DNAMEYES NONO

SCOTTEMPDEPT_VU LOCYES NONO

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

2setdeptno = 66

3whereempno = 7499

4*

updateempdept_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!