Update Multiple Base Tables

Question

Update Multiple Base Tables

fryere

Ten Centuries

Points: 1054
More actions
January 6, 2006 at 5:43 am

#166964

I am trying to update data by using a SP to update a view that is made up of two tables. I get an error back saying the view is not updatable because the modification affects multiple base tables. (the two tables are a One to One join if that matters) However I can cut and paste the code in the Query Analyzer and it works fine. I can also manually edit the view with no problems. I can always edit the SP to update the two tables directly, but I liked the simplicity of using the view. Does anyone know why the SP won’t work while the other ways do.

Thanks,

fryere
fryere

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

fryere Ten Centuries Points: 1054 More actions · Answer 1

Sorry,

I am not sure what I did the first time I ran the update code with the query analyzer, but I tried it again and got the same error mentioned above. I guess I will just change the SP to update the two tables independently.

Thanks.

fryere

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 2

Fryere,

when you try to update a view which contains columns from more than one table you need to define an INSTEAD OF Trigger on your view. The trigger should when handle which columns in which tables need to be updated. Without the trigger SQL server don't always know which table needs to be updated.

By the way, don't let yourself be fooled by EM, because there it will work, because in the background EM does direct updates on the tables.

Markus

[font="Verdana"]Markus Bohse[/font]

fryere Ten Centuries Points: 1054 More actions · Answer 3

MarkusB,

Thanks for the reply. Where do you access Triggers for Views. I can't seem to find it.

Thanks.

fryere

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 4

Look in BOL for INSTED OF TRIGGERs. There are some examples.

Markus

[font="Verdana"]Markus Bohse[/font]

chakri002 SSC Rookie Points: 32 More actions · Answer 5

Use Instead of Triggers on the Views to Delete or Update Records directly from the view.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

chakri002 (10/3/2008)
Use Instead of Triggers on the Views to Delete or Update Records directly from the view.

You did notice that the thread you answered was 2 years old?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Garadin One Orange Chip Points: 29613 More actions · Answer 7

Not to mention the answer he gave had already been given, better, and in more detail before he bumped it. That post was in general a failure of epic proportions.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]