VIEWS WITH DML

Rules for DML’s on views:-

For delete: -

·         It is not possible when the view is derived from more than one database table.

For update: -

·         It is not possible when the view is derived from more than one database table.

·         It is not possible to update the columns those are derived from arithmetic expression and function from pseudo columns.

For insertion:-

·         It is not possible when the view is derived from more than one database table.

·         It is not possible to update the columns those are derived from arithmetic expression and function from pseudo columns.

·         It is not possible to insert the data into the view if the mandatory columns of the base tables are missing in the view definition.

When ever we want to create view we should have base table.

Based on that base table we should create view.

So, first we are going to create a table

SQL>create table vdept (deptno number (10), dname varchar2 (10), loc varchar2 (10));

--table created

Now we are able to create view

SQL>create view na_dept_v as select *from vdept;

-- View created.

Now we are going to insert record in view to know whether DML operations will reflect on table when we perform on view

SQL>insert into na_dept_v values(10,'ACCOUNTING','ENGLAND');

--1 row inserted

Now we can see whether records are inserted or not into view

SQL>select * from na_dept_v;

Record is inserted into view.

Now we are going to see whether record is inserted into base table or not when we inserted into view.

SQL>Select *from vdept;

So now we can conclude that DML operations are reflecting on base table when we perform on view.

*Similarly we can update and delete on view that will affect on base table.

Force view:-

Creating a view on the base table which does not exist in the database

 If we create a view like this system will throw a warning as “View created with compilation errors”.

Syntax:-

Create or replace force view <> as select *from <table name>;

Example: -

SQL>create or replace force view fview as select *from kdept;

Output:-

Warning: View created with compilation errors.

DML operations on complex views:-

Complex view: - creating a view based on two tables is nothing but complex view.

So first create one complex view and then perform DML operations on that view.

SQL> create view na_com_v as select e.empno, e.ename, e.sal, d.dname, d.loc from EMP e, DEPT d;

-- View created.

Now perform DML operations on that complex view.

First we are trying to insert record into the view what we have created above.

Sql>Insert into na_com_v values (7625,’BLAKE’, 12000,’TESTING’,’CHICAGO’);

Insert into na_com_v values (7625,'BLAKE', 12000,'TESTING','CHICAGO')

*

ERROR at line 1:

ORA-01776: cannot modify more than one base table through a join view

System will throw an error because we can not perform any DML operations on COMPLEX VIEWS.

But we can over come this by using INSTEAD OF TRIGGERS

INSTEAD OF TRIGGERS:-

• Instead of triggers can be used only with views.
• Effective for joins which are based on equi join
• To have an cascading effect of update on both the table if columns are matching]
• Also to update uncommon columns through equi join views

Now we are going to INSERT into Complex view (na_com_v) which we had created above by using INSERTED OF TRIGGERS

SQL> create or replace trigger na_comm_trig instead of insert on na_com_v for each row

Begin

Insert into na_com_v values (:new.DEPTNO, new.ENAME, new.DNAME, new.LOC, new.HIREDATE);

End;