I am new here and have no coding experience whatsoever. I am a Salesforce admin with 3 years experience.

Before I started at my current company, they hired a consultant to create some APEX triggers. They were working fine until a couple weeks ago. I created a workflow rule to send out a welcome email when an Opportunity is closed won and this workflow updates the End User Contact field on the Opportunity. Ever since I activated it, we have been randomly getting this too many SOQL query error when a Lead is attempting to convert to an Account, Contact and Opportunity.

It's completely random in the fact that it doesn't happen for every Lead and if I were to create a Lead with all the exact same information as the one giving me the problem, the new Lead will convert fine.

If I convert a Lead to just an Account and Contact, no error is received.

I've spoke with several different people at Salesforce Dev support and they keep telling me the trigger is running in a for loop but can't explain why it is happening now (trigger is about 6 months old) and only specific records which I can't figure out the differences in the ones that give the error and the ones that don't. So they keep telling me to edit the code to remove the for loop (which I don't know how to do).

Also, the workflow rule I mentioned previously? If I deactivate the Workflow rule, the problem goes away.

Can anyone help me?

trigger LeadSetTerritory on Lead (before insert, before update, after insert, after update) 
    Set<String> CountryLookup = new Set<String>();
    Map<Id, String> LeadToCountry = new Map<Id, String>();

    for(Lead lead : trigger.new)
        // Not if we are doing a conversion
        if(! lead.IsConverted )
            String lookup = lead.Country__c;

            // Use the State and Country to find the appropriate Territory Object
            if(lead.Country__c == 'United States')
                // This is a US Territory so we need to 
                // append to the Territory Name ' - ' + State                
                //lookup  += ' - ' + lead.State_Territory__c;

                String stateString = '';
                //cbarisic - made a mod here to build it from End user/Reseller/Distributor
                if(lead.End_User_State_Territory2__c != null && lead.End_User_State_Territory2__c != '') {
                  stateString = lead.End_User_State_Territory2__c;
                else if(lead.Reseller_State_Territory2__c != null && lead.Reseller_State_Territory2__c != '') {
                  stateString = lead.Reseller_State_Territory2__c;
                else if(lead.Distributor_State_Territory2__c != null && lead.Distributor_State_Territory2__c != '') {
                  stateString = lead.Distributor_State_Territory2__c;
                else if(lead.State_Territory__c != null && lead.State_Territory__c != '') {
                  stateString = lead.State_Territory__c;
                lookup  += ' - ' + stateString;

            LeadToCountry.put(lead.Id, lookup);          

    //Populate OWNERSHIP Section

    Map<string,Territory__c> TerritoryByCountryMap=TerritoryRecord.BuildTerritoryByCountryMap(CountryLookup);

        for(Lead l : trigger.new){
            String key = LeadToCountry.get(l.Id);
            if (TerritoryByCountryMap.containsKey(key)){                       
                l.Sales_Engineer_T__c = TerritoryByCountryMap.get(key).Global_Acct_Mgr__r.name;                
                l.Inside_Sales_Mgr_T__c = TerritoryByCountryMap.get(key).Inside_Sales_Mgr__r.name;
                l.ITAM_T__c = TerritoryByCountryMap.get(key).ITAM__r.name;
                l.ITAM_2_T__c = TerritoryByCountryMap.get(key).ITAM_2__r.name;
                l.ITAM_3_T__c = TerritoryByCountryMap.get(key).ITAM_3__r.name;
                l.Sales_Director_T__c = TerritoryByCountryMap.get(key).Sales_Director__r.name;
                l.TAM_Agent_T__c = TerritoryByCountryMap.get(key).TAM_Agent__r.name;
                l.TAM_Agent_2_T__c = TerritoryByCountryMap.get(key).TAM_Agent_2__r.name;        
                l.TAM_Agent_3_T__c = TerritoryByCountryMap.get(key).TAM_Agent_3__r.name;
                l.Sales_Territory_Manager_T__c = TerritoryByCountryMap.get(key).Sales_Territory_Manager__r.name;
                l.Sales_Vice_President_T__c = TerritoryByCountryMap.get(key).Sales_Vice_President__r.name;
                l.Global_Sales_Vice_President_T__c = TerritoryByCountryMap.get(key).Global_Sales_Vice_President__r.name;
              //email fields
              l.Sales_Engineer_Email__c = TerritoryByCountryMap.get(key).Global_Acct_Mgr__r.email;
              l.Inside_Sales_Mgr_Email__c = TerritoryByCountryMap.get(key).Inside_Sales_Mgr__r.Email;
                l.ITAM_Email__c = TerritoryByCountryMap.get(key).ITAM__r.Email;
                l.ITAM_2_Email__c = TerritoryByCountryMap.get(key).ITAM_2__r.Email;
                l.ITAM_3_Email__c = TerritoryByCountryMap.get(key).ITAM_3__r.Email;
                l.Sales_Director_Email__c = TerritoryByCountryMap.get(key).Sales_Director__r.Email;
                l.TAM_Agent_Email__c = TerritoryByCountryMap.get(key).TAM_Agent__r.Email;
                l.TAM_Agent_2_Email__c = TerritoryByCountryMap.get(key).TAM_Agent_2__r.Email;        
                l.TAM_Agent_3_Email__c = TerritoryByCountryMap.get(key).TAM_Agent_3__r.Email;
                l.Sales_Territory_Manager_Email__c = TerritoryByCountryMap.get(key).Sales_Territory_Manager__r.Email;
                l.Sales_Vice_President_Email__c = TerritoryByCountryMap.get(key).Sales_Vice_President__r.Email;
                l.Global_Sales_Vice_President_Email__c = TerritoryByCountryMap.get(key).Global_Sales_Vice_President__r.Email;

    // Set the Sharing for Lead

        //set of Active User for checkups
        Map<id,User> ActiveUserIds = new Map<id,User>([select id from User where Isactive=true]);

        // List of share objects to delete
        List<Id> sharesToDelete = new List<Id>();

        //Deleting Existing Manual Shares
        for(Lead l : trigger.new)

        delete [SELECT Id FROM LeadShare WHERE LeadId IN :sharesToDelete and RowCause = 'Manual'];

      //Recreate Share
      Map<string,Territory__c> TerritoryByCountryMap=TerritoryRecord.BuildTerritoryByCountryMap(CountryLookup);

      List<LeadShare> listOfSharing = new List<LeadShare>();

        for(Lead l : trigger.new) {        

            String key = LeadToCountry.get(l.Id);
            if (TerritoryByCountryMap.containsKey(key)){         

              // Added a check to see if the share is being added to an owner
              if (TerritoryByCountryMap.get(key).Global_Acct_Mgr__c!=null&&TerritoryByCountryMap.get(key).Global_Acct_Mgr__c!=l.OwnerId && ActiveUserIds.containsKey(TerritoryByCountryMap.get(key).Global_Acct_Mgr__c))
                listOfSharing.add(new LeadShare(LeadAccessLevel = 'Edit',LeadId = l.Id,UserOrGroupId = TerritoryByCountryMap.get(key).Global_Acct_Mgr__c));

              if (TerritoryByCountryMap.get(key).Inside_Sales_Mgr__c!=null&&TerritoryByCountryMap.get(key).Inside_Sales_Mgr__c!=l.OwnerId && ActiveUserIds.containsKey(TerritoryByCountryMap.get(key).Inside_Sales_Mgr__c))
                listOfSharing.add(new LeadShare(LeadAccessLevel = 'Edit',LeadId = l.Id,UserOrGroupId = TerritoryByCountryMap.get(key).Inside_Sales_Mgr__c));

              if (TerritoryByCountryMap.get(key).ITAM__c!=null&&TerritoryByCountryMap.get(key).ITAM__c!=l.OwnerId && ActiveUserIds.containsKey(TerritoryByCountryMap.get(key).ITAM__c))
                listOfSharing.add(new LeadShare(LeadAccessLevel = 'Edit',LeadId = l.Id,UserOrGroupId = TerritoryByCountryMap.get(key).ITAM__c));

              if (TerritoryByCountryMap.get(key).ITAM_2__c!=null&&TerritoryByCountryMap.get(key).ITAM_2__c!=l.OwnerId && ActiveUserIds.containsKey(TerritoryByCountryMap.get(key).ITAM_2__c))
                listOfSharing.add(new LeadShare(LeadAccessLevel = 'Edit',LeadId = l.Id,UserOrGroupId = TerritoryByCountryMap.get(key).ITAM_2__c));

              if (TerritoryByCountryMap.get(key).ITAM_3__c!=null&&TerritoryByCountryMap.get(key).ITAM_3__c!=l.OwnerId && ActiveUserIds.containsKey(TerritoryByCountryMap.get(key).ITAM_3__c))
                listOfSharing.add(new LeadShare(LeadAccessLevel = 'Edit',LeadId = l.Id,UserOrGroupId = TerritoryByCountryMap.get(key).ITAM_3__c));

              if (TerritoryByCountryMap.get(key).Sales_Director__c!=null&&TerritoryByCountryMap.get(key).Sales_Director__c!=l.OwnerId && ActiveUserIds.containsKey(TerritoryByCountryMap.get(key).Sales_Director__c))
                listOfSharing.add(new LeadShare(LeadAccessLevel = 'Edit',LeadId = l.Id,UserOrGroupId = TerritoryByCountryMap.get(key).Sales_Director__c));

              if (TerritoryByCountryMap.get(key).TAM_Agent__c!=null&&TerritoryByCountryMap.get(key).TAM_Agent__c!=l.OwnerId && ActiveUserIds.containsKey(TerritoryByCountryMap.get(key).TAM_Agent__c))
                listOfSharing.add(new LeadShare(LeadAccessLevel = 'Edit',LeadId = l.Id,UserOrGroupId = TerritoryByCountryMap.get(key).TAM_Agent__c));

              if (TerritoryByCountryMap.get(key).TAM_Agent_2__c!=null&&TerritoryByCountryMap.get(key).TAM_Agent_2__c!=l.OwnerId && ActiveUserIds.containsKey(TerritoryByCountryMap.get(key).TAM_Agent_2__c))
                listOfSharing.add(new LeadShare(LeadAccessLevel = 'Edit',LeadId = l.Id,UserOrGroupId = TerritoryByCountryMap.get(key).TAM_Agent_2__c));

              if (TerritoryByCountryMap.get(key).TAM_Agent_3__c!=null&&TerritoryByCountryMap.get(key).TAM_Agent_3__c!=l.OwnerId && ActiveUserIds.containsKey(TerritoryByCountryMap.get(key).TAM_Agent_3__c))
                listOfSharing.add(new LeadShare(LeadAccessLevel = 'Edit',LeadId = l.Id,UserOrGroupId = TerritoryByCountryMap.get(key).TAM_Agent_3__c));

              if (TerritoryByCountryMap.get(key).Sales_Territory_Manager__c!=null&&TerritoryByCountryMap.get(key).Sales_Territory_Manager__c!=l.OwnerId && ActiveUserIds.containsKey(TerritoryByCountryMap.get(key).Sales_Territory_Manager__c))
                listOfSharing.add(new LeadShare(LeadAccessLevel = 'Edit',LeadId = l.Id,UserOrGroupId = TerritoryByCountryMap.get(key).Sales_Territory_Manager__c));

              if (TerritoryByCountryMap.get(key).Sales_Vice_President__c!=null&&TerritoryByCountryMap.get(key).Sales_Vice_President__c!=l.OwnerId && ActiveUserIds.containsKey(TerritoryByCountryMap.get(key).Sales_Vice_President__c))
                listOfSharing.add(new LeadShare(LeadAccessLevel = 'Edit',LeadId = l.Id,UserOrGroupId = TerritoryByCountryMap.get(key).Sales_Vice_President__c));

              if (TerritoryByCountryMap.get(key).Global_Sales_Vice_President__c!=null&&TerritoryByCountryMap.get(key).Global_Sales_Vice_President__c!=l.OwnerId && ActiveUserIds.containsKey(TerritoryByCountryMap.get(key).Global_Sales_Vice_President__c))
                listOfSharing.add(new LeadShare(LeadAccessLevel = 'Edit',LeadId = l.Id,UserOrGroupId = TerritoryByCountryMap.get(key).Global_Sales_Vice_President__c));


        if (listofsharing.size()>0)
          insert listOfSharing;        

And the TerritoryRecord Class:

public with sharing class TerritoryRecord {

  public static Map<string,Territory__c> BuildTerritoryByCountryMap(set<string> CountryLookups){

    // Get a list of Country Id's 
      List<Country__c> countries = [SELECT Id FROM Country__c WHERE Name IN : CountryLookups];

  // make a map of the Territories by Country lookup    
  Map<string,Id> MapCountriesTerritory = new Map<string,Id>();

  for (Territory_By_Country__c tc:[SELECT Territory__c, Country__r.Name FROM Territory_By_Country__c WHERE Country__c IN : countries]){      

  //Map<String, Id> mapOfTerritories = new Map<String, Id>();    

  //for(Territory_By_Country__c tbc : listOfCountries){
      //Id tId = tbc.Territory__c;
      //String c = tbc.Country__r.Name;        
      //mapOfTerritories.put(c, tId);

  Map<Id, Territory__c> mapTerritory=new Map<Id,Territory__c>();

  for (Territory__c t:[SELECT Id,name,  US_EMEA__c,
             Global_Acct_Mgr__c,Global_Acct_Mgr__r.name, Global_Acct_Mgr__r.email,
                         Global_Sales_Vice_President__c,Global_Sales_Vice_President__r.name, Global_Sales_Vice_President__r.email,
                                                FROM Territory__c
                                                WHERE Id IN : MapCountriesTerritory.values()]){
      mapTerritory.put(t.Id,t) ;                                             

Map<string,Territory__c> result= new Map<string,Territory__c>();

for (string s:MapCountriesTerritory.keyset()){

  //What's the parent territory
  Id TerritoryId=MapCountriesTerritory.get(s);

   return result;
  • the workflow rule - does it do a field update on the Opportunity?
    – cropredy
    Aug 6, 2014 at 18:46
  • Yes, it updates the email address on the End User Contact field. I use that field to send the welcome email to the End User
    – Trisha M
    Aug 6, 2014 at 18:48
  • you are going to have to post the trigger (which hopefully is short/concise)
    – cropredy
    Aug 6, 2014 at 18:49
  • It's not... It is super long and complicated and to be honest, I can't see what it has to do with Opportunities. It is 145 lines
    – Trisha M
    Aug 6, 2014 at 18:50
  • 1
    Just because the error gets thrown in the code you posted does not mean that is where the problem is (Lets say the opportunity code uses 99 SQL and this code uses 2, this code will throw the error but the opportunity code is the real issue). I do not see anything obvious that would cause the issue. I suspect that the code with the actual problem is elsewhere.
    – Eric
    Aug 6, 2014 at 19:11

1 Answer 1


It appears you'd benefit from a caching algorithm. I use this technique in cases where data that's not expected to change in the course of a single transaction is "cached" in static memory for future use. Here's how I design that type of code:

public class TerritoryRecord {
    static Map<String, Territory__c> cache = new Map<String, Territory__c>();

    public static Map<String, Territory__c> BuildTerritoryByCountryMap(Set<String> CountryLookup) {
        Set<String> cacheMiss = CountryLookup.clone();
        if(!cacheMiss.isEmpty()) {
            // Do query, add results into cache; don't worry about the result map yet
        // Build a new map
        Map<String, Territory__c> results = cache.clone();
        // And return only the values the code calls for
        return results;

This code reduces queries by only requiring a database call when new values are requested.

Caches have three benefits. First, when a workflow rule causes recursion, the cache will be called the second time around, saving the associated database call from the recursion. Secondly, large batches processed at once (e.g. a batch class that updates 2,000 records per execution) won't invoke each database call multiple times due to the 200 per batch processing that occurs behind the scenes, potentially reducing such queries by ten times. Thirdly, other triggers can benefit if they're called recursively from a trigger and also use the cache, producing even further savings.

Caches do have a penalty, however. They can grow stale when the data is modified and the cache isn't updated within the same transaction. This means that caching users and Territory__c records are probably fine, but caching account queries would probably be a bad idea, especially if you expect them to be altered during the course of a transaction.

You also have to sacrifice memory for those caches, so if they grow too large, you'll run into heap errors, even without having much data used in the local stack. Keep caches to only as large as they need to be (e.g. avoid querying all fields on the record if not necessary). Avoid clearing the cache early though, as you have to presume that recursion will probably need those same records again.

Also note that salesforce.com specifically mentions that you shouldn't try to "pre-warm" a cache, for example, by preemptively querying every single Territory__c record ahead of time. It'll simply have a worse performance than if you stuck to querying exactly what you need.

I would highly recommend caching only the most frequently used queries. Dial "profiling" up to the "finest" setting to see the frequency of queries, and cache those first.

  • I really appreciate you helping with this. Unfortunately, I am not a developer so I wouldn't know how to edit the code to do this. Sounds like it is exactly what I need though.
    – Trisha M
    Aug 6, 2014 at 20:00
  • @TrishaM Did you figure it out, or are you going to need additional help? I'd be glad to try and assist you offline if you're short on developer resources.
    – sfdcfox
    Aug 6, 2014 at 23:15

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .