View or function XXX is not updatable because the modification affects multiple base tables

Problem

You get this error when running your application:

Unable to create record. View or function 'XXX' is not updatable because the modification affects multiple base tables.

This message is generated by the underlying database and displayed by your application. Most likely, one or more fields in the database tables underlying the database view are read-only and cannot be updated. The referenced database view must be updatable.

When Iron Speed Designer updates a view, it requires all fields in all the tables involved in the view to be updatable. You will receive the above error message if even one field (belonging to the tables involved in the view) is not updatable.

Solution: Make sure fields are editable in database

Step 1: Make sure each field in the database tables underlying the database view is editable.

Step 2: From Iron Speed Designer, scan your database for changes (Databases, Scan Database Schema for Changes).

Step 3: Rebuild and run your application.

Solution: Database Triggers

You can write an ‘Instead of’ Trigger on top of the view and handle the update, delete and insert events for the tables involved in the view. Here are several articles that address creating and using these triggers:

http://www.sql-server-performance.com/nn_triggers.asp

http://www.databasejournal.com/features/mssql/article.php/1437741

Solution: Handle the Inserting, Updating and Deleting Events

Assuming your view has a Virtual Primary Key enabled, you can handle the inserting, updating and deleting events for the view in the Data Access Layer.

You will find the <ViewName>Record.cs file in the Business Access Layer of your application. In the <ViewName>Record.cs file, write a custom function that handles the Base Class's UpdatingRecord event. The ‘UpdatingRecord’ event is fired before the record is updated and saved into the database. Handle this event and use CancelEventArgs to cancel the record’s update operation. Write custom code to independently update all tables involved in your view. For example:

Public Sub MyCustomFunction(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.UpdatingRecord

e.Cancel = True

‘ Write custom code to update all the tables involved in the view

‘ independently.

End Sub

Once the default UpdatingRecord event is cancelled, this function should also update all the tables this view might modify. You will have to update each of these tables that are likely to be affected by the view and save the changes.

The same procedure applies when you try to override the InsertingRecord and DeletingRecord events. Below are the signatures of the InsertingRecord and DeletingRecord events, and your custom function should handle these events.

Public Event InsertingRecord(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Implements IRecordWithTriggerEvents.InsertingRecord

Public Event DeletingRecord(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Implements IRecordWithTriggerEvents.DeletingRecord

Microsoft SQL Server Limitations on Updating Database Views

Microsoft SQL Server does not permit updating fields in multiple database tables used within a SQL view. According to Microsoft, “INSERT, UPDATE, and DELETE statements also must meet certain qualifications before they can reference a view that is updatable... UPDATE and INSERT statements can reference a view only if the view is updatable and the UPDATE or INSERT statement is written so that it modifies data in only one of the base tables referenced in the FROM clause of the view. A DELETE statement can reference an updatable view only if the view references exactly one table in its FROM clause."

The modifications made by the UPDATE statement cannot affect more than one of the base tables referenced in the FROM clause of the view. In general, you should create an INSTEAD OF UPDATE trigger to update only the field that you want in your view.”

Microsoft also offers this information:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ua-uz_1mpf.asp