System.LimitException: Too many SOQL queries: 101

Question

I have the below trigger which works perfectly but when I add an additional trigger, I get Error(System.LimitException: Too many SOQL queries: 101 ) which is really worrying me. Basically i can't add another trigger and when I combine the additional trigger with the initial one I still get the same error. So far from my research the error seems to be caused by putting an SOQL query inside a loop but mine doesn't have that so i'm not sure how to solve this. Any ideas?

trigger UpdateLatestRecords on Student_Records__c(after insert, after update, after delete) {
    if( trigger.isinsert || trigger.isupdate ){
        list<Id> accIdList = new list<id>();
        list<Decimal> ExamValue = new list<Decimal>();

        for(Student_Records__c c : trigger.new){
            accIdList.add(c.Student__c); 
        }

        list<Contact> acc = [
            SELECT id, Latest_Overall_Marks__c 
            FROM Contact 
            WHERE id in: accIdList];

        list<Student_Records__c> examrecords = [
            SELECT position_in_class_F__c, Type_of_Exam__c, Overall_Marks__c, Name_of_school__c, Count_Number_of_Marks__c 
            FROM Student_Records__c 
            WHERE RecordTypeId in (
                    SELECT Id 
                    FROM RecordType 
                    WHERE Name = 'Student Exam Record') 
                AND (Type_of_Exam__c = 'Secondary End Term Exam' OR Type_of_Exam__c = 'Primary End Term Exam') 
                AND Student__c in: accIdList 
            ORDER BY Date__c DESC LIMIT 1];

        list<Student_Records__c> KCPEexamrecords = [
            SELECT position_in_class_F__c, Type_of_Exam__c, Overall_Marks__c, Name_of_school__c, Count_Number_of_Marks__c 
            FROM Student_Records__c 
            WHERE RecordTypeId in (
                    SELECT Id 
                    FROM RecordType 
                    WHERE Name = 'Student Exam Record') 
                AND Type_of_Exam__c = 'KCPE' 
                AND Student__c in: accIdList 
            ORDER BY Date__c DESC LIMIT 1];       

        list<Student_Records__c> KCSEexamrecords = [
            SELECT position_in_class_F__c, Type_of_Exam__c, Overall_Marks__c, Name_of_school__c, Count_Number_of_Marks__c
            FROM Student_Records__c 
            WHERE RecordTypeId in (
                    SELECT Id 
                    FROM RecordType 
                    WHERE Name = 'Student Exam Record') 
                AND Type_of_Exam__c = 'KCSE' 
                AND Student__c in: accIdList 
            ORDER BY Date__c DESC LIMIT 1];   

        list<Student_Records__c> feerecords = [
            SELECT Amount__c, Date__c, Name_of_school__c 
            FROM Student_Records__c 
            WHERE RecordTypeId in (
                    SELECT Id 
                    FROM RecordType 
                    WHERE Name = 'Student Fees Record') 
                AND Type_of_fees__c = 'Tuition Fees' 
                AND Student__c in: accIdList 
            ORDER BY Date__c DESC LIMIT 1];                                                                                                                                   

        If(examrecords.size() == 0){
        }
        Else{
            for(Contact a : acc){
                a.Latest_Overall_Marks__c = examrecords[0].Overall_Marks__c;
                a.Latest_Exam__c = examrecords[0].Type_of_Exam__c;
                a.Latest_Position_in_Class__c = examrecords[0].position_in_class_F__c;
            }

            update acc;  
            System.debug('///ENDTERM CONTACT FIELDS updated///');  

        }


        If(KCPEexamrecords.size() == 0){
        }
        Else{
            for(Contact a : acc){
                a.KCPE_Marks__c = KCPEexamrecords[0].Overall_Marks__c;
                a.Primary_School__c = KCPEexamrecords[0].Name_of_school__c;

            }

            update acc;
            System.debug('///KCPE CONTACT FIELDS updated///');
        }

        If(KCSEexamrecords.size() == 0){
        }
        Else{
            for(Contact a : acc){
                a.KCSE_Marks__c = KCSEexamrecords[0].Overall_Marks__c;

            }

            update acc;  
            System.debug('///KCSE CONTACT FIELDS updated///');  
       }

       If(feerecords.size() == 0){
       }
       Else{
           for(Contact a : acc){
               a.Last_School_Fees__c = feerecords[0].Amount__c;
               a.Current_School__c = feerecords[0].Name_of_school__c;
           }

           update acc; 
           System.debug('///FEES CONTACT FIELDS UPDATED///');
       }
   }

    if( trigger.isdelete ){
        list<Id> accIdList = new list<id>();
        list<Decimal> ExamValue = new list<Decimal>();

        for(Student_Records__c c : trigger.old){
            accIdList.add(c.Student__c); 
        }

        list<Contact> acc = [
            SELECT id, Latest_Overall_Marks__c 
            FROM Contact 
            WHERE id in: accIdList];

        list<Student_Records__c> examrecords = [
            SELECT position_in_class_F__c, Type_of_Exam__c, Overall_Marks__c, Name_of_school__c, Count_Number_of_Marks__c 
            FROM Student_Records__c 
            WHERE RecordTypeId in (
                    SELECT Id 
                    FROM RecordType 
                    WHERE Name = 'Student Exam Record') 
                AND (Type_of_Exam__c = 'Secondary End Term Exam' OR Type_of_Exam__c = 'Primary End Term Exam') 
                AND Student__c in: accIdList 
            ORDER BY Date__c DESC LIMIT 1];

        list<Student_Records__c> KCPEexamrecords = [
                SELECT position_in_class_F__c, Type_of_Exam__c, Overall_Marks__c, Name_of_school__c, Count_Number_of_Marks__c
                FROM Student_Records__c 
                WHERE RecordTypeId in (
                        SELECT Id
                        FROM RecordType 
                        WHERE Name = 'Student Exam Record')
                    AND Type_of_Exam__c = 'KCPE' AND Student__c in: accIdList 
                ORDER BY Date__c DESC LIMIT 1];     

        list<Student_Records__c> KCSEexamrecords = [
            SELECT position_in_class_F__c, Type_of_Exam__c, Overall_Marks__c, Name_of_school__c, Count_Number_of_Marks__c
            FROM Student_Records__c 
            WHERE RecordTypeId in (
                    SELECT Id 
                    FROM RecordType 
                    WHERE Name = 'Student Exam Record') 
                AND Type_of_Exam__c = 'KCSE' AND Student__c in: accIdList 
            ORDER BY Date__c DESC LIMIT 1];   

        list<Student_Records__c> feerecords = [
            SELECT Amount__c, Date__c, Name_of_school__c 
            FROM Student_Records__c 
            WHERE RecordTypeId in (
                    SELECT Id 
                    FROM RecordType 
                    WHERE Name = 'Student Fees Record') 
                AND Type_of_fees__c = 'Tuition Fees' AND Student__c in: accIdList 
            ORDER BY Date__c DESC LIMIT 1];        

        If(examrecords.size() == 0){
            for(Contact a : acc){
                a.Latest_Overall_Marks__c = NULL;
                a.Latest_Exam__c = NULL;
                a.Latest_Position_in_Class__c = NULL;

            }
        }
        Else{
            for(Contact a : acc){
                a.Latest_Overall_Marks__c = examrecords[0].Overall_Marks__c;
                a.Latest_Exam__c = examrecords[0].Type_of_Exam__c;
                a.Latest_Position_in_Class__c = examrecords[0].position_in_class_F__c;
             }

             update acc;  
             System.debug('///ENDTERM CONTACT FIELDS updated///');
       }

       If(KCPEexamrecords.size() == 0){
       }
       Else{
           for(Contact a : acc){
               a.KCPE_Marks__c = KCPEexamrecords[0].Overall_Marks__c;
               a.Primary_School__c = KCPEexamrecords[0].Name_of_school__c;
           }

           update acc;
           System.debug('///KCPE CONTACT FIELDS updated///');
       }

       If(KCSEexamrecords.size() == 0){
       }
       Else{
           for(Contact a : acc){
               a.KCSE_Marks__c = KCSEexamrecords[0].Overall_Marks__c;
           }

           update acc;  
           System.debug('///KCSE CONTACT FIELDS updated///');  
       }

       If(feerecords.size() == 0){
       }
       Else{
           for(Contact a : acc){
               a.Last_School_Fees__c = feerecords[0].Amount__c;
               a.Current_School__c = feerecords[0].Name_of_school__c;
           }

           update acc; 
           System.debug('///FEES CONTACT FIELDS UPDATED///');
       }       
   }
}

Answer 1

So, there are number of problems in your code, let's start with

Problem # 1

list<Student_Records__c> examrecords = [SELECT position_in_class_F__c, Type_of_Exam__c, Overall_Marks__c, Name_of_school__c, Count_Number_of_Marks__c FROM Student_Records__c 
                                            WHERE RecordTypeId in (SELECT Id FROM RecordType WHERE Name = 'Student Exam Record') AND (Type_of_Exam__c = 'Secondary End Term Exam' OR Type_of_Exam__c = 'Primary End Term Exam') AND Student__c in: accIdList ORDER BY Date__c DESC LIMIT 1];
list<Student_Records__c> KCPEexamrecords = [SELECT position_in_class_F__c, Type_of_Exam__c, Overall_Marks__c, Name_of_school__c, Count_Number_of_Marks__c FROM Student_Records__c 
                                            WHERE RecordTypeId in (SELECT Id FROM RecordType WHERE Name = 'Student Exam Record') AND Type_of_Exam__c = 'KCPE' AND Student__c in: accIdList ORDER BY Date__c DESC LIMIT 1];             
list<Student_Records__c> KCSEexamrecords = [SELECT position_in_class_F__c, Type_of_Exam__c, Overall_Marks__c, Name_of_school__c, Count_Number_of_Marks__c FROM Student_Records__c 
                                            WHERE RecordTypeId in (SELECT Id FROM RecordType WHERE Name = 'Student Exam Record') AND Type_of_Exam__c = 'KCSE' AND Student__c in: accIdList ORDER BY Date__c DESC LIMIT 1];   
list<Student_Records__c> feerecords = [SELECT Amount__c, Date__c, Name_of_school__c FROM Student_Records__c 

Issue with above code

In Salesforce your priority number one is to beware of the platform limits and develop your code efficiently. Above, you are querying Student_Records__c object four times.

Solution - Problem # 1 Try something like the following to make your code efficient, and make maps of exam type lists. They will be useful in the code scenario.

// maps with key=contactid, and value=student_record__c record.
Map<id, Student_Record__c> ExamRecordsMap = new Map<id, Student_Record__c>();
Map<id, Student_Record__c> KPCRecordsMap = new Map<id, Student_Record__c>();
Map<id, Student_Record__c> FeeRecordsMap = new Map<id, Student_Record__c>();
for ( Student_Records__c student : [SELECT position_in_class_F__c, Type_of_Exam__c, Overall_Marks__c, Name_of_school__c, Count_Number_of_Marks__c
                                    FROM Student_Records__c 
                                    WHERE Student__c in: accIdList ] )
{
    if(student.Type_of_Exam__c == 'Secondary End Term Exam' || student.Type_of_Exam__c == 'Primary End Term Exam') ExamRecordsMap.put(student.Student__c, student);
    else if (student.Type_of_Exam__c == 'KP Exams... ') KPCRecordsMap.put(student.Student__c, student);
    else if (student.Type_of_Exam__c == 'FeeExam') FeeRecordsMap.put(student.Student__c, student);
}

Problem # 2 (not relevant to SOQL 101 error)

If(examrecords.size() == 0){
}
Else{
    for(Contact a : acc){
        a.Latest_Overall_Marks__c = examrecords[0].Overall_Marks__c;
        a.Latest_Exam__c = examrecords[0].Type_of_Exam__c;
        a.Latest_Position_in_Class__c = examrecords[0].position_in_class_F__c;
    }

    update acc;  
    System.debug('///ENDTERM CONTACT FIELDS updated///');  
}

Issues with above code

1. Why have a blank if(list.size == 0) check in which you are doing nothing? It creates visual noise and totally worthless. Use the following instead just put single check if (list.size() > 0 )
2. update acc;, I belive acc is the list of contacts you created earlier. Now, this will be a DML no matter there was any change or not, and that too on the entire list (also rename that list to contacts). Have a list of Contacts-to-update and then build it as your code conditions are met and perform DML on that. It will only hold valid contact records that you actually want to update.
3. Your code is way too repetitive. Now, in a multi-tenant world of development, like Salesforce, this is not gonna work. You are taking big bites off the allocated CPU time. So, if not SOQL 101, you are gonna be hit by CPU time limit exceeded error for a batch of records.
4. There are other problems as well, like List[0].Overall_Marks__c, inside a loop on a list to refer another list, this will always index to the zero position record in that referred list. Use maps instead to refer them in such situations.
5. Comment your code, it really took me a long time to understand your code, without any description header and logical comments it was difficult and think of this as handing your code to your code reviewer in your company. He won't be very pleased. :)

Complete Solution (something roughly like La La Land… no wait, Moo Moo Moonlight)

trigger UpdateLatestRecords on Student_Records__c(after insert, after update, after delete) {
    if( trigger.isinsert || trigger.isupdate ){
        List<Contact> contactsToUdate = new List<Contact>();
        list<Id> contactIds = new list<id>();
        list<Decimal> ExamValue = new list<Decimal>();

        for(Student_Records__c c : trigger.new){
            contactIds.add(c.Student__c); 
        }

        list<Contact> contacts = [
                        SELECT id, Latest_Overall_Marks__c 
                        FROM Contact 
                        WHERE id in: contactIds];
        if (contacts.size() == 0  ) return; 


        // maps with key=contactid, and value=student_record__c record.
        Map<id, Student_Record__c> ExamRecordsMap = new Map<id, Student_Record__c>();
        Map<id, Student_Record__c> KPCRecordsMap = new Map<id, Student_Record__c>();
        Map<id, Student_Record__c> FeeRecordsMap = new Map<id, Student_Record__c>();
        for ( Student_Records__c student : [SELECT position_in_class_F__c, Type_of_Exam__c, Overall_Marks__c, Name_of_school__c, Count_Number_of_Marks__c
                                            FROM Student_Records__c 
                                            WHERE Student__c in: accIdList ] )
        {
            if(student.Type_of_Exam__c == 'Secondary End Term Exam' || student.Type_of_Exam__c == 'Primary End Term Exam') ExamRecordsMap.put(student.Student__c, student);
            else if (student.Type_of_Exam__c == 'KP Exams... ') KPCRecordsMap.put(student.Student__c, student);
            else if (student.Type_of_Exam__c == 'FeeExam') FeeRecordsMap.put(student.Student__c, student);
        }
        for(Contact con : contacts){
            if(ExamRecordsMap.get(con.id) != null) 
            {
                Student_Record__c examStudent = ExamRecordsMap.get(con.id);
                con.Latest_Overall_Marks__c = examStudent.Overall_Marks__c;
                con.Latest_Exam__c = examStudent.Type_of_Exam__c;
                con.Latest_Position_in_Class__c = examStudent.position_in_class_F__c;

                // build the contact to update list
                contactsToUdate.add(con);
            }
            else if(KPCRecordsMap.get(con.id) != null) 
            {
                Student_record__c kpcStudent = KPCRecordsMap.get(con.id);
                con.KCPE_Marks__c = kpcStudent.Overall_Marks__c;
                con.Primary_School__c = kpcStudent.Name_of_school__c;

                // build the contact to update list
                contactsToUdate.add(con);
            }
            else if(FeeRecordsMap.get(con.id) != null) 
            {
                Student_Record__c feeStudent = FeeRecordsMap.get(con.id);
                con.Last_School_Fees__c = feeStudent.Amount__c;
                con.Current_School__c = feeStudent.Name_of_school__c;

                // build the contact to update list
                contactsToUdate.add(con);
            }
        }
        // no need to check for list size here, as the dml won't be counted if "contactToUpdate" has no contact to update
        update contactsToUdate;  
            System.debug('///ENDTERM CONTACT FIELDS updated///');  

    }

    // delete event logic. 
    // follow similar pattern and where possible create functions with paramters to call them in handler logics. 
    // It reduces your code and simplies it to read and maintain.
    if( trigger.isdelete ){
        // delete logic
    }
}

Answer 2

Though by seeing your code there is no room which may lead to more than 100 SOQL queries in a single request but what i can suspect is that you trigger is getting into recursive mode and causing the issue.

The following link may help you

https://help.salesforce.com/articleView?id=000133752&type=1

Also the Context variable isExecuting will help you ..

Answer 3

You shouldn't query the same table several times in the same transaction. Query each table only once.

E.g.:

for(Student_Record__c studentRecord  : [select id, name from Student_Record__c where student__c in accIdList]){

    if(Type of Exam == 'CKPE){


///do some logic    
}   

    else if(Type of Examp == 'KCSE'){       
//do other logic    
}   

else...

}