Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2002
    Posts
    63

    Question Unanswered: Update multiple tables via view.

    Do you know if it is possible to update multiple table in one insert statement via a view?
    The view select all the Columns with simple join between 1 – many relation.

    When I tried to insert values for both table I got the following message:

    Server: Msg 4405, Level 16, State 2, Line 1
    View or function ‘XXX' is not updatable because the modification affects multiple base tables.

    It is not a problem to update via the view the parent table and than update via the view the child table.

    Thanks,
    Eyal

  2. #2
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    I'm a bit puzzled here but are you really saying that you update tables through views? I have always been under the impression that views are compiled select-statements...? I think you need to post some code...
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  3. #3
    Join Date
    Jul 2002
    Posts
    63
    USE Northwind
    GO
    --Eyal Create
    CREATE VIEW VIEW_Region_Territorie
    AS
    SELECT R.RegionID,R.RegionDescription ,T.TerritoryID ,T.TerritoryDescription ,T.RegionID as 'tRegionID'
    FROM Territories as T ,Region As R
    WHERE T.RegionID = R.RegionID
    GO


    -- Insert into Region
    INSERT INTO VIEW_Region_Territorie (RegionID , RegionDescription ) VALUES (6 ,'NewRegion2')

    -- Insert into Territories
    INSERT INTO VIEW_Region_Territorie (TerritoryID , TerritoryDescription , tRegionID ) VALUES (98105 ,'NewTerritory' , 5 )

    -- The The problem statement
    INSERT INTO VIEW_Region_Territorie (RegionID , RegionDescription ,TerritoryID , TerritoryDescription , tRegionID ) VALUES (7 ,'NewRegion2',98106 ,'NewTerritory' , 5 )

  4. #4
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    I found the following in books online inder "CREATE VIEW" in the t-sql reference:

    Conditions for Modifying Partitioned Views

    Only the Developer and Enterprise Editions of SQL Server 2000 allow INSERT, UPDATE, and DELETE operations on partitioned views. To modify partitioned views, the statements must meet these conditions:

    The INSERT statement must supply values for all the columns in the view, even if the underlying member tables have a DEFAULT constraint for those columns or if they allow NULLs. For those member table columns that have DEFAULT definitions, the statements cannot use the keyword DEFAULT explicitly.

    The value being inserted into the partitioning column should satisfy at least one of the underlying constraints; otherwise, the INSERT action will fail with a constraint violation.

    UPDATE statements cannot specify the DEFAULT keyword as a value in the SET clause even if the column has a DEFAULT value defined in the corresponding member table.

    PRIMARY KEY columns cannot be modified through an UPDATE statement if the member tables have text, ntext, or image columns.

    Columns in the view that are an IDENTITY column in one or more of the member tables cannot be modified through an INSERT or UPDATE statement.

    If one of the member tables contains a timestamp column, the view cannot be modified through an INSERT or UPDATE statement.

    INSERT, UPDATE, and DELETE actions against a partitioned view are not allowed if there is a self-join with the same view or with any of the member tables in the statement.

    Note To update a partitioned view, the user must have INSERT, UPDATE, and DELETE permissions on the member tables
    Did this help at all?
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  5. #5
    Join Date
    Jul 2002
    Posts
    63

    Unhappy

    Well Still I have not got the answer can I update the with the sample view VIEW_Region_Territorie both tables in one Insert statement?

    Thanks,
    eyal

  6. #6
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Use instead on insert trigger

  7. #7
    Join Date
    Jul 2002
    Posts
    63

    Wink

    Can you be more specific? can you post sample ?

    Thanks!

  8. #8
    Join Date
    Jan 2003
    Location
    Leamington Spa, UK
    Posts
    17
    Originally posted by eschapir
    Can you be more specific? can you post sample ?

    Thanks!
    Instead of triggers were introduced in SQL Server 2000 to solve just this problem.

    From Books Online (BOL):
    INSTEAD OF UPDATE triggers can be defined on a view or table to replace the standard action of the UPDATE statement. Usually, the INSTEAD OF UPDATE trigger is defined on a view to modify data in one or more base tables.
    The syntax is as follows:
    Code:
    CREATE TRIGGER trgInsteadOfUpdate ON dbo.Someview
    INSTEAD OF UPDATE 
    AS 
    
    UPDATE Person 
    SET Person = inserted.Person_Name
    FROM inserted 
    
    UPDATE Company
    SET Company =  inserted.Company_Name 
    FROM inserted 
    
    GO
    hth,

    macka.

  9. #9
    Join Date
    Jul 2002
    Posts
    63
    Thanks,

    I have change it to insert with select …. From inserted and it works .


    CREATE TRIGGER trgInsteadOfUpdate ON dbo.VIEW_Region_Territorie
    INSTEAD OF INSERT
    AS

    -- Insert into Region
    INSERT INTO Region SELECT RegionID ,RegionDescription FROM inserted

    -- Insert into Territories
    INSERT INTO Territories SELECT TerritoryID ,TerritoryDescription ,tRegionID FROm inserted

    GO


    Thanks!
    Eyal

Posting Permissions

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