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;
}
}
}
Best practice is to compile a list of objects/Ids in a loop, then obtain related records via SOQL outside the loop, then re-loop through these lists and perform the changes you're looking to make. I'm sure it seems a little redudant, but it helps you stay within governor limits. SF only allows 100 SOQL queries, but you can have up to 200,000 executed code statements, so a couple extra loops will help keep those SOQL queries low. Also, I would try to add a condition to only make the changes on a lead based on a condition, rather than have it execute every time a lead is inserted or update. For example, Lead.CC_Lookup__c == NULL. Anyway, this is how I would accomplish moving the SOQL calls outside the loop:
All Answers
Best practice is to compile a list of objects/Ids in a loop, then obtain related records via SOQL outside the loop, then re-loop through these lists and perform the changes you're looking to make. I'm sure it seems a little redudant, but it helps you stay within governor limits. SF only allows 100 SOQL queries, but you can have up to 200,000 executed code statements, so a couple extra loops will help keep those SOQL queries low. Also, I would try to add a condition to only make the changes on a lead based on a condition, rather than have it execute every time a lead is inserted or update. For example, Lead.CC_Lookup__c == NULL. Anyway, this is how I would accomplish moving the SOQL calls outside the loop:
Also I noticed as I was editing your trigger that you did not "ask" the SOQL query to retrieve the value for CodeOnCC__c on Campaign_Codes__c, so the query will not return any records. Since you have your SOQL and Lead value assignment inside a try statement and they fail, the compiler/system does not throw an error, and goes right into the catch statement, and it's possible that your "l.CC_Lookup__c = null;" statement is causing the trigger to invoke itself, thus getting stuck in a infinite loop, so to speak.
I have little knowledge of Apex but my original code was not suppose to return the CodeonCC_cc value, just try and see if it matches CampaignCode on the Lead object then if it does return the ID of Campaign_Codes__c.
I may not be making sense!
Glad to help. I understand why you would have thought that, but you need to return the value of CodeonCC_cc in order to use it as an identifier in the lookup. See below:
// retrieve value here // in order to do comparison here
[SELECT Id, CodeOnCC__c FROM Campaign_Codes__c WHERE CodeOnCC__c IN :lIds];
Thanks again Kyle. Much appreciaed. I'm about to test this.
Hi Kyle,
I've just tried creaing teh Trigger you coded and i get this error:
Error: Compile Error: Incompatible element type LIST<Id> for collection of Id at line 11 column 13
trigger NewCCLookupField on Lead (before insert, before update) {
List<Lead> leads = new List<Lead>();
List<Id> lIds = new List<Id>();
for (Lead l : Trigger.new)
{
if(l.CC_Lookup__c == NULL)
{
leads.add(l);
lIds.add(lIds);
}
}
List<Campaign_Codes__c> AssociatedCCs = [SELECT Id, CodeOnCC__c FROM Campaign_Codes__c WHERE CodeOnCC__c IN :lIds];
for(Campaign_Codes__c cc: AssociatedCCs)
{
for(Lead l: leads)
if(l.Campaign_Code__c == cc.CodeOnCC__c)
l.CC_Lookup__c = cc.Id;
}
}
Can you help again? Sorry!
Antony
My bad. The line:
Should actually be:
Once again a huge thank you for helping Kyle.