Thread: How to's

Modifying Complex views using Instead of Triggers

I am creating a complex view using emp and dept tables.

Code:

 
sql> create view emp_dept 
2    as select a.empno,a.ename,a.sal,b.deptno, b.dname 
3    from emp a, dept b 
4    where a.deptno = b.deptno; 

view created. 

Sql> insert into emp_dept values (7456,'krishna',10000,50 ,'purchases'); 
insert into emp_dept values (7456,'krishna',10000,50 ,'purchases') 
* error at line 1: 
ora-01776: cannot modify more than one base table through a join view

General rule is any insert, update, or delete operation on a join view can modify only one underlying base table at a time. Here we are inserting values into two base tables emp, dept. So it raised error ora-01776.

We may overcome this problem using Instead of triggers.
INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through UPDATE, INSERT, and DELETE statemets.

Code:

 
Create or replace trigger emp_dept_insert 
instead of insert on emp_dept 
referencing new as n 
for each row 

declare 
 rowcnt number; 

begin 
 select count(*) into rowcnt 
 from emp 
 where empno = :n.empno; 

 if rowcnt = 0 then 
  insert into emp (empno,ename,sal ) values (:n.empno, :n.ename , :n.sal ); 
 else 
  update emp 
  set emp.ename = :n.ename, emp.sal = :n.sal 
  where emp.empno = :n.empno; 
 end if; 

 select count(*) into rowcnt 
 from dept 
 where deptno = :n.deptno; 
 
 if rowcnt = 0 then 
  insert into dept (deptno, dname) values(:n.deptno, :n.dname);
 else 
  update dept 
  set dept.dname = :n.dname 
  where dept.deptno = :n.deptno; 
 end if; 

end; 
/

sql> insert into emp_dept values (7456,'krishna',10000,50 ,'purchases');
1 row created.

Database Trigger execution order

Code:

create table test_11(A NUMBER, B	NUMBER);

create or replace TRIGGER tr_before_tab_inst before insert  on test_11 
begin  
	dbms_output.put_line('Before insert on table');
end;

create or replace  TRIGGER tr_after_tab_inst after insert  on test_11 
begin  
	dbms_output.put_line('After insert on table');
end;

create or replace  TRIGGER tr_before_row_inst before insert  on test_11 
for each row
begin  
	dbms_output.put_line('before insert on row');
end;

create or replace  TRIGGER tr_after_row_inst after insert  on test_11 
for each row
begin  
	dbms_output.put_line('after insert on row');
end;

SQL>  insert into test_11 values(1,1);
Before insert on table
before insert on row
after insert on row
After insert on table

1 row created.