Inserting into Oracle Views and Using “Instead Of” Triggers

Inserting into Oracle Views and Using “Instead Of” Triggers

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 asselect  EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,dnamefrom 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      401 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 NULLORA-06512: at "SCOTT.UPDATE_EMP_THRU_OUTEMP_VIEW", line 2ORA-04088: error during execution of trigger'SCOTT.UPDATE_EMP_THRU_OUTEMP_VIEW'scott@fis≫ update outemp set dname = 'OPERATIONS' where ename = 'C Farmer';1 row updated.


Share the Post: