top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Oracle: How to update COMPLEX VIEW?

+1 vote
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
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
     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 )
          ( -20001, 'Error updating the outemp view ...' );
     end if;


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

answer Sep 8, 2015 by Shivaranjini