Home » Tip Bank » Database Development » Oracle

Language: SQL
Expertise: Intermediate

Jun 29, 2004

Sometimes you need to update or insert into a view. But say your view has multiple tables like the outemp view:

create or replace view outemp 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_outemp_view
 instead of update on outemp
 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 you would a table:

scott@fis> update outemp set ename = 'C Farmer' where ename = 'FARMER';

1 row updated.

scott@fis> select * from emp where ename = 'C Farmer';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM    DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
      3456 C Farmer   DBA             7839 01-JUL-02       4000          4      40


1 row selected.

You can get some strange errors from the update statement if you try to update with invalid data:

scott@fis> update outemp set dname = 'dkjsk' where ename = 'C Farmer';
update outemp set dname = 'dkjsk' where ename = 'C Farmer'
       *
ERROR at line 1:
ORA-01407: cannot update ("SCOTT"."EMP"."DEPTNO") to NULL
ORA-06512: at "SCOTT.UPDATE_EMP_THRU_OUTEMP_VIEW", line 2
ORA-04088: error during execution of trigger
'SCOTT.UPDATE_EMP_THRU_OUTEMP_VIEW'


scott@fis&Gt update outemp set dname = 'OPERATIONS' where ename = 'C Farmer';

1 row updated.