How to determine which table of a multitable view are updatable?

Question

Update: My problem doesn't seem to be with the SQL server. I executed an update statement inside the database manually against the view and I was able to update the Bar table. I'll close this and research the OleDbDataAdapters more since I think the problem lies with them.

This question applies to MS SQL Server 2000. How do I determine which table of the multitable view can be modified?

I have the following view:

CREATE VIEW dbo.MyView
AS
SELECT     dbo.Foo.Id, dbo.Bar.Id, dbo.Foo.column1, dbo.Foo.column2,
           dbo.Foo.column3, dbo.Bar.column1, dbo.Bar.column2, 
           dbo.Bar.column3
FROM       dbo.Bar INNER JOIN
                  dbo.Foo ON dbo.Bar.Id = dbo.Foo.ForeignId

When I update this view, (using VB.NET OleDbDataAdapters), I can update columns of Foo, but not columns of Bar. My investigation into this tells me that in a multitable view like this that MS SQL server only allows you to update one of the tables. So my question is, how does SQL server determine which table can be updated?

I tried a test where I edit the fields of a particular row from the view. Afterwards, I used the OleDbDataAdapter to update the view. Only the edits to the Foo table were accepted. The edits to the Bar table were ignored (no exception thrown).

Is there a way to predict which of the tables can be updated or a way to control which one? What if I wanted Bar to be the updateable table instead of Foo?

Update: I found this on google, MS SQL Server 2000 Unleased:

http://books.google.com/books?id=G2YqBS9CQ0AC&pg=RA1-PA713&lpg=RA1-PA713&dq=ms+sql+server+"multitable+view"++updated&source=bl&ots=ZuQXIlEPbO&sig=JbgdDe5yU73aSkxh-SLDdtMYZDs&hl=en&ei=b-0SSq-aHZOitgPB38zgDQ&sa=X&oi=book_result&ct=result&resnum=1#PRA1-PA713,M1

(For some reason the URL I'm trying to paste doesn't work with this site, sorry that you have to copy&paste.)

Which says:

• An update through a multitable view cannot affect more than one underlying base table.
• A delete cannot be executed against multitable views.

But, I don't yet see an answer to my question.

Again, my question is:

How do I determine which table of the multitable view can be modified?

I realize I can write two update statements one for each table. My concern is different. I need to audit code that uses views like the one above and does updates on the views. I was hoping to find a way to determine which parts of the updates will be silently ignored.

Example:

I edit Bar.Column1 and then call the Update() method of the OleDbDataAdapter. This results in the Bar table not being modified nor is an exception thrown. If I edit Foo.Column2 then call Update() the Foo table does get modified.

Answer 1

You need to be able to uniquely identify the row in the table by returning the primary key. Try returning dbo.Bar.Id in the view, and you should be able to edit columns in table Bar.

Answer 2

See if this helps.

Answer 3

You can update any table in the view, but only fields that are all in the same table in that statement. If you need to update fields from two tables in a view, then you must write two update statements.

Personally I prefer not to update or delete from views at all. I use the base tables for that.

There are also rules concerning whether view is updateble. See Books online. Search for this: views-SQL Server, modifying data