none
View referencing multiple tables not updatable ?

    Question

  • Hi

    This is for Sql Server 2000.

    I get the following error :

    View or function 'PS_EMPLOYMENT' is not updatable because the modification affects multiple base tables.

    Sql is as follows:

    INSERT INTO PS_EMPLOYMENT(EMPLID,EMPL_RCD,PER_ORG,BENEFIT_RCD_NBR,HOME_HOST_CLASS,OWN_5PERCENT_CO,
                              SECURITY_CLEARANCE,NEE_PROVIDER_ID,POSITION_PHONE,BUSINESS_TITLE,REPORTS_TO,
                              SUPERVISOR_ID)
    VALUES('000004', 0, ' ', 0, 'M', 'N', ' ', ' ', ' ', ' ', ' ', ' ')

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------

    Searching the KB i only found links to SP1 and SP3, the server is already at SP3.

    BOL says the following:

    SQL Server 6.x :

    Updatable views were restricted to modifications that affected only one table

    SQL Server 2000:

    Updatable views can modify more than one table involved in the view. The DELETE, INSERT, and UPDATE statements can reference a view as long as SQL Server can translate the user's update request unambiguously to updates in the base tables referenced in the view's definition.

    So why can this view not be updated in SS2000 SP3 ?

    Here is a copy of the view defintion:

    ALTER  VIEW PS_EMPLOYMENT
    (EMPLID, EMPL_RCD, PER_ORG, BENEFIT_RCD_NBR, HOME_HOST_CLASS, LAST_DATE_WORKED, HIRE_DT, REHIRE_DT,
    TERMINATION_DT, SENIORITY_PAY_DT, CMPNY_SENIORITY_DT, SERVICE_DT, PROF_EXPERIENCE_DT,
    LAST_VERIFICATN_DT, EXPECTED_RETURN_DT, LAST_INCREASE_DT, OWN_5PERCENT_CO, PROBATION_DT,
    SECURITY_CLEARANCE, NEE_PROVIDER_ID, POSITION_PHONE, BUSINESS_TITLE, REPORTS_TO, SUPERVISOR_ID)

    AS
    SELECT A.EMPLID ,A.EMPL_RCD ,A.PER_ORG ,A.BENEFIT_RCD_NBR ,A.HOME_HOST_CLASS ,D.LAST_DATE_WORKED ,
    D.HIRE_DT ,D.LAST_HIRE_DT ,D.TERMINATION_DT ,A.SENIORITY_PAY_DT ,A.CMPNY_SENIORITY_DT ,A.SERVICE_DT ,
    A.PROF_EXPERIENCE_DT ,A.LAST_VERIFICATN_DT ,D.EXPECTED_RETURN_DT ,A.LAST_INCREASE_DT ,A.OWN_5PERCENT_CO ,
    A.PROBATION_DT ,A.SECURITY_CLEARANCE ,C.NEE_PROVIDER_ID ,A.POSITION_PHONE ,A.BUSINESS_TITLE ,D.REPORTS_TO ,
    D.SUPERVISOR_ID


    FROM PS_PER_ORG_ASGN A ,PS_PER_ORG_INST C , PS_JOB D


    WHERE A.EMPLID = C.EMPLID AND A.ORG_INSTANCE_ERN = C.ORG_INSTANCE_ERN AND A.EMPLID = D.EMPLID AND A.EMPL_RCD = D.EMPL_RCD AND D.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB JOB2 WHERE D.EMPLID = JOB2.EMPLID AND D.EMPL_RCD = JOB2.EMPL_RCD AND (( JOB2.EFFDT <= { FN CURDATE() }) OR (JOB2.EFFDT > { FN CURDATE() } AND { FN CURDATE() } < ( SELECT MIN(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = D.EMPLID AND J2.EMPL_RCD = D.EMPL_RCD) ) )) AND D.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB JOB3 WHERE JOB3.EMPLID = D.EMPLID AND JOB3.EMPL_RCD = D.EMPL_RCD AND JOB3.EFFDT = D.EFFDT )

    Wednesday, June 07, 2006 10:59 AM

Answers

  • Hi

    I have resolved this, the insert references more that one table, this is not allowed in an insert , only one of the base tables of a view can be referenced when updating or inserting through the view,\.

    Cheers

     

    Thursday, June 08, 2006 10:21 AM

All replies

  • Hi

    I have resolved this, the insert references more that one table, this is not allowed in an insert , only one of the base tables of a view can be referenced when updating or inserting through the view,\.

    Cheers

     

    Thursday, June 08, 2006 10:21 AM
  • One other trick that you can use is an instead of trigger. Then you won't have to care which columns are inserted.  Here is an example.

    CREATE TABLE tableNameA
    (
        tableNameId int PRIMARY KEY,
        valueA  varchar(10)
    )
    CREATE TABLE tableNameB
    (
        tableNameId int PRIMARY KEY,
        valueB  varchar(10)
    )
    GO
    CREATE VIEW tableName
    AS
        SELECT  coalesce(tableNameA.tableNameId, tableNameB.tableNameId) AS tableNameId,
                tableNameA.valueA, tableNameB.valueB
        FROM    tableNameA
                    FULL OUTER JOIN tableNameB
                        ON tableNameA.tableNameId = tableNameB.tableNameId
    GO

    ALTER TRIGGER tableName_insteadOFInsert
    ON tableName
    INSTEAD OF INSERT
    AS
      BEGIN
        SET NOCOUNT ON

        INSERT INTO tableNameA(tableNameId,valueA)
        SELECT tableNameId, valueA
        FROM inserted
        WHERE valueA IS NOT null

        INSERT INTO tableNameB(tableNameId,valueB)
        SELECT tableNameId, valueB
        FROM inserted
        WHERE valueB IS NOT null
     END
    GO

    INSERT INTO tableName(tableNameId, valueA, valueB)
    VALUES (1, NULL, '10')
    INSERT INTO tableName(tableNameId, valueA, valueB)
    VALUES (2, '20', '20')
    INSERT INTO tableName(tableNameId, valueA, valueB)
    VALUES (3, '30', NULL)
    GO             

    SELECT *
    FROM tableName
    GO

    Thursday, June 08, 2006 1:24 PM
    Moderator