Oracle: How to update COMPLEX VIEW?

Question

Do i need to write in PL/SQL or is it possible in SQL query?

Answer 1

you can update complex view through 'instead of triggers' in oracle.

let take an example to update the view v1 (below)

create or replace view v1 as
select  EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,dname
from emp e ,dept d where d.deptno = e.deptno

Instead of updating the view, you can create a trigger which overrides the default operation of

the update statement:

create or replace trigger update_emp_thru_v1_view
 instead of update on v1
 referencing new as new
 begin
     update emp
      set ename = :new.ename,
      empno = :new.empno,
      job = :new.job,
      mgr = :new.mgr,
      hiredate = :new.hiredate,
      sal = :new.sal,
      comm = :new.comm,
      deptno = ( select deptno from dept where dname = :new.dname )
      where empno = :old.empno;
     if ( sql%rowcount = 0 )
       then
         raise_application_error
          ( -20001, 'Error updating the outemp view ...' );
     end if;

 end;

Then, you can update this from SQL*Plus as normally you do to a table ..