function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
learnSFlearnSF 

System.Exception: Too many SOQL queries: 21

Hi,

 

I have apex terriger and within trigger I have logic and three query  which is like this.

 

 List<Account> allAccount = [Select Market_Territory__c,RecordTypeId,RecordType.Name,Territory_Affiliate__c,County_and_State_Combo__c,Affiliate_Sales_Manager__c, Id,Selling_Territory__c From Account where Id in :putallAccount.KeySet()];
	List<Affiliate_Management__c> affiliateManagement = [Select Name, Id From Affiliate_Management__c where Name IN :affiliateName];
	List<Territory_Market__c> territoryMarket = [Select t.Territory_State__c, t.Territory_Market__c, t.Territory_County__c,t.Name,t.Market_Category__c,
             t.Parent_Market__r.Name,t.Parent_Market__r.Market_Category__c, t.Parent_Market__c,t.County_State_Combo__c From Territory_Market__c t 
             where t.Name in : account];
 
all this query is outside of for loop.
 
Still I am getting toomanySoql query error.
 
I am getting error at last two query.
 
Can someone teel me what is wrong in this code?
 

Thanks
 


sfdcfoxsfdcfox
This is a red herring-- these queries are not your problem. You have too many overall queries, so I would recommend that you look earlier in your code for a construct like this:
 
for( ... : someList )
{   someObject so = [select ... from someObject where ... ];
     // Do something here.
}
 
Having a query inside of a for loop will cause you to quickly reach your SOQL call limit. If that is not the problem, count the number of queries you are executing across all of your triggers that are being called (you can use the debug log for this purpose). 


learnSFlearnSF
Thanks for reply.
 
 
Queries are not inside for loop so that is not a problem.
 
But this trigger fires while batch uploading accounts. With trigger I have these three queries. No other query inside code except these.
 
could you please help me to check limits why it giving me too many soql?
 
Thanks


JimRaeJimRae
Post all of your trigger code, so we can see the context of these queries being executed.


learnSFlearnSF
trigger FindTerritoryMarket on Account (after insert, after update) {


	/*if its dealer cusomter -- populate terrotry market as selling terriotry
	  if its prosepect or former customer and terrotory affilate does not contains cars.com -- populate terrotry market as terriotry affilate
	  if its prosepect or former customer and terrotory affilate contains cars.com -- populate terrotry market from county state lookup obejct(TerritoryMarket object)
 	  if selling terriotry is 'cars com o& o' populate market territory as 'cars.com national'
 	  if territory market object give 'cars-com national with number,polupate market territory as  simply 'Cars.com - National' without number.
 	*/
 
    /* populate affiliate sales manager in account from Affiliate_Management__c llok up
      if its cusomter lookup wiht selling terriotry
      if its other type of customer lookup based terriotry affiliate
    */
    
    /* if account recrod type is dealer-prospect,dealer-customer,former-customer and territory market contains cars.com
     do lookup to market territory object to match territory market field in account 
     and populate market catagory from market territory object to account object
     for 'cars.com national' territory market-show market catagroy as 'I'
    */
       
        
    String dealerProspect = 'Dealer - Prospect'; //dealer prospect recType1 
    String dealerCustomer = 'Dealer - Customer'; // dealer cusomter recType2 
    String formerCustomer = 'Dealer - Former Customer'; // former customer recType3 
	String deadAccount = 'Dealer - Dead Account'; //dead account
	String outOfBusiness = 'Dealer - Out of Business'; //out of business
	
	Id recType1 = 'xyz'; //dealer prospect recType1 
    Id recType2 = 'abc'; // dealer cusomter recType2 
    Id recType3 = 'ahc'; // former customer recType3 
	Id recType4 = 'dfe'; //dead account
	Id recType5 = 'abcd'; //out of business
	
    Map<Id,Account> putallAccount= new Map<Id,Account>();
    List<Account> updateAccount = new List<Account>();
    Set<String> affiliateName = new Set<String>();
	Set<String> account= new Set<String>();
			
  	for(Account a : Trigger.new) { 
        if(System.Trigger.isInsert){
            if(a.RecordTypeId == recType2){
            	system.debug('customer insert');
                putallAccount.put(a.Id,a);
                affiliateName.add(a.Selling_Territory__c);
                if(a.Selling_Territory__c !=null && a.Selling_Territory__c.contains('Cars.com')){
                	account.add(a.Selling_Territory__c);
                }
            }
            if((a.RecordTypeId == recType1 ||a.RecordTypeId == recType3) 
            && (a.Territory_Affiliate__c==null||!a.Territory_Affiliate__c.contains('Cars.com'))){
            	system.debug('prospect or former with not cars.com  insert');
                putallAccount.put(a.Id,a);
                 affiliateName.add(a.Territory_Affiliate__c);
            }
            if((a.RecordTypeId == recType1 ||a.RecordTypeId == recType3)
            	&& a.Territory_Affiliate__c != null
                ){
                	
                if(a.Shipping_County__c!=null&& a.ShippingState!=null&& a.Territory_Affiliate__c.contains('Cars.com')){
                	system.debug('prospect or former with cars.com with no state county insert');
                	account.add(a.Shipping_County__c+a.ShippingState);
                	 putallAccount.put(a.Id,a);
                     affiliateName.add(a.Territory_Affiliate__c);
                }else if(a.Territory_Affiliate__c.contains('Cars.com')){
                	system.debug('prospect or former with cars.com  insert');
                	putallAccount.put(a.Id, a);
	       		 	affiliateName.add(a.Territory_Affiliate__c);
                }
            }
            
        }else{
            
            if(((a.RecordTypeId != Trigger.oldMap.get(a.Id).RecordTypeId) && a.RecordTypeId == recType2) 
                ||(a.RecordTypeId == recType2 && (a.Selling_Territory__c != Trigger.oldMap.get(a.Id).Selling_Territory__c))){
                	system.debug('cusomter with selling territory update');
                putallAccount.put(a.Id,a);
                affiliateName.add(a.Selling_Territory__c);
                if(a.Selling_Territory__c !=null && a.Selling_Territory__c.contains('Cars.com')){
                	account.add(a.Selling_Territory__c);
                }
            }
            if(  ((a.RecordTypeId != Trigger.oldMap.get(a.Id).RecordTypeId) 
                 && (a.RecordTypeId == recType1 ||a.RecordTypeId == recType3) 
                 && (a.Territory_Affiliate__c==null||!a.Territory_Affiliate__c.contains('Cars.com'))
                 )
              ||( (a.RecordTypeId == recType1 ||a.RecordTypeId == recType3) 
                  && ( (a.Territory_Affiliate__c != Trigger.oldMap.get(a.Id).Territory_Affiliate__c) 
                       && (a.Territory_Affiliate__c==null||!a.Territory_Affiliate__c.contains('Cars.com'))
                     )
                 )){
                 	system.debug('cars.com not update');
                 putallAccount.put(a.Id,a);
                 affiliateName.add(a.Territory_Affiliate__c);
            }
            
            if(  ((a.RecordTypeId != Trigger.oldMap.get(a.Id).RecordTypeId) 
                 && (a.RecordTypeId == recType1 ||a.RecordTypeId == recType3)
                 && a.Territory_Affiliate__c != null 
                 )
              ||( (a.RecordTypeId == recType1 ||a.RecordTypeId == recType3) 
                  && ( (a.Territory_Affiliate__c != Trigger.oldMap.get(a.Id).Territory_Affiliate__c) 
                  		&& a.Territory_Affiliate__c != null
                     )
                 )
              ||( (a.RecordTypeId == recType1 ||a.RecordTypeId == recType3) 
              	   && a.Territory_Affiliate__c != null
                   &&(a.Shipping_County__c!=Trigger.oldMap.get(a.Id).Shipping_County__c) 
                 )
              ||( (a.RecordTypeId == recType1 ||a.RecordTypeId == recType3) 
              	   && a.Territory_Affiliate__c != null
                   &&(a.ShippingState!=Trigger.oldMap.get(a.Id).ShippingState) 
                 )   
              ) {
              	if(a.Territory_Affiliate__c.contains('Cars.com')){
              		system.debug('cars.com update');
              		  putallAccount.put(a.Id,a);
                      affiliateName.add(a.Territory_Affiliate__c);
                      account.add(a.Shipping_County__c+a.ShippingState);
              	}
            }
            if ((a.RecordTypeId == recType4 || a.RecordTypeId == recType5) && Trigger.oldMap.get(a.Id).Territory_Affiliate__c != a.Territory_Affiliate__c && a.Territory_Affiliate__c != null){
	       		system.debug('dead account update');
	       		 putallAccount.put(a.Id, a);
	       		 affiliateName.add(a.Territory_Affiliate__c);
	        }
        }
    }
    
    List<Account> allAccount = [Select Market_Territory__c,RecordTypeId,RecordType.Name,Territory_Affiliate__c,County_and_State_Combo__c,Affiliate_Sales_Manager__c, Id,Selling_Territory__c From Account where Id in :putallAccount.KeySet()];
	List<Affiliate_Management__c> affiliateManagement = [Select Name, Id From Affiliate_Management__c where Name IN :affiliateName];
	List<Territory_Market__c> territoryMarket = [Select t.Territory_State__c, t.Territory_Market__c, t.Territory_County__c,t.Name,t.Market_Category__c,
             t.Parent_Market__r.Name,t.Parent_Market__r.Market_Category__c, t.Parent_Market__c,t.County_State_Combo__c From Territory_Market__c t 
             where t.Name in : account];
             
    for (Account a : allAccount){
    	system.debug('recrod type'+a.RecordType.Name);
    	if(a.RecordType.Name==dealerCustomer){
    		system.debug('it enter in to cusmer loop');
    		for (Affiliate_Management__c loopAffMgmt : affiliateManagement) {
     		   system.debug('custoemr account update with selling territpry'+a.Selling_Territory__c);
     		   if (a.Selling_Territory__c != null && loopAffMgmt.Name == a.Selling_Territory__c) { 
     			   a.Affiliate_Sales_Manager__c = loopAffMgmt.Id;
     			   break;
 			   }
     	    }
     	    if(a.Selling_Territory__c=='Cars.com O & O'){
     	    	a.Market_Territory__c = 'Cars.com - National';
     	    	a.Market_Category__c = 'I';
     	    }else{
     			a.Market_Territory__c = a.Selling_Territory__c;
     			if(a.Selling_Territory__c!=null && a.Selling_Territory__c.contains('Cars.com')){
     				for(Territory_Market__c market : territoryMarket){
     					if(market.Name.equals(a.Selling_Territory__c)){
     						a.Market_Category__c = market.Market_Category__c;
     						break;
     					}
     				}
     			}else{
     				a.Market_Category__c = null;
     			}
     	    }
        	updateAccount.add(a);
    	}else if(a.RecordType.Name==deadAccount||a.RecordType.Name==outOfBusiness){
    		system.debug('it enter in to dead loop');
    		for (Affiliate_Management__c loopAffMgmt : affiliateManagement) {
     		   system.debug('dead account update with  territpry affiliate'+a.Territory_Affiliate__c);
     		   if (a.Territory_Affiliate__c != null && loopAffMgmt.Name == a.Territory_Affiliate__c) { 
     			   a.Affiliate_Sales_Manager__c = loopAffMgmt.Id;
     			   updateAccount.add(a);
     			   break;
 			   }
     	    }
     	}else if((a.RecordType.Name==dealerProspect||a.RecordType.Name==formerCustomer) && (a.Territory_Affiliate__c == null ||!a.Territory_Affiliate__c.contains('Cars.com')) ){
     		system.debug('it enter in to prospect loop without cars.com');
    		for (Affiliate_Management__c loopAffMgmt : affiliateManagement) {
     		   		system.debug('prospect or former account update with territpry affiliate with not cars.com'+a.Territory_Affiliate__c);
     		   		if (a.Territory_Affiliate__c != null && loopAffMgmt.Name == a.Territory_Affiliate__c) { 
     		   			system.debug('not with cars.com'+loopAffMgmt.Name);
     			   		a.Affiliate_Sales_Manager__c = loopAffMgmt.Id;
     			   		break;
 			   		}
 			}
 			a.Market_Territory__c = a.Territory_Affiliate__c;
 			a.Market_Category__c = null;
        	updateAccount.add(a);
    	}else if((a.RecordType.Name==dealerProspect||a.RecordType.Name==formerCustomer) && (a.Territory_Affiliate__c != null && a.Territory_Affiliate__c.contains('Cars.com'))){
    		system.debug('it enter in to prospect loop cars.com');
    		for (Affiliate_Management__c loopAffMgmt : affiliateManagement) {
     		   system.debug('prospect or former account update with territpry affiliate with cars.com'+a.Territory_Affiliate__c);
     		   	if (a.Territory_Affiliate__c != null && loopAffMgmt.Name == a.Territory_Affiliate__c) { 
     		   		system.debug('entering to populate affiliate'+loopAffMgmt.Name);
     			   	a.Affiliate_Sales_Manager__c = loopAffMgmt.Id;
     			   	break;
 			   	}
 			}
 			for(Territory_Market__c market : territoryMarket){
 				system.debug('enter to pouplate territory'+market.County_State_Combo__c);
        		if(market.County_State_Combo__c==a.County_and_State_Combo__c){
        		 	String name=market.Parent_Market__r.Name;
        		 	system.debug('name'+name);
        		 	if(name!=null && name!=''&& name.contains('/')){
        		 		List <String> nameOfTerritory = name.split('/');
        		 		for(Integer j=0;j<nameOfTerritory.size();j++){
        		 			if(nameOfTerritory.get(j).contains('Cars.com')){
        		 				system.debug('found cars.com market territory'+nameOfTerritory.get(j));
        		 				 if(nameOfTerritory.get(j).contains('Cars.com - National')){
     	    						a.Market_Territory__c = 'Cars.com - National';
     	    						a.Market_Category__c = 'I';
     	    					 }else{
        		 					a.Market_Territory__c = nameOfTerritory.get(j);
        		 					a.Market_Category__c = market.Parent_Market__r.Market_Category__c;
        		 				 }
        		 			}
        		 		}
        		 	}else{
        		 		
        		 		 system.debug('found cars.com without / market territory'+name);
        		 		 if(name!=null && name.contains('Cars.com - National')){
     	    				a.Market_Territory__c = 'Cars.com - National';
     	    				a.Market_Category__c = 'I';
     	    			 }else{
        		 			a.Market_Territory__c = name;
        		 			a.Market_Category__c = market.Parent_Market__r.Market_Category__c;
        		 		 }
        		 	}
        		 	break;
        		 }
        	}
        	updateAccount.add(a);
        }
     }
  
 	 update updateAccount;
 	 
}
 
 
Here is my code.


JimRaeJimRae
That is quite a trigger, you have a lot going on.  I would guess, based on the code you posted, that you have other insert and/or update triggers on your Account object, and it is when you combine this trigger with those other triggers, that you exceed your limit.  If you walk through your debug log, you should see everytime a different trigger is fired, and you should see how many SOQL calls have been executed so far during that run.  

Remember, the limit of 20 SOQL queries is for the entire transaction, not just for the one trigger.  Maybe adding these 3 additional queries put you over the limit.
 


sreejasreeja
hi sf, i have a small doudt , can you please provide your emailid ,