+ Start a Discussion
AntonyWarcAntonyWarc 

Help with trigger (System.LimitException: Too many SOQL queries: 101)

Hi

 

I have no Apex training but have managed to piece this trigger together with help from these boards! Now i have my trigger in place I need to update all my leads to use it. However I get the  System.LimitException: Too many SOQL queries: 101 error when mass-updating leads.

 

I understand the concept that I'm calling too many SOQL queiers. Anyone willing to help re-code this trigger to bring teh SOQL out of the for loop?

 

trigger updateCCLookupField on Lead (before insert, before update) {

List<Lead> leads = new List<Lead>();

  for (Lead l : Trigger.new)
  { 
  Try
  {
     Campaign_Codes__c AssociatedCC = [SELECT Id FROM Campaign_Codes__c WHERE CodeOnCC__c= :l.Campaign_Code__c];
    l.CC_Lookup__c = AssociatedCC.Id;
  }
  Catch(Exception e)
  {
  l.CC_Lookup__c = null;
  } 
  }
}

Best Answer chosen by Admin (Salesforce Developers) 
Deepak Kumar ShyoranDeepak Kumar Shyoran

Yes you are right there there are some error on my code. Please view the below code to remove those error 

 

List<Campaign_Codes__c> AssociatedCCList = [SELECT Id,CodeOnCC__c FROM Campaign_Codes__c WHERE CodeOnCC__c in : CampaignCodeList];


for(Campaign_Codes__c cc : AssociatedCCList)
mapOfAssociatedCC.put(cc.CodeOnCC__c , cc.Id) ;


for(Lead l :Trigger.new())
l.CC_Lookup__c = mapOfAssociatedCC.get(l.Campaign_Code__c) ;

 

Hopefully this will solve your problem.

 

If this post helps you then hit kudus by clicking on star and accept my post as a solution to your question.

 

All Answers

alibzafaralibzafar

You are using SOQL query inside for loop thats why you are getting this error, according to best practices you shouldn't be using any query or dml operation inside a for loop.

 

For more detials see Apex Best Practices  

 

 

Deepak Kumar ShyoranDeepak Kumar Shyoran

Hi,

 

You get this exception because you are using a SOQL query inside a for loop which is a bad practice and Leeds to SOQL exception.

 

You need to maintain a Map which contain CodeOnCC__c of  Campaign_Code__c  field of  as Key and CodeOnCC__c id as a value.

 

Use the following code by modifying the below code it might solve your problem.

 

 

trigger updateCCLookupField on Lead (before insert, before update) {

 

//Change the type of list to the type of Campaign_Code__c field (as I don't know the field type of your field so I take it as String ) 

 

Map<String,Id> mapOfAssociatedCC = new Map<String,Id>() ;
List<String> CampaignCodeList = new List<String>() ;
for (Lead l : Trigger.new)
CampaignCodeList.add(l.Campaign_Code__c) ;

List<Campaign_Codes__c> AssociatedCCList = [SELECT Id,CodeOnCC__c FROM Campaign_Codes__c WHERE CodeOnCC__c in : CampaignCodeList];
for(Campaign_Codes__c cc : AssociatedCCList)
l.CC_Lookup__c = mapOfAssociatedCC.put(cc.CodeOnCC__c , cc.Id) ;

 

 

If this post helps you then hit kudus by clicking on star and accept my post as a solution to your question.

AntonyWarcAntonyWarc

Hi Deepak,

 

Thanks for this as a solution. I get the follwoing error when I use this:

 


Error: Compile Error: Variable does not exist: l.CC_Lookup__c at line 12 column 1

Deepak Kumar ShyoranDeepak Kumar Shyoran

Yes you are right there there are some error on my code. Please view the below code to remove those error 

 

List<Campaign_Codes__c> AssociatedCCList = [SELECT Id,CodeOnCC__c FROM Campaign_Codes__c WHERE CodeOnCC__c in : CampaignCodeList];


for(Campaign_Codes__c cc : AssociatedCCList)
mapOfAssociatedCC.put(cc.CodeOnCC__c , cc.Id) ;


for(Lead l :Trigger.new())
l.CC_Lookup__c = mapOfAssociatedCC.get(l.Campaign_Code__c) ;

 

Hopefully this will solve your problem.

 

If this post helps you then hit kudus by clicking on star and accept my post as a solution to your question.

 

This was selected as the best answer
AntonyWarcAntonyWarc

Thanks Deepak, working perfectly.