+ Start a Discussion
Jerry ClifftJerry Clifft 

System.LimitException: Too many SOQL queries: 101 - Don't see the cause

The below trigger works fine as long I update/insert between 1 and 50 records at a time. Anything above that I get System.LimitException: Too many SOQL queries: 101

I have my SOQL outside of th for loop, so I am not sure what else I can do to make this operate properly?


trigger xEquipment_HiddenFieldOpportunity on Equipment__c (After Insert, After Update) {  

    Set<Id> oppId=new Set<Id>();
    Set<Id> eqId=new Set<Id>();
    for(equipment__c CA1:Trigger.New)    {
        oppId.add(CA1.Opportunity__c);
        eqId.add(CA1.Id);
        if(oppId.size()>0){           


List<Opportunity> oppupdatelist = new List<Opportunity>();
System.debug('=== contents of List Opp: ' +oppupdatelist );

List<Opportunity> oppswitheqs = [select Id, (select id, Name, statis__c, Opportunity__c, lastModifiedDate, LastModifiedBy.Name from equipment__r where (statis__c = 'Activation Requested' OR statis__c = 'Drop Requested' )) from Opportunity where Id in :oppId ];
System.debug('=== contents of List Opp with EQ: ' +oppswitheqs );

                 
for(Opportunity Opp :oppswitheqs){
             List<String> NewList= new List<String>();
                   for (equipment__c eq2: Opp.equipment__r )    {
                       NewList.add(eq2.Statis__c);
                       NewList.add(' - ');
                       NewList.add(eq2.Name);
                       NewList.add(' - ');
                       NewList.add(eq2.LastModifiedBy.Name);
                       NewList.add(' on ');
                       String str1 = '' + eq2.lastModifiedDate;
                       NewList.add(str1);
                       NewList.add(' GMT 0');
                       NewList.add(' <br />');
                       System.debug('=== contents of NewList: ' +NewList);
                   }
                 
                  String s = '';

                   for(String c : NewList)    {
                       s = s + c;
                       system.debug('This data should go into the Opp' +  c);
                     }
                   Opp.Hidden_Bulk_Equipment__c = s;


oppupdatelist.add(Opp);
//System.debug('=== contents of oppupdatelist: ' +oppupdatelist );
                }
if(oppupdatelist.size()>0){
  //      update oppupdatelist;
Database.update(oppswitheqs);

}
}
}
}
Best Answer chosen by Jerry Clifft
Chidambar ReddyChidambar Reddy
Hi Jerry
Try the following code

trigger xEquipment_HiddenFieldOpportunity on Equipment__c (After Insert, After Update) {

    Set<Id> oppId=new Set<Id>();
    Set<Id> eqId=new Set<Id>();
    for(equipment__c CA1:Trigger.New)    {
        oppId.add(CA1.Opportunity__c);
        eqId.add(CA1.Id);
}//End of for loop




        if(oppId.size()>0){         

List<Opportunity> oppupdatelist = new List<Opportunity>();
System.debug('=== contents of List Opp: ' +oppupdatelist );


List<Opportunity> oppswitheqs = [select Id, (select id, Name, statis__c, Opportunity__c, lastModifiedDate, LastModifiedBy.Name from equipment__r where (statis__c = 'Activation Requested' OR statis__c = 'Drop Requested' )) from Opportunity where Id in :oppId ];
System.debug('=== contents of List Opp with EQ: ' +oppswitheqs );

               
for(Opportunity Opp :oppswitheqs){
           
                              List<String> NewList= new List<String>();

                   for (equipment__c eq2: Opp.equipment__r )    {
                       NewList.add(eq2.Statis__c);
                       NewList.add(' - ');
                       NewList.add(eq2.Name);
                       NewList.add(' - ');
                       NewList.add(eq2.LastModifiedBy.Name);
                       NewList.add(' on ');
                       String str1 = '' + eq2.lastModifiedDate;
                       NewList.add(str1);
                       NewList.add(' GMT 0');
                       NewList.add(' <br />');
                       System.debug('=== contents of NewList: ' +NewList);
                   }
               
                  String s = '';
                   for(String c : NewList)    {
                       s = s + c;
                       system.debug('This data should go into the Opp' +  c);
                     }
                    
                    Opp.Hidden_Bulk_Equipment__c = s;


                                oppupdatelist.add(Opp);


                }//End of for(Opportunity Opp :oppswitheqs)
          


            if(oppupdatelist.size()>0){
                               Database.update(oppswitheqs);

                           }


}//End of if(oppId.size()>0)

}


-Thank you,
You can choose this as best answer, if it resolves your issue.

All Answers

Ramu_SFDCRamu_SFDC
The issue is due to the usage of SOQL query inside for loop which definately hits governor limits after certain # of records. The best practise is to use Maps and remove the SOQL query inside for loop.

Please review the below posts for better understanding of this

http://www.sfdc99.com/2014/01/25/use-maps-navigate-across-lists/

In the below blog post, review Bulk Mode Triggers section

http://www.iterativelogic.com/salesforce-apex-trigger-best-practices/
Chidambar ReddyChidambar Reddy
Hi Jerry
Try the following code

trigger xEquipment_HiddenFieldOpportunity on Equipment__c (After Insert, After Update) {

    Set<Id> oppId=new Set<Id>();
    Set<Id> eqId=new Set<Id>();
    for(equipment__c CA1:Trigger.New)    {
        oppId.add(CA1.Opportunity__c);
        eqId.add(CA1.Id);
}//End of for loop




        if(oppId.size()>0){         

List<Opportunity> oppupdatelist = new List<Opportunity>();
System.debug('=== contents of List Opp: ' +oppupdatelist );


List<Opportunity> oppswitheqs = [select Id, (select id, Name, statis__c, Opportunity__c, lastModifiedDate, LastModifiedBy.Name from equipment__r where (statis__c = 'Activation Requested' OR statis__c = 'Drop Requested' )) from Opportunity where Id in :oppId ];
System.debug('=== contents of List Opp with EQ: ' +oppswitheqs );

               
for(Opportunity Opp :oppswitheqs){
           
                              List<String> NewList= new List<String>();

                   for (equipment__c eq2: Opp.equipment__r )    {
                       NewList.add(eq2.Statis__c);
                       NewList.add(' - ');
                       NewList.add(eq2.Name);
                       NewList.add(' - ');
                       NewList.add(eq2.LastModifiedBy.Name);
                       NewList.add(' on ');
                       String str1 = '' + eq2.lastModifiedDate;
                       NewList.add(str1);
                       NewList.add(' GMT 0');
                       NewList.add(' <br />');
                       System.debug('=== contents of NewList: ' +NewList);
                   }
               
                  String s = '';
                   for(String c : NewList)    {
                       s = s + c;
                       system.debug('This data should go into the Opp' +  c);
                     }
                    
                    Opp.Hidden_Bulk_Equipment__c = s;


                                oppupdatelist.add(Opp);


                }//End of for(Opportunity Opp :oppswitheqs)
          


            if(oppupdatelist.size()>0){
                               Database.update(oppswitheqs);

                           }


}//End of if(oppId.size()>0)

}


-Thank you,
You can choose this as best answer, if it resolves your issue.
This was selected as the best answer
Jerry ClifftJerry Clifft
Thanks @Chidambar this works perfectly. Also, thanks for the quick response.