Updating oracle views best speed dating winnipeg
You can only create a view with errors by using the FORCE option of the CREATE VIEW command: CREATE FORCE VIEW AS ...; When a view is created with errors, Oracle returns a message and leaves the status of the view as INVALID.If conditions later change so that the query of an invalid view can be executed, then the view can be recompiled and become valid.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; 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 also grant several object privileges to roles and other users.However, now you realize that you must redefine the ACCOUNTS_STAFF view to correct the department number specified in the WHERE clause of the defining query, because it should have been 30. A view derives its data from the tables on which it is based. Views can be based on actual tables or another view also.Views are very powerful and handy since they can be treated just like any other table but do not occupy the space of a table.
For example, suppose all the employee working in Department No.
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.
select * from accounts_staff;select sum(sal) from accounst_staff; select max(sal) from accounts_staff; As you can see how views make things easier.
The query that defines the ACCOUNTS_STAFF view references only rows in department 10.