1 2 Previous Next 19 Replies Latest reply on Jul 24, 2008 2:27 PM by BluShadow

    how to overcome mutating table error.

    544866
      Hi,

      I am writing a trigger on a table.In that trigger i want to query the same table for checking the row which i am going to insert exists or not by takeing rowcount.

      What is the solution for this?


      Thanks in advance,
      Nilesh Malekar
        • 1. Re: how to overcome mutating table error.
          Satish Kandi
          Use packages.

          Check out this link
          • 2. Re: how to overcome mutating table error.
            460631
            u can even use Autonomus trigger to avoid Mutating table error.
            U just have to add
            Pragma Autonomous_transaction in declare
            & include a commit.
            Although this practice has its own drawbacks as the trigger will be independent
            from the triggering event .Hence it may cause discrepency of data
            • 3. Re: how to overcome mutating table error.
              Colin'tHart
              Why can't you use a unique constraint?

              Cheers,

              Colin
              • 4. Re: how to overcome mutating table error.
                APC
                Why can't you use a unique constraint?
                Alternatively, depending on what action you want to happen if the row does exist, MERGE?

                Mutating table errors are almost always an indicator of a sub-optimal design, so it is worth checking our requirements and assumptions to see if there is a different approach which will meet our requirements without causing a mutatting table error.

                Cheers, APC
                • 5. Re: how to overcome mutating table error.
                  riedelme
                  Using the AUTONOMOUS TRANSACTION pragma in the trigger's PL/SQL block is one way to got - probably the easiest - but has some considerations.

                  Under just the wrong conditions it can cause data issues. With luck you won't encounter these conditions, though. There are complete write-ups on this issue on-line, and particuarly in Tom Kyte's web site. In particular you need to know that if you do use ATs you can't see any uncommited changes on the trigger table
                  • 6. Re: how to overcome mutating table error.
                    464504
                    One possible solution to avoid make a count on this table for the id you want to insert if count returns 0 then insert else do not insert , This should not be handled inside the trigger but before making an Insert .
                    • 7. Re: how to overcome mutating table error.
                      APC
                      This should not be handled inside the trigger but before making an Insert .
                      This will be no use in a multi-user environment.

                      Cheers, APC
                      • 8. Re: how to overcome mutating table error.
                        464504
                        Then Possibility deduces to Pragma Autonomous_transaction;

                        Thanx for opening the corridors (Multi User Environment)
                        • 9. Re: how to overcome mutating table error.
                          APC
                          you do use ATs you can't see any uncommited changes on the trigger table
                          The autonomous transaction approach is flawed in two ways. Yes it will not show you any uncommitted changes in other sessions, but that just results in ugly exceptions when we come to commit. More importantly it won't include any changes in out own session...
                          SQL> create or replace trigger trg1 before insert on t1
                            2  for each row
                            3  declare
                            4    n pls_integer;
                            5    pragma autonomous_transaction;
                            6  begin
                            7    select count(*) into n from t1
                            8    where col1 = :NEW.col1;
                            9    if n > 0 then raise_application_error(-20001, 'Problem!');
                          10    end if;
                          11    commit;
                          12  end;
                          13  /

                          Trigger created.

                          SQL> insert into t1 values (42, 'JJJJ')
                            2  /

                          1 row created.

                          SQL> insert into t1 values (42, 'JJJJ')
                            2  /

                          1 row created.

                          SQL> insert into t1 values (42, 'JJJJ')
                            2  /

                          1 row created.

                          SQL> select * from  t1 where col1 = 42
                            2  /
                                COL1 COL2
                          ---------- ----------
                                  42 JJJJ
                                  42 JJJJ
                                  42 JJJJ

                          SQL> commit;

                          Commit complete.

                          SQL> delete from t1 where col1 = 42
                            2  /

                          3 rows deleted.

                          SQL> insert into t1 values (42, 'JJJJ')
                            2  /
                          insert into t1 values (42, 'JJJJ')
                          *
                          ERROR at line 1:
                          ORA-20001: Problem!
                          ORA-06512: at "A.TRG1", line 7
                          ORA-04088: error during execution of trigger 'A.TRG1'


                          SQL>
                          Cheers, APC
                          • 10. Re: how to overcome mutating table error.
                            APC
                            Then Possibility deduces to Pragma Autonomous_transaction;
                            Nope, as I have just shown, the autonomous transaction approach doesn't work either. Depending on the precise requirenments the best solutions are:
                            (1) Primary or unique keys
                            (2) merge
                            (3) Redesign of data model or process flow
                            There is also
                            (4) serialization, e.g. with user defined locks, but we probably don't want to go there unless we really have to.

                            Cheers, APC
                            • 11. Re: how to overcome mutating table error.
                              464504
                              Create a function with Pragma Autonomous _Transaction and check for the newly inserted values by passing :new values frmo the trigger body to the Function  if found then handle exceptions else insert into table based on this :new value passed.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
                              • 12. Re: how to overcome mutating table error.
                                Himanshu Kandpal
                                Please refer to Oracle help
                                http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96590/786.

                                Thanks
                                • 13. Re: how to overcome mutating table error.
                                  APC
                                  Create a function with Pragma Autonomous _Transaction
                                  Fnord. What difference would that make? Precisely none. The whole point about autonomous transactions are that they are[i] independent of the current transaction.

                                  This does not just mean that our current transaction is isolated from commits or rollbacks in the autonomous transaction. It also means that any queries in the autonomous transaction cannot see the changes in the main transaction. Think of an autonomous transaction as opening up a new session: the exact same data consistency rules apply.

                                  Cheers, APC
                                  • 14. Re: how to overcome mutating table error.
                                    APC
                                    http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96590/786.
                                    That link is broken. I think you meant to direct Ritesh to here.

                                    Cheers, APC
                                    1 2 Previous Next