3 Replies Latest reply: Dec 10, 2012 9:17 AM by Hoek RSS

    mutating error problem

    978999
      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
          JustinCave
          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
            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
              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