Updatable views - SQL Server 2008

Question

A question about updatable db views: I'm reading through some MSDN documentation on the subject, and I come across the following restriction:

Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.

I just want to be sure I understand the restriction. I'd like to use views in a couple of my media review projects. The relational data is spread throughout tables, but a view seems to be the best way to be able to consolidate the data I need from multiple tables (some of which are linked via foreign keys) into a centralized location. Since the columns would come from a variety of tables, does this mean I can't run one blanket INSERT or UPDATE to persist changes in all the columns?

Solution 1

Yes that's what it means. I see no advantage to updating through a view since you have to know what the base tables involved are anyway.

Solution 2

You can use an INSTEAD OF trigger on a view to keep your application only dealing with the view instead of the collection of base tables the view references.

Here is an example : Designing INSTEAD OF Triggers

Comments

[Major Productions]

Well, if I want to edit info it's easier to populate the form fields with the values of the view and save them back the same way.

[eugened]

As long as the view contains the key columns from the underlying table, or otherwise allows SQL Server to uniquely identify a row in the table, directly updating through a view can simplify things now - especially in situations like where a view is mapped to a form or a datagrid. This concerned me a bit at first, but since the user still needs rights to update the table anyways, there's no added security risk.

[dburges]

But it won't work if the fields updated are from differnt underlying tables. That's why it is not useful to update from a view.

[Adamantish]

SQLRyan has it spot on. Many checklists are created as part of workflows for adhoc projects never to be used again. Exposing a view to MS Access or something else that does grid editing out of the box is the perfect 5 minute solution so long as you remember to keep most columns non-editable. In answer to HLGEM, most of these tickbox, workflow exercises require contextual information from many tables but updates to just one.

[dburges]

@Adamantish, in case you missed that part, the OP wanted to be anble to update all columns in one update which is not possible with a view from multiple tables.

[Adamantish]

Thanks, I did miss that. I thought you were commenting that it was generally unhelpful to use updateable views.