DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Mutating Error

  1. #1
    Join Date
    May 2001
    Location
    Dallas, US
    Posts
    78

    Unhappy

    Hai Friends,
    Any one help me out?...

    I have a written a trigger for a table.. I got an error at the time of testing that trigger...

    The error gives as

    ORA-04091: table REF_COST_CENTRE is mutating, trigger/function may not see it
    ORA-06512: at T_DU_REF_COST_CENTRE", line 41
    ORA-04088: error during execution of trigger T_DU_REF_COST_CENTRE'

    I know that mutating means... we are updating a row, which is selected by the trigger...

    How to overcome this problem..


    My trigger is

    CREATE OR REPLACE TRIGGER T_DU_REF_COST_CENTRE
    BEFORE DELETE OR UPDATE ON REF_COST_CENTRE FOR EACH ROW
    DECLARE
    lv_numrows INTEGER;
    lv_numrowsmst INTEGER;
    BEGIN
    IF DELETING THEN
    SELECT COUNT(*) INTO lv_numrows
    FROM M_BP_COST_CENTRE
    WHERE IE_COST_CENTRE_CODE
    = :OLD.COST_CENTRE_CODE
    AND COUNTRYCODE = :OLD.COUNTRYCODE;

    IF lv_numrows > 0 THEN
    raise_application_error(-20002,
    'CANNOT DELETE FROM REF_COST_CENTRE TABLE
    BECAUSE VALUE IS EXISTING IN
    M_BP_COST_CENTRE TABLE');
    END IF;
    END IF;

    IF UPDATING THEN
    IF :NEW.COST_CENTRE_CODE
    <> :OLD.COST_CENTRE_CODE THEN
    SELECT COUNT(*) INTO lv_numrows
    FROM M_BP_COST_CENTRE
    WHERE IE_COST_CENTRE_CODE
    = :OLD.COST_CENTRE_CODE
    AND COUNTRYCODE = :OLD.COUNTRYCODE;

    IF lv_numrows > 0 THEN
    SELECT COUNT(*) INTO lv_numrowsmst
    FROM REF_COST_CENTRE
    WHERE COST_CENTRE_CODE
    = :OLD.COST_CENTRE_CODE
    AND COUNTRYCODE = :OLD.COUNTRYCODE;

    IF lv_numrowsmst = 1 THEN
    raise_application_error(-20002,
    'CANNOT UPDATE REF_COST_CENTRE TABLE
    BECAUSE VALUE IS EXISTING IN
    M_BP_COST_CENTRE TABLE');
    END IF;
    END IF;
    END IF;



    Thanx..

    Palani Kumar. R
    RP Kumar
    You Can Win, if u believe Yourself

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Hi,

    How to overcome this problem..

    1. The best way would be using Foreign key instead of triggers.
    Oracle referential integrity provides the same functionality as your trigger.
    2. If you insist on using triggers, then remove statement causing error.
    If you are trying to delete a row and your trigger is BEFORE delete, you can be sure that in the table REF_COST_CENTRE is at least one row with COST_CENTRE_CODE and COUNTRYCODE you write in WHERE clause - the row you are deleting.
    Thus, you don't need to test it.

    Regards,
    Ales

  3. #3
    Join Date
    May 2001
    Location
    Dallas, US
    Posts
    78

    Mutating Error

    Hai Ales,
    Thanks for your Answer...

    I need more clarification about your Answer..

    Thanx.
    Palani Kumar. R

    palanir@sakinfotech.co.in

    RP Kumar
    You Can Win, if u believe Yourself

  4. #4
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    There's lack of information about your tables to expand on that.
    Post definition of tables, constraints ...

    Why do you count rows in REF_COST_CENTRE table when UPDATING a row?
    I guess that's useless and even causing the ORA-04091 error:

    IF lv_numrows > 0 THEN
    SELECT COUNT(*) INTO lv_numrowsmst
    FROM REF_COST_CENTRE
    WHERE COST_CENTRE_CODE
    = :OLD.COST_CENTRE_CODE
    AND COUNTRYCODE = :OLD.COUNTRYCODE;



  5. #5
    Join Date
    Aug 2000
    Posts
    462
    Mutating table errors are caused by a trigger attempting to run DML against a table which is undergoing DML (mutating table error.

    Basically, here's how you get around it:

    1. Create a package. In the package, declare a PL/SQL table of the PK value or rowid from the table undergoing the DML.

    2. Create a before statement trigger on the table undergoing DML, which initializes the PL/SQL table.

    3. Create a row trigger which populates the PL/SQL table with the rows you'd like to update/process in a way that was causing the mutating table error.

    4. Create an after statement trigger. In this trigger, process the rows of the PL/SQL table and do whatever action you need. The mutation of the table is over, so the DML is now allowed.

    http://technet.oracle.com/doc/oracle...3trg.htm#11808








    Here's an example from Metalink:










    Doc ID: Note:37906.1
    Subject: ORACLE MUTATING TABLE PROBLEM
    Type: FAQ
    Status: PUBLISHED
    Content Type: TEXT/PLAIN
    Creation Date: 31-OCT-1996
    Last Revision Date: 08-MAR-2000


    ORACLE MUTATING TABLE PROBLEM
    =============================

    This paper and the associated programs are offered to the public domain for
    enhancement and research by an Oracle customer, Arup Nanda. No warranty is
    either expressed or implied. The end user is solely responsible for results
    of the execution of the programs.


    PROBLEM:
    -------

    It is a pretty common problem in Oracle that happens and frustrates many
    application developers and DBAs as the application developers bug them to find
    a solution to it. It happens when a trigger on a table tries to insert,
    update, or even select the table of whose trigger is being executed. Sometimes
    the inability to see the table causes standstill in the development. This
    paper and the associated scripts attempt to present a work around for the
    problem. The work around is not the best one nor the author claims it to be
    an elegant one, but it provides a solution that will be applicable in most
    cases. The approach is described by a case study.


    SCENARIO
    --------

    The mutating table problem comes when the trigger tries to select or modify
    any row of the same table. This situation comes when a table preserves some
    effective date.

    To describe this method, I will be using the table SALGRADE.

    The table SALGRADE contains information on salary limits for each grade. The
    salary limits are also based on a time factor, i.e. the employees' salary is
    determined by checking which grade level was effective when they joined or
    reviewed, not necessarily the grade effective now.

    So the table looks like this:

    SQL> desc salgrade;
    Name Null? Type
    ------------------------------- -------- ----
    GRADE NUMBER
    LOSAL NUMBER
    HISAL NUMBER
    START_DATE DATE
    END_DATE DATE

    The table data looks like:

    GRADE LOSAL HISAL START_DATE END_DATE
    ------- ------- ------- ---------- ---------
    1 1000 2000 1-APR-94 3-AUG-95
    1 1200 2200 3-AUG-95 <---- Null
    2 1500 3000 23-JUL-92 12-DEC-93
    2 1600 3200 12-dec-93 11-jan-95
    2 1800 3400 11-JAN-95 <---- Null

    This means the effective salary range of Grade 1 now is (1200-2200) nut the
    employees who had review between 1-APR-94 to 3-AUG-95 will be in the range
    (1000-2000). This is a purely hypothetical scenario. Our objective is to
    devise a trigger that does the following when a new record is inserted:

    (1) Integrity checking for overlapping dates, i.e. the new record can't have a
    start date that is already covered.

    (2) Update the record for the current grade to make the end_date equal to the
    start date of the new record (the new record's end_date must be null as
    that is the current record).

    In both cases the table SALGRADE has to be selected and updated on the after
    insert row trigger on the same table. But the table will be mutating when the
    trigger fires and thus a run-time error will occur.

    For the first requirement, consider the following trigger:

    create or replace trigger taiudr_salgrade
    after insert on salgrade
    for each row
    declare
    hold_found varchar2(1);
    begin
    select 'Y' into hold_found
    from salgrade
    where grade = :new.grade
    and end_date is null
    and start_date > :new.start_date;

    exception
    when NO_DATA_FOUND then
    raise_application_error(-20000,'Overlapping Dates');
    end;
    /

    Although the trigger can be created with no errors, when a user tries to
    insert into the table he will receive the mutating table error:

    SQL> insert into salgrade values (2, 9000, 100000, '25-dec-95', null);
    insert into salgrade values (2, 9000, 100000, '25-dec-93', null)
    *
    ERROR at line 1:
    ORA-04091: table JACK.SALGRADE is mutating, trigger/function may not see it
    ORA-06512: at line 4
    ORA-04088: error during execution of trigger 'JACK.TAIUDR_SALGRADE'

    SOLUTION
    --------

    The following approach is another possibility for the task:

    1. Create a package "SALGRADE_PKG" that contains PL/SQL tables for holding the
    SALGRADE data. Here we create 3 tables - one for holding start_dates, one
    for end_dates, and one for holding the change_flag that identifies the
    updated row.

    2. Create a BEFORE INSERT STATEMENT trigger that populates the PL/SQL table
    with the start dates, end_dates and changed_grades flag ('N').

    3. Create an AFTER INSERT ROW trigger that compares the newly inserted row
    against this PL/SQL table not the Database table. This way the integrity
    check can be done. The same trigger should assign the new end_date value
    to the PL/SQL table and update the value of the flag to indicate that this
    has to be changed.

    4. Create a AFTER INSERT STATEMENT trigger to update the SALGRADE table with
    the values in the PL/SQL table after looking at the change flag.

    All these programs can be created by the sources found below. I urge you to
    test them and make any enhancements to them as you find necessary.


    CODE:
    ----

    Code to create test table and populate it with data:

    drop table salgrade;

    CREATE TABLE SALGRADE
    (GRADE NUMBER,
    LOSAL NUMBER,
    HISAL NUMBER,
    START_DATE DATE,
    END_DATE DATE);

    INSERT INTO SALGRADE VALUES (1,1000,2000, '1-apr-94', '3-aug-95');
    INSERT INTO SALGRADE VALUES (1,1200,2200, '3-aug-95', null);
    INSERT INTO SALGRADE VALUES (2,1500,3000, '23-Jul-92', '12-dec-93');
    INSERT INTO SALGRADE VALUES (2,1600,3200, '12-dec-93', '11-jan-95');
    INSERT INTO SALGRADE VALUES (2,1800,3400, '11-jan-95', null);

    Code for package STEP 1 above:

    create or replace package salgrade_pkg as
    type datetabtype is table of date index by binary_integer;
    type chartabtype is table of char(1) index by binary_integer;
    type rowidtabtype is table of rowid index by binary_integer;
    start_date_tab datetabtype;
    end_date_tab datetabtype;
    rowid_tab rowidtabtype;
    changed_grade chartabtype;
    start_date_tab_size binary_integer;
    end;
    /

    Code for before insert statement trigger STEP 2 above:

    create or replace trigger tbiuds_salgrade
    before insert on salgrade
    declare
    hold_start_date date;
    hold_end_date date;
    hold_rowid rowid;
    hold_grade binary_integer;
    cursor start_date_cur is
    select rowid, grade, start_date
    from salgrade
    where end_date is null
    order by grade;
    begin
    open start_date_cur;
    loop
    fetch start_date_cur into
    hold_rowid, hold_grade, hold_start_date;
    exit when start_date_cur%notfound;
    salgrade_pkg.start_date_tab(hold_grade) := hold_start_date;
    salgrade_pkg.end_date_tab(hold_grade) := hold_end_date;
    salgrade_pkg.rowid_tab(hold_grade) := hold_rowid;
    salgrade_pkg.changed_grade(hold_grade) := 'N';
    end loop;
    salgrade_pkg.start_date_tab_size := hold_grade;
    close start_date_cur;
    end;
    /

    Code for after insert row trigger STEP 3 above:

    create or replace trigger taiudr_salgrade
    after insert on salgrade
    for each row
    begin
    if (:new.grade <= salgrade_pkg.start_date_tab_size) then
    if salgrade_pkg.start_date_tab(:new.grade)
    > :new.start_date then
    raise_application_error(-20001,'Overlapping Dates');
    end if;
    salgrade_pkg.end_date_tab(:new.grade) := :new.start_date;
    salgrade_pkg.changed_grade(:new.grade) := 'Y';
    else
    salgrade_pkg.start_date_tab(:new.grade) := :new.start_date;
    salgrade_pkg.end_date_tab(:new.grade) := :new.end_date;
    salgrade_pkg.changed_grade(:new.grade) := 'N';
    salgrade_pkg.start_date_tab_size :=
    salgrade_pkg.start_date_tab_size + 1;
    end if;
    end;
    /

    Code for after insert statement trigger STEP 4 above:

    create or replace trigger taiuds_salgrade
    after insert on salgrade
    begin
    for i in 1..(salgrade_pkg.start_date_tab_size) loop
    if (salgrade_pkg.changed_grade(i) = 'Y') then
    update salgrade
    set end_date = salgrade_pkg.end_date_tab(i)
    where rowid = salgrade_pkg.rowid_tab(i);
    end if;
    end loop;
    end;
    /

    Once all the package and triggers are created, the same insert doesn't succeed
    but gives the user the a more appropriate error:

    SQL> insert into salgrade values (2, 9000, 100000, '25-dec-93', null);
    insert into salgrade values (2, 9000, 100000, '25-dec-93', null)
    *
    ERROR at line 1:
    ORA-20001: Overlapping Dates
    ORA-06512: at line 5
    ORA-04088: error during execution of trigger 'JACK.TAIUDR_SALGRADE'

    CONCLUSION:
    ----------

    This method is not submitted as a solution to all cases, however, it is
    provided as one possibility. The test and code are provided so modifications
    can be made to suit the individual cases. Be sure to test this completely
    before relying on it's functionality.

    Oracle DBA and Developer

  6. #6
    Join Date
    Jul 2001
    Location
    Singapore
    Posts
    2
    Originally posted by kmesser
    Mutating table errors are caused by a trigger attempting to run DML against a table which is undergoing DML (mutating table error.

    Basically, here's how you get around it:

    1. Create a package. In the package, declare a PL/SQL table of the PK value or rowid from the table undergoing the DML.

    2. Create a before statement trigger on the table undergoing DML, which initializes the PL/SQL table.

    3. Create a row trigger which populates the PL/SQL table with the rows you'd like to update/process in a way that was causing the mutating table error.

    4. Create an after statement trigger. In this trigger, process the rows of the PL/SQL table and do whatever action you need. The mutation of the table is over, so the DML is now allowed.

    http://technet.oracle.com/doc/oracle...3trg.htm#11808








    Here's an example from Metalink:










    Doc ID: Note:37906.1
    Subject: ORACLE MUTATING TABLE PROBLEM
    Type: FAQ
    Status: PUBLISHED
    Content Type: TEXT/PLAIN
    Creation Date: 31-OCT-1996
    Last Revision Date: 08-MAR-2000


    ORACLE MUTATING TABLE PROBLEM
    =============================

    This paper and the associated programs are offered to the public domain for
    enhancement and research by an Oracle customer, Arup Nanda. No warranty is
    either expressed or implied. The end user is solely responsible for results
    of the execution of the programs.


    PROBLEM:
    -------

    It is a pretty common problem in Oracle that happens and frustrates many
    application developers and DBAs as the application developers bug them to find
    a solution to it. It happens when a trigger on a table tries to insert,
    update, or even select the table of whose trigger is being executed. Sometimes
    the inability to see the table causes standstill in the development. This
    paper and the associated scripts attempt to present a work around for the
    problem. The work around is not the best one nor the author claims it to be
    an elegant one, but it provides a solution that will be applicable in most
    cases. The approach is described by a case study.


    SCENARIO
    --------

    The mutating table problem comes when the trigger tries to select or modify
    any row of the same table. This situation comes when a table preserves some
    effective date.

    To describe this method, I will be using the table SALGRADE.

    The table SALGRADE contains information on salary limits for each grade. The
    salary limits are also based on a time factor, i.e. the employees' salary is
    determined by checking which grade level was effective when they joined or
    reviewed, not necessarily the grade effective now.

    So the table looks like this:

    SQL> desc salgrade;
    Name Null? Type
    ------------------------------- -------- ----
    GRADE NUMBER
    LOSAL NUMBER
    HISAL NUMBER
    START_DATE DATE
    END_DATE DATE

    The table data looks like:

    GRADE LOSAL HISAL START_DATE END_DATE
    ------- ------- ------- ---------- ---------
    1 1000 2000 1-APR-94 3-AUG-95
    1 1200 2200 3-AUG-95 <---- Null
    2 1500 3000 23-JUL-92 12-DEC-93
    2 1600 3200 12-dec-93 11-jan-95
    2 1800 3400 11-JAN-95 <---- Null

    This means the effective salary range of Grade 1 now is (1200-2200) nut the
    employees who had review between 1-APR-94 to 3-AUG-95 will be in the range
    (1000-2000). This is a purely hypothetical scenario. Our objective is to
    devise a trigger that does the following when a new record is inserted:

    (1) Integrity checking for overlapping dates, i.e. the new record can't have a
    start date that is already covered.

    (2) Update the record for the current grade to make the end_date equal to the
    start date of the new record (the new record's end_date must be null as
    that is the current record).

    In both cases the table SALGRADE has to be selected and updated on the after
    insert row trigger on the same table. But the table will be mutating when the
    trigger fires and thus a run-time error will occur.

    For the first requirement, consider the following trigger:

    create or replace trigger taiudr_salgrade
    after insert on salgrade
    for each row
    declare
    hold_found varchar2(1);
    begin
    select 'Y' into hold_found
    from salgrade
    where grade = :new.grade
    and end_date is null
    and start_date > :new.start_date;

    exception
    when NO_DATA_FOUND then
    raise_application_error(-20000,'Overlapping Dates');
    end;
    /

    Although the trigger can be created with no errors, when a user tries to
    insert into the table he will receive the mutating table error:

    SQL> insert into salgrade values (2, 9000, 100000, '25-dec-95', null);
    insert into salgrade values (2, 9000, 100000, '25-dec-93', null)
    *
    ERROR at line 1:
    ORA-04091: table JACK.SALGRADE is mutating, trigger/function may not see it
    ORA-06512: at line 4
    ORA-04088: error during execution of trigger 'JACK.TAIUDR_SALGRADE'

    SOLUTION
    --------

    The following approach is another possibility for the task:

    1. Create a package "SALGRADE_PKG" that contains PL/SQL tables for holding the
    SALGRADE data. Here we create 3 tables - one for holding start_dates, one
    for end_dates, and one for holding the change_flag that identifies the
    updated row.

    2. Create a BEFORE INSERT STATEMENT trigger that populates the PL/SQL table
    with the start dates, end_dates and changed_grades flag ('N').

    3. Create an AFTER INSERT ROW trigger that compares the newly inserted row
    against this PL/SQL table not the Database table. This way the integrity
    check can be done. The same trigger should assign the new end_date value
    to the PL/SQL table and update the value of the flag to indicate that this
    has to be changed.

    4. Create a AFTER INSERT STATEMENT trigger to update the SALGRADE table with
    the values in the PL/SQL table after looking at the change flag.

    All these programs can be created by the sources found below. I urge you to
    test them and make any enhancements to them as you find necessary.


    CODE:
    ----

    Code to create test table and populate it with data:

    drop table salgrade;

    CREATE TABLE SALGRADE
    (GRADE NUMBER,
    LOSAL NUMBER,
    HISAL NUMBER,
    START_DATE DATE,
    END_DATE DATE);

    INSERT INTO SALGRADE VALUES (1,1000,2000, '1-apr-94', '3-aug-95');
    INSERT INTO SALGRADE VALUES (1,1200,2200, '3-aug-95', null);
    INSERT INTO SALGRADE VALUES (2,1500,3000, '23-Jul-92', '12-dec-93');
    INSERT INTO SALGRADE VALUES (2,1600,3200, '12-dec-93', '11-jan-95');
    INSERT INTO SALGRADE VALUES (2,1800,3400, '11-jan-95', null);

    Code for package STEP 1 above:

    create or replace package salgrade_pkg as
    type datetabtype is table of date index by binary_integer;
    type chartabtype is table of char(1) index by binary_integer;
    type rowidtabtype is table of rowid index by binary_integer;
    start_date_tab datetabtype;
    end_date_tab datetabtype;
    rowid_tab rowidtabtype;
    changed_grade chartabtype;
    start_date_tab_size binary_integer;
    end;
    /

    Code for before insert statement trigger STEP 2 above:

    create or replace trigger tbiuds_salgrade
    before insert on salgrade
    declare
    hold_start_date date;
    hold_end_date date;
    hold_rowid rowid;
    hold_grade binary_integer;
    cursor start_date_cur is
    select rowid, grade, start_date
    from salgrade
    where end_date is null
    order by grade;
    begin
    open start_date_cur;
    loop
    fetch start_date_cur into
    hold_rowid, hold_grade, hold_start_date;
    exit when start_date_cur%notfound;
    salgrade_pkg.start_date_tab(hold_grade) := hold_start_date;
    salgrade_pkg.end_date_tab(hold_grade) := hold_end_date;
    salgrade_pkg.rowid_tab(hold_grade) := hold_rowid;
    salgrade_pkg.changed_grade(hold_grade) := 'N';
    end loop;
    salgrade_pkg.start_date_tab_size := hold_grade;
    close start_date_cur;
    end;
    /

    Code for after insert row trigger STEP 3 above:

    create or replace trigger taiudr_salgrade
    after insert on salgrade
    for each row
    begin
    if (:new.grade <= salgrade_pkg.start_date_tab_size) then
    if salgrade_pkg.start_date_tab(:new.grade)
    > :new.start_date then
    raise_application_error(-20001,'Overlapping Dates');
    end if;
    salgrade_pkg.end_date_tab(:new.grade) := :new.start_date;
    salgrade_pkg.changed_grade(:new.grade) := 'Y';
    else
    salgrade_pkg.start_date_tab(:new.grade) := :new.start_date;
    salgrade_pkg.end_date_tab(:new.grade) := :new.end_date;
    salgrade_pkg.changed_grade(:new.grade) := 'N';
    salgrade_pkg.start_date_tab_size :=
    salgrade_pkg.start_date_tab_size + 1;
    end if;
    end;
    /

    Code for after insert statement trigger STEP 4 above:

    create or replace trigger taiuds_salgrade
    after insert on salgrade
    begin
    for i in 1..(salgrade_pkg.start_date_tab_size) loop
    if (salgrade_pkg.changed_grade(i) = 'Y') then
    update salgrade
    set end_date = salgrade_pkg.end_date_tab(i)
    where rowid = salgrade_pkg.rowid_tab(i);
    end if;
    end loop;
    end;
    /

    Once all the package and triggers are created, the same insert doesn't succeed
    but gives the user the a more appropriate error:

    SQL> insert into salgrade values (2, 9000, 100000, '25-dec-93', null);
    insert into salgrade values (2, 9000, 100000, '25-dec-93', null)
    *
    ERROR at line 1:
    ORA-20001: Overlapping Dates
    ORA-06512: at line 5
    ORA-04088: error during execution of trigger 'JACK.TAIUDR_SALGRADE'

    CONCLUSION:
    ----------

    This method is not submitted as a solution to all cases, however, it is
    provided as one possibility. The test and code are provided so modifications
    can be made to suit the individual cases. Be sure to test this completely
    before relying on it's functionality.

    If you are using Oracle 8i version and above then you can use materialized views to avoid this mutating problem.
    This is another alternative.

    Regards,
    Vel

Posting Permissions

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


Click Here to Expand Forum to Full Width