****POC: Updating the complex View****

Is Complex views are updatable ?

yes,we can as follows :)

Generally we have two types of views are there, simple view(the view which created on single table) and complex view(the view which is created using more than one table and we can consider the view as complex view even if we create on single table if it contains(distinct,aggregate function,group by,having clauses,computed columns and set operators).

So a simple view can be updatable but a complex view can't be updatable because it contains the columns of two or more tables.

But it is not an achievable thing, we can achieve this thing by using Instead of trigger

please find the below tables.

From the above two tables I am creating a view as follows

create view emp_dept
as
select e.eno,e.ename,e.job,e.sal,d.dno,d.dname,d.loc
from employee10 e inner join department d
on e.deptno=d.dno

when ever we are trying to insert a record in the view,we got the below error

"View or function 'emp_dept' is not updatable because the modification affects multiple base tables."

So we can overcome this drawback using instead of triggers as shown in below 

Now the same insertion will success as shown below.

Now 

The new row got inserted in both of the tables.The remaining columns which are not available on view will be filled with null value.

By using this approach we can update the complex view.

Let me know if you have any queries on this.

Thanks
Srinivas
9059361460