You need to sign in to do that
Don't have an account?
mw6
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;
}
}
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;
}
}
- Add a index on Class__c.Teacher__c
Let me know if it solves your error.All Answers
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
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;
}
}
- Add a index on Class__c.Teacher__c
Let me know if it solves your error.