+ Start a Discussion
Saad Ahmad 27Saad Ahmad 27 

DML Error - too many queries

Hello, I have a custom object that has a look up to the account object. I've written the trigger below to count the child records and update a field on the Account object. The code also sums the total of the child records and updates a field on the Account.

The trigger works fine if I update one child record but throws a DML error "InvoiceRollUp: System.LimitException: Too many SOQL queries: 101"  when I do a bulk update via dataloader. Thank you in advance for your help!
trigger InvoiceRollUp on Order_Line_Item__c (after insert, after update,after delete,after undelete) {
    
    set<id> AccountIds = new set<id>();
    
    if(trigger.isInsert || trigger.isUpdate){
        for(Order_Line_Item__c li : trigger.new){
            AccountIds.add(li.Ship_To__c);
        }
    }
    
    //When deleting payments
    if(trigger.isDelete){
        for(Order_Line_Item__c li : trigger.old){
            AccountIds.add(li.Ship_To__c);
        }
    }
    
   map<Id,Double> AccountMap = new map<Id,Double>();
   
    for(AggregateResult q : [select Ship_To__c , sum(Line_Item_Total__c) from Order_Line_Item__c where (Ship_To__c IN :AccountIds AND (Invoice_Date__c=Last_N_Months:12)) group by Ship_To__c]){
        AccountMap.put((Id)q.get('Ship_To__c'),(Double)q.get('expr0'));
    }

    List<Account> AccountsToUpdate = new List<Account>();
    
    for(Account acc : [Select Id, Invoice_Total__c from Account where Id IN :AccountIds]){
        Double PaymentSum = AccountMap.get(acc.Id);
        acc.Invoice_Total__c = PaymentSum;
        AccountsToUpdate.add(acc);
    }
    
    for(AggregateResult OC : [select Ship_To__c , count(Id) from Order_Line_Item__c where (Ship_To__c IN :AccountIds) group by Ship_To__c]){
        AccountMap.put((Id)OC.get('Ship_To__c'),(Double)OC.get('expr0'));
    }
    
    List<Account> AccountsToCount = new List<Account>();
    
    for(Account ac : [Select Id, Invoice_Total__c from Account where Id IN :AccountIds]){
        Double counter = AccountMap.get(ac.Id);
        ac.Invoice_Line_Item_Count__c = counter;
        AccountsToCount.add(ac);
    }
 
  update AccountsToUpdate;
  update AccountsToCount;
}

 
Best Answer chosen by Saad Ahmad 27
Pradeep SinghPradeep Singh
You can refer below code to stop recursion
public class RecursiveHandler{
     public static Boolean run = true;
}
----------------------------------------------------

trigger DemoTrigger on Contact (after update){
 
    Set<String> accIdSet = new Set<String>();
     
    if(RecursiveHandler.run ){
        RecursiveHandler.run = false;
         
        for(Contact con: Trigger.New){
            if(con.Lastname != 'Demo'){
                accIdSet.add(con.accountId);
            }
        }
    }
}


 

All Answers

Pradeep SinghPradeep Singh
Hi, have you used something to stop the recursion of the trigger that comes in case of After Update.
As after each update DML, your trigger seems to be executed again and again. This causes the queries run again and hits the limit.
Pradeep SinghPradeep Singh
You can refer below code to stop recursion
public class RecursiveHandler{
     public static Boolean run = true;
}
----------------------------------------------------

trigger DemoTrigger on Contact (after update){
 
    Set<String> accIdSet = new Set<String>();
     
    if(RecursiveHandler.run ){
        RecursiveHandler.run = false;
         
        for(Contact con: Trigger.New){
            if(con.Lastname != 'Demo'){
                accIdSet.add(con.accountId);
            }
        }
    }
}


 
This was selected as the best answer
Saad Ahmad 27Saad Ahmad 27
Thanks Pradeep. This resolved my issue.