Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136

    Unanswered: ORA-01776: cannot modify more than one base table through a join view

    Hello!

    I've got the following problem.

    I've created a view selecting columns from 2 or more different tables:

    CREATE OR REPLACE VIEW VIEW_TEST
    AS SELECT
    A.NAME,
    B.ZIP,
    A.PHONE
    FROM A, B
    WHERE A.ID = B.ID;

    Now trying to make an insert into the VIEW_TEST:

    insert into VIEW_TEST (NAME, ZIP, PHONE) values ('name', 123, 123);


    an error occurs:
    ORA-01776: cannot modify more than one base table through a join view

    Do i have any possibility to realize this?


    Version: 8.1.7
    Tool: Toad
    Regards,
    Julia

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: ORA-01776: cannot modify more than one base table through a join view

    You can create an INSTEAD OF trigger on the view that translates the insert statement into DML on the 2 base tables.

  3. #3
    Join Date
    Feb 2004
    Location
    India
    Posts
    135
    Hi Julia,

    You can't insert into view ! but can update.

  4. #4
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Hello again!

    I have the following view:
    ============================
    CREATE VIEW VIEW_TEST
    AS SELECT A.NAME
    , A.ID_LOC
    , B.CITY
    , B.STATE
    , B.COUNTRY
    , A.DEPARTMENT
    FROM A, B
    where A.ID_LOC = B.ID_LOC;
    ============================

    In my application i wanted to do on the presentation-layer this insert:

    insert into view_test(name, id_loc, city, state, country, department)
    values (.......);
    =============================
    -------
    Now i want to make a parameter (var...) passing in my Database-Layer.

    I have an INSTEAD-OF-trigger:

    CREATE TRIGGER TRG_VIEW_TEST_INST_OF
    INSTEAD OF INSERT ON VIEW_TEST
    FOR EACH ROW
    BEGIN
    insert into B (ID_LOC, CITY, state, country)
    values (varID, varCity, varState, varCountry);

    INSERT INTO A (ID, NAME, ID_LOC, DEPARTMENT)
    VALUES(varID, varName, varLoc, varDept);

    END TRG_TEST_INST_OF;
    ============================
    Maybe i don't need any trigger but function/package/procedure?

    It would be great if somebody could help me!
    Last edited by julla27; 03-22-04 at 10:49.
    Regards,
    Julia

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You don't need to pass any parameters just use :new like this:

    CREATE TRIGGER TRG_VIEW_TEST_INST_OF
    INSTEAD OF INSERT ON VIEW_TEST
    FOR EACH ROW
    BEGIN
    insert into B (ID_LOC, CITY, state, country)
    values (:new.ID, :new.City, :new.State, :new.Country);

    INSERT INTO A (ID, NAME, ID_LOC, DEPARTMENT)
    VALUES(:new.ID, :new.Name, :new.Loc, :new.Dept);

    END TRG_TEST_INST_OF;

    The :new variable names should match the view columns.

  6. #6
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    I have the structure like ISA.jpg,
    whereby :
    ====================
    T
    ====================
    ID (primary key)
    CATEGORY (e.g. 'A', 'B' or 'C')


    =========================
    A
    =========================
    ID (Primary key and Foreign key to T)
    NAME
    ID_LOC (Foreign Key To LOCATION)
    DEPARTMENT

    === B ===
    ID...
    =========

    =========
    LOCATION
    =========
    ID_LOC (Primary Key)
    ...
    ######################################
    My Trigger for Insert into a VIEW
    ######################################

    CREATE OR REPLACE TRIGGER TRG_VIEW_A_INSTEAD_OF_INSERT
    INSTEAD OF INSERT ON VIEW_A
    FOR EACH ROW
    BEGIN

    INSERT INTO LOCATION (id_loc, city)
    values (:new.ID_LOC, :new.CITY);


    INSERT INTO T (LIC_CATEGORY) VALUES ('A');


    INSERT INTO A (ID,
    NAME, ID_LOC, DEPARTMENT)
    VALUES(:new.ID,
    , :new.NAME
    , :new.ID_LOC
    , :new.DEPARTMENT);
    END;


    ###################################

    MY Trigger For ID
    ####################################

    CREATE OR REPLACE TRIGGER TRG_T_BEF_INS
    BEFORE INSERT ON T
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    DECLARE
    tmpVar1 NUMBER;
    BEGIN
    tmpVar1 := 0;
    SELECT SEQ_T.NEXTVAL INTO tmpVar1 FROM dual;
    :NEW.ID := tmpVar1;
    :NEW.CREATED_ON := SYSDATE;
    :NEW.CREATED_BY := USER;
    :NEW.CHANGED_ON := SYSDATE;
    :NEW.CHANGED_BY := USER;
    END;





    ################################

    Now the Question:

    How can I 'import' ID of T into A, view_A?

    I need parameters?


    #################################


    Thanks!
    Attached Thumbnails Attached Thumbnails isa.jpg  
    Regards,
    Julia

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Use SEQ_T.CURRVAL in the insert into A. That will return the value you just got by SEQ_T.NEXTVAL in the insert into T.

Posting Permissions

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