Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2004
    Location
    Canada
    Posts
    1

    Unanswered: 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!
    N
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No, 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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  4. #4
    Join Date
    Mar 2004
    Location
    Dubai
    Posts
    29

    Thumbs up

    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.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    cool
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I believe you mean...

    "Cool, IMHO."
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Mar 2008
    Posts
    1
    How do you check how much data is distributed in each partition

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    See $partition in BOL, it gives good examples on the usage.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •