Remember, it’s important to use a development environment that is a proper match for the production environment.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 !!!
Whatever DML operations you performed on a view they actually affect the base table of the view. You can Query, Insert, Update and delete from views, just as any other table.
A view derives its data from the tables on which it is based. Views can be based on actual tables or another view also.
We can recompile a view using the Alter view statement, but oracle automatically recompiles the view once it is accessed.
On recompiling the dependent objects become invalid.
select e.empno,e.ename,e.sal,e.deptno,d.dname,From emp e, dept d where e.deptno=d.deptno; So everytime we want to see emp details and department names where they are working we have to give a long join query.
Instead of giving this join query again and again, we can create a view on these table by using a CREATE VIEW command given below create view emp_det as select e.empno, e.ename,e.sal,e.deptno,d.dname,from emp e, dept d where e.deptno=d.deptno; Now to see the employee details and department names we don’t have to give a join query, we can just type the following simple query.For example, suppose all the employee working in Department No.10 belongs to accounts department and most of the time you deal with these people.CREATE VIEW sup_orders AS SELECT suppliers.supplier_id, orders.quantity, orders.price FROM suppliers INNER JOIN orders ON suppliers.supplier_id = orders.supplier_id WHERE suppliers.supplier_name = 'Microsoft'; CREATE or REPLACE VIEW sup_orders AS SELECT suppliers.supplier_id, orders.quantity, orders.price FROM suppliers INNER JOIN orders ON suppliers.supplier_id = orders.supplier_id WHERE suppliers.supplier_name = 'Apple'; A VIEW in Oracle is created by joining one or more tables.When you update record(s) in a VIEW, it updates the records in the underlying tables that make up the View.ALTER VIEW View-Name COMPILE ; Use Drop View statement to drop a view. Insert, Delete and Update on Views When you update a view oracle will update the underlying base table.