top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Oracle: How to update COMPLEX VIEW?

+1 vote
4,254 views
Oracle: How to update COMPLEX VIEW?
posted Sep 8, 2015 by Vidhya Sagar

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

+1 vote

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 ..

answer Sep 8, 2015 by Shivaranjini
...