Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Money Care 7Money Care 7 

Error in Too many soql 101

Hi All

How to overcome salesforce limitation System.LimitException: Too many SOQL queries: 101

  
List<MasterCopy__c> masterCopyList = new List<MasterCopy__c>([Select Name,DateUpdate__c, Opening_Balance__c, Closing_Balance__c, Master_Code__c, Master_Name__c from MasterCopy__c]);
    List<MasterCopy__c> upsertMasterList = new List<MasterCopy__c>();
    MasterCopy__c masterCopyInsert;
    List<MasterTest__c> masterTestList = new List<MasterTest__c>([Select Date__c, Opening_Balance__c, Master_Code__c, Name, Closing_Balance__c from MasterTest__c]);
    if(trigger.isBefore && trigger.isInsert  ){
        for(MasterTest__c masterTest : trigger.new){
            masterTest.EntryDate__c = masterTest.Date__c;
            masterTest.To_Date__c = masterTest.Date__c;
            masterTest.From_Date__c = masterTest.Date__c;
            
            
            masterTest.Closing_Balance__c = masterTest.Opening_Balance__c;
        }
    }

 
Prabhat gangwarPrabhat gangwar

See This code  : 

 if(trigger.isBefore && trigger.isInsert  ){
        for(MasterTest__c masterTest : trigger.new){
            masterTest.EntryDate__c = masterTest.Date__c;
            masterTest.To_Date__c = masterTest.Date__c;
            masterTest.From_Date__c = masterTest.Date__c;
            
            
            masterTest.Closing_Balance__c = masterTest.Opening_Balance__c;
        }
    }

Note : Here you are not passing any Soql in this trigger code .  So how are you getting this Error Too many SOQL queries: 101

 

Please Share Full code 
Money Care 7Money Care 7
Hi @ Prabhat  the error is showing line no 2
  
trigger MasterTestCopy on MasterTest__c (before insert,before update, after insert,after update)
 {
    List<MasterCopy__c> masterCopyList = new List<MasterCopy__c>([Select Name,DateUpdate__c, Opening_Balance__c, Closing_Balance__c, Master_Code__c, Master_Name__c from MasterCopy__c]);
    List<MasterCopy__c> upsertMasterList = new List<MasterCopy__c>();
    MasterCopy__c masterCopyInsert;
    List<MasterTest__c> masterTestList = new List<MasterTest__c>([Select Date__c, Opening_Balance__c, Master_Code__c, Name, Closing_Balance__c from MasterTest__c]);
    if(trigger.isBefore && trigger.isInsert  ){
        for(MasterTest__c masterTest : trigger.new){
            masterTest.EntryDate__c = masterTest.Date__c;
            masterTest.To_Date__c = masterTest.Date__c;
            masterTest.From_Date__c = masterTest.Date__c;
            
            
            masterTest.Closing_Balance__c = masterTest.Opening_Balance__c;
        }
    }
    
    if(trigger.isBefore && trigger.isUpdate ){
        for(MasterTest__c masterTest : trigger.new){
            if(trigger.oldMap.get(masterTest.id).Opening_Balance__c != masterTest.Opening_Balance__c){
                masterTest.Closing_Balance__c = masterTest.Opening_Balance__c;
            }
            
           
            
           
        }
    }
       
    else if(trigger.isAfter && trigger.isInsert){
        Set<Date> dateSet = new Set<Date>();
        for(MasterCopy__c masterCopy : masterCopyList){
            dateSet.add(masterCopy.DateUpdate__c);
        }
        if(!dateSet.contains(trigger.new[0].Date__c)){
            for(MasterTest__c masterTest : masterTestList){
                masterCopyInsert = new MasterCopy__c();
                masterCopyInsert.DateUpdate__c = trigger.new[0].Date__c;
                masterCopyInsert.Opening_Balance__c = masterTest.Opening_Balance__c;
                masterCopyInsert.Closing_Balance__c = masterTest.Closing_Balance__c;
                masterCopyInsert.Master_Code__c = masterTest.Master_Code__c;
                masterCopyInsert.Master_Name__c = masterTest.Name;
                upsertMasterList.add(masterCopyInsert);
            }
        }
        else{
            for(MasterTest__c masterTest : trigger.new){
                masterCopyInsert = new MasterCopy__c();
                masterCopyInsert.DateUpdate__c = masterTest.Date__c;
                masterCopyInsert.Opening_Balance__c = masterTest.Opening_Balance__c;
                masterCopyInsert.Closing_Balance__c = masterTest.Opening_Balance__c;
                masterCopyInsert.Master_Code__c = masterTest.Master_Code__c;
                masterCopyInsert.Master_Name__c = masterTest.Name;
                upsertMasterList.add(masterCopyInsert);
            }
        }
    }
    else if(trigger.isAfter && trigger.isUpdate){
        List<MasterCopy__c> updateMasterCopy = new List<MasterCopy__c>();
        Set<Id> updateMasterCopySet = new Set<Id>();
        Set<Date> dateSet = new Set<Date>();
        for(MasterCopy__c masterCopy : masterCopyList){
            dateSet.add(masterCopy.DateUpdate__c);
        }
        
        if(!dateSet.contains(trigger.new[0].Date__c)){
           
            for(MasterTest__c masterTest : masterTestList){
                masterCopyInsert = new MasterCopy__c();
                masterCopyInsert.DateUpdate__c = trigger.new[0].Date__c;
                masterCopyInsert.Opening_Balance__c = masterTest.Opening_Balance__c;
                masterCopyInsert.Closing_Balance__c = masterTest.Closing_Balance__c;
                masterCopyInsert.Master_Code__c = masterTest.Master_Code__c;
                masterCopyInsert.Master_Name__c = masterTest.Name;
                upsertMasterList.add(masterCopyInsert);
            }
        }
        
        else{
            //system.assert(false,trigger.new[0]);
            //List<BankBookTest__c> updatedBankBookRecord = new List<BankBookTest__c>([Select Name, Date__c, Party_Code__c,Is_Latest_Updated__c , BankAccount__c from BankBookTest__c where Is_Latest_Updated__c = true limit 1]);
            //system.assert(false,Utility.updatedRecordPartyName);
            Set<Date> dateSetMC = new Set<Date>();
            List<BankBookTest__c> bankBookDateList = new List<BankBookTest__c>();
            for(BankBookTest__c bankBook : [Select Date__c,Party_Closing_Balance__c,Bank_Closing_Balance__c,Party_Code__c,Party_Code__r.Name,BankAccount__r.Name from BankBookTest__c where Date__c >= :Utility.updatedRecordDate AND (Party_Code__c = :Utility.updatedRecordPartyName OR BankAccount__c = :Utility.updatedRecordBankName) Order By Date__c Asc]){
                bankBookDateList.add(bankBook);
                //dateSetMC.add(bankBook.Date__c);
            }
            Map<Date,Set<String>> mapOfDateWithPartyName = new Map<Date,Set<String>>();
            for(BankBookTest__c bankBookCode : bankBookDateList){
                if(!mapOfDateWithPartyName.containsKey(bankBookCode.Date__c)){
                    mapOfDateWithPartyName.put(bankBookCode.Date__c,new Set<String>{bankBookCode.Party_Code__c});
                }
                else {
                    mapOfDateWithPartyName.get(bankBookCode.Date__c).add(bankBookCode.Party_Code__c);
                }
            }
            //system.assert(false,mapOfDateWithPartyName);
            for(Date datevalue : mapOfDateWithPartyName.keySet()){
                if(mapOfDateWithPartyName.get(datevalue).contains(Utility.updatedRecordPartyName)){
                    dateSetMC.add(datevalue);
                }
            }
            /*for(MasterCopy__c masterCopy : masterCopyList){
                for(BankBookTest__c bankBookCode : bankBookDateList){
                    if(bankBookCode.Party_Code__r.Name == masterCopy.Master_Name__c){
                          dateSetMC.add(bankBookCode.Date__c);
                    }
                }
            }*/
            //system.assert(false,dateSetMC);
            List<MasterCopy__c> otherMasterCopyRecrods = new List<MasterCopy__c>();
            for(MasterCopy__c masterCopy : masterCopyList){
                if(!dateSetMC.contains(masterCopy.DateUpdate__c) && masterCopy.DateUpdate__c > Utility.updatedRecordDate) {
                    otherMasterCopyRecrods.add(masterCopy);
                }
            }
            //system.assert(false,otherMasterCopyRecrods);
            for(MasterCopy__c masterCopy : otherMasterCopyRecrods){
                for(BankBookTest__c bankBookCode : bankBookDateList){
                    if(bankBookCode.Party_Code__r.Name == masterCopy.Master_Name__c && masterCopy.DateUpdate__c > bankBookCode.Date__c){
                        masterCopy.Closing_Balance__c = bankBookCode.Party_Closing_Balance__c;  
                        updateMasterCopySet.add(masterCopy.id);
                    }
                    //if(bankBookCode.BankAccount__r.Name == masterCopy.Master_Name__c && masterCopy.DateUpdate__c > bankBookCode.Date__c){
                       // masterCopy.Closing_Balance__c = bankBookCode.Bank_Closing_Balance__c;
                       // updateMasterCopySet.add(masterCopy.id);
                    //}
                }
            }
            for(MasterCopy__c masterCopy : masterCopyList){
                for(BankBookTest__c bankBookCode : bankBookDateList){
                    if(bankBookCode.Party_Code__r.Name == masterCopy.Master_Name__c && masterCopy.DateUpdate__c == bankBookCode.Date__c){
                        masterCopy.Closing_Balance__c = bankBookCode.Party_Closing_Balance__c;
                        updateMasterCopySet.add(masterCopy.id);
                    }
                   // if(bankBookCode.BankAccount__r.Name == masterCopy.Master_Name__c && masterCopy.DateUpdate__c == bankBookCode.Date__c){
                        //masterCopy.Closing_Balance__c = bankBookCode.Bank_Closing_Balance__c;
                        //updateMasterCopySet.add(masterCopy.id);
                    //}
                }
            }
        }
        
        Set<Date> existingDateSet = new Set<Date>();
        
        List<BankBookTest__c> Banks = [Select Date__c,Party_Closing_Balance__c,Bank_Closing_Balance__c,Party_Code__r.Name,BankAccount__r.Name from BankBookTest__c where Date__c >= :trigger.new[0].Date__c Order By Id Desc];
            List<BankBookTest__c> bankBookDateList = new List<BankBookTest__c>();
            for(BankBookTest__c bankBook :Banks) {
                if(!existingDateSet.contains(bankBook.Date__c)){
                    bankBookDateList.add(bankBook);
                    existingDateSet.add(bankBook.Date__c);
                }
            } 
        
        List<BankBookTest__c> bankBookMasterCodeList = new List<BankBookTest__c>([Select Date__c,Party_Closing_Balance__c,Bank_Closing_Balance__c,Party_Code__r.Name,BankAccount__r.Name from BankBookTest__c where Party_Code__r.Name = :trigger.new[0].Name OR BankAccount__r.Name = :trigger.new[0].Name]);
            
            //system.assert(false,bankBookMasterCodeList);
            for(MasterCopy__c masterCopy : masterCopyList){
                for(BankBookTest__c bankBookDate : bankBookDateList){
                    for(BankBookTest__c bankBookCode : bankBookDateList){
                       
                        if(bankBookCode.BankAccount__r.Name == masterCopy.Master_Name__c && masterCopy.DateUpdate__c == bankBookDate.Date__c){
                            masterCopy.Closing_Balance__c = bankBookDate.Bank_Closing_Balance__c;
                        }
                    }
                }
                upsertMasterList.add(masterCopy);
            }
        for(MasterCopy__c masterCopy : masterCopyList){
            if(updateMasterCopySet.contains(masterCopy.id)){
                updateMasterCopy.add(masterCopy);
            }
        }
        update updateMasterCopy;
    }
    upsert upsertMasterList;
}

 
NagendraNagendra (Salesforce Developers) 
Hi Money Care 7,

The "System.LimitException: Too many SOQL queries: 101" error appears when you've hit the Execution Governors limit, which means you can run a total 100 SOQL queries in a context.  All the triggers fired will be counted in a single context or call. To fix the issue, you'll need to change your code in such a way that SOQL fired is less than 100. If you need to change the context then you can use @future annotation which will run the code asynchronously.


The error is fired at line number 2 because you are using a list(collection) data type and writing the SOQL to fetch the details.List datatype can contain multiple records.Please make sure what's the amount of data you are fetching in the SOQL.It should not fire more than 100.

How to handle the above error:
  • Since Apex runs on a Multitenant structure, Apex runtime engine strictly enforces limits to ensure code doesn't monopolize shared resources. Learn about the Governors Limit.
  •  Avoid SOQL queries that are inside FOR loop. 
  • Check out the Salesforce Developer Blog where you can find Best Practices for Triggers.
  • Review best practices for Trigger and Bulk requests on our Force.com Apex Code Developer's Guide. 
  • Be sure you're following the key coding principals for Apex Code in our Developer's Guide.
Note : Salesforce cannot stop the Governors Limit or raise it. Following the best practices above should ensure that you don't hit this limit in the future.

Please let us know if this helps you.

Best Regards,
Nagendra.P
harsha__charsha__c
This issue can only be identified by looking into DEBUG logs as this trigger alone can not hit tha SOQL issue.

By looking at logs, you can come to know which are all triggers being invoked in the execution flow and from there you can know if there is recursion(same trigger getting called multiple times).

Then a solution can be derived. I suggest you first check DEBUG LOGS.

- Harsha