November 16, 2012 at 11:41 am
i have a view that references three tables and i want to update the table when someone updates the value in the view is that even possible
tables as
Location
Customer
Books
the view is defined as
Select locationName,CustomerName,BooksName
from Locations l inner join customer c on L.LocationID = C. LocationID
inner join books b on b.bookID = c. book ID
so I want to update the bookID from the view so that it changes on customer as well is it possible?
November 16, 2012 at 11:54 am
SQLTestUser (11/16/2012)
i have a view that references three tables and i want to update the table when someone updates the value in the view is that even possibletables as
Location
Customer
Books
the view is defined as
Select locationName,CustomerName,BooksName
from Locations l inner join customer c on L.LocationID = C. LocationID
inner join books b on b.bookID = c. book ID
so I want to update the bookID from the view so that it changes on customer as well is it possible?
No you can't do that with a view. Any update can only reference a column that exists in a single base table.
http://msdn.microsoft.com/en-us/library/ms180800.aspx
http://msdn.microsoft.com/en-us/library/ms187956.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 16, 2012 at 12:34 pm
Actually, you can, but indirectly. Put an "Instead of Update" trigger on the view, and have the trigger parse out the update into the three tables.
Warning, this can cause odd behavior in the application that runs the update. It needs to be set up to deal with informational messages about the transaction being interrupted in the process. I don't remember the exact error, you'll need to try it out and get that. This is because an "Instead Of" style trigger pretty much counts as a rollback as far as SQL Server is concerned.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply