Tuesday, July 28, 2015

Java: Trigger is Mutating

Error:
 org.apache.ibatis.exceptions.PersistenceException:   
 ### Error committing transaction. Cause: org.apache.ibatis.executor.BatchExecutorException: 
 DEPENDENT.insertDependentToStaging (batch index #1) failed. Cause: java.sql.BatchUpdateException:   
 ORA-04091: table MO0RCO00_UAT.RCMBENSTAGWF is mutating, trigger/function may not see it  
 ORA-06512: at "MO0RCO00_UAT.TRIG_DEPENDENTNO", line 4  
 ORA-04088: error during execution of trigger 'MO0RCO00_UAT.TRIG_DEPENDENTNO'  


I have a DAO

   public void saveBeneficiariesToStaging(List<DependentBean> dependents){  
     SqlSession session = getSqlSessionFactory().openSession(ExecutorType.BATCH);  
     int len = dependents.size();  
     try {  
      for(int i = 0; i < len; i++) {        
       session.update("insertDependentToStaging", dependents.get(i));       
      }  
     } catch(Exception e) {  
      logger.error("saveBeneficiariesToStaging", e);  
      e.printStackTrace();  
      session.close();  
     }finally{   
       session.close();    
     }  
   }  

Solution
 public void saveBeneficiariesToStaging(List<DependentBean> dependents){  
      SqlSession session = getSqlSessionFactory().openSession(ExecutorType.BATCH);  
      int len = dependents.size();  
      try {  
       for(int i = 0; i < len; i++) {        
           session.update("insertDependentToStaging", dependents.get(i));  
           session.commit();      
       }  
      } catch(Exception e) {  
       logger.error("saveBeneficiariesToStaging", e);  
       e.printStackTrace();  
       session.close();  
      }finally{  
           session.close();   
      }  
 }    
Explanation: The trigger is selecting from the latest inserted rows in the batch update. But Trigger is mutating because it selects something that is not yet on the table because I am using batch update. Program should commit each row it inserts so that the trigger can select the recently inserted row.

No comments:

Post a Comment