+ 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) 
k_bentsenk_bentsen

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:

 

 

trigger updateCCLookupField 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;
    }
}

 

All Answers

k_bentsenk_bentsen

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:

 

 

trigger updateCCLookupField 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;
    }
}

 

This was selected as the best answer
k_bentsenk_bentsen

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.

AntonyWarcAntonyWarc
Wow, thanks for the response and code Kyle. I'll give it a go.

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!
k_bentsenk_bentsen

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];

AntonyWarcAntonyWarc

Thanks again Kyle. Much appreciaed. I'm about to test this.

AntonyWarcAntonyWarc

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

k_bentsenk_bentsen

My bad. The line:

lIds.add(lIds);

Should actually be:

lIds.add(l.Id);

 

 

AntonyWarcAntonyWarc

Once again a huge thank you for helping Kyle.