System.LimitException: Too many SOQL queries: 101

Question

I am getting "System.LimitException: Too many SOQL queries: 101" when I am trying to load 500 opptys through dataloader.I think insert statement in for loop is where it is failing.But it ran successfully when I set the batch size to 1. I need to assign the inserted event controller id to tempfevent tempfevent.Event_Controller__c = fec.Id;

trigger CreateAndUpdatefEvent on Opportunity (after insert,after update) {


 list<Id> OppIdList = new list<Id>();

 list<fEvent__c> felist = new list<fEvent__c>(); 

 list<fEvent__c> felist2 = new list<fEvent__c>(); 

 fEvent__c tempfevent = new  fEvent__c();

  for(Opportunity o:trigger.new){
      OppIdList.add(o.Id); 
  }  

   list<Opportunity>insertOpptyList =[Select o.StageName,o.IsClosed,(Select Id,  Name,   Amount__c Opportunity__c From fEvent3__r  where Status__c!='Cancelled' limit 1) From  Opportunity o where o.Id in:OppIdList];

for(Opportunity o:insertOpptyList){
 if(o.fEvent3__r.size()==0){ 
     Event_Controller__c fec = new Event_Controller__c(Amount__c = o.Amount,Years__c =1,Fiscal_Year__c = string.valueOf(o.CloseDate.year()));
     **insert fec;**
     tempfevent.Opportunity__c = o.Id;
     **tempfevent.Event_Controller__c = fec.Id;**
     tempfevent.Amount__c = o.Amount; 
     felist.add(tempfevent);
     tempfevent = new fEvent__c(); 
  }
 else{
  if(o.fEvent3__r.size()>0){
        o.fEvent3__r[0].Amount__c = o.Amount;
        felist2.add(o.Funding_Event3__r[0]); 
    }    
    }
   }

if(felist.size()>0)
   insert felist;

 if(felist2.size()>0)
    update felist2; 

 }

to prevent this, unit tests should test triggers in bulk (100+ records) — Samuel De Rycke, Feb 19 '14 at 22:15
possible duplicate of System.Exception: Too many SOQL queries: 101 — Christian Deckert, Aug 14 '14 at 21:57

Mike Chale · Accepted Answer · 2014-02-19T22:21:43.143

You're right, it's because you have DML inside of a loop. Instead, add the new records to a list or a map and then do your insert at the end. I've provided an untested-sample:

Edit

I missed the need to store off values from the Opportunity into the other variables. I've switched to using a map, keyed on the Opp id, to quickly get these values in a secondary loop.

trigger CreateAndUpdatefEvent on Opportunity (after insert,after update) {
 list<Id> OppIdList = new list<Id>();    
 list<fEvent__c> felist = new list<fEvent__c>();     
 list<fEvent__c> felist2 = new list<fEvent__c>(); 

 // Store new records in the map
 Map<Id,Event_Controller__c> newEc = new Map<Id,Event_Controller__c>();

 fEvent__c tempfevent = new  fEvent__c();

  for(Opportunity o:trigger.new){
      OppIdList.add(o.Id); 
  }  

   list<Opportunity>insertOpptyList =[Select o.StageName,o.IsClosed,(Select Id,  Name,   Amount__c Opportunity__c From fEvent3__r  where Status__c!='Cancelled' limit 1) From  Opportunity o where o.Id in:OppIdList];

for(Opportunity o:insertOpptyList){
 if(o.fEvent3__r.size()==0){ 
     // Add new records to the map instead of one-off insertion
     newEc.put(o.id, new Event_Controller__c(Amount__c = o.Amount,Years__c =1,Fiscal_Year__c = string.valueOf(o.CloseDate.year())));
  }
 else{
  if(o.fEvent3__r.size()>0){
        o.fEvent3__r[0].Amount__c = o.Amount;
        felist2.add(o.Funding_Event3__r[0]); 
    }    
    }
   }

// Do your DML here, outside of the loop.
insert newEc.values();
for(Id index: newEc.keyset()){       
     tempfevent.Opportunity__c = index;
     tempfevent.Event_Controller__c = newEc[index].Id;
     tempfevent.Amount__c = Trigger.newMap[index].Amount; 
     felist.add(tempfevent);
     tempfevent = new fEvent__c(); 
}
if(felist.size()>0)
   insert felist;

 if(felist2.size()>0)
    update felist2; 

 }

I need to assign Event Controller ID tempfevent.Event_Controller__c = fec.Id; ..please look at my highlighted code — sfdc, Feb 19 '14 at 22:05
After inserting your new Event_Controller__c records you will need another loop to populate felist; I'll update my code to what I think should work. — Mike Chale, Feb 19 '14 at 22:12

System.LimitException: Too many SOQL queries: 101

1 Answers1