INSERT Records in multiple tables

I need to update two tables. I have created a view and am using the code in the attached file to insert into the two tables.

The page loads without errors, but I get this message that the view is not updatable because the modification affects multiple base tables.

I thought this was the purpose of views?

Does anyone have any suggestions? I am using Dreamweaver MX and SQL Server.

Thanks!
NNo, that is not the purpose of views. Views are frequently not updateable, and I don't think it is ever possible to update different columns from different tables in the same view. Even a direct SQL Update statement will only update one table at a time, so you will need to issues separate update statements or handle the problem through triggers or cascading updates.

Truth is, views don't serve much purpose any more.

Good database application design principles dictate making all your updates through stored procedures. Your application should rarely if ever have direct access to the database tables, even for retrieving data.|||No, you can update the columns of each of the base table independantly (one or more UPDATE statements per base table), but you can't update multiple base tables in a single pass.

Thinking outside of the SQL box, a table represents a relational algebra entity. An entity has no inherant order for either columns or rows, they behave something like a hash in that respect.

Views represent a relational algebra result. A result can have order, there can be a first, middle, and last for both rows and columns in a view.

-PatP|||Views (with multiple base tables) can be updated at one shot by using INSTEAD OF trigger

Here is some supporting article from MSDN

Cheers

Benny
-----------------------

Modifying Data Through a View
You can modify data through a view in these ways:

Use INSTEAD OF triggers with logic to support INSERT, UPDATE and DELETE statements.

Use updatable partitioned views that modify one or more member tables.
If a view does not use an INSTEAD OF trigger or is not an updatable partitioned view, it can still be updatable provided that:

The view contains at least one table in the FROM clause of the view definition; the view cannot be based solely on an expression.

No aggregate functions (AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, VARP) or GROUP BY, UNION, DISTINCT, or TOP clauses are used in the select list. However, aggregate functions can be used within a subquery defined in the FROM clause provided that the derived values generated by the aggregate functions are not modified.

Note Partitioned views using the UNION ALL operator can be updatable.

No derived columns are used in the select list. Derived columns are result set columns formed by anything other than a simple column reference.
Guidelines for Modifying Data Through a View
Before you modify data through a view without using an INSTEAD OF trigger or an updatable partitioned view, consider these guidelines:

All data modification statements executed against the view must adhere to the criteria set within the SELECT statement defining the view if the WITH CHECK OPTION clause is used in the definition of the view. If the WITH CHECK OPTION clause is used, rows cannot be modified in a way that causes them to disappear from the view. Any modification that would cause this to happen is canceled and an error is displayed.

SQL Server must be able to resolve unambiguously the modification operation to specific rows in one of the base tables referenced by the view. You cannot use data modification statements on more than one underlying table in a single statement. Therefore, the columns listed in the UPDATE or INSERT statement must belong to a single base table within the view definition.

All the columns in the underlying table that are being updated and do not allow null values have values specified in either the INSERT statement or DEFAULT definitions. This ensures that all the columns in the underlying table that require values have them.

The data modified in the columns in the underlying table must adhere to the restrictions on those columns, such as nullability, constraints, DEFAULT definitions and so on. For example, if a row is deleted, all the underlying FOREIGN KEY constraints in related tables must still be satisfied for the delete to succeed.

A distributed partition view (remote view) cannot be updated using a keyset-driven cursor. This restriction can be resolved by declaring the cursor on the underlying tables and not on the view itself.
Additionally, to delete data in a view:

Only one table can be listed in the FROM clause of the view definition.|||Originally posted by blindman
Truth is, views don't serve much purpose any more.

what??!!

maybe not for use by the DBA, but for use by end users in a reporting environment, views are invaluable

"much purpose any more"?

what do you suppose the purpose of a view used to be then, before it got to where this purpose was diluted?

okay, here's an example

say a table is called Accounts and say it contains a column called LedgerCode and say the column values range from A to E, and now you have to change the table so that instead of values A to E, the LedgerCode becomes a numeric tinyint foreign key to a Ledger table with values A through Z

the mere fact that you can have a view with a join in it eliminates the need for the end user to figure out how to write a join

rename the table, change the table, declare a view called Accounts, build the join into the view, and voila, all existing code that used to select from the Accounts table still works

a long time ago i used to work in a shop where end users never got to use base tables, they were always given just views, and i can definitely see the logic behind that

it's called program-data independence|||Let me rephrase that...

Truth is, views don't serve much purpose any more, "IMHO".

I used to use views a lot too, specifically for program-data independence. Now, at least for application development, I always use Sprocs or UDFs.|||cool :cool:|||I believe you mean...

"Cool, IMHO." ;)|||indeed

burying application code inside sporcs and fuds is not cool to everybody, i admit -- especially those folks who would like to see a clear separation of application logic from proprietary database languages

usually i insist on declarative relational integrity but otherwise force application logic outside the database

you know, like so that your app is not dependent on any particular dbms

but sporcs and fuds are reasonably coolish, in my opinion, yeah|||Okey-dokey. I usually take the exact opposite approach, putting as much application logic into the RDBMS as possible, so that the application is not dependent on any particular interface. The reasoning is that these days people frequently want to access their data through different channels, such as a VB application, a Crystal Report, Access ADP project, Dot-Net, etc. By putting the application logic in the database you ensure consistent input and output and you avoid duplicating code. Let the the interface do what it does best: display the data and guide the user through it.

I guess the decision depends upon whether it is more likely that your application will need to be ported to a different RDBMS, or that users will come up with new requirements for accessing it. Perhaps I prefer the latter because the former results in boring "file cabinet" databases which frequently lack any sort of referential integrity. They just aren't as much fun or rewarding to work with as a database which is practically an application in itself.