You need to sign in to do that
Don't have an account?
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
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
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?
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