updating views - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

updating views

Author

Topic

born2acheive
Yak Posting Veteran

65 Posts

Posted - 2006-12-15 : 23:48:57

hi,is it possible to update views?
if means please tell how?
this is my view:

create view upd_view as
select a.name,a.id,a.salary,b.designation from emp0 a,updview b where a.id=b.id;

select * from upd_view;

NAME ID SALARY DESIGNATION
-------------------- --------- --------- --------------------
Amama 2 3 manager
papa 23 10000 engineer
ammu 24 12345 father

i want to update name where id=2 in the view so please can any one help me to update.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-15 : 23:54:37

Read about Updatable Views in Books Online. Also take a look at Instead Of triggers.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

born2acheive
Yak Posting Veteran

65 Posts

Posted - 2006-12-16 : 00:47:32

hi harsh_athlye,i used this following query:

create view upd_view as select a.name,a.age,b.designation from emp1 a,iii b where a.id=b.id

create trigger tg on upd_view
instead of update
as
begin
update emp1 set name=i.name,designation=j.designation from emp1 i,iii j where id=i.id

end;

update upd_view set name='karthik123',designation='doctor' where id=24

but it's not getting updated,i don't what is the problen,so kindly help me to solve this problem.

Kristen
Test

22859 Posts

Posted - 2006-12-16 : 04:26:54

I've found updateable views lead to a variety of collateral problems, and I would advise avoiding them if you can.

We thought they were going to a brilliant wheeze to let us get from Version 1 to Version 2 where we had renamed tables and columns - the intention being to allow "legacy code" to continue to work with the new Version 2 names. We had so much code that wouldn't still work that we gave up and just found & fixed all the old code.

Kristen

madhuotp
Yak Posting Veteran

78 Posts

Posted - 2006-12-17 : 12:32:27

Hi,

WHat is the error u r getting

also
update emp1 set name=i.name,designation=j.designation from emp1 i,iii j where id=i.id

check the bold part,where is the relation with I and J

Madhu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources