Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2003
    Posts
    68

    Unanswered: ORA-04091: table SYSADM.Mytable is mutating, trigger/function may not see it

    Hi to all

    please help to over come mutating in oracle

    create or replace trigger jtest
    after insert on MYTABLE
    FOR EACH ROW
    DECLARE
    v_username varchar2(10);
    emesg VARCHAR2(250);
    begin
    SELECT user INTO v_username FROM dual;
    update MYTABLE set EVENT_SEQNO=9999 ,EVENT_USER=v_username;
    EXCEPTION
    WHEN OTHERS THEN
    emesg := SQLERRM;
    dbms_output.put_line(emesg);
    end ;

    Trigger is created success fully

    Now i want to update mytable after insert the record in mytable.

    When i try to insert record in mytable it’s giving error



    insert into Mytable(EVENT_TYPE,ENTDATE,VALID_FROM,EVENT_SEQNO, EVENT_USER)
    values (123,sysdate,sysdate,999,'jtest' )

    ORA-04091: table SYSADM.Mytable is mutating, trigger/function may not see it


    please let me know how to over come mutating problem.



    regards
    jagan

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Use the referencing clause in the trigger and then just set :new.event_seqno:=9999 etc.

    Alan

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    In other words:
    Code:
    create or replace trigger jtest
    before insert on MYTABLE
    for each row
    begin
      :new.EVENT_SEQNO := 9999;
      :new.EVENT_USER := v_username;
    end;
    Note it needs to be a BEFORE trigger to change values.
    (And don't ever catch exceptions just to call DBMS_OUTPUT.PUT_LINE instead of letting them be raised properly!)

  4. #4
    Join Date
    Nov 2003
    Posts
    68

    thank u,its not wrokin

    hi
    Thank u for u reply

    i tried that also its not working, i think this mutating problem.
    how to over come mutating problem in this situation

    create or replace trigger jtest
    after insert on EXTRACT_QUEUE
    FOR EACH ROW
    DECLARE
    v_username varchar2(10);
    emesg VARCHAR2(250);
    begin
    SELECT user INTO v_username FROM dual;
    --update EXTRACT_QUEUE set EVENT_SEQNO=9999 ,EVENT_USER=v_username;
    :new.EVENT_SEQNO := 9999;
    :new.EVENT_USER := v_username;
    EXCEPTION
    WHEN OTHERS THEN
    emesg := SQLERRM;
    dbms_output.put_line(emesg);
    end ;


    create or replace trigger jtest
    *
    ERROR at line 1:
    ORA-04084: cannot change NEW values for this trigger type


    regards
    jagan

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Did you read my answer? Particularly the part where I said this:
    Note it needs to be a BEFORE trigger to change values.
    BEFORE, not AFTER.

    And I also showed that you don't want or need a SELECT statement or an EXCEPTION section in the trigger.

    There is no mutating problem here.

  6. #6
    Join Date
    Nov 2003
    Posts
    68

    thank u

    hi
    Thank u very much andrewst.
    Its working fine.
    Please tell me what is the difference between i before and after option is this trigger.
    Why it is giving mutating error in after option.
    When mutating problem will come?
    Please explain me, because “Before” insert the columns are null.
    How it will work internally?
    When the trigger will fire? Suppose there is a error in insert statement what will happen?
    “Before” option how it will work internally?


    Regards & thanks
    jagan

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Lots of questions!

    A BEFORE trigger fires before the data is actually modified in the database; an AFTER trigger fires afterwards. That is why you can change :new values in a BEFORE trigger but not in an AFTER trigger (when it is too late).

    It was not giving a mutating error in the AFTER option, it was giving "ORA-04084: cannot change NEW values for this trigger type" for the reason I just explained.

    The mutating problem occurs when you try to access the table on which the trigger is based during a FOR EACH ROW trigger. In your original trigger you were (unnecessarily) trying to perform an UPDATE on mytable in a FOR EACH ROW trigger on mytable. That is not allowed.

    :new refers to the new values to which you are setting the columns, : old refers to the values the columns had before the statement (for an insert, all : old values are of course null).

    The trigger will fire just before the data is inserted. If there is an error then an exception will be raised and the insert statement will be rolled back.

    I don't know what you mean by "internally" really, but I suggest you read the Concepts Guide to get a good understanding of triggers (and everything else come to that).

  8. #8
    Join Date
    Nov 2003
    Posts
    68

    thank very much

    hi
    thank veru much ur replay.
    please let me know what is the use of
    PRAGMA AUTONOMOUS_TRANSACTION; block
    in trigger?

    regards
    Jagan

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Autonomous transactions are transactions that are independent from the main transaction. A correct usage of this in a trigger would be to write audit or error message information to a table even when the triggering transaction failed and rolled back.

    Some people think it is a solution to the mutating table issue, but it isn't. Well, it works in as much as you no longer get the "table is mutating" error; but it potentially corrupts your data too, which ought to be considered a bad thing.

Posting Permissions

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