Apex Code Development You need to sign in to do that
Don't have an account?
Trigger - Error - AccountUpdateTerms: System.LimitException: Too many SOQL queries: 101
I wrote a trigger that updates 4 fields into account that are related... our customization makes that we have Billing and Shipping accounts, so the shipping account has a lookup field that relates this shipping account to the billing account, so that this trigger does is basically change this 4 fields in all these relate accounts automatically.
In general, this trigger is working well, but I'm receiving the error when there are 30 - 40 accounts associated with this Billing account. How can I fix this issue?
In general, this trigger is working well, but I'm receiving the error when there are 30 - 40 accounts associated with this Billing account. How can I fix this issue?
trigger AccountUpdateTerms on Account (before update, after update) {
Map<Id, Account> acc = new Map<Id, Account>();
acc = trigger.oldMap;
for(account newacc : trigger.new){
if(newacc.Credit_Hold__c != acc.get(newacc.Id).Credit_Hold__c || newacc.Payment_Terms__c != acc.get(newacc.Id).Payment_Terms__c ||
newacc.Price_Level__c != acc.get(newacc.Id).Price_Level__c){
List<Account> accupd = new List<Account>();
accupd = [SELECT Id,Company_Code__c,Bill_To_Account__c,Credit_Hold__c,Payment_Terms__c FROM Account WHERE Bill_To_Account__c =: newacc.Id ];
if(trigger.isBefore && accupd.size() >0){
for(account upd : accupd){
upd.ByPass_Validation_Rules__c= TRUE;
}
update accupd;
}
if(trigger.isAfter && accupd.size() >0){
for(account upd : accupd){
upd.Company_Code__c = newacc.Company_Code__c;
upd.Credit_Hold__c = newacc.Credit_Hold__c;
upd.Payment_Terms__c = newacc.Payment_Terms__c;
upd.Price_Level__c= newacc.Price_Level__c;
}
update accupd;
}
}
}
}
The code hits governer limit for SOQL and thats why you receving the error.
SOQL Queries should not be used inside FOR LOOPS.
you have used above query inside for loop.
you can use like below,
for(){ //accumulate data here accountLISTID.add(newacc.Id); } SOQL QUERY [ SELECT Id,Company_Code__c,Bill_To_Account__c,Credit_Hold__c,Payment_Terms__c FROM Account WHERE Bill_To_Account__c=:accountLISTID];if this solves hit like.Thanks for your help... I tried to make the changes but I couldn't make it happen... I think that I missing something, what type of variable do I need to declare???
Variable does not exist: accountLISTID
THANKS!!!
There are two issues with your code. One soql inside for loop and another both before and after trigger updates the record. it will create trigger recursion execution.
trigger AccountUpdateTerms on Account (before update, after update) { Map<Id, List<Account>> acc = new Map<Id, List<Account>>(); acc = trigger.oldMap; List<Id> accountIds= new List<Id>(); list<Account> accupd=new List<Account>(); for(account newacc : trigger.new){ if(newacc.Credit_Hold__c != acc.get(newacc.Id).Credit_Hold__c || newacc.Payment_Terms__c != acc.get(newacc.Id).Payment_Terms__c || newacc.Price_Level__c != acc.get(newacc.Id).Price_Level__c){ // List<Account> accupd = new List<Account>(); accountIds.add(newacc.id); } accupd = [SELECT Id,Company_Code__c,Bill_To_Account__c,Credit_Hold__c,Payment_Terms__c FROM Account WHERE Bill_To_Account__c in =: accountIds ]; for(Account a:accupd) { for(Account b:accupd) { List<Account> macc; if(b.Bill_To_Account__c==a.id) { if(acc.contains(a.id)) { macc=acc.get(a.id); macc.add(b); macc.put(a,b); } else { macc=new List<Account>(); macc.add(b); macc.put(a,b); } } } } // acc contain map of account and its related accupd in list // Btw for your code these steps are not necessary if(trigger.isBefore && accupd.size() >0){ for(account upd : accupd){ upd.ByPass_Validation_Rules__c= TRUE; } update accupd; } if(trigger.isAfter && accupd.size() >0){ for(account upd : accupd){ upd.Company_Code__c = newacc.Company_Code__c; upd.Credit_Hold__c = newacc.Credit_Hold__c; upd.Payment_Terms__c = newacc.Payment_Terms__c; upd.Price_Level__c= newacc.Price_Level__c; } update accupd; }The above mentioned will throw error due to loop recursion
trigger AccountUpdateTerms on Account (before update,after update) { Map<Id, Account> acc = new Map<Id, Account>(); acc = trigger.oldMap; List<Account> allNewRecords = accupd = [SELECT Id,Company_Code__c,Bill_To_Account__c,Credit_Hold__c,Payment_Terms__c FROM Account WHERE Bill_To_Account__c =: newacc.newMap.keysets()]; List<Account> accupd=new list<Account>(); for(account newacc : trigger.new) { if(newacc.Credit_Hold__c != acc.get(newacc.Id).Credit_Hold__c || newacc.Payment_Terms__c != acc.get(newacc.Id).Payment_Terms__c || newacc.Price_Level__c != acc.get(newacc.Id).Price_Level__c) { accupd.add(acc); } } Map<Id,List<Account>> newacc=new Map<Id,List<Account>>(); for(Account a:accupd) { for(Account b:accupd) { List<Account> macc; if(b.Bill_To_Account__c==a.id) { if(acc.contains(a.id)) { macc=acc.get(a.id); macc.add(b); macc.put(a,b); } else { macc=new List<Account>(); macc.add(b); macc.put(a,b); } } } } if (Trigger.isBefore) { if (ContractTriggerHandler.runBU()) { ContractTriggerHandler.processBeforeUpdate(accupd); } } else { if (ContractTriggerHandler.runAU()) { ContractTriggerHandler.processAfterUpdate(newacc); } } }Trigger handler class :
public class ContractTriggerHandler { private static boolean runBeforeUpdate = true; private static boolean runAfterUpdate = true; public static boolean runBU() { if (runBeforeUpdate) { runBeforeUpdate = false; return true; } else { return false; } } public static boolean runAU() { if (runAfterUpdate) { runAfterUpdate = false; return true; } else { return false; } } public static void processBeforeUpdate(List<account> Inputs) { for(account upd : Inputs) { upd.ByPass_Validation_Rules__c= TRUE; } update Inputs; } public static void processAfterUpdate(Map<Id,List<Account>> newacc) { List<Account> allAccountRecordsToUpdate=new List<Account>(); for(Id mapKeyId : newacc.keySet()) { List<Account> upAllRecordForKey=newacc.get(mapKeyId); for(Account upd:upAllRecordForKey) { upd.Company_Code__c = .Company_Code__c; upd.Credit_Hold__c = newacc.Credit_Hold__c; upd.Payment_Terms__c = newacc.Payment_Terms__c; upd.Price_Level__c= newacc.Price_Level__c; allAccountRecordsToUpdate.add(upd); } } update allAccountRecordsToUpdate; } }Hope this code helps. This is not a verified one but does the functionality as expected by you , it follows the bulk trigger practise.Let me know if you need further help.
Thanks and Regards,
Shiva RV
Finally, I achieved my goal, made changes over my trigger which is now working as I expected...
The issue now is, I need the test code, and honestly, I have not found the way how to do it!!! If somebody can help me... would be great... below the code with no errors and working 100 % well!!!
trigger AccountUpdateTerms on Account (before update, after update) { Map<Id, Account> acc = new Map<Id, Account>(); acc = trigger.oldMap; List<Account> accupd = new List<Account>(); accupd = [SELECT Id,Company_Code__c,Bill_To_Account__c,Credit_Hold__c,Payment_Terms__c FROM Account WHERE Bill_To_Account__c =: Trigger.newMap.keySet() ]; for(account newacc : trigger.new){ if(newacc.Credit_Hold__c != acc.get(newacc.Id).Credit_Hold__c || newacc.Payment_Terms__c != acc.get(newacc.Id).Payment_Terms__c || newacc.Price_Level__c != acc.get(newacc.Id).Price_Level__c){ //////// if(trigger.isBefore && accupd.size() >0){ for(account upd : accupd){ upd.ByPass_Validation_Rules__c= TRUE; } update accupd; } /////////// if(trigger.isAfter && accupd.size() >0){ for(account upd : accupd){ upd.Company_Code__c = newacc.Company_Code__c; upd.Credit_Hold__c = newacc.Credit_Hold__c; upd.Payment_Terms__c = newacc.Payment_Terms__c; upd.Price_Level__c= newacc.Price_Level__c; } update accupd; } } } }