function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
LloydSilverLloydSilver 

Too Many SOQL Queries

I'm getting a Too many SOQL queries error on a trigger. Unfortunately, my developer has gone MIA. I'd greatly appreciate help and if it's something not simple I'm willing to pay to get it fixed (just send me a PM).

Specifically, the error is:
  
Apex script unhandled trigger exception by user/organization: 005i0000001Fi5L/00Di0000000H8nt

LumpSum_Trigger: System.LimitException: CFL:Too many SOQL queries: 101

Here is the relevant code (classes and triggers, likley some not involved but I wasn't sure). Thanks for taking a peak. I've done everything within my limited knowledge to figure it out to no avail.

TRIGGER 
trigger LumpSum_Trigger on Insurance_Case__c (after update) {
    
    if (!CommissionExtensionHelper.hasLumpsumAlreadyCreated()) {
        InsuranceCaseHelperclass.UpdateAgentCaseCommissionOnCaseUpdate(Trigger.NewMap,Trigger.oldMap);
    }    
    CommissionExtensionHelper.setLumpsumAlreadyCreated();
    
    
}

COMMISSION EXTENSION HELPER CLASS 
public with sharing class CommissionExtensionHelper {
  
   private static boolean x1035CommissionCalculated = false;
   private static boolean LumpsumCommissionCalculated = false;
   
    private static boolean bytrigger = false;
   
     public static boolean getbytrigger() {
        return  bytrigger;
    }
    
     public static void setbytrigger() {
       bytrigger = true;
    }
    
    public static boolean has1035AlreadyCreated() {
        return x1035CommissionCalculated;
    }

    
    
    public static void set1035AlreadyCreated() {
        x1035CommissionCalculated= true;
    }


    public static String get1035AlreadyCreated(String x1035created) {
        return 'x1035created: ' + x1035created;
    }
    
    
     public static boolean hasLumpsumAlreadyCreated() {
        return LumpsumCommissionCalculated;
    }

    
    
    public static void setLumpsumAlreadyCreated() {
        LumpsumCommissionCalculated= true;
    }


    public static String getLumpsumAlreadyCreated(String Lumpsum) {
        return 'Lumpsumcreated: ' + Lumpsum;
    }
   
  

}
INSURANCE CASE HELPER CLASS 
public class InsuranceCaseHelperclass{


    public static void UpdateAgentCaseCommissionOnCaseUpdate(Map<Id,Insurance_Case__c> newMap,Map<Id,Insurance_Case__c> oldMap){
            Map<Id, Insurance_Case__c> cases = new Map<Id, Insurance_Case__c>();
            Map<Id, Insurance_Case__c> casesstatus = new Map<Id, Insurance_Case__c>();
            Agent_Cases_Commissions__c[] accToUpdate = new Agent_Cases_Commissions__c[0];
            
            for(Insurance_Case__c record: newMap.Values()){
                Insurance_Case__c oldcase = oldMap.get(record.Id);
                if(record.Status__c != oldcase.Status__c && record.Status__c != 'Submitted'){
                    casesstatus.put(record.Id, record);
                } 
                else {
                    cases.put(record.Id, record);
                }
            }
            
            Agent_Cases_Commissions__c[] accstatus = [SELECT Id, Insurance_Case__c, Contract_Type__c FROM Agent_Cases_Commissions__c WHERE Insurance_Case__c IN :casesstatus.keyset() AND Contract_Type__c = 'Agent Contract'];
            Agent_Cases_Commissions__c[] accnostatus = [SELECT Id, Insurance_Case__c, Contract_Type__c FROM Agent_Cases_Commissions__c WHERE Insurance_Case__c IN :cases.keyset() AND Contract_Type__c = 'Agent Contract'];
            
            for(Agent_Cases_Commissions__c acc :accstatus){
                acc.Case_Status_Change__c = TRUE;
                accToUpdate.add(acc);
            }
            
            for(Agent_Cases_Commissions__c acc2 :accnostatus){
                accToUpdate.add(acc2);
            }
            
            if(!accToUpdate.isEmpty()){
                update accToUpdate;
            }
      }
}

UPDATEAGENTCASECOMMISSIONONUPDATE TRIGGER 
trigger UpdateAgentCaseCommissionOnCaseUpdate on Insurance_Case__c (after update) {

    Map<Id, Insurance_Case__c> cases = new Map<Id, Insurance_Case__c>();
    Map<Id, Insurance_Case__c> casesstatus = new Map<Id, Insurance_Case__c>();
    Agent_Cases_Commissions__c[] accToUpdate = new Agent_Cases_Commissions__c[0];
    
    for(Insurance_Case__c record: Trigger.new){
        Insurance_Case__c oldcase = Trigger.oldMap.get(record.Id);
        if(record.Status__c != oldcase.Status__c && record.Status__c != 'Submitted'){
            casesstatus.put(record.Id, record);
        } else {
            cases.put(record.Id, record);
        }
    }
    
    Agent_Cases_Commissions__c[] accstatus = [SELECT Id, Insurance_Case__c, Contract_Type__c FROM Agent_Cases_Commissions__c WHERE Insurance_Case__c IN :casesstatus.keyset() AND Contract_Type__c = 'Agent Contract'];
    Agent_Cases_Commissions__c[] accnostatus = [SELECT Id, Insurance_Case__c, Contract_Type__c FROM Agent_Cases_Commissions__c WHERE Insurance_Case__c IN :cases.keyset() AND Contract_Type__c = 'Agent Contract'];
    
    for(Agent_Cases_Commissions__c acc :accstatus){
        acc.Case_Status_Change__c = TRUE;
        accToUpdate.add(acc);
    }
    
    for(Agent_Cases_Commissions__c acc2 :accnostatus){
        accToUpdate.add(acc2);
    }
    
    if(!accToUpdate.isEmpty())
    update accToUpdate;

}



Again, thanks so much. I'm not sure if this is simple or not. If not, happy to pay for a fix.
 
BalajiRanganathanBalajiRanganathan
The code you posted looks good. The Trigger on Agent_Cases_Commissions__c might be having the issue.
Please check the trigger on Agent_Cases_Commissions__c object or post the code on that trigger.
William LópezWilliam López
Hello LloydSilver

Too Many SOQL Queries its an issues that happen when the sumary of the SOQL of the whole process are more than 100.

Usually this problem fails in a trigger that maybe dont have the blame, EG: If the previous trigger was not efficient and it ran 95 SQOL and this one do only 6 the counter will get to 100 and you will get the exception.

Other caouse for this problem that I see are loops, your trigger at the end update the accunts. Maybe one trigger in the account try to update back the Insurance_Case__c and a loop its created.

Without seeing the whole trace will be hard to see the problem but so far I can see that:
  • LumpSum_Trigger and UpdateAgentCaseCommissionOnCaseUpdate do the exact same thing, if both are active this can be an issue.
  • LumpSum_Trigger uses a class call it "CommissionExtensionHelper" Form what I can see here you dev tried to apply a lopp prevention logic.
I would say try to deactive the "UpdateAgentCaseCommissionOnCaseUpdate" and try again LumpSum_Trigger seems to do the same thing in a better way.

If this does not work I will start developer console log (explaniation here):
https://www.salesforce.com/us/developer/docs/apexcode/Content/apex_debugging_system_log_console.htm

And chechk for a value like this:

11:47:46.546|LIMIT_USAGE_FOR_NS|(default)|
Number of SOQL queries: 0 out of 100

You will see the text "Number of SOQL queries:" changing in the log, so you will be able to see the increases in the SOQL quieres, if you see a big jump, maybe the problem its there.

Please let me know how it goes,

Bill.
 
William LópezWilliam López

Hello 

Too Many SOQL Queries its an issues that happen when the summary of the SOQL of the whole process are more than 100.

Usually this problem fails in a trigger that maybe don't have the blame, EG: If the previous trigger was not efficient and it ran 95 SQOL and this one do only 6 the counter will get to 100 and you will get the exception.

Other cause for this problem that I see are loops, your trigger ask for an update and this cases a loop, if happen from time to time maybe under some conditions you have more SOQL than in other cases (Eg one of your triggers rungs only for some record not all of them).

To debug Start developer console log (explanation here):
https://www.salesforce.com/us/developer/docs/apexcode/Content/apex_debugging_system_log_console.htm

And when you get the row debug log check for a value like this:

11:47:46.546|LIMIT_USAGE_FOR_NS|(default)|
Number of SOQL queries: 0 out of 100

You will see the text "Number of SOQL queries:" Several times, the things its to track which block of code add more SQL to the SUM then you can analize that trigger and see how to optimized.

Pleae let me know how its goes,

Bill