You need to sign in to do that
Don't have an account?
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;
}
}
}
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
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
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.
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
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.
Thanks Deepak, working perfectly.