+ Start a Discussion
sudhirn@merunetworks.comsudhirn@merunetworks.com 

Error: System.LimitException: Too many SOQL queries: 101 Trigger

Hi,

  I wrote a trigger which is working fine when deployed to production am getting below eror Please suggest me how to fix this issue

Error: System.LimitException: Too many SOQL queries: 101 Trigger.logosince: line 28, column 1
 
trigger logosince on Account (before  update) 
{
  Try
 {
   Set<Id> accountId = new Set<Id>();
   List<Id> chkoneprt = new List<Id>();
   List<Id> chkoneid = new List<Id>();
   for (Account a : Trigger.New) 
   {     
      accountId.add(a.id);
      chkoneid.add(a.id);
      chkoneprt.add(a.parentid);
      
      } 
       
      /* not required for ( account actone : [select id,parentid from account 
                                where id in :accountId 
                               ])
        {
          chkoneid.add(actone.id);  
          chkoneprt.add(actone.parentid);  
        }  */
        
          system.debug('ID' + chkoneid);
         system.debug('Parent ID' + chkoneprt);
 
        List<Id> finalid = new List<Id>();
        for ( account actpar : [select id,parentid from account 
                                where 
                                     ( parentid != '' and
                                       parentid in :chkoneprt ) or 
                                     ( parentid != '' and
                                       parentid in :chkoneid ) or
                                      id in :chkoneid 
                               ])
        {
          finalid.add(actpar.id);  
            
        }
        system.debug('ID' + chkoneid); 
        system.debug('Final ID' + finalid);        
             
     List<AggregateResult> gr = [ 
     SELECT min(closedate) from opportunity 
     WHERE accountid IN :chkoneid or accountid in :finalid];
   
       
     for (AggregateResult ar : gr)  {
        system.debug('Min Opp date' + (Datetime)ar.get('expr0'));
        for(Account act : trigger.new) 
              {
              act.Logo_Since__c = (Datetime)ar.get('expr0') + 1;
              
              }
         }     
     
 }
  catch (System.NullPointerException e) {
    system.debug('Null Exception');
  }    
             
         
}

 
Best Answer chosen by sudhirn@merunetworks.com
Mandodari RawatMandodari Rawat
Error: System.LimitException: Too many SOQL queries: 101 comes when you hit Governors Limit (check at this link:https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_gov_limits.htm). To fix this problem you have to take your select query out of the for loop. Please see the modified code below:
trigger Logosince on Account (before  update)  {
    Try  {
        Set<Id> accountId = new Set<Id>();
        List<Id> chkoneprt = new List<Id>();
        List<Id> chkoneid = new List<Id>();
        for (Account a : Trigger.New)     {     
            accountId.add(a.id);
            chkoneid.add(a.id);
            chkoneprt.add(a.parentid);
        }
        
        system.debug('ID' + chkoneid);
        system.debug('Parent ID' + chkoneprt);
        
        List<Id> finalid = new List<Id>();
        List<Account> accounts =   [select id,parentid from account 
                                    where  ( parentid != '' and   parentid in :chkoneprt ) or 
                                               ( parentid != '' and   parentid in :chkoneid ) or
                                                 id in :chkoneid ];
        for ( Account actpar :  accounts)  {
            finalid.add(actpar.id);  
        }
        system.debug('ID' + chkoneid); 
        system.debug('Final ID' + finalid);        
        
        List<AggregateResult> gr = [
            SELECT min(closedate) from opportunity 
            WHERE accountid IN :chkoneid or accountid in :finalid];
        
        for (AggregateResult ar : gr)  {
            system.debug('Min Opp date' + (Datetime)ar.get('expr0'));
            for(Account act : trigger.new)  {
                act.Logo_Since__c = (Datetime)ar.get('expr0') + 1;
            }
        }
    } catch (System.NullPointerException e) {
        system.debug('Null Exception');
    }
}

 

All Answers

Mandodari RawatMandodari Rawat
Error: System.LimitException: Too many SOQL queries: 101 comes when you hit Governors Limit (check at this link:https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_gov_limits.htm). To fix this problem you have to take your select query out of the for loop. Please see the modified code below:
trigger Logosince on Account (before  update)  {
    Try  {
        Set<Id> accountId = new Set<Id>();
        List<Id> chkoneprt = new List<Id>();
        List<Id> chkoneid = new List<Id>();
        for (Account a : Trigger.New)     {     
            accountId.add(a.id);
            chkoneid.add(a.id);
            chkoneprt.add(a.parentid);
        }
        
        system.debug('ID' + chkoneid);
        system.debug('Parent ID' + chkoneprt);
        
        List<Id> finalid = new List<Id>();
        List<Account> accounts =   [select id,parentid from account 
                                    where  ( parentid != '' and   parentid in :chkoneprt ) or 
                                               ( parentid != '' and   parentid in :chkoneid ) or
                                                 id in :chkoneid ];
        for ( Account actpar :  accounts)  {
            finalid.add(actpar.id);  
        }
        system.debug('ID' + chkoneid); 
        system.debug('Final ID' + finalid);        
        
        List<AggregateResult> gr = [
            SELECT min(closedate) from opportunity 
            WHERE accountid IN :chkoneid or accountid in :finalid];
        
        for (AggregateResult ar : gr)  {
            system.debug('Min Opp date' + (Datetime)ar.get('expr0'));
            for(Account act : trigger.new)  {
                act.Logo_Since__c = (Datetime)ar.get('expr0') + 1;
            }
        }
    } catch (System.NullPointerException e) {
        system.debug('Null Exception');
    }
}

 
This was selected as the best answer
sudhirn@merunetworks.comsudhirn@merunetworks.com
Thank You Very much this worked now I have a similar issue firing from other trigger request you please help me on this issue
 
 Below is the error I am getting while converting lead also am getting governance limit message
 
Error: System.LimitException: Too many SOQL queries: 101 Trigger.Public_Opportunity_View_trg: line 39, column 1
trigger Public_Opportunity_View_trg on Opportunity (After Insert, After Update, After Delete) {

/* Fires during INSERT */
if(trigger.isInsert)
 {
   List<Public_Opportunity_View__c> POVi = new list<Public_Opportunity_View__c>();
   List<Opportunity_Compensation__c> OCi = new list<Opportunity_Compensation__c>();
   
 for(Opportunity opp :trigger.new)
  {
   Public_Opportunity_View__c POV = new Public_Opportunity_View__c();
   Opportunity_Compensation__c OC = new Opportunity_Compensation__c();
    
      POV.Account_ID__c = opp.accountid;
      POV.Opportunity_ID__c    =  opp.ID;
      POV.Close_Date__c        =  opp.CloseDate;
      POV.Opportunity_Name__c  =  opp.Name;
      POV.Name  =  opp.Name;
      POV.Stage__c             =  opp.StageName;
      POV.Lead_Source__c = opp.LeadSource;
      POVi.add(POV);
      
      /* Insert into Opportunity_Compensation__c */
      OC.Name =  opp.Name;
      OC.Opportunity_ID__c =  opp.ID;
      OCi.add(OC);
   
    }
     
     Insert POVi;
     Insert OCi;
   
   }

/* Fires during UPDATE */
  if(trigger.isUpdate)
   {
     List <Opportunity> Opp = [ SELECT id,accountid,CloseDate,Name,StageName,LeadSource FROM Opportunity WHERE id = :Trigger.newMap.keySet()]; 
     
     List <Public_Opportunity_View__c> Pubopp = [  select id,Account_ID__c,Opportunity_ID__c,Close_Date__c,Opportunity_Name__c,Stage__c,Lead_Source__c   
                                                from Public_Opportunity_View__c
                                                WHERE Opportunity_ID__c = :Trigger.newMap.keySet()];

  
  for ( opportunity opps : opp )
  {
    for (Public_Opportunity_View__c Pubopps : Pubopp )
     {
       
        Pubopps.Account_ID__c        =  opps.accountid;
        Pubopps.Opportunity_ID__c    =  opps.ID;
        Pubopps.Close_Date__c        =  opps.CloseDate;
        Pubopps.Opportunity_Name__c  =  opps.Name;
        Pubopps.Name  =  opps.Name;
        Pubopps.Stage__c             =  opps.StageName;
        Pubopps.Lead_Source__c   =       opps.LeadSource;
      
      
      }
  
    update Pubopp;
    } 
     
   }  
 
   /* Fires during DELETE*/
 if(Trigger.isDelete) 
 {
     
  for(Opportunity opp :trigger.old)
  {   
     
    List<Public_Opportunity_View__c> existopp = [Select Id from Public_Opportunity_View__c where Opportunity_ID__c = :opp.id];
     
     delete existopp;
   }   
    
    List<Public_Opportunity_View__c> nullopp = [Select Id from Public_Opportunity_View__c where Opportunity_ID__c = NULL]; 
     
     delete nullopp;
  }
 
    
}

Thanks
Sudhir
Deepthi BDeepthi B
Hi Sudhir,
When query operations are placed inside a for loop, database operations are invoked once per iteration of the loop making it very easy to reach these governor limits. Kindly query the records before using them inside FOR loop.

For more reference, look at the best practices for writing triggers https://developer.salesforce.com/page/Apex_Code_Best_Practices
Mandodari RawatMandodari Rawat
Sushir,
Have a look at the Apex Best Practice #7: Use of the Limits Apex Methods to Avoid Hitting Governor Limits
Here is link https://developer.salesforce.com/page/Apex_Code_Best_Practices

Apex has a System class called Limits that lets you output debug messages for each governor limit. There are two versions of every method: the first returns the amount of the resource that has been used in the current context, while the second version contains the word limit and returns the total amount of the resource that is available for that context.

The following example shows how to embed these types of statements in your code and ultimately determine if or when you are about to exceed any governor limits. Using either the System Log or Debug Logs, you can evaluate the output to see how the specific code is performing against the governor limits. Additionally, you can embed logic in the Apex code directly to throw error messages before reaching a governor limit. The code sample below has an IF statement to evaluate if the trigger is about to update too many Opportunities.

Here is an example of how you can use a combination of System.debug statements and the Limits Apex class to generate some very useful output as it relates to governor limits and the overall efficiency of your code.
 
trigger accountLimitExample on Account (after delete, after insert, after update) {

    System.debug('Total Number of SOQL Queries allowed in this Apex code context: ' +  Limits.getLimitQueries());
    System.debug('Total Number of records that can be queried  in this Apex code context: ' +  Limits.getLimitDmlRows());
    System.debug('Total Number of DML statements allowed in this Apex code context: ' +  Limits.getLimitDmlStatements() );
    System.debug('Total Number of CPU usage time (in ms) allowed in this Apex code context: ' +  Limits.getLimitCpuTime());
    
   // Query the Opportunity object 
    List<Opportunity> opptys = 
        [select id, description, name, accountid,  closedate, stagename from Opportunity where accountId IN: Trigger.newMap.keySet()];
   
    System.debug('1. Number of Queries used in this Apex code so far: ' + Limits.getQueries());
    System.debug('2. Number of rows queried in this Apex code so far: ' + Limits.getDmlRows());
    System.debug('3. Number of DML statements used so far: ' +  Limits.getDmlStatements());    
    System.debug('4. Amount of CPU time (in ms) used so far: ' + Limits.getCpuTime());
    
    //NOTE:Proactively determine if there are too many Opportunities to update and avoid governor limits
    if (opptys.size() + Limits.getDMLRows() > Limits.getLimitDMLRows()) {
            System.debug('Need to stop processing to avoid hitting a governor limit. Too many related Opportunities to update in this trigger');
            System.debug('Trying to update ' + opptys.size() + ' opportunities but governor limits will only allow ' + Limits.getLimitDMLRows());
            for (Account a : Trigger.new) {
                a.addError('You are attempting to update the addresses of too many accounts at once. Please try again with fewer accounts.');
            }
    }
    
    else{
        System.debug('Continue processing. Not going to hit DML governor limits');
        System.debug('Going to update ' + opptys.size() + ' opportunities and governor limits will allow ' + Limits.getLimitDMLRows());
        for(Account a : Trigger.new){
            System.debug('Number of DML statements used so far: ' +  Limits.getDmlStatements());
            
            
            for(Opportunity o: opptys){ 
                if (o.accountid == a.id)
                   o.description = 'testing';
            }
           
        }
        update opptys;
        System.debug('Final number of DML statements used so far: ' +  Limits.getDmlStatements());
        System.debug('Final heap size: ' +  Limits.getHeapSize());
    }
}


I hope this will help you in resolving above issue.

Thanks!
Mandy

 
arul asmarul asm
Hi,
System.LimitException: Too many SOQL queries: 101
  I wrote a trigger which is working fine when deployed to production am getting below eror Please suggest me how to fix this issue
(TriggerUtility.prePopulateADAndOfficerFieldsUnderbli:)
if(isBefore && (isInsert || isUpdate))
        {
            try
            {
                if(newBliList != null && newBliList.Size() != 0)
                {
                    set<String> budgetIdSet = new set<String>();
                    Set<String> setOfNames = new Set<String>();
                    Map<String, EEG_SP_Budget__c> budMap = new Map<String, EEG_SP_Budget__c>();
                    
                    for(EEG_SP_Budget_Line_Items__c bli : newBliList)
                    {
                        if(bli.EEG_SP_Budget__c != null)
                        budgetIdSet.add(bli.EEG_SP_Budget__c);
                    }
                    
                    if(budgetIdSet != null && budgetIdSet.size() != 0)
                    {
                        List<EEG_SP_Budget__c> listOfBudgets = [Select id, Name, Officer__c, Account_Director__c From EEG_SP_Budget__c WHERE Id IN : budgetIdSet];
                        
                        if(listOfBudgets != null && listOfBudgets.Size() != null)
                        {
                            for(EEG_SP_Budget__c bud : listOfBudgets)
                            {
                                budMap.put(bud.id, bud);
                            }
                        }
                        /*for(EEG_SP_Budget__c bud : [Select id, Name, Officer__c, Account_Director__c From EEG_SP_Budget__c WHERE Id IN : budgetIdSet])
                        {
                            budMap.put(bud.id, bud);
                        }*/
                        
                        if(budMap != null && budMap.size() != 0)
                        {
                            for(EEG_SP_Budget_Line_Items__c bli : newBliList)
                            {
                                if(budMap.containsKey(bli.EEG_SP_Budget__c))
                                {
                                    if(budMap.get(bli.EEG_SP_Budget__c).Account_Director__c != null && bli.Account_Director__c == null)
                                        bli.Account_Director__c = budMap.get(bli.EEG_SP_Budget__c).Account_Director__c;
                                    
                                    if(budMap.get(bli.EEG_SP_Budget__c).Officer__c != null && bli.Officer__c == null)
                                        bli.Officer__c = budMap.get(bli.EEG_SP_Budget__c).Officer__c;
                                }
                            }
                        }
                    }
                }
            }
CCGNCCGN
I have a similar case... i need your help

trigger SFDC_Trigger_Edificio on SFDC_Edificio__c (after insert, after update) {
    if(trigger.isAfter) {
        if(trigger.isInsert || trigger.isUpdate) {
            if(trigger.new[0].RecordTypeId == Schema.SObjectType.SFDC_Edificio__c.getRecordTypeInfosByName().get('Edificio Venta').getRecordTypeId()) {
                map<Id, decimal> edificio_DeGracia = new map<Id, decimal>();
                map<Id,map<Id,Date>> idContrato_idEdificio_Fecha = new map<Id,map<Id,Date>>();
                
                for(SFDC_Edificio__c forData : trigger.new) {
                    if(trigger.isUpdate && (forData.Dias_de_gracia__c != trigger.oldMap.get(forData.Id).Dias_de_gracia__c)) {
                        if(!edificio_DeGracia.containsKey(forData.Id)) {
                            edificio_DeGracia.put(forData.Id, forData.Dias_de_gracia__c);
                        }                                                                               
                    }
                }
    
                list<OrderItem> consultaGlobal = [select Id, Fecha_de_entrega_Departamento__c, OrderId, Order.ContractId, Order.Contract.Oportunidad__c, Order.Contract.Oportunidad__r.Edificio__c, Order.Contract.Oportunidad__r.Edificio__r.Dias_de_gracia__c from OrderItem where Order.Contract.Oportunidad__r.Edificio__c IN : edificio_DeGracia.keySet() and Fecha_de_entrega_Departamento__c <> null and Order.Contract.Bloquear_DDGracia__c = false and Order.Contract.Status = 'Borrador'];
                if(!consultaGlobal.isEmpty()) {
                    for(OrderItem forData : consultaGlobal) {
                        if(!edificio_DeGracia.containsKey(forData.Order.Contract.Oportunidad__r.Edificio__c)) { edificio_DeGracia.put(forData.Order.Contract.Oportunidad__r.Edificio__c, forData.Order.Contract.Oportunidad__r.Edificio__r.Dias_de_gracia__c); }
                        if(!idContrato_idEdificio_Fecha.containsKey(forData.Order.ContractId)) {
                            map<Id,Date> tmp = new map<Id,Date>();
                            tmp.put(forData.Order.Contract.Oportunidad__r.Edificio__c,forData.Fecha_de_entrega_Departamento__c);
                            idContrato_idEdificio_Fecha.put(forData.Order.ContractId, new map<Id,Date>());
                            idContrato_idEdificio_Fecha.put(forData.Order.ContractId, tmp);
                        }
                    }
                    list<Contract> contratosInvolucrados = [select Id, Dias_de_gracia__c, Oportunidad__r.Edificio__c from Contract where Id IN : idContrato_idEdificio_Fecha.keySet()];
                    if(!contratosInvolucrados.isEmpty()) {
                        for(Contract forData : contratosInvolucrados) {
                            forData.Dias_de_gracia__c = idContrato_idEdificio_Fecha.get(forData.Id).get(forData.Oportunidad__r.Edificio__c).addDays((Integer)edificio_DeGracia.get(forData.Oportunidad__r.Edificio__c));
                        }
                        update contratosInvolucrados;     
madhu reddy 93madhu reddy 93
HI every one cloud you please help me on this thanks for advance.


Error: System.LimitException: Too many SOQL queries: 101 Trigger

 
for(WorkOrderLineItem workLine : [select id, workOrderId from workOrderlineItem where workOrderId IN :WorkOrderIds]){
        mapWorkOrderLineItem.put(workLine.workOrderId, workLine);
    }
    Id ProdId = [select Id, Product2Id, Product2.ProductCode from PriceBookEntry where Name = 'Service Fee' and Id = '01u7F000007Xf5XQAS'].Id;
     system.debug('**********Product2Id*********    '+ProdId);
List<ProductConsumed> prodConsumedList = new List<ProductConsumed>();
    if(Trigger.isupdate && trigger.isafter && firstAfterUpdate)
    {
        
        for(ServiceAppointment svc : [select id, status, Actual_Duration_Hours__c,ParentRecordId from ServiceAppointment where id IN :trigger.new]){
            if(WorkOrderIds.contains(svc.ParentRecordId) && svc.Status =='Completed'){
                system.debug('**********svc1*********    '+svc);
                mapWorkOrderLineItem.get(svc.ParentRecordId).Asset_Title__c='Service Fee';
                    system.debug('**********svc2*********    '+svc);
               ProductConsumed pc = new ProductConsumed();
                pc.QuantityConsumed = svc.Actual_Duration_Hours__c;
                pc.PricebookEntryId = ProdId;
                pc.WorkOrderId=svc.ParentRecordId;
                pc.WorkOrderLineItemId=mapWorkOrderLineItem.get(svc.ParentRecordId).id;
                prodConsumedList.add(pc);
            }
            
        }
         system.debug('**********svc3*********    ');
             update mapWorkOrderLineItem.values(); 
      insert prodConsumedList;
         system.debug('**********svc4*********    ');
        
        
    }