7 Replies Latest reply on Apr 12, 2013 11:23 AM by 994099

    How to solve ora-04091: mutating table

    994099
      Hello all,

      Oracle version: 10g express.

      case study: one class has many students, only one student has reward.

      original design:
      drop table student;
      drop table class;
      
      create table class(
      clsid varchar2(9) primary key);
      
      create table student(
      stuid varchar2(9) primary key,
      clsid varchar2(9) not null references class(clsid) on delete cascade,
      reward varchar2(9));
      sample data:
      insert into class values('cls1');
      insert into student values('stu1','cls1','yes');
      questions:
      1. If I want
       insert into student values('stu2','cls1','yes'); 
      I can put a trigger on student to check whether it has only one student in class has reward. This trigger should tell me error: I can not let 'stu2' has 'yes' on reward column. But it will lead Oracle error: ora-04091: mutating table. It seems use "after" or "instead of" trigger can solve this problem, so how to do it?

      2. Another way to make sure only one student in a class has reward is change design as:
      drop table student;
      drop table class;
      
      create table class(
      clsid varchar2(9) primary key,
      reward_stuid varchar2(9));
      
      create table student(
      stuid varchar2(9) primary key,
      clsid varchar2(9) not null references class(cid) on delete cascade);
      But the question is these two tables has foreign key from each other, is it a good design?

      Thanks.

      Edited by: 991096 on 11-Apr-2013 04:56
        • 1. Re: How to solve ora-04091: mutating table
          Vite DBA
          Hi,

          what happens if a student takes more than one class?

          Regards
          Andre
          • 2. Re: How to solve ora-04091: mutating table
            BluShadow
            Don't use a trigger for this. It's business logic that you should include in your application as a 'pre check' before you even attempt to insert, or...
            You use an INSERT ... SELECT ... statement that incorporates the check as part of it, so the row will only be inserted if the correct criteria is met.

            e.g.
            insert into student (stuid, clsid, reward)
            select 'stu2', 'cls1', 'yes'
            from   dual left outer join student s2 on (s2.clsid = 'cls1' and s2.reward = 'yes')
            where  s2.stuid is null; -- only insert if no other student is found in same class already with reward
            and then your application can check SQL%ROWCOUNT to see if the row was inserted or not.

            (likewise you could use a MERGE statement or the (NOT) EXISTS clause or whatever method to do it)
            • 3. Re: How to solve ora-04091: mutating table
              994099
              Hi,

              One student belongs one class and one class has many student.
              • 4. Re: How to solve ora-04091: mutating table
                John Spencer
                As an alternative to Blu's solution, you could do something like:
                SQL> create table class(
                  2  clsid varchar2(9) primary key);
                
                Table created.
                
                SQL> create table student(
                  2  stuid varchar2(9) primary key,
                  3  clsid varchar2(9) not null references class(clsid) on delete cascade,
                  4  reward varchar2(9));
                
                Table created.
                
                SQL> create unique index class_reward
                  2  on student (clsid, case when reward = 'yes' then reward end);
                
                Index created.
                
                SQL> insert into class values('cls1');
                
                1 row created.
                
                SQL> commit;
                
                Commit complete.
                
                SQL> insert into student values('stu1','cls1','yes');
                
                1 row created.
                
                SQL> insert into student values('stu2','cls1','no');
                
                1 row created.
                
                SQL> insert into student values('stu3','cls1','yes');
                insert into student values('stu3','cls1','yes')
                *
                ERROR at line 1:
                ORA-00001: unique constraint (OPS$ORACLE.CLASS_REWARD) violated
                John
                • 5. Re: How to solve ora-04091: mutating table
                  Chris Hunt
                  But John, that will fail if you do this too:
                  insert into student values('stu3','cls1','no');
                  because there'll be multiple students with clsid=cls1 and reward != yes.

                  You need to set the index up like this instead:
                   create unique index class_reward
                    on student (clsid, case when reward = 'yes' then reward else stuid end);
                  • 6. Re: How to solve ora-04091: mutating table
                    John Spencer
                    Chris:

                    You are absolutely correct. I really shouldn't post before the coffee kicks in :-(

                    John

                    Edited by: John Spencer on Apr 11, 2013 2:56 PM


                    Although, on further reflection, if the intent is solely to maintain only one reward per class, then I over-complicated it.
                    SQL> create table class(
                      2  clsid varchar2(9) primary key);
                    
                    Table created.
                    
                    SQL> create table student(
                      2  stuid varchar2(9) primary key,
                      3  clsid varchar2(9) not null references class(clsid) on delete cascade,
                      4  reward varchar2(9));
                    
                    Table created.
                    
                    SQL> create unique index one_reward_per_class
                      2  on student (case when reward = 'yes' then clsid end);
                    
                    Index created.
                    
                    SQL> insert into class values('cls1');
                    
                    1 row created.
                    
                    SQL> insert into student values('stu1','cls1','yes');
                    
                    1 row created.
                    
                    SQL> insert into student values('stu2','cls1','no');
                    
                    1 row created.
                    
                    SQL> insert into student values('stu3','cls1','yes');
                    insert into student values('stu3','cls1','yes')
                    *
                    ERROR at line 1:
                    ORA-00001: unique constraint (OPS$ORACLE.ONE_REWARD_PER_CLASS) violated
                    
                    
                    SQL> insert into student values('stu3','cls1','no');
                    
                    1 row created.
                    John
                    • 7. Re: How to solve ora-04091: mutating table
                      994099
                      Thanks John.

                      The use of unique index can give an error message, instead of "no row updated" message when insert wrong records. Especially, If I use a procedure to do this, such as EXEC CHOOSE_REWARD_STUDENT('&STUID'), system can tell me what has happened, instead of only show "PL/SQL procedure successfully completed" whether inserted or not.

                      In this case, your first version is fine, because it takes same mean when put 'no' on reward column or leave null on reward column, as long as we use procedure to do this as I mentioned before. But second version is much better, it makes system strong to take any unexpected operation.

                      Edited by: 991096 on 12-Apr-2013 04:15