+ Start a Discussion
learnSFlearnSF 

Too many SOQL query error in update,insert trigger

Hi,

 

Here is my code.

trigger affiliateHierarchyChange on Account (after insert, after update) { List<Account> accountUpdate = new List<Account>(); Map<Id,String> newAccount= new Map<Id,String>(); for(Account a : Trigger.new) { if(System.Trigger.isInsert){ if((a.RecordTypeId=='012300000000AHF') && a.Territory_Affiliate__c !=null) newAccount.put(a.Id,a.Territory_Affiliate__c); }else{ //dealer-customer if(a.RecordTypeId=='012300000000AGr' && Trigger.oldMap.get(a.Id).Selling_Territory__c!=a.Selling_Territory__c && a.Selling_Territory__c !=null){ newAccount.put(a.Id,a.Selling_Territory__c); }else if((a.RecordTypeId=='012300000000AHC'||a.RecordTypeId=='012300000000AHF') && Trigger.oldMap.get(a.Id).Territory_Affiliate__c!=a.Territory_Affiliate__c && a.Territory_Affiliate__c !=null){ newAccount.put(a.Id,a.Territory_Affiliate__c); } } } Map<Id,String> test=new Map<Id,String>(); for(Id ids:newAccount.KeySet()){ List<Affiliate_Management__c> affiliateManagement = new List<Affiliate_Management__c>(); affiliateManagement = [Select a.Name, a.Id From Affiliate_Management__c a where a.Name=: newAccount.get(ids)]; if(affiliateManagement.size() > 0){ test.put(ids,affiliateManagement[0].Id); //Account accountSreach = [Select Affiliate_Sales_Manager__c,Id From Account WHERE Id =: ids]; //accountSreach.Affiliate_Sales_Manager__c =affiliateManagement[0].Id; //accountUpdate.add(accountSreach); } } for(Account accountSearch :[Select Affiliate_Sales_Manager__c,Id From Account WHERE Id in : test.keySet() ]){ // Account accountSreach = [Select Affiliate_Sales_Manager__c,Id From Account WHERE Id =: ids]; accountSearch.Affiliate_Sales_Manager__c =test.get(accountSearch.Id); accountUpdate.add(accountSearch); } update accountUpdate; }

 

I am getting system exception:Too many SOQL queries:21

 

Can some one point how to solve this.

 

I know that we can not write query in insert or update trigger,so I separate it out in for loop.

 

Still it fails at

 

for(Account accountSearch :[Select Affiliate_Sales_Manager__c,Id From Account WHERE Id in : test.keySet() ]){

 

-Thanks

ahab1372ahab1372

you can use queries in triggers, no matter if before, after, insert or update

 

But never write the query inside a for-loop because then it is executed in every single iteration. Just put it before the loop

 

 

HTH,

Arnt

learnSFlearnSF

Hi,

 

I have tried to wriiten query outside of loop. Still sale problem.

trigger affiliateHierarchyChange on Account (after insert, after update) { List<Account> accountUpdate = new List<Account>(); Map<Id,String> newAccount= new Map<Id,String>(); Map<Id,Account> testAccount= new Map<Id,Account>(); for(Account a : Trigger.new) { if(System.Trigger.isInsert && (a.RecordTypeId=='012300000000AHF') && a.Territory_Affiliate__c !=null ){ //if((a.RecordTypeId=='012300000000AHF') && a.Territory_Affiliate__c !=null) newAccount.put(a.Id,a.Territory_Affiliate__c); }else{ //dealer-customer if(a.RecordTypeId=='012300000000AGr' && Trigger.oldMap.get(a.Id).Selling_Territory__c!=a.Selling_Territory__c && a.Selling_Territory__c !=null){ newAccount.put(a.Id,a.Selling_Territory__c); }else if((a.RecordTypeId=='012300000000AHC'||a.RecordTypeId=='012300000000AHF') && Trigger.oldMap.get(a.Id).Territory_Affiliate__c!=a.Territory_Affiliate__c && a.Territory_Affiliate__c !=null){ newAccount.put(a.Id,a.Territory_Affiliate__c); } } } List<Account> myAccount = [Select Affiliate_Sales_Manager__c,Id From Account where Id in :newAccount.KeySet()]; for(Id ids:newAccount.KeySet()){ List<Affiliate_Management__c> affiliateManagement = new List<Affiliate_Management__c>(); affiliateManagement = [Select a.Name, a.Id From Affiliate_Management__c a where a.Name=: newAccount.get(ids)]; if(affiliateManagement.size() > 0){ for(Integer i=0;i<myAccount.size();i++){ if(ids==myAccount[i].Id){ myAccount[i].Affiliate_Sales_Manager__c =affiliateManagement[0].Id; accountUpdate.add(myAccount[i]); } } } } update accountUpdate; }

 

 

It fails at List<Account> myAccount = [Select Affiliate_Sales_Manager__c,Id From Account where Id in :newAccount.KeySet()];

It seems in after query,trigger does not allow to write query on object which has trigger on it.

 

Could you please post some solution for this?

 

 

ahab1372ahab1372

you still have a query inside a for loop, almost at the end:

affiliateManagement = [Select a.Name

 

I think that's what uses a lot of the queries