+ Start a Discussion
Jeff_SFJeff_SF 

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

I am getting the above error when running a flow to update records In an object.  The same object has a trigger that updates whenever a record is udated.  Is there a work around for this limit issue in this particular instance? I have a couple of flows that will need to update records on this object but the trigger is getting in the way.  thank you
trigger ServiceBillingsTrigger on Service_Billings__c (after delete, after insert, after update) {
 
  Map<String,Special_Services_Sched__c> spcMap = new Map<String,Special_Services_Sched__c>();
  List<Special_Services_Sched__c> spcList = new List<Special_Services_Sched__c>();

  Map<String,Account> acctMap = new Map<String,Account>();
  List<Account> acctList = new List<Account>();
  
  final Product2 prod = [Select Id, Name From Product2 Where RecordTypeId = '012A0000000IqewIAC' And Name = 'JC' LIMIT 1][0];
  final RecordType inSpecsRt = [Select Id From RecordType WHERE SobjectType='Special_Services_Sched__c' AND Name='Specs' LIMIT 1];
  
  if (trigger.isDelete)
  {
      for (Service_Billings__c s : trigger.old) 
      {
      if(s.Cleaner__c != null)
      {
        if(!acctMap.containsKey(s.Cleaner__c))
        {
          Account clnrAcct = new Account(Id=s.Cleaner__c);
          clnrAcct.Active_Service__c = [Select count() 
                          From Service_Billings__c 
                          Where Active__c=true 
                          And Cleaner__c=:s.Cleaner__c];
          AggregateResult[] groupedResults = [Select SUM(Cleaner_Price__c) 
                          From Service_Billings__c 
                          Where Active__c=true 
                          And Cleaner__c=:s.Cleaner__c];
          clnrAcct.Services_Total__c = Double.valueOf(groupedResults[0].get('expr0'));

          acctMap.put(s.Cleaner__c,clnrAcct);
        }
      }

      if(s.Customer__c != null)
      {
        if(!acctMap.containsKey(s.Customer__c))
        {
          Account custAcct = new Account(Id=s.Customer__c);
          custAcct.Active_Service__c = [Select count() 
                          From Service_Billings__c 
                          Where Active__c=true 
                          And Customer__c=:s.Customer__c];
          AggregateResult[] groupedResults = [Select SUM(Customer_Price__c) 
                          From Service_Billings__c 
                          Where Active__c=true 
                          And Customer__c=:s.Customer__c];
          custAcct.Services_Total__c = Double.valueOf(groupedResults[0].get('expr0'));

          acctMap.put(s.Customer__c,custAcct);
        }
        if(s.Active__c)
        {
          if(s.Service__c == prod.Id)
          {
            for(Special_Services_Sched__c spc : [Select Id 
                                From Special_Services_Sched__c 
                                Where Active__c=true 
                                  And RecordTypeId =:inSpecsRt.Id
                                  And Customer__c=:s.Customer__c])
            {
              if(spcMap.containsKey(spc.Id))
                spcMap.remove(spc.Id);
              spc.Service_Billing__c = s.Id;
              spcMap.put(spc.Id,spc);
            }
          }
        }
      } 
      }    
  } else {

    if(Trigger.isUpdate)
    {
      for(integer x = 0; x < trigger.old.size(); x++)
          {
            if(trigger.old[x].LastBilled__c != trigger.new[x].LastBilled__c)
            {
              // billing Cycle Ignore Request.
            } else {
              Service_Billings__c s = trigger.new[x];
          if(s.Cleaner__c != null)
          {
            if(!acctMap.containsKey(s.Cleaner__c))
            {
              Account clnrAcct = new Account(Id=s.Cleaner__c);
              clnrAcct.Active_Service__c = [Select count() 
                              From Service_Billings__c 
                              Where Active__c=true 
                              And Cleaner__c=:s.Cleaner__c];
              AggregateResult[] groupedResults = [Select SUM(Cleaner_Price__c) 
                              From Service_Billings__c 
                              Where Active__c=true 
                              And Cleaner__c=:s.Cleaner__c];
              clnrAcct.Services_Total__c = Double.valueOf(groupedResults[0].get('expr0'));
              
              acctMap.put(s.Cleaner__c,clnrAcct);
            }
          }
    
          if(s.Customer__c != null)
          {
            if(!acctMap.containsKey(s.Customer__c))
            {
              Account custAcct = new Account(Id=s.Customer__c);
              custAcct.Active_Service__c = [Select count() 
                              From Service_Billings__c 
                              Where Active__c=true 
                              And Customer__c=:s.Customer__c];
              AggregateResult[] groupedResults = [Select SUM(Customer_Price__c) 
                              From Service_Billings__c 
                              Where Active__c=true 
                              And Customer__c=:s.Customer__c];
              custAcct.Services_Total__c = Double.valueOf(groupedResults[0].get('expr0'));
              acctMap.put(s.Customer__c,custAcct);
            }    
            if(s.Active__c)
            {
              if(s.Service__c == prod.Id)
              {
                for(Special_Services_Sched__c spc : [Select Id 
                                    From Special_Services_Sched__c 
                                    Where Active__c=true 
                                      And RecordTypeId =:inSpecsRt.Id
                                      And Customer__c=:s.Customer__c])
                {
                  if(spcMap.containsKey(spc.Id))
                    spcMap.remove(spc.Id);
                  spc.Service_Billing__c = s.Id;
                  spcMap.put(spc.Id,spc);
                }
              }
            }
          } 
        }
          }
    }

    if(Trigger.isInsert)
    {    
        for (Service_Billings__c s : trigger.new) 
        {
        if(s.Cleaner__c != null)
        {
          if(!acctMap.containsKey(s.Cleaner__c))
          {
            Account clnrAcct = new Account(Id=s.Cleaner__c);
            clnrAcct.Active_Service__c = [Select count() 
                            From Service_Billings__c 
                            Where Active__c=true 
                            And Cleaner__c=:s.Cleaner__c];
  
            AggregateResult[] groupedResults = [Select SUM(Cleaner_Price__c) 
                            From Service_Billings__c 
                            Where Active__c=true 
                            And Cleaner__c=:s.Cleaner__c];
            clnrAcct.Services_Total__c = Double.valueOf(groupedResults[0].get('expr0'));

            acctMap.put(s.Cleaner__c,clnrAcct);
          }
        }
  
        if(s.Customer__c != null)
        {
          if(!acctMap.containsKey(s.Customer__c))
          {
            Account custAcct = new Account(Id=s.Customer__c);
            custAcct.Active_Service__c = [Select count() 
                            From Service_Billings__c 
                            Where Active__c=true 
                            And Customer__c=:s.Customer__c];
            AggregateResult[] groupedResults = [Select SUM(Customer_Price__c) 
                            From Service_Billings__c 
                            Where Active__c=true 
                            And Customer__c=:s.Customer__c];
            custAcct.Services_Total__c = Double.valueOf(groupedResults[0].get('expr0'));
            acctMap.put(s.Customer__c,custAcct);
          }  
          if(s.Active__c)
          {
            if(s.Service__c == prod.Id)
            {
              for(Special_Services_Sched__c spc : [Select Id 
                                  From Special_Services_Sched__c 
                                  Where Active__c=true 
                                    And RecordTypeId =:inSpecsRt.Id
                                    And Customer__c=:s.Customer__c])
              {
                if(spcMap.containsKey(spc.Id))
                  spcMap.remove(spc.Id);
                spc.Service_Billing__c = s.Id;
                spcMap.put(spc.Id,spc);
              }
            }
          }
        } 
        }
    }
    }
   
     if(!acctMap.isEmpty())
       acctList.addAll(acctMap.values());
    if(acctList.size() > 0)
      update(acctList);
      
    if(!spcMap.isEmpty())
      spcList.addAll(spcMap.values());
  if(spcList.size() > 0)
    update(spcList);
}

 
Best Answer chosen by Jeff_SF
rajat Maheshwari 6rajat Maheshwari 6

@Jeff, You can create one checkbox field which got update true by using flow.....and in trigger, You can check, If those checkbox field is untrue, then it will fire otherwise no

Please let me know in case of any help :)

Rajat Maheshwari

rajatzmaheshwari@gmail.com

All Answers

Shweta_AgarwalShweta_Agarwal
Hi Jeff,

You need to add logic to stop recursive call.  Go through the below link to know more about recursive trigger.
https://help.salesforce.com/articleView?id=000133752&type=1 

Thanks,
Shweta
rajat Maheshwari 6rajat Maheshwari 6

@Jeff, You need to put some conditions under "update logic", so that when your record got update by flows, then after your trigger will not fire when condition doesn't met.

Note : -You are using soql query under for loops, so try to use outside the forloop, as it will contribute to governor limit exception.

Thanks
Rajat Maheshwari
rajatzmaheshwari@gmail.com

Jeff_SFJeff_SF
Rajat- HOw would the flow update best be indentified in the trigger so that the trigger does not activate under this condition?  I believe this would fix the issue because over 200 records are needing updated in this flow...
rajat Maheshwari 6rajat Maheshwari 6

@Jeff, You can create one checkbox field which got update true by using flow.....and in trigger, You can check, If those checkbox field is untrue, then it will fire otherwise no

Please let me know in case of any help :)

Rajat Maheshwari

rajatzmaheshwari@gmail.com

This was selected as the best answer