This discussion is archived
3 Replies Latest reply: Dec 10, 2012 7:17 AM by Hoek RSS

mutating error problem

978999 Newbie
Currently Being Moderated
I have a trigger insert/update trigger_A on table A which updates the anathor table B.
I have created one more insert trigger_B on table B which updates a row in table A.

I tried to fixed it by using pragma autonomous_transaction but it is giving me deadlock error. Please help.
  • 1. Re: mutating error problem
    Justin Cave Oracle ACE
    Currently Being Moderated
    What, exactly, are you trying to accomplish?

    If this setup were to work, what would stop the infinite loop of triggers (an insert into A fires trigger_A which inserts into B which fires trigger_B which inserts into A which fires trigger_A...)?

    Are you storing the same data in two different tables? If so, why? That violates basic normalization. Why didn't you choose a properly normalized design?

    Justin
  • 2. Re: mutating error problem
    Paul Horth Expert
    Currently Being Moderated
    I tried to fixed it by using pragma autonomous_transaction but it is giving me deadlock error. Please help.
    Dom't use pragma autonomous_transaction in a trigger. The results can be detrimental to the data on your DB.

    Remember a trigger can fire more than once for a particular event.
  • 3. Re: mutating error problem
    Hoek Guru
    Currently Being Moderated
    Welcome to the forum.

    Triggers+autonomous transactions = recipe for disaster and the wrong way to go.
    You should rethink what it is you're trying to accomplish.
    Probably a COMMIT would get you past the deadlock error (if I recall correctly), but then your data can get corrupted, as you are messing with transactions.
    Probably a procedural approach would be best.
    Triggers have several disadvantages (slow-by-slow processing, performance, 'automagical' stuff happening), as you now are discovering.

    Consult the Online Oracle Documentation regarding autonomous transactions and the deadlock error @
    http://www.oracle.com/pls/db112/homepage

    And for some more background:
    http://www.oracle.com/technetwork/issue-archive/2008/08-sep/o58asktom-101055.html

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points