Apex Code Development You need to sign in to do that
Don't have an account?
how to bulkified the trigger getting the error message System.LimitException: Too many SOQL queries: 201
Hi Team,
Below apex trigger Getting the error message System.LimitException: Too many SOQL queries: 201.can you please help me to bulkified the code.
Below apex trigger Getting the error message System.LimitException: Too many SOQL queries: 201.can you please help me to bulkified the code.
trigger InvestigatorObjectTrigger on Investigator__c (before update, after insert, after update, before insert){
List<Messaging.SingleEmailMessage> allMails = new List<Messaging.SingleEmailMessage>();
EmailTemplate etmpltmgr = [Select id from EmailTemplate where DeveloperName = 'template_for_investigation_mgr_vf' limit 1];
EmailTemplate etmpltPrimary = [Select id from EmailTemplate where DeveloperName = 'template_for_investigation_unit_vf' limit 1];
EmailTemplate etmpltAlt = [Select id from EmailTemplate where DeveloperName = 'template_for_investigation_alter_vf' limit 1];
//List<Contact> lstAccount = [select id,Name from Contact limit 1];
//for record attachments.
if(Trigger.isUpdate && Trigger.isBefore){
//Set<String> lstIds = new Set<String>();
profile sAdmID = [select id from profile where name ='System Administrator'];
for(Investigator__c inv : trigger.new){
//Complaint__c cmpl = [select id,Name, Complaint_status__c from Complaint__c where Id =: inv.Complaint__c];
/*if(cmpl.Complaint_status__c == 'Investigator / Reviewer'){
if(inv.Investigation_Report_Attached__c == true || inv.Additional_Document_Attached__c == true){
lstIds.add(inv.Id);
}
}
else{
inv.addError('You cannot update the record as case is at another stage.');
} */
if(inv.Investigation_Status__c == 'Investigation Closed'){
if(UserInfo.getProfileId() != sAdmID.Id){
inv.Investigator_Name1_c__c = UserInfo.getFirstName()+' '+UserInfo.getLastName();
inv.Date_of_review__c = System.Today();
}
}
}
/*
if(lstIds.size()>0){
List<Attachment> lstAttchment = [select id,Name,parentId from attachment where ParentId IN : lstIds];
map<String,Attachment> mapOfAttachment = new map<String,Attachment>();
for(Attachment att : lstAttchment){
mapOfAttachment.put(att.ParentId,att);
}
for(Investigator__c inv : trigger.new){
if(inv.Investigation_Report_Attached__c == true || inv.Additional_Document_Attached__c == true){
if(!mapOfAttachment.containsKey(inv.id)){
string Url = System.URL.getSalesforceBaseUrl().toExternalForm();
String str = '<html><Head><script>window.onload = function (){document.getElementById("errorDiv_ep").style.display = "none";window.open("/apex/AttachmentForAllObject?Id='+inv.Id+'","test","Height=400,width=800,top=200,left=300");}</script></Head><Body></Body></html>';
inv.addError('Please Attach File '+str, false);
}
}
}
} */
}
// The following code is for shooting mails to investigator unit and chain and sharing of records .
if(Trigger.isInsert){
boolean myb = false;
if(!test.isrunningtest()){
notification_chain_enable_disable__c mc = notification_chain_enable_disable__c.getValues('Investigator Notification Chain');
myb = mc.Is_Active__c;
}
else{
myb = true;
}
if(Trigger.isAfter){
allMails = new List<Messaging.SingleEmailMessage>();
List<Investigator__share> lastRecShare = new List<Investigator__share>();
String caseownername ='';
for(Investigator__c inv : trigger.new){
if(inv.PCC_Role__c != null){
List<Sub_Directory__c> lstUser = [select id,SF_User__c,SF_User_Email__c,Category__c,SF_User_Name__c from Sub_Directory__c where Master_Directory__c =:inv.PCC_Role__c AND isActive__c = true ];
Complaint__c cmpl = [select id,Country__c,name,Contact_Person__c,epersonname__c ,Entry_Person_Email__c,Entry_Person_Phone__c,Company_Name__c,Contact_Email__c,Complaint_Type__c,Complaint_Date__c,Other_Comments__c from Complaint__c where id =: inv.Complaint__c];
/*for(Sub_Directory__c usr : lstUser){
if(usr.Category__c == 'Primary' || usr.Category__c == 'Alternate'){
caseownername = caseownername+', '+usr.SF_User_Name__c;
}
}*/
for(Sub_Directory__c usr : lstUser){
Boolean check = false;
// caseownername = caseownername.removestart(', ');
if(inv.ownerId != usr.SF_User__c){
Investigator__share recShare = new Investigator__share();
if(usr.Category__c == 'Primary' || usr.Category__c == 'Alternative'){
recShare.AccessLevel = 'Edit';
caseownername = caseownername+', '+usr.SF_User_Name__c;
check = true;
}
if(caseownername.contains(usr.SF_User_Name__c) == false){
check = true;
recShare.AccessLevel = 'Read';
}
if(check){
recShare.ParentId = inv.Id;
recShare.UserOrGroupId = usr.SF_User__c;
lastRecShare.add(recShare);
}
}
if(myb){
Messaging.SingleEmailMessage msg = new Messaging.SingleEmailMessage();
string Url = System.URL.getSalesforceBaseUrl().toExternalForm()+'/'+cmpl.Id;
msg.settargetObjectId(usr.SF_User__c);
msg.setWhatId(cmpl.Id);
if(usr.Category__c == 'Primary'){
msg.setTemplateId(etmpltPrimary.Id);
}
if(usr.Category__c == 'Alternative'){
msg.setTemplateId(etmpltAlt.Id);
}
if(usr.Category__c == 'Manager'){
msg.setTemplateId(etmpltmgr.Id);
}
//msg.setToAddresses(new String[]{usr.SF_User_Email__c });
msg.setsaveAsActivity(false);
allMails.add(msg);
}
}
}
}
if(lastRecShare.size()>0){
System.debug('lastREcShare====>>>> '+lastRecShare);
insert lastRecShare;
}
if(allMails.size()>0){
Messaging.sendEmail(allMails);
}
}
}
//For status updation of complaint according to investigator status update.
if(Trigger.isUpdate && Trigger.isAfter){
Boolean chkStk = false;
Boolean chkSales = false;
Boolean chkNOreviewer = true;
Boolean lastChk = false;
List<Stakeholder__c> ReviewerRcds = new List<Stakeholder__c>();
List<String> RevwCmpledLst = new List<String>();
for(Investigator__c inv : trigger.new){
if(inv.Investigation_Status__c == 'Investigation Closed' && trigger.oldMap.get(inv.Id).Investigation_Status__c != 'Investigation Closed'){
Complaint__c cmpl = [select id,Investigation_Review_completed__c,name,Complaint_status__c,isSales__c,isReviewer__c,isStakeholder__c from Complaint__c where id =:inv.Complaint__c];
if(cmpl.isReviewer__c == true){
system.debug('came inside rwchk truee loop');
chkNOreviewer = false;
//To check for reviewer records if they are present.
ReviewerRcds = [select id,name,Review_Completed__c,Complaint_for_Reviewer__c from Stakeholder__c where recordType.DeveloperName='Reviewer' and Complaint_for_Reviewer__c=:inv.Complaint__c ];
for(Stakeholder__c st :ReviewerRcds){
if(st.Review_Completed__c == true){
RevwCmpledLst.add(st.Id);
}
}
if(RevwCmpledLst.size() != ReviewerRcds.size()){
cmpl.Complaint_status__c = 'Investigator / Reviewer';
update cmpl;
}
if(RevwCmpledLst.size() == ReviewerRcds.size()){
cmpl.Investigation_Review_completed__c = DateTime.Now();
if(cmpl.isStakeholder__c == true){
chkStk = true;
chkSales = false;
lastChk = false;
}
if(chkStk){
cmpl.Complaint_status__c = 'Stakeholder';
update cmpl;
}
if(!chkStk){
if(cmpl.isSales__c == true){
chkSales = true;
lastChk = false;
}
else{
lastChk = true;
}
}
if(chkSales){
cmpl.Complaint_status__c = 'Sales';
update cmpl;
}
if(lastChk){
cmpl.Complaint_status__c = 'Complaint Closed';
update cmpl;
}
}
}
if(chkNOreviewer){
cmpl.Investigation_Review_completed__c = DateTime.Now();
if(cmpl.isStakeholder__c == true){
chkStk = true;
chkSales = false;
lastChk = false;
}
if(chkStk){
cmpl.Complaint_status__c = 'Stakeholder';
update cmpl;
}
if(!chkStk){
if(cmpl.isSales__c == true){
chkSales = true;
lastChk = false;
}
else{
lastChk = true;
}
}
if(chkSales){
cmpl.Complaint_status__c = 'Sales';
update cmpl;
}
if(lastChk){
cmpl.Complaint_status__c = 'Complaint Closed';
update cmpl;
}
}
}
if(inv.Investigation_Status__c == 'Send back to QRC' && trigger.oldMap.get(inv.Id).Investigation_Status__c != 'Send back to QRC'){
Complaint__c cmp = new Complaint__c();
cmp.id = inv.Complaint__c;
cmp.Complaint_status__c = 'Assigned to QRC Postmaster';
String Comment = '';
if(inv.Comments__c != null){
if(inv.Comments__c.length() > 255){
Comment = inv.Comments__c.substring(0,254);
}
else{
Comment = inv.Comments__c;
}
cmp.Iu_send_back2qrc__c = Comment;
}
cmp.IU_Send_back_to_QRC_CHECKBOX__c = true;
update cmp;
}
if(inv.Investigation_Status__c == 'Investigation / Review' && trigger.oldMap.get(inv.Id).Investigation_Status__c != 'Investigation / Review'){
Complaint__c cmp = new Complaint__c();
cmp.id = inv.Complaint__c;
cmp.Complaint_status__c = 'Investigator / Reviewer';
update cmp;
}
}
}
//// added for batch sharing
if(Trigger.isUpdate && Trigger.isAfter){
List<Investigator__share> lastRecShare = new List<Investigator__share>();
SET<Investigator__share> SetRecShare = new SEt<Investigator__share>();
List<Sub_Directory__c> lstUser = new List<Sub_Directory__c>();
String caseownername ='';
for(Investigator__c inv : trigger.new){
lstUser = [select id,SF_User__c,SF_User_Email__c,Category__c,SF_User_Name__c from Sub_Directory__c where Master_Directory__c =:inv.PCC_Role__c AND isActive__c = true ];
for(Sub_Directory__c usr : lstUser){
Boolean check = false;
if(inv.ownerId != usr.SF_User__c){
Investigator__share recShare = new Investigator__share();
if(usr.Category__c == 'Primary' || usr.Category__c == 'Alternative'){
recShare.AccessLevel = 'Edit';
caseownername = caseownername+', '+usr.SF_User_Name__c;
check = true;
}
if(caseownername.contains(usr.SF_User_Name__c) == false){
check = true;
recShare.AccessLevel = 'Read';
}
if(check){
recShare.ParentId = inv.Id;
recShare.UserOrGroupId = usr.SF_User__c;
SetRecShare.add(recShare);
}
}
}
}
if(SetRecShare.size() > 0){
lastRecShare.addall(SetRecShare);
Upsert lastRecShare;
}
}
}
The most common governor limit restricts you to 100 SOQL queries. Loops, especially those iterating over Trigger.new, will easily run 200+ queries and push you across the limit.
You don't fetch a query inside the fol loop because total number of SOQL queries issued is 100.
Check the apex Governor Limits:-
https://developer.salesforce.com/docs/atlas.en-us.salesforce_app_limits_cheatsheet.meta/salesforce_app_limits_cheatsheet/salesforce_app_limits_platform_apexgov.htm
In case you find any other issue please mention.
If you find your Solution then mark this as the best answer.
Thanks and Regards
Suraj Tripathi.
can you bulkify this trigger .
What is the datatype of Master_Directory__c field in Sub_Directory__c object?
I have resolved your issue, Plese check this code:-
trigger InvestigatorObjectTrigger on Investigator__c (before update, after insert, after update, before insert){ List<Messaging.SingleEmailMessage> allMails = new List<Messaging.SingleEmailMessage>(); EmailTemplate etmpltmgr = [Select id from EmailTemplate where DeveloperName = 'template_for_investigation_mgr_vf' limit 1]; EmailTemplate etmpltPrimary = [Select id from EmailTemplate where DeveloperName = 'template_for_investigation_unit_vf' limit 1]; EmailTemplate etmpltAlt = [Select id from EmailTemplate where DeveloperName = 'template_for_investigation_alter_vf' limit 1]; if(Trigger.isUpdate && Trigger.isBefore){ //Set<String> lstIds = new Set<String>(); profile sAdmID = [select id from profile where name ='System Administrator']; for(Investigator__c inv : trigger.new){ if(inv.Investigation_Status__c == 'Investigation Closed'){ if(UserInfo.getProfileId() != sAdmID.Id){ inv.Investigator_Name1_c__c = UserInfo.getFirstName()+' '+UserInfo.getLastName(); inv.Date_of_review__c = System.Today(); } } } } if(Trigger.isInsert){ boolean myb = false; if(!test.isrunningtest()){ notification_chain_enable_disable__c mc = notification_chain_enable_disable__c.getValues('Investigator Notification Chain'); myb = mc.Is_Active__c; } else{ myb = true; } if(Trigger.isAfter){ allMails = new List<Messaging.SingleEmailMessage>(); List<Investigator__share> lastRecShare = new List<Investigator__share>(); String caseownername =''; set<id> sid= new set<id>(); set<id> sid1= new set<id>(); for(Investigator__c inv : trigger.new){ if(inv.Complaint__c!=null){ sid.add(inv.Complaint__c); } if(inv.PCC_Role__c!=null){ sid1.add(inv.PCC_Role__c); } } List<Sub_Directory__c> lstUser = [select id,SF_User__c,SF_User_Email__c,Category__c,SF_User_Name__c from Sub_Directory__c where Master_Directory__c IN:sid1 AND isActive__c = true ]; map<id,Complaint__c> compMap=new map<id,Complaint__c>([select id,Country__c,name,Contact_Person__c,epersonname__c ,Entry_Person_Email__c,Entry_Person_Phone__c,Company_Name__c,Contact_Email__c,Complaint_Type__c,Complaint_Date__c,Other_Comments__c from Complaint__c where id IN: sid]); map<id,List<Sub_Directory__c>> subMap=new map<id,List<Sub_Directory__c>>(); for(Sub_Directory__c sd:lstUser){ if(!subMap.containsKey(sd.Master_Directory__c)){ List<Sub_Directory__c> sub= new List<Sub_Directory__c>(); sub.add(sd); subMap.put(sd.Master_Directory__c,sub); } else{ List<Sub_Directory__c> sub=subMap.get(sd.Master_Directory__c); sub.add(sd); subMap.put(sd.Master_Directory__c,sub); } } for(Investigator__c inv : trigger.new){ if(inv.PCC_Role__c != null && inv.Complaint__c!=null){ for(Sub_Directory__c usr : subMap.get(inv.PCC_Role__c)){ Boolean check = false; if(inv.ownerId != usr.SF_User__c){ Investigator__share recShare = new Investigator__share(); if(usr.Category__c == 'Primary' || usr.Category__c == 'Alternative'){ recShare.AccessLevel = 'Edit'; caseownername = caseownername+', '+usr.SF_User_Name__c; check = true; } if(caseownername.contains(usr.SF_User_Name__c) == false){ check = true; recShare.AccessLevel = 'Read'; } if(check){ recShare.ParentId = inv.Id; recShare.UserOrGroupId = usr.SF_User__c; lastRecShare.add(recShare); } } if(myb){ Messaging.SingleEmailMessage msg = new Messaging.SingleEmailMessage(); string Url = System.URL.getSalesforceBaseUrl().toExternalForm()+'/'+(compMap.get(inv.Complaint__c)).id; msg.settargetObjectId(usr.SF_User__c); msg.setWhatId((compMap.get(inv.Complaint__c)).id); if(usr.Category__c == 'Primary'){ msg.setTemplateId(etmpltPrimary.Id); } if(usr.Category__c == 'Alternative'){ msg.setTemplateId(etmpltAlt.Id); } if(usr.Category__c == 'Manager'){ msg.setTemplateId(etmpltmgr.Id); } msg.setsaveAsActivity(false); allMails.add(msg); } } } } if(lastRecShare.size()>0){ System.debug('lastREcShare====>>>> '+lastRecShare); insert lastRecShare; } if(allMails.size()>0){ Messaging.sendEmail(allMails); } } } //For status updation of complaint according to investigator status update. if(Trigger.isUpdate && Trigger.isAfter){ Boolean chkStk = false; Boolean chkSales = false; Boolean chkNOreviewer = true; Boolean lastChk = false; List<Stakeholder__c> ReviewerRcds = new List<Stakeholder__c>(); List<String> RevwCmpledLst = new List<String>(); set<id> sid= new set<id>(); for(Investigator__c inv : trigger.new){ if(inv.Complaint__c!=null){ sid.add(inv.Complaint__c); } } map<id,Complaint__c> compMap=new map<id,Complaint__c>([select id,Country__c,name,Contact_Person__c,epersonname__c ,Entry_Person_Email__c,Entry_Person_Phone__c,Company_Name__c,Contact_Email__c,Complaint_Type__c,Complaint_Date__c,Other_Comments__c from Complaint__c where id IN: sid]); ReviewerRcds = [select id,name,Review_Completed__c,Complaint_for_Reviewer__c from Stakeholder__c where recordType.DeveloperName='Reviewer' and Complaint_for_Reviewer__c IN: Sid ]; map<id,List<Stakeholder__c>> stakeMap=new map<id,List<Stakeholder__c>>(); for(Stakeholder__c st:ReviewerRcds){ if(!stakeMap.containsKey(st.Complaint_for_Reviewer__c)){ List<Stakeholder__c> stake= new List<Stakeholder__c>(); stake.add(st); stakeMap.put(st.Complaint_for_Reviewer__c,stake); } else{ List<Stakeholder__c> stake=stakeMap.get(st.Complaint_for_Reviewer__c); stake.add(st); stakeMap.put(st.Complaint_for_Reviewer__c,stake); } } for(Investigator__c inv : trigger.new){ if(inv.Investigation_Status__c == 'Investigation Closed' && trigger.oldMap.get(inv.Id).Investigation_Status__c != 'Investigation Closed'){ if(inv.Complaint__c!=null){ if((compMap.get(inv.Complaint__c)).isReviewer__c == true){ system.debug('came inside rwchk truee loop'); chkNOreviewer = false; //To check for reviewer records if they are present. for(Stakeholder__c st :stakeMap.get(inv.Complaint__c)){ if(st.Review_Completed__c == true){ RevwCmpledLst.add(st.Id); } } if(RevwCmpledLst.size() != ReviewerRcds.size()){ (compMap.get(inv.Complaint__c)).Complaint_status__c = 'Investigator / Reviewer'; } if(RevwCmpledLst.size() == ReviewerRcds.size()){ (compMap.get(inv.Complaint__c)).Investigation_Review_completed__c = DateTime.Now(); if((compMap.get(inv.Complaint__c)).isStakeholder__c == true){ chkStk = true; chkSales = false; lastChk = false; } if(chkStk){ (compMap.get(inv.Complaint__c)).Complaint_status__c = 'Stakeholder'; } if(!chkStk){ if((compMap.get(inv.Complaint__c)).isSales__c == true){ chkSales = true; lastChk = false; } else{ lastChk = true; } } if(chkSales){ (compMap.get(inv.Complaint__c)).Complaint_status__c = 'Sales'; } if(lastChk){ (compMap.get(inv.Complaint__c)).Complaint_status__c = 'Complaint Closed'; } } if(chkNOreviewer){ (compMap.get(inv.Complaint__c)).Investigation_Review_completed__c = DateTime.Now(); if((compMap.get(inv.Complaint__c)).isStakeholder__c == true){ chkStk = true; chkSales = false; lastChk = false; } if(chkStk){ (compMap.get(inv.Complaint__c)).Complaint_status__c = 'Stakeholder'; } if(!chkStk){ if((compMap.get(inv.Complaint__c)).isSales__c == true){ chkSales = true; lastChk = false; } else{ lastChk = true; } } if(chkSales){ (compMap.get(inv.Complaint__c)).Complaint_status__c = 'Sales'; } if(lastChk){ (compMap.get(inv.Complaint__c)).Complaint_status__c = 'Complaint Closed'; } } if(inv.Investigation_Status__c == 'Send back to QRC' && trigger.oldMap.get(inv.Id).Investigation_Status__c != 'Send back to QRC'){ (compMap.get(inv.Complaint__c)).Complaint_status__c = 'Assigned to QRC Postmaster'; String Comment = ''; if(inv.Comments__c != null){ if(inv.Comments__c.length() > 255){ Comment = inv.Comments__c.substring(0,254); } else{ Comment = inv.Comments__c; } (compMap.get(inv.Complaint__c)).Iu_send_back2qrc__c = Comment; } (compMap.get(inv.Complaint__c)).IU_Send_back_to_QRC_CHECKBOX__c = true; } if(inv.Investigation_Status__c == 'Investigation / Review' && trigger.oldMap.get(inv.Id).Investigation_Status__c != 'Investigation / Review'){ (compMap.get(inv.Complaint__c)).Complaint_status__c = 'Investigator / Reviewer'; } } } } } update compMap.values(); } //// added for batch sharing if(Trigger.isUpdate && Trigger.isAfter){ List<Investigator__share> lastRecShare = new List<Investigator__share>(); SET<Investigator__share> SetRecShare = new SEt<Investigator__share>(); String caseownername =''; set<id> sid= new set<id>(); for(Investigator__c inv : trigger.new){ if(inv.PCC_Role__c!=null){ sid.add(inv.PCC_Role__c); } } List<Sub_Directory__c> lstUser = [select id,SF_User__c,SF_User_Email__c,Category__c,SF_User_Name__c from Sub_Directory__c where Master_Directory__c IN:sid AND isActive__c = true ]; map<id,List<Sub_Directory__c>> subMap=new map<id,List<Sub_Directory__c>>(); for(Sub_Directory__c sd:lstUser){ if(!subMap.containsKey(sd.Master_Directory__c)){ List<Sub_Directory__c> sub= new List<Sub_Directory__c>(); sub.add(sd); subMap.put(sd.Master_Directory__c,sub); } else{ List<Sub_Directory__c> sub=subMap.get(sd.Master_Directory__c); sub.add(sd); subMap.put(sd.Master_Directory__c,sub); } } for(Investigator__c inv : trigger.new){ if(inv.PCC_Role__c!=null){ for(Sub_Directory__c usr : subMap.get(inv.PCC_Role__c)){ Boolean check = false; if(inv.ownerId != usr.SF_User__c){ Investigator__share recShare = new Investigator__share(); if(usr.Category__c == 'Primary' || usr.Category__c == 'Alternative'){ recShare.AccessLevel = 'Edit'; caseownername = caseownername+', '+usr.SF_User_Name__c; check = true; } if(caseownername.contains(usr.SF_User_Name__c) == false){ check = true; recShare.AccessLevel = 'Read'; } if(check){ recShare.ParentId = inv.Id; recShare.UserOrGroupId = usr.SF_User__c; } SetRecShare.add(recShare); } } } } if(SetRecShare.size() > 0){ lastRecShare.addall(SetRecShare); Upsert lastRecShare; } } }In case you find any other issue please mention.
If you find your Solution then mark this as the best answer.
Thanks and Regards
Suraj Tripathi.
Original code:
if(inv.Investigation_Status__c == 'Send back to QRC' && trigger.oldMap.get(inv.Id).Investigation_Status__c != 'Send back to QRC'){
Complaint__c cmp = new Complaint__c();
cmp.id = inv.Complaint__c;
cmp.Complaint_status__c = 'Assigned to QRC Postmaster';
String Comment = '';
if(inv.Comments__c != null){
if(inv.Comments__c.length() > 255){
Comment = inv.Comments__c.substring(0,254);
}
else{
Comment = inv.Comments__c;
}
cmp.Iu_send_back2qrc__c = Comment;
}
cmp.IU_Send_back_to_QRC_CHECKBOX__c = true;
update cmp;
}
Can you explain in your bulkyfied code how below two lines are handled?
Complaint__c cmp = new Complaint__c();
cmp.id = inv.Complaint__c;
Hi Ashish,
There is no need to create this Complaint__c object because we can get the value of this object through map. Complaint__c is the parent of investigator__c object so we can fetch the Complaint__c record through the investigator__c object.
Is this code working properly or not ?
I have bulkified all the triggers and also batch size is set to 1. Still getting two many soql queries 201 error for 1 trigger.
Please help me to resolve below scenario:
My batch updates more than 700 records of object A, then trigger on A object gets called. now from A trigger update perform on B object,
then B object trigger gets called and too many soql queries:201 error comes up.