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:
[email protected]> update outemp set ename = 'C Farmer' where ename = 'FARMER';1 row [email protected]> 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:
[email protected]> 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'[email protected]≫ update outemp set dname = 'OPERATIONS' where ename = 'C Farmer';1 row updated.