+ 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 ,