"ORA-04091" Mutating Error

  • Minnu

    SSCertifiable

    Points: 6451

    ora

  • hcprasadv

    Ten Centuries

    Points: 1215

    Check it out at....

    http://www.techonthenet.com/oracle/errors/ora04091.php

  • Minnu

    SSCertifiable

    Points: 6451

    Already checked the links. but not getting exact solution.

    is there any syntax error in trigger any issues.

    Please help me...

  • Lynn Pettis

    SSC Guru

    Points: 442116

    There are a few people here with Oracle experience that may be able to help but you might get better (or faster) answers on a dedicated Oracle forum rather than a MS SQL Server site.

    I worked with Oracle for a year but never wrote a trigger so I am not much help here.

  • Lowell

    SSC Guru

    Points: 323350

    i believe the mutating issue is when you try to update from anything other than the old; or new:

    you want to avoid that.

    it's not clear what you are trying to update; with no DDL, we can only assume all the columns referenced belong to the MODEL_CODE table the trigger seems to be defined on..

    old: would be empty/blank for INSERT commands.

    new: would be empty/blank for DELETE commands.

    it looks to me like there is no reason whatsoever to try and use the other table USER_ID

    CREATE OR REPLACE TRIGGER TRG_IUD

    AFTER INSERT OR DELETE OR UPDATE

    OF CODE,DESC

    ON MODEL_CODE

    REFERENCING OLD AS OLD

    FOR EACH ROW

    BEGIN

    UPDATE UID SET DATE_SET = current_timestamp

    WHERE UID_ID = :old.code;

    END;

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Minnu

    SSCertifiable

    Points: 6451

    Hi Team,

    given code is not working, here am giving my scenario with details.

    Table : Stud

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

    Stud_Code | Name

    View : CST

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

    Stud_id | LIb_id

    Table : LIB

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

    Lib_id | Current_Date

    Stud_Code column in "STUD" and stud_id column in "CST" are similar data.

    if there is any insert/update/delete on table "STUD" of columns "Stud_code or Name"

    then udpate current_date in LIB table where Lib_id = Lib_id of "CST" view.

    am getting mutating error, plz help

  • Lowell

    SSC Guru

    Points: 323350

    it's been a long time since you followed up on this, and you've changed which tables you are referring to.

    did you fix the issue on MODEL_CODE and now have the same mutating issue on another suite of tables?

    can you show us the DDL of both the STUD table, and the trigger you created to try and update the LIB table?

    there's lots and lots of examples you could modify if you are stuck on syntax, so you've got to provide a lot more detail if you want help from a forum.

    Search q=oracle+trigger+update+another+table

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Minnu

    SSCertifiable

    Points: 6451

    Hi,

    Below is the query am using..

    create or replace trigger tr_stud

    after insert or update or delete

    of stud_code , name

    on stud

    referencing old as old new as new

    for each row

    begin

    update lib set current_date =current_date

    where

    (select c.lib_id from cst c, stud t

    where t.stud_code=c.stud_id);

    end;

  • Lowell

    SSC Guru

    Points: 323350

    Minnu (4/16/2013)


    Hi,

    Below is the query am using..

    create or replace trigger tr_stud

    after insert or update or delete

    of stud_code , name

    on stud

    referencing old as old new as new

    for each row

    begin

    update lib set current_date =current_date

    where

    (select c.lib_id from cst c, stud t

    where t.stud_code=c.stud_id);

    end;

    well you didn't provide anything i can paste into Oracle SQLDeveloperr/TOAD, so all i can do is best-guess at syntax.

    your not referencing new; or old: anywhere, and you should have separate logic for when you insert vs when you delete...for example, why would you update the other table to some value if a row was deleted? you know your logic better than me, obviously.

    for example, if this trigger is doing insert/update/delete, i'd expect a block of code to determine each action?

    BEGIN

    IF inserting

    THEN

    trigger_api.tab1_row_change(p_id => :new.projectid, p_action => 'INSERT');

    ELSIF updating

    THEN

    trigger_api.tab1_row_change(p_id => :new.projectid, p_action => 'UPDATE');

    ELSIF deleting

    THEN

    trigger_api.tab1_row_change(p_id => :old.projectid, p_action => 'DELETE');

    END IF;

    END;

    until you are able to provide the complete table ddls (for all teh tables involved), the compete trigger, and a sample insert/update/delete and finally your expected results for each action, you are still going to have to google for the syntax and get it to work yourself.

    Best of luck to you!

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Minnu

    SSCertifiable

    Points: 6451

    Thanks.

    what ever i have, i given to you and asking for suggetion / help to come out from the error.

    if u want detailed ddl's and trigger.

    i'll complete the trigger after successfully execution without errors,

    i'll send u the code.

    Any how thanks.

  • PaulB-TheOneAndOnly

    SSC Guru

    Points: 66773

    Hard to tell for sure since poster cleaned up the original post but, ORA-04091 "Mutating Table Error" happens when a trigger attempts to select from, update or delete on the table that hosts the trigger.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply