Forum Stats

  • 3,852,284 Users
  • 2,264,086 Discussions
  • 7,905,021 Comments

Discussions

ora 04091

user3266490
user3266490 Member Posts: 1,441
edited Sep 29, 2010 9:03AM in General Database Discussions
Hi,
we created two trigger for the table .before insert and after insert on that table.

when i executed,i got the error ora 04091
Tagged:

Answers

  • Centinul
    Centinul Member Posts: 6,871 Bronze Crown
    So that translates to a "mutating trigger" error.

    The only way we can help you is if you provide the following:

    1. Oracle version (SELECT * FROM V$VERSION)
    2. Table structure (CREATE statements)
    3. Trigger code
    4. Explanation of what you are trying to do.
  • Anurag Tibrewal
    Anurag Tibrewal Member Posts: 3,901 Gold Trophy
    user3266490 wrote:
    Hi,
    we created two trigger for the table .before insert and after insert on that table.

    when i executed,i got the error ora 04091
    Are you trying to select from the same table inside the trigger in which you have a trigger.
    If that is the case you are getting this error.
    You need to change the logic of the query.

    Regards
    Anurag
  • 800039
    800039 Member Posts: 5
    edited Sep 29, 2010 6:30AM
    Well. I have the same error and I cannot find how to solve it.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    1. We use Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    2. The table structures:
    --------------------------------
    //The table PAPER with a foreign key to the table BOOK
    //THIS TABLE HAS THE TRIGGER.
    CREATE TABLE PAPER
    (
    PAPER_ID NUMBER(10) NOT NULL,
    BOOK_ID NUMBER(10) NOT NULL,
    TEXT VARCHAR2(30)
    )
    //The table PAPER_MEM with a foreign key to the table BOOK AND a foreign key to the table VERSION.
    //THIS TABLE IS FILLED THROUGH THE TRIGGER.
    CREATE TABLE PAPER_MEM
    (
    PAPER_ID NUMBER(10) NOT NULL,
    BOOK_ID NUMBER(10) NOT NULL,
    TEXT VARCHAR2(30)
    VERSION_ID NUMBER(10) NOT NULL,
    )
    //The table BOOK has a foreign key to the table VERSION.
    CREATE TABLE BOOK
    (
    BOOK_ID NUMBER(10) NOT NULL,
    VERSION_ID NUMBER(10) NOT NULL,
    COMMENTS CLOB,
    )
    //The table VERSION
    CREATE TABLE VERSION
    (
    VERSION_ID NUMBER(10) NOT NULL,
    INFO CLOB,
    )
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    3. The Trigger I have so far:
    ---------------------------------------
    CREATE OR REPLACE TRIGGER TRIGGER_SAVE_PAPER
    AFTER INSERT OR UPDATE
    ON KATALOGPORTAL.PAPER
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    DECLARE
    v_version_id NUMBER;
    BEGIN

    --HAAL DE VERSION_ID OP
    SELECT v.version_id INTO v_version_id FROM VERSION v, BOOK ck, PAPER p WHERE v.VERSION_ID = ck.VERSION_ID AND p.KATALOG_ID = ck.KATALOG_ID AND p.ID = :old.ID;

    INSERT INTO PAPER_MEM VALUES (
    :new.PAPER_ID,
    :new.BOOK_ID,
    :new.TEXT,
    v_version_id
    );

    EXCEPTION
    WHEN OTHERS THEN
    -- Consider logging the error and then re-raise
    RAISE;
    END TRIGGER_SAVE_PAPER;
    /
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    4. Explanation
    --------------------
    The idea is that we have a table PAPER and PAPER_MEM.
    When a DML (INSERT/UPDATE/DELETE) is executed, all the data from table paper must be copied to the table paper_mem
    as we want to keep track of al the changes in the lifetime of a papper.

    The tricky part for me is that we need an extra field, called version_id, in the paper_mem table. which refers to another table VERSION.
    Every PAPER ha a BOOK which has VERSIONS. See the structure above.


    Greetings



    Christof

    Edited by: 797036 on Sep 29, 2010 3:30 AM
  • 800039
    800039 Member Posts: 5
    So, I found the answer.
    In the SELECT to get the VERSION_ID, I should start browsing from the book instead of the page.
    In this case, I don't make use of the paqe table.
This discussion has been closed.