This article presents how to execute DML operations on complex views using INSTEAD OF triggers that otherwise could not be done.

Update/Insert/Delete via view is possible in Oracle.

create table test_tbl
(
  id  number,
  id1 number,
  id2 number
);

create view test_vw
as
select * from test_tbl;

INSERT, UPDATE, DELETE

insert into test_vw values(1,1,1);
insert into test_vw values(2,2,2);
insert into test_vw values(3,3,3);

commit;

update test_vw
set id2=4
where id1=3;

commit;

delete from test_vw
where id=2;

commit;

select * from test_vw;

        ID        ID1        ID2
---------- ---------- ----------
         1          1          1 
         3          3          4

but with many exceptions

The view must not contain any of the following constructs:

• A set operator
• A DISTINCT operator
• An aggregate or analytic function
• A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause
• A collection expression in a SELECT list
• A subquery in a SELECT list
• A subquery designated WITH READ ONLY

and many many others – more you can read in Oracle documentation. If view violates one of above rule then it’s called complex view.

To check which columns can be used for DML operations you can check following dictionary view USER_UPDATABLE_COLUMNS.

Let’s create complex view

create view test_complex_vw
as
select id, sum(id2) s_id2
from test_tbl
group by id;

select * 
from user_updatable_columns 
where table_name='TEST_COMPLEX_VW';

OWNER   TABLE_NAME       COLUMN_NAME  UPDATABLE INSERTABLE DELETABLE
------- ---------------- ------------ --------- ---------- ---------
TOMASZ  TEST_COMPLEX_VW  ID           NO        NO         NO
TOMASZ  TEST_COMPLEX_VW  S_ID2        NO        NO         NO

Now DML operations are not allowed. However it can be changed by using INSTEAD OF triggers. INSTEAD OF triggers enables to update complex views that otherwise could not be updated.

They can also be used to enforce constraints, check privileges, and validate DML statements. Using these triggers, you can control mutation that might be caused by inserting, updating, and deleting in the objects created though an object view.

so after creation of INSTEAD OF trigger INSERT is possible. Logic included in such triggers depends only on your imagination 🙂

create or replace trigger test_complex_vw_ins_trg 
instead of insert on test_complex_vw
for each row
begin
 insert into test_tbl values(:new.id, :new.s_id2, null);
end;
/

create or replace trigger test_complex_vw_upd_trg 
instead of update on test_complex_vw
for each row
begin
 update test_tbl
 set id1=:new.s_id2, id2=null
 where id = :OLD.id;
end;
/

create or replace trigger test_complex_vw_del_trg 
instead of delete on test_complex_vw
for each row
begin
  delete from test_tbl where id=:old.id;
end;
/

select *
from user_updatable_columns
where table_name='TEST_COMPLEX_VW';

OWNER   TABLE_NAME       COLUMN_NAME  UPDATABLE INSERTABLE DELETABLE 
------- ---------------- ------------ --------- ---------- --------- 
TOMASZ TEST_COMPLEX_VW   ID           YES       YES        YES 
TOMASZ TEST_COMPLEX_VW   S_ID2        NO        NO         NO

insert into test_complex_vw values(5,5);
commit;

select * from test_tbl

        ID        ID1        ID2
---------- ---------- ----------
         1          1          1 
         3          3          4 
         5          5

update test_complex_vw
set s_id2=5
where id=3;
commit;

        ID        ID1        ID2
---------- ---------- ----------
         1          1          1 
         3          5            
         5          5            

delete from test_complex_vw
where id=1;
commit;

        ID        ID1        ID2
---------- ---------- ----------
         3          5            
         5          5

Have a fun 🙂

Tomasz