+ Start a Discussion
BobbyQBobbyQ 

Help with First Apex Trigger: Too many SOQL queries: 101

Hello, I am getting the following error message. This is my first Apex Trigger and would really appreciate some help. The trigger looks up the lead postalcode in a custom object of zip codes and territory information and populates a lookup field on the lead with the record id of the corresponding zip code in the custom object.
System.LimitException: Too many SOQL queries: 101

Are there any changes I can make to elimate this error? Here is the code:
1trigger LeadAssignmentTrigger on Lead (before insert, before update)
2{
3    List<Lead> leadsToUpdate = new List<Lead>();3
4
5    for (Lead lead : Trigger.new)
6    {    
7      if (lead.PostalCode != NULL)
8      {
9          // Find the sales rep for the current zip code
10          List<Territory_Zip_Codes__c> zip = [select id from Territory_Zip_Codes__c
11                             where Name = :lead.PostalCode limit 1];     
12               
13          // if you found one
14          if (zip.size() > 0)
15          {   
16              //assign the lead owner to the zip code owner
17              lead.Territory_Zip_Code__c = zip[0].id;
18         
19              leadsToUpdate.add(lead);
20         
21          }
22       }
23    }
24}
Best Answer chosen by BobbyQ
Andrew GAndrew G
the issue is the SOQL inside the Loop for the Trigger.new
better would be to build a list of Postcode values in a list, then run the query, then process the results.
trigger LeadAssignmentTrigger on Lead (before insert, before update) {
    List<String> postcodenames = new List<String>();
//    List<Lead> leadsToUpdate = new List<Lead>();  // not required
//build a list of postcode value that we will search for
    for (Lead lead : Trigger.new) {    
        if (lead.PostalCode != NULL) {
          // Find the sales rep for the current zip code
          postcodenames.add(lead.PostalCode);
        }
     }

//run the SOQL outside the FOR loop using the list we built of postcode values
     List< Territory_Zip_Codes__c> zipList = [select id, Name from Territory_Zip_Codes__c 
                             where Name IN :postcodenames];     
         
    //now we need a map indexed by postcode name
    Map<String,Id> mapZipByName = new Map<String,Id>();
    for(Territory_Zip_Codes__c zip : zipList) {
        mapZipByName.put(zip.name,zip.Id);
    }

//now all the background work is done, do the actual processing of the records in the trigger
    for (Lead lead : Trigger.new) {
        // if you found one
        if (mapZipByName.containsKey(lead.PostalCode ) ) {   
            //assign the lead owner to the zip code owner
            lead.Territory_Zip_Code__c = mapZipByName.get(lead.PostalCode).Id;
         
//              leadsToUpdate.add(lead);
//no need to add them to an "update" list as we are in a before trigger. and it will take care of itself.
         
        }
    }
}

The above should put you on the right path.

Regards
Andrew

note: all code provided uncompiled and as-is
 

All Answers

Andrew GAndrew G
the issue is the SOQL inside the Loop for the Trigger.new
better would be to build a list of Postcode values in a list, then run the query, then process the results.
trigger LeadAssignmentTrigger on Lead (before insert, before update) {
    List<String> postcodenames = new List<String>();
//    List<Lead> leadsToUpdate = new List<Lead>();  // not required
//build a list of postcode value that we will search for
    for (Lead lead : Trigger.new) {    
        if (lead.PostalCode != NULL) {
          // Find the sales rep for the current zip code
          postcodenames.add(lead.PostalCode);
        }
     }

//run the SOQL outside the FOR loop using the list we built of postcode values
     List< Territory_Zip_Codes__c> zipList = [select id, Name from Territory_Zip_Codes__c 
                             where Name IN :postcodenames];     
         
    //now we need a map indexed by postcode name
    Map<String,Id> mapZipByName = new Map<String,Id>();
    for(Territory_Zip_Codes__c zip : zipList) {
        mapZipByName.put(zip.name,zip.Id);
    }

//now all the background work is done, do the actual processing of the records in the trigger
    for (Lead lead : Trigger.new) {
        // if you found one
        if (mapZipByName.containsKey(lead.PostalCode ) ) {   
            //assign the lead owner to the zip code owner
            lead.Territory_Zip_Code__c = mapZipByName.get(lead.PostalCode).Id;
         
//              leadsToUpdate.add(lead);
//no need to add them to an "update" list as we are in a before trigger. and it will take care of itself.
         
        }
    }
}

The above should put you on the right path.

Regards
Andrew

note: all code provided uncompiled and as-is
 
This was selected as the best answer
BobbyQBobbyQ
Thank you so much, Andrew! I was getting a variable error so I removed ".Id" from the end of this line. Hope that won't break anything. I did some quick tests in the sandbox and seems to be working perfectly so far!
lead.Territory_Zip_Code__c = mapZipByName.get(lead.PostalCode).Id;
Andrew GAndrew G
all good.  As I re-read the code I posted, I see the error.  The things you miss when you don't use a compiler or IDE.

til next time
Andrew