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.
