DML Operations on view affecting BaseTable in SQl

DML operations on view affecting BaseTable in SQl

Any DML operation(insert ,update delete) on view affect the base table only when the view is from only one base table.and it should not be custom field like created using Aggregate function(sum,max....).Secondaly, if a view is created using join then the view will not be updatable view.

Below  is sample data with example

--first sample table

create table Tbl1 (C1 int, c2 varchar(100))

--second sample table

create table Tbl2 ( c3 int ,c4 varchar(100))

go

--view created with only 1 table

create view vw1 as select * from Tbl1 

--inserting through view 

insert into Vw1 values (1,'2')

--insert successfully showing record in both table and view

select * from Vw1 

select * from Tbl1 

--Second view created using joins

create view Vw2 as Select tbl1.C1   ,c2 =c4 from Tbl1  join tbl2 on tbl1.C1  =tbl2 .c3 

go

--now try to insert  in second view

insert into Vw2   values (1,'4')

Msg 4405, Level 16, State 1, Line 2

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

--insert record in table 2

insert into tbl2   values (1,'4')

select * from vw2

--one record 

--Now we will try  to delete record from both view

--first view created with one table

delete from Vw1 where C1=1

succesfully

--second view created using joins

delete from Vw2 where C1=1

Msg 4405, Level 16, State 1, Line 2

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

so above example have given a clear image that you can perform insert update delete in a view only when it is created using a single table. u can't update a view created using Multiple tables.