+ Start a Discussion
mw6mw6 

error in the trigger

I have a trigger and when running hit the below error. not sure how to fix this, requesting expert help.

Error:Apex trigger updateReliefTeacheronTeacherClassSession caused an unexpected exception, contact your administrator: updateReliefTeacheronTeacherClassSession: execution of AfterUpdate caused by: System.QueryException: Non-selective query against large object type (more than 200000 rows). Consider an indexed filter or contact salesforce.com about custom indexing. Even if a field is indexed a filter might still not be selective when: 1. The filter value includes null (for instance binding with a list that contains null) 2. Data skew exists whereby the number of matching rows is very large (for instance, filtering for a particular foreign key value that occurs many times): Trigger.updateReliefTeacheronTeacherClassSession: line 14, column 1

trigger updateReliefTeacheronTeacherClassSession on Staff_Leave__c (after insert, after update)
    {
        map<string, Staff_Leave__c> ObjMap = new map<string, Staff_Leave__c>();
        DateTime myDateTime;
        String dayOfWeek;
        
  
        for(Staff_Leave__c obj: Trigger.new)
        {   List<Class__c> classList = [SELECT Id,Day__c from Class__c where id =: obj.Active_Class__c];
            
            if (obj.Relief_Teacher__c != Null)
            {   myDateTime = (DateTime)obj.date_of_leave__c;
                dayOfWeek = myDateTime.format('E');
                
                for(Class__c classRec:classList)
                {
                    if(dayOfWeek != classRec.day__c)
                    {
                       obj.addError('Please check leave dates.'); 
                    }
                    else
                    {
                        ObjMap.put(obj.Teacher__c, obj);
                    }
                }
                System.debug('-----Teacher is = '+ obj.Teacher__c + ' ------');
            }
        }
         
        List<Teacher_Class_Session__c> T = [SELECT Id,Teacher__c, Staff_Leave__c ,Assigned_Teacher__c,Relief_Type__c , Class__c,Class__r.teacher__c, Session_Date__c, Attendant_Status__c FROM Teacher_Class_Session__c WHERE Class__r.teacher__c IN :ObjMap.KeySet()];
        List<Teacher_Class_Session__c> TUpdateList = new List<Teacher_Class_Session__c>();
         
        for(Teacher_Class_Session__c c: T)
        {   System.debug('----------- Iterating through all class sessions-----');
            
            Staff_Leave__c obj = ObjMap.get(c.class__r.teacher__c);
            System.debug('Class session id = '+ c.id);
            System.debug('c.teacher= ' + c.class__r.teacher__c);
            System.debug('obj teacher= ' +obj.teacher__c);
            System.debug('c.class= ' + c.class__c + ' ' + 'obj class= ' +obj.Active_Class__c );
            System.debug('c.date= ' + c.session_date__c + ' ' + 'obj class= ' +obj.date_of_leave__c);
                        
            if(obj.Teacher__c == c.Class__r.teacher__c && obj.Active_Class__c == c.Class__c && c.session_date__c == obj.date_of_leave__c)
            { System.debug('Match found');
             c.Teacher__c = obj.Relief_Teacher__c;
             c.Relief_Type__c = obj.Leave_Type__c;
             c.Attendant_Status__c= 'Relief';
             c.Staff_Leave__c = obj.id;
             tUpdateList.add(c);
             }
         
           
        }
     
        if(tUpdateList.size() > 0)
        {
            update tUpdateList;
        }
        
    }
Best Answer chosen by mw6
Asif Ali MAsif Ali M
I would suggest 2 things.
  • check not null before adding teacher__c to objMap. Find the update if block below.
if (obj.Relief_Teacher__c != Null && obj.Teacher__c != null)
{
     ObjMap.put(obj.Teacher__c, obj);
     System.debug('-----Teacher is = '+ obj.Teacher__c + ' ------');
}
  • Add a index on Class__c.Teacher__c
Let me know if it solves your error.

 

All Answers

Shruti SShruti S
Could you please tell which is the 14th line? It would help me to decide what exactly is causing the error.
AvaneeshAvaneesh
Hi
You are getting this error because your trigger is not bulkyfied Look at line no 8 and 9
under for loop u can't Query
and this is the first rule of a trigger
again you are using a nested for loop 
So because of this, you are getting this error at the time you hit only 100 query.

Thank you 
Avaneesh Singh
mw6mw6
Hi

Below is the trigger from the production, earlier it was from sandbox, on the producton we are having the error.

trigger updateReliefTeacheronTeacherClassSession on Staff_Leave__c (after insert, after update)
    {
        map<string, Staff_Leave__c> ObjMap = new map<string, Staff_Leave__c>();
         
        for(Staff_Leave__c obj: Trigger.new)
        {
            if (obj.Relief_Teacher__c != Null)
            {
                ObjMap.put(obj.Teacher__c, obj);
                System.debug('-----Teacher is = '+ obj.Teacher__c + ' ------');
            }
        }
         
        List<Teacher_Class_Session__c> T = [SELECT Id,Teacher__c, Staff_Leave__c ,Assigned_Teacher__c,Relief_Type__c , Class__c,Class__r.teacher__c, Session_Date__c, Attendant_Status__c FROM Teacher_Class_Session__c WHERE Class__r.teacher__c IN :ObjMap.KeySet()];
        List<Teacher_Class_Session__c> TUpdateList = new List<Teacher_Class_Session__c>();

         
        for(Teacher_Class_Session__c c: T)
        {   System.debug('----------- Iterating through all class sessions-----');
            
            Staff_Leave__c obj = ObjMap.get(c.class__r.teacher__c);
            System.debug('Class session id = '+ c.id);
            System.debug('c.teacher= ' + c.class__r.teacher__c);
            System.debug('obj teacher= ' +obj.teacher__c);
            System.debug('c.class= ' + c.class__c + ' ' + 'obj class= ' +obj.Active_Class__c );
            System.debug('c.date= ' + c.session_date__c + ' ' + 'obj class= ' +obj.date_of_leave__c);
                        
            if(obj.Teacher__c == c.Class__r.teacher__c && obj.Active_Class__c == c.Class__c && c.session_date__c == obj.date_of_leave__c)
            { System.debug('Match found');
             c.Teacher__c = obj.Relief_Teacher__c;
             c.Relief_Type__c = obj.Leave_Type__c;
             c.Attendant_Status__c= 'Relief';
             c.Staff_Leave__c = obj.id;
             tUpdateList.add(c);
             }
             else
             {
                 System.debug('Match not found');
             }
        }
     
        if(tUpdateList.size() > 0)
        {
            update tUpdateList;
        }
    }
Asif Ali MAsif Ali M
I would suggest 2 things.
  • check not null before adding teacher__c to objMap. Find the update if block below.
if (obj.Relief_Teacher__c != Null && obj.Teacher__c != null)
{
     ObjMap.put(obj.Teacher__c, obj);
     System.debug('-----Teacher is = '+ obj.Teacher__c + ' ------');
}
  • Add a index on Class__c.Teacher__c
Let me know if it solves your error.

 
This was selected as the best answer