Apex Code Development You need to sign in to do that
Don't have an account?
Dnyaneshwar I am getting System.LimitException: Too many SOQL queries: 101 error in trigger
Hi,
I have a trigger to do lots of actions before and after DML's. but in my test class i am getting erro: System.LimitException: Too many SOQL queries: 101. I have followed best practises for test classes such as not using query or DML in for loops but still i am getting this error. Can sombody locate cause of this error.
Code:
trigger LeadTrigger on Lead (before insert, before update, after insert) {
Group queue = [SELECT Id, name FROM Group WHERE type='Queue' and Name='TCC Counsellors' Limit 1];
List<GroupMember> queueMembers = [SELECT Group.Name, UserOrGroupId FROM GroupMember WHERE GroupId =: queue.Id ];
integer val = 0;
List<Lead> Exleads = [SELECT Id, name, email, MobilePhone, Program__c FROM Lead WHERE Id NOT IN : Trigger.new ];
List<Duplicate_Lead__c> DupLeads = new List <Duplicate_Lead__c>();
List<Lead> dupLeadsUpdate = new List<Lead>();
List<Lead> dupLeadsDelete = new List<Lead>();
List<Lead> LeadsToUpdate = new List<Lead>();
Set<Lead> myset = new Set<Lead>();
List<Lead> result = new List<Lead>();
for( Lead ld : Trigger.new ) {
if(Trigger.isBefore && (Trigger.isInsert || Trigger.isUpdate)){
/*Remove Company field value*/
if(ld.Company != null){
ld.Company = null;
}
/*Start - Set Assigned_to_Sales_Team value */
for( Group q : [SELECT Id, name FROM Group WHERE type='Queue' And name != 'TCC Counsellors']){
if(ld.OwnerId == q.Id && ld.Assigned_to_Sales_Team__c == false){
ld.Assigned_to_Sales_Team__c = true;
}
}
/*End*/
/*Start - FetchIntake */
for( Calender__c cal : [SELECT Id, Start_Date__c, End_Date__c, Program__c, Intake_Month__c, Intake_Year__c FROM Calender__c WHERE Start_Date__c <= :System.Today() and End_Date__c >= :System.Today() ]) {
if(Trigger.isInsert && ld.Program__c == cal.Program__c) {
ld.Intake__c = cal.id;
ld.Intake_Month__c = cal.Intake_Month__c;
ld.Intake_Year__c = cal.Intake_Year__c;
//matchLead.add(le);
}
}
/*End*/
/*Start - FetchTccValues*/
for (GroupMember qm :queueMembers ) {
if( ld.ownerid == queue.Id ) {
ld.TCC_Lead_Status__c = ld.Status;
ld.TCC_Lead_Owner__c = qm.Group.Name;
} else if( ld.ownerid == qm.UserOrGroupId ) {
User us = [SELECT Id,name FROM User where id =: qm.UserOrGroupId];
ld.TCC_Lead_Status__c = ld.Status;
ld.TCC_Lead_Owner__c = us.name;
}
}
/*End*/
/*Start - Lead Rating calculation*/
if( ld.Program__c == 'EMBA' ) {
if(ld.Work_Experience__c=='3 + years'){
val=val+1;
}if(ld.Qualification__c!='Undergraduate'){
val=val+1;
}if(ld.Lead_City__c=='Mumbai'){
val=val+1;
}if(ld.LeadSource=='Walk In'||ld.LeadSource=='Incomming Call'||ld.LeadSource=='Chat'){
val=val+1;
}if(ld.Comfortable_with_Fee__c=='Yes'){
val=val+1;
}if(ld.Info_session_Attended__c =='Yes'){
val=val+1;
}if(ld.Interested_in_this_Intake__c=='Yes'){
val=val+1;
}if(ld.Duplicacy__c==true){
val=val+1;
}if(ld.Interested_in_Program_For_Self__c=='Yes'){
val=val+1;
}if(ld.Interested_in_SPJAT__c=='Yes'){
val=val+1;
}
}else if(ld.Program__c == 'Cyber Security' || ld.Program__c == 'Machine Learning' || ld.Program__c == 'Virtual Reality') {
if(ld.Work_Experience__c=='3 + years'){
val=val+1;
}if(ld.Qualification__c!='Undergraduate'&&ld.Qualification__c!='Graduate'&&ld.Qualification__c!='Post Graduate Diploma'&&ld.Qualification__c!='Masters Degree'&&ld.Qualification__c!='Undergraduate or postgraduate degree in Engineering, Mathematics, Physics, Statistics, B.Sc, B.Pharma, BBA, Economics or Commerce'){
val=val+1;
}if(ld.Lead_City__c=='Mumbai'||ld.Lead_City__c=='Bengaluru' ||ld.Lead_City__c=='Pune'||ld.Lead_City__c=='Chennai'||ld.Lead_City__c=='Trivandrum'){
val=val+1;
}if(ld.LeadSource=='Walk In'||ld.LeadSource=='Incomming Call'||ld.LeadSource=='Chat'){
val=val+1;
}if(ld.Comfortable_with_Fee__c=='Yes'){
val=val+1;
}if(ld.Info_session_Attended__c =='Yes'){
val=val+1;
}if(ld.Interested_in_this_Intake__c=='Yes'){
val=val+1;
}if(ld.Duplicacy__c==true){
val=val+1;
}if(ld.Interested_in_Program_For_Self__c=='Yes'){
val=val+1;
}if(ld.Interested_in_SPJAT__c=='Yes'){
val=val+1;
}
}
system.debug('Value of value variable:' + val);
if( ld.Program__c == 'EMBA' ) {
ld.Lead_Rating_Percentile__c = (((Double)val)*100)/10;
}else if(ld.Program__c == 'DMM'&& ld.Program_Type__c=='PT') {
ld.Lead_Rating_Percentile__c = (((Double)val)*100)/10;
}else if(ld.Program__c == 'DMM'&& ld.Program_Type__c=='FT') {
ld.Lead_Rating_Percentile__c = (((Double)val)*100)/9;
}else if(ld.Program__c == 'BDAP'&& ld.Program_Type__c=='PT') {
ld.Lead_Rating_Percentile__c = (((Double)val)*100)/10;
}else if(ld.Program__c == 'BDAP'&& ld.Program_Type__c=='FT') {
ld.Lead_Rating_Percentile__c = (((Double)val)*100)/9;
}else if(ld.Program__c == 'MGB') {
ld.Lead_Rating_Percentile__c = (((Double)val)*100)/10;
}else if(ld.Program__c == 'GMBA') {
ld.Lead_Rating_Percentile__c = (((Double)val)*100)/10;
}else if(ld.Program__c == 'GFMB') {
ld.Lead_Rating_Percentile__c = (((Double)val)*100)/9;
}else if(ld.Program__c == 'MgLuxM') {
ld.Lead_Rating_Percentile__c = (((Double)val)*100)/9;
}else if(ld.Program__c == 'Cyber Security' || ld.Program__c == 'Machine Learning' || ld.Program__c == 'Virtual Reality') {
ld.Lead_Rating_Percentile__c = (((Double)val)*100)/11;
}else if(ld.Program__c == 'BBA'|| ld.Program__c == 'BBC' || ld.Program__c == 'BEC') {
ld.Lead_Rating_Percentile__c = (((Double)val)*100)/10;
}
/*End*/
/*Start Lead status update to Not Contacted when Lead goes from TCC to RM*/
if( Trigger.isBefore && Trigger.isUpdate ){
Lead OldLead = Trigger.oldMap.get(ld.Id);
for (GroupMember qm :queueMembers ) {
if( (OldLead.Ownerid == qm.UserOrGroupId) && ld.ownerid != queue.Id && string.valueOf(ld.OwnerId).startsWith('00G') ) {
ld.Status = 'Not Contacted';
}
}
}
/*End*/
/*Start - Updating custom fields for mapping with account*/
if(ld.Status != null){
ld.Hidden_Lead_Status__c = ld.Status;
}
if(ld.LeadSource != null){
ld.Hidden_Lead_Source__c = ld.LeadSource;
}
if(true){
ld.Hidden_Do_Not_Call__c = ld.DoNotCall;
ld.Hidden_Partial_Application__c = ld.Partial_Application__c;
}
/*End*/
} // End of Before trigger
/*Start - After trigger for Lead Duplication*/
if(trigger.isAfter && trigger.isInsert){
for(Lead Ele : Exleads){
if(( (ld.email != null && ld.email == Ele.email) && (ld.Program__c != null && ld.Program__c==Ele.Program__c) ) || ( (ld.MobilePhone != null && ld.MobilePhone == ELe.MobilePhone) && (ld.Program__c != null && ld.Program__c==Ele.Program__c) )){
Duplicate_Lead__c DPLead = new Duplicate_Lead__c();
DPLead.Name = 'Duplicate Of'+' '+ Ele.Name;
DPLead.Lead_Name__c = ld.FirstName+ ' '+ld.LastName ;
DPLead.Email__c = ld.Email;
DPLead.Mobile__c = ld.MobilePhone;
DPLead.Lead_Status__c = ld.Status;
DPLead.Lead_Source__c = ld.LeadSource;
DPLead.Work_Experience__c = ld.Work_Experience__c;
DPLead.program__c = ld.Program__c;
DPLead.Lead_City__c = ld.Lead_City__c;
DPLead.Lead_State__c = ld.Lead_State__c;
DPLead.Lead_Country__c = ld.Lead_Country__c;
Ele.Duplicacy__c = true;
LeadsToUpdate.add(Ele);
DupLeads.add(DPLead);
Lead leadDel = new Lead(Id=ld.Id);
dupLeadsDelete.add(leadDel);
}
}
}
/*End*/
}
if(!LeadsToUpdate.isEmpty()){
myset.addAll(LeadsToUpdate);
result.addAll(myset);
update result;
}
if(!dupLeadsUpdate.isEmpty()){
update dupLeadsUpdate;
}
if(!DupLeads.isEmpty()){
Insert DupLeads;
}
if(!dupLeadsDelete.isEmpty()){
Delete dupLeadsDelete;
}
}
Thanks in advance!
I have a trigger to do lots of actions before and after DML's. but in my test class i am getting erro: System.LimitException: Too many SOQL queries: 101. I have followed best practises for test classes such as not using query or DML in for loops but still i am getting this error. Can sombody locate cause of this error.
Code:
trigger LeadTrigger on Lead (before insert, before update, after insert) {
Group queue = [SELECT Id, name FROM Group WHERE type='Queue' and Name='TCC Counsellors' Limit 1];
List<GroupMember> queueMembers = [SELECT Group.Name, UserOrGroupId FROM GroupMember WHERE GroupId =: queue.Id ];
integer val = 0;
List<Lead> Exleads = [SELECT Id, name, email, MobilePhone, Program__c FROM Lead WHERE Id NOT IN : Trigger.new ];
List<Duplicate_Lead__c> DupLeads = new List <Duplicate_Lead__c>();
List<Lead> dupLeadsUpdate = new List<Lead>();
List<Lead> dupLeadsDelete = new List<Lead>();
List<Lead> LeadsToUpdate = new List<Lead>();
Set<Lead> myset = new Set<Lead>();
List<Lead> result = new List<Lead>();
for( Lead ld : Trigger.new ) {
if(Trigger.isBefore && (Trigger.isInsert || Trigger.isUpdate)){
/*Remove Company field value*/
if(ld.Company != null){
ld.Company = null;
}
/*Start - Set Assigned_to_Sales_Team value */
for( Group q : [SELECT Id, name FROM Group WHERE type='Queue' And name != 'TCC Counsellors']){
if(ld.OwnerId == q.Id && ld.Assigned_to_Sales_Team__c == false){
ld.Assigned_to_Sales_Team__c = true;
}
}
/*End*/
/*Start - FetchIntake */
for( Calender__c cal : [SELECT Id, Start_Date__c, End_Date__c, Program__c, Intake_Month__c, Intake_Year__c FROM Calender__c WHERE Start_Date__c <= :System.Today() and End_Date__c >= :System.Today() ]) {
if(Trigger.isInsert && ld.Program__c == cal.Program__c) {
ld.Intake__c = cal.id;
ld.Intake_Month__c = cal.Intake_Month__c;
ld.Intake_Year__c = cal.Intake_Year__c;
//matchLead.add(le);
}
}
/*End*/
/*Start - FetchTccValues*/
for (GroupMember qm :queueMembers ) {
if( ld.ownerid == queue.Id ) {
ld.TCC_Lead_Status__c = ld.Status;
ld.TCC_Lead_Owner__c = qm.Group.Name;
} else if( ld.ownerid == qm.UserOrGroupId ) {
User us = [SELECT Id,name FROM User where id =: qm.UserOrGroupId];
ld.TCC_Lead_Status__c = ld.Status;
ld.TCC_Lead_Owner__c = us.name;
}
}
/*End*/
/*Start - Lead Rating calculation*/
if( ld.Program__c == 'EMBA' ) {
if(ld.Work_Experience__c=='3 + years'){
val=val+1;
}if(ld.Qualification__c!='Undergraduate'){
val=val+1;
}if(ld.Lead_City__c=='Mumbai'){
val=val+1;
}if(ld.LeadSource=='Walk In'||ld.LeadSource=='Incomming Call'||ld.LeadSource=='Chat'){
val=val+1;
}if(ld.Comfortable_with_Fee__c=='Yes'){
val=val+1;
}if(ld.Info_session_Attended__c =='Yes'){
val=val+1;
}if(ld.Interested_in_this_Intake__c=='Yes'){
val=val+1;
}if(ld.Duplicacy__c==true){
val=val+1;
}if(ld.Interested_in_Program_For_Self__c=='Yes'){
val=val+1;
}if(ld.Interested_in_SPJAT__c=='Yes'){
val=val+1;
}
}else if(ld.Program__c == 'Cyber Security' || ld.Program__c == 'Machine Learning' || ld.Program__c == 'Virtual Reality') {
if(ld.Work_Experience__c=='3 + years'){
val=val+1;
}if(ld.Qualification__c!='Undergraduate'&&ld.Qualification__c!='Graduate'&&ld.Qualification__c!='Post Graduate Diploma'&&ld.Qualification__c!='Masters Degree'&&ld.Qualification__c!='Undergraduate or postgraduate degree in Engineering, Mathematics, Physics, Statistics, B.Sc, B.Pharma, BBA, Economics or Commerce'){
val=val+1;
}if(ld.Lead_City__c=='Mumbai'||ld.Lead_City__c=='Bengaluru' ||ld.Lead_City__c=='Pune'||ld.Lead_City__c=='Chennai'||ld.Lead_City__c=='Trivandrum'){
val=val+1;
}if(ld.LeadSource=='Walk In'||ld.LeadSource=='Incomming Call'||ld.LeadSource=='Chat'){
val=val+1;
}if(ld.Comfortable_with_Fee__c=='Yes'){
val=val+1;
}if(ld.Info_session_Attended__c =='Yes'){
val=val+1;
}if(ld.Interested_in_this_Intake__c=='Yes'){
val=val+1;
}if(ld.Duplicacy__c==true){
val=val+1;
}if(ld.Interested_in_Program_For_Self__c=='Yes'){
val=val+1;
}if(ld.Interested_in_SPJAT__c=='Yes'){
val=val+1;
}
}
system.debug('Value of value variable:' + val);
if( ld.Program__c == 'EMBA' ) {
ld.Lead_Rating_Percentile__c = (((Double)val)*100)/10;
}else if(ld.Program__c == 'DMM'&& ld.Program_Type__c=='PT') {
ld.Lead_Rating_Percentile__c = (((Double)val)*100)/10;
}else if(ld.Program__c == 'DMM'&& ld.Program_Type__c=='FT') {
ld.Lead_Rating_Percentile__c = (((Double)val)*100)/9;
}else if(ld.Program__c == 'BDAP'&& ld.Program_Type__c=='PT') {
ld.Lead_Rating_Percentile__c = (((Double)val)*100)/10;
}else if(ld.Program__c == 'BDAP'&& ld.Program_Type__c=='FT') {
ld.Lead_Rating_Percentile__c = (((Double)val)*100)/9;
}else if(ld.Program__c == 'MGB') {
ld.Lead_Rating_Percentile__c = (((Double)val)*100)/10;
}else if(ld.Program__c == 'GMBA') {
ld.Lead_Rating_Percentile__c = (((Double)val)*100)/10;
}else if(ld.Program__c == 'GFMB') {
ld.Lead_Rating_Percentile__c = (((Double)val)*100)/9;
}else if(ld.Program__c == 'MgLuxM') {
ld.Lead_Rating_Percentile__c = (((Double)val)*100)/9;
}else if(ld.Program__c == 'Cyber Security' || ld.Program__c == 'Machine Learning' || ld.Program__c == 'Virtual Reality') {
ld.Lead_Rating_Percentile__c = (((Double)val)*100)/11;
}else if(ld.Program__c == 'BBA'|| ld.Program__c == 'BBC' || ld.Program__c == 'BEC') {
ld.Lead_Rating_Percentile__c = (((Double)val)*100)/10;
}
/*End*/
/*Start Lead status update to Not Contacted when Lead goes from TCC to RM*/
if( Trigger.isBefore && Trigger.isUpdate ){
Lead OldLead = Trigger.oldMap.get(ld.Id);
for (GroupMember qm :queueMembers ) {
if( (OldLead.Ownerid == qm.UserOrGroupId) && ld.ownerid != queue.Id && string.valueOf(ld.OwnerId).startsWith('00G') ) {
ld.Status = 'Not Contacted';
}
}
}
/*End*/
/*Start - Updating custom fields for mapping with account*/
if(ld.Status != null){
ld.Hidden_Lead_Status__c = ld.Status;
}
if(ld.LeadSource != null){
ld.Hidden_Lead_Source__c = ld.LeadSource;
}
if(true){
ld.Hidden_Do_Not_Call__c = ld.DoNotCall;
ld.Hidden_Partial_Application__c = ld.Partial_Application__c;
}
/*End*/
} // End of Before trigger
/*Start - After trigger for Lead Duplication*/
if(trigger.isAfter && trigger.isInsert){
for(Lead Ele : Exleads){
if(( (ld.email != null && ld.email == Ele.email) && (ld.Program__c != null && ld.Program__c==Ele.Program__c) ) || ( (ld.MobilePhone != null && ld.MobilePhone == ELe.MobilePhone) && (ld.Program__c != null && ld.Program__c==Ele.Program__c) )){
Duplicate_Lead__c DPLead = new Duplicate_Lead__c();
DPLead.Name = 'Duplicate Of'+' '+ Ele.Name;
DPLead.Lead_Name__c = ld.FirstName+ ' '+ld.LastName ;
DPLead.Email__c = ld.Email;
DPLead.Mobile__c = ld.MobilePhone;
DPLead.Lead_Status__c = ld.Status;
DPLead.Lead_Source__c = ld.LeadSource;
DPLead.Work_Experience__c = ld.Work_Experience__c;
DPLead.program__c = ld.Program__c;
DPLead.Lead_City__c = ld.Lead_City__c;
DPLead.Lead_State__c = ld.Lead_State__c;
DPLead.Lead_Country__c = ld.Lead_Country__c;
Ele.Duplicacy__c = true;
LeadsToUpdate.add(Ele);
DupLeads.add(DPLead);
Lead leadDel = new Lead(Id=ld.Id);
dupLeadsDelete.add(leadDel);
}
}
}
/*End*/
}
if(!LeadsToUpdate.isEmpty()){
myset.addAll(LeadsToUpdate);
result.addAll(myset);
update result;
}
if(!dupLeadsUpdate.isEmpty()){
update dupLeadsUpdate;
}
if(!DupLeads.isEmpty()){
Insert DupLeads;
}
if(!dupLeadsDelete.isEmpty()){
Delete dupLeadsDelete;
}
}
Thanks in advance!
/*Start - Set Assigned_to_Sales_Team value */
for( Group q : [SELECT Id, name FROM Group WHERE type='Queue' And name != 'TCC Counsellors']){
if(ld.OwnerId == q.Id && ld.Assigned_to_Sales_Team__c == false){
ld.Assigned_to_Sales_Team__c = true;
}
}
/*End*/
But i don't get it how this is working..
Hi Dnyaneshwar,
Try to create a list or Map outside the loop. then Call it for your Loop.
list<Group> grList = [SELECT Id, name FROM Group WHERE type='Queue' And name != 'TCC Counsellors'];
for (Group gr: grList){ //do something }
I hope it'll help you to solve this :-)
Thanks for the reply though!
Have you added Test.startTest and stopTest in each method ,Please check once .