quote:

---

Originally posted by vmvadivel

Just to correct what karthikeyan has mentioned: Views can be updated directly as long as it is being created based on one single table. Insteadof triggers needs to be used for updating a view if it is been created based on multiple tables.

A complete sample of how to use INSTEADOF Trigger is up there in MSDN. Check this out http://msdn.microsoft.com/en-us/library/ms175521.aspx

Best Regards
Vadivel

http://vadivel.blogspot.com

---

--base tables
create table atest
( ID int IDENTITY(1,1),
a varchar(50)
)
create table btest
( ID int IDENTITY(1,1),
b varchar(50)
)
INSERT atest(a)
SELECT 'test' union all
SELECT 'tip' union all
SELECT 'tin' union all
SELECT 'mind' union all
SELECT 'pot' union all
SELECT 'top' 

INSERT btest(b)
SELECT 'wqdew' union all
SELECT 'xvfvv' union all
SELECT 'dbgwtr' union all
SELECT 'fe' union all
SELECT 'poewgert' union all
SELECT 'ewgqg'

select * from atest
select * from btest

--creation of view
create view ab
as
select a.id,a.a,b.b
from atest a
join btest b
on b.id = a.id


select * from ab

--updating only atest's column
update ab
set a=a+'123'

select * from ab

--updating only btest's column
update ab
set b=b+'345'
select * from ab

--updating multiple base tables column this will fail with error
update ab
set a=a+'678',
b=b+'910'


output
----------------------------
Original view

id	a	b
-------------------------------
1	test	wqdew
2	tip	xvfvv
3	tin	dbgwtr
4	mind	fe
5	pot	poewgert
6	top	ewgqg

after atest column update

id	a	b
------------------------------
1	test123	wqdew
2	tip123	xvfvv
3	tin123	dbgwtr
4	mind123	fe
5	pot123	poewgert
6	top123	ewgqg

after btest column update
--------------------------------
id	a	b
--------------------------------
1	test123	wqdew345
2	tip123	xvfvv345
3	tin123	dbgwtr345
4	mind123	fe345
5	pot123	poewgert345
6	top123	ewgqg345


after both tables column update



Msg 4405, Level 16, State 1, Line 1
View or function 'ab' is not updatable because the modification affects multiple base tables.