0

I received the following error:

Error: Invalid Data. Review all error messages below to correct your data. Apex trigger ISOTrigger caused an unexpected exception, contact your administrator: ISOTrigger: System.LimitException: Too many SOQL queries: 101

In the debug log I've found the following:

15:11:56.0 (762902111)|SOQL_EXECUTE_BEGIN|[94]|Aggregations:0|SELECT Id, OwnerId, IsRecurrence, RecurrenceActivityId FROM Task WHERE (IsClosed = FALSE AND (IsRecurrence = TRUE OR RecurrenceActivityId = '') AND AccountId = :tmpVar1)
15:11:56.0 (762942801)|EXCEPTION_THROWN|[94]|System.LimitException: Too many SOQL queries: 101
15:11:56.0 (763220286)|FATAL_ERROR|System.LimitException: Too many SOQL queries: 101

Class.AccountHandler.beforeUpdate: line 94, column 1
Class.TriggerFactory.execute: line 95, column 1
Class.TriggerFactory.createHandler: line 29, column 1
Trigger.AccountTrigger: line 3, column 1

15:11:56.0 (763234604)|FATAL_ERROR|System.LimitException: Too many SOQL queries: 101

Here is the code in method beforeUpdate and AccountHandler class:

public void beforeUpdate(SObject oldSo, SObject so) {

   for(SObject bu :Trigger.new) {
       Account a = (Account) bu;

       List<Task> tasksFromDatabase = new List<Task> ([SELECT Id, OwnerId, IsRecurrence, RecurrenceActivityId FROM Task WHERE IsClosed=false AND (IsRecurrence = true OR RecurrenceActivityId = '') AND AccountId = :a.Id]);

       String agentName = a.Agents__c;
       String recruiterName = a.Recruiter__c;//TO DO ::: delete, not used

       if(agentName != null) {
           List<ISOs__c>isoName = [SELECT Name, Recruiter_ID_num__c, Active__c FROM ISOs__c WHERE Id IN (SELECT ISO_Company__c FROM Agent__c WHERE Id = :agentName)];
           if(isoName.size() > 0) {
               if(isoName[0].Active__c == false) {
                   a.ISO_Name__c.addError('The selected agent is linked to an inactive Iso');
               }

           a.ISO_Name__c = isoName[0].Name;
           a.ISO_recruiter_id__c = IsoName[0].Recruiter_ID_num__c;
           }
       } else if(agentName == null) {
           a.ISO_Name__c = '';
           a.ISO_recruiter_id__c = '';
       }

       for(SObject sob :Trigger.new) {
           Account ab = (Account) sob;
           Account oldAB = (Account)Trigger.oldMap.get(ab.id);
           if (ab.Status__c != null && oldAB.Status__c!=null) {       
               if(ab.Status__c != oldAB.Status__c) {   
                   statusmail mail = new statusmail();
                   mail.setAccountInfo(ab);
                   mail.SendAccountEmail();            
                   if(ab.Agents__c != null)
                       mail.sendAgentEmail();
                   if(ab.Recruiter__c != null)
                       mail.sendRecruiterEmail();
               }
           }
       }       
    }
}

Please advise how to avoid this error?

  • you have multiple soql statements inside a for loop - please read up on trigger bulkification (trailhead, apex doc, google) – cropredy Jan 9 '17 at 14:48
3

Bulkification 101

This is the most basic pattern to bulkify code. You need to build your entire coding paradigm around collections, not individual records. You should query the records you need before your loop and cache them in a Map for retrieval later on.

Child Records

It looks like all of your queries are for child data, so this is the basic principle you need to apply.

Incorrect Bulkification

for (/*loop definition*/)
{
    List<ChildObject__c> childRecords = [/*query*/];
}

Correct Bulkification

Map<Id, List<ChildObject__c>> parentToChildren = new Map<Id, List<ChildObject__c>>();
for (ChildObject__c child : [/*child query*/])
{
    if (!parentToChildren.containsKey(child.Parent__c))
        parentToChildren.put(child.Parent__c, new List<ChildObject__c>());
    parentToChildren.get(child.Parent__c).add(child);
}

for (/*original loop definition*/)
{
    List<ChildObject__c> children = parentToChildren.get(loopRecord.Id);
}

Parent Records

The pattern for parent records is similar, and you should familiarize yourself with the idea here as well.

Incorrect Bulkification

for (/*loop definition*/)
{
    ParentObject__c parent = [/*query*/];
}

Correct Bulkification

Map<Id, ParentObject__c> parents = new Map<Id, ParentObject__c>([/*parent query*/]);
// if you need to map parents by Id, you can leverage the built in constructor

for (/*original loop definition*/)
{
    ParentObject__c parent = parents.get(loopRecord.ParentId);
}
| improve this answer | |
1

You have two queries in a for loop. Here's the solution if you want to remove the first query from the for loop. You have to do the same for ISOs__c query.

public void beforeUpdate(SObject oldSo, SObject so) {

   List<Id> accountIds = new List<Id>();
   for(SObject bu :Trigger.new) {
       Account a = (Account) bu;
       accountIds.add(a.Id);
   }

   Map<Id, List<Task>> tasksByAccountId = new Map<Id, List<Task>>();
   for(Task task : [SELECT Id, OwnerId, IsRecurrence, RecurrenceActivityId, AccountId FROM Task WHERE IsClosed=false AND (IsRecurrence = true OR RecurrenceActivityId = '') AND AccountId in :accountIds]){
      if(tasksByAccountId.containsKey(task.AccountId)){
         tasksByAccountId.get(task.AccountId).add(task);
      }else{
         List<Task> tasks = new List<Task>(){task};
         taskByAccountId.put(task.AccountId, tasks);
      }
   }

   for(SObject bu :Trigger.new) {
       Account a = (Account) bu;

       List<Task> tasksFromDatabase = taskByAccountId.get(a.Id);
| improve this answer | |
  • FYI your code will be more efficient if you take advantage of for loop batching. You also left a query in the for loop. – Adrian Larson Jan 9 '17 at 14:54
  • Sorry for the missing for loop batching. I mentioned the second query in the for loop. I was just trying to show him how to do it and leaving him some work. – Martin Lezer Jan 9 '17 at 14:57
  • That might be more clear if you just trim out the code you didn't fix. :) – Adrian Larson Jan 9 '17 at 14:58
  • Yes sorry.... You're right. As always ! – Martin Lezer Jan 9 '17 at 14:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.