Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2010
    Posts
    24

    Red face Unanswered: PL SQL trigger problem

    Dear all,
    I got a requirement to create a trigger on a table X and wanted to update the same table X in trigger body.
    But this will return Mutating error, as same table is used.
    Hence I used alternative approach.

    Step 1.
    Code:
    CREATE OR REPLACE TRIGGER new_trigger AFTER INSERT ON X FOR EACH ROW
    when (condition)
    DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN 
    insert into dummy_table values(new.value1, new.value2....);
    commit;
     END;

    step 2.
    Code:
    CREATE OR REPLACE TRIGGER another_trigger AFTER INSERT ON dummy_table FOR EACH ROW
    when (condition)
    DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN 
    insert into X values (new.value1, new.value2....);
    commit;
     END;
    This should work logically. But when I tested, it succeeds only once in 5 times. It will not complete step 1 itself.
    Why is this discrepancy? Is it because I am using autonomous transaction?
    What can be the possible workaround for this ?

    Thanks in Advance

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Your code differs from your words. You said that you want to UPDATE the same table, but you used INSERT statement. What is true?

    Besides, columns are referenced with :NEW identifiers (colon + NEW).

  3. #3
    Join Date
    Jul 2010
    Posts
    24

    Question

    In step 2, there are 2 statements.. One is update and another is insert.. I just wrote insert statement there.
    But my prob is, step 1 itself is not completing successfully.

    Regarding new, I have included :new itself.

    Thanks for your suggestion.

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Firstly, mutating is caused by accessing the same table on which is the trigger fired (or when multiple triggers are fired, all tables which are involved). The code you posted does not do that - so the problem is in another code/trigger which you did not post. Maybe you should analyze the whole error stack, not only its first line.

    Anyway, even in the posted code, you have different problem. After INSERT to X, a trigger INSERTing to DUMMY_TABLE is fired. Which fires the second trigger which INSERTs to X. After that, a trigger INSERTing to DUMMY_TABLE is fired ... and you are in endless loop unless <condition> handles it. Which is impossible to tell from your post.

    If you are really facing mutating error, you should read following articles on AskTom:
    some comments: http://asktom.oracle.com/pls/apex/f?...:9579487119866
    about a "workaround": http://asktom.oracle.com/pls/asktom/...D:290416059674
    or here: http://asktom.oracle.com/pls/asktom/...98119097816936
    about using "autonomous": http://asktom.oracle.com/pls/asktom/...#9110881918510
    (for the latest, you should not, that will just introduce another issues)

    In the end, I would stress one sentence from the first one:
    My personal opinion -- when I hit a mutating table error, I've got a serious fatal flaw in my logic.

  5. #5
    Join Date
    Jul 2010
    Posts
    24

    Question

    Thanks for your reply!


    Actual requirement is:

    When ever there is any insert on table X, another row should be entered on table X itself with different values..

    For example:

    Code:
    insert into X values('manager'); is executed,
    
    then insert:
    
    insert into X values ('employee');
    If I directly do it in a single trigger, it will throw mutation error. Hence I created one more trigger. and followed the logic pasted above.

    It will not go to endless loop, as I am checking in the condition whether value inserted is 'manager'.


    Any idea?!

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    Reconsider, whether you want to do it in a database trigger and not in the application. Also think about your design and rather normalize it instead of making multiple workarounds to keep it "working" (whatever it is, as you did not post any details, I cannot judge it; but bad design often leads to requirements like these).

    The only correct workaround is described in the AskTom links I posted previously - e.g. this: http://asktom.oracle.com/pls/asktom/...D:290416059674. Shortly, it consists of declaring additional collection variable(s) and creating three triggers: before statement for clearing them, before/after row for filling them and after statement for running conflicting statements.

    Involve only necessary table - as you could see, spreading it to multiple tables or abusing autonomous transactions is the wrong (and blind) way.

Tags for this Thread

Posting Permissions

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