function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
sourabh shresthasourabh shrestha 

too many sql queries

Hi, I am having one trigger " updatePhysicianTerritory". The trigger works fine in production without error. But in QA sandbox ,it showing error "

Apex script unhandled trigger exception by user/organization: 005E0000002r7g3/00Dc0000001JfAx Source organization: 00DE0000000e7EN (null)

updatePhysicianTerritory: System.LimitException: Too many SOQL queries: 101 "

 

This error doesnt come regularly.  But once or twice in a week, it will come. Below is the code for trigger. 

Any ideas on how to resolve it.

 

trigger updatePhysicianTerritory on Account (after update)
{

List<account> accPhysLst=new List<account>();

String bfreUpdateTerr{get;set;}
String aftrUpdateTerr {get;set;}
String newTerrOfAccount {get;set;}
List<Affiliation__c> lstAff = new List<Affiliation__c>();

List<String> phyIds = new List<String>();

try{


for(Account a:trigger.new) // For every Account that has been updated
{

Account beforeUpdate = System.Trigger.oldMap.get(a.Id);
bfreUpdateTerr = beforeUpdate.territory__c;
newTerrOfAccount=a.Territory__c;
if(bfreUpdateTerr!=newTerrOfAccount){
if(a.IsPersonAccount == false)
{ //Get all affliations related to the corresponding business account
for(Affiliation__c aff:[Select physician1__c,physician1__r.territory__c,hospital__c,Hospital__r.territory__c from Affiliation__c where hospital__c=:a.id])
{
if(aff.physician1__c!=null)
{
aftrUpdateTerr = aff.Hospital__r.territory__c;
phyIds.add(aff.physician1__c); // The list contains the physician ids of the corresponding affliation

}
}
}
}
}
if(phyIds!=null && phyIds.size()>0) // Find all the affliations of the physician
{
for(Account phys:[Select id,territory__c from Account where id IN:phyIds])
{
String territory ='';
for(Affiliation__c aff:[Select physician1__c,physician1__r.territory__c,hospital__c,Hospital__r.territory__c from Affiliation__c where physician1__c=:phys.id])
{
if(phys.id == aff.physician1__c)
{
if(territory!='' && territory!=null && aff.Hospital__r.territory__c!=null && !(territory.contains(aff.Hospital__r.territory__c)))
territory = territory+';'+aff.Hospital__r.territory__c;
else if(aff.Hospital__r.territory__c == null)
territory = territory;
else
{
if(phys.territory__c !=null && !(phys.territory__c.contains(aff.Hospital__r.territory__c)))
territory = aff.Hospital__r.territory__c;
else
territory = aff.Hospital__r.territory__c;

}
}
}
phys.territory__c = territory ;
accPhysLst.add(phys);

}
update accPhysLst;
}

}
catch(Exception e)
{
system.debug('Exception in person acc territory update'+e.getMessage());
}
}

 

Best Answer chosen by Admin (Salesforce Developers) 
Dhaval PanchalDhaval Panchal

You have written queries in loop. Remove queries from all loops and use List/Map.

 

see red lines in your code.

 

trigger updatePhysicianTerritory on Account (after update)
{

    List<account> accPhysLst=new List<account>();
    String bfreUpdateTerr{get;set;}
    String aftrUpdateTerr {get;set;}
    String newTerrOfAccount {get;set;}
    List<Affiliation__c> lstAff = new List<Affiliation__c>();
    List<String> phyIds = new List<String>();
    try{
        for(Account a:trigger.new) // For every Account that has been updated
        {
            Account beforeUpdate = System.Trigger.oldMap.get(a.Id);
            bfreUpdateTerr = beforeUpdate.territory__c;
            newTerrOfAccount=a.Territory__c;
            if(bfreUpdateTerr!=newTerrOfAccount){
                if(a.IsPersonAccount == false)
                { //Get all affliations related to the corresponding business account
                    for(Affiliation__c aff:[Select physician1__c,physician1__r.territory__c,hospital__c,Hospital__r.territory__c from Affiliation__c where hospital__c=:a.id])
                    {
                        if(aff.physician1__c!=null)
                        {
                            aftrUpdateTerr = aff.Hospital__r.territory__c;
                            phyIds.add(aff.physician1__c); // The list contains the physician ids of the corresponding affliation
                        }
                    }
                }
            }
        }
        if(phyIds!=null && phyIds.size()>0) // Find all the affliations of the physician
        {
            for(Account phys:[Select id,territory__c from Account where id IN:phyIds])
            {
                String territory ='';
                for(Affiliation__c aff:[Select physician1__c,physician1__r.territory__c,hospital__c,Hospital__r.territory__c from Affiliation__c where physician1__c=:phys.id])
                {
                    if(phys.id == aff.physician1__c)
                    {
                        if(territory!='' && territory!=null && aff.Hospital__r.territory__c!=null && !(territory.contains(aff.Hospital__r.territory__c)))
                            territory = territory+';'+aff.Hospital__r.territory__c;
                        else if(aff.Hospital__r.territory__c == null)
                            territory = territory;
                        else
                        {
                            if(phys.territory__c !=null && !(phys.territory__c.contains(aff.Hospital__r.territory__c)))
                                territory = aff.Hospital__r.territory__c;
                            else
                                territory = aff.Hospital__r.territory__c;
                        }
                    }
                }
                phys.territory__c = territory ;
                accPhysLst.add(phys);
            }
            update accPhysLst;
        }
    }
    catch(Exception e)
    {
        system.debug('Exception in person acc territory update'+e.getMessage());
    }
}

All Answers

Dhaval PanchalDhaval Panchal

You have written queries in loop. Remove queries from all loops and use List/Map.

 

see red lines in your code.

 

trigger updatePhysicianTerritory on Account (after update)
{

    List<account> accPhysLst=new List<account>();
    String bfreUpdateTerr{get;set;}
    String aftrUpdateTerr {get;set;}
    String newTerrOfAccount {get;set;}
    List<Affiliation__c> lstAff = new List<Affiliation__c>();
    List<String> phyIds = new List<String>();
    try{
        for(Account a:trigger.new) // For every Account that has been updated
        {
            Account beforeUpdate = System.Trigger.oldMap.get(a.Id);
            bfreUpdateTerr = beforeUpdate.territory__c;
            newTerrOfAccount=a.Territory__c;
            if(bfreUpdateTerr!=newTerrOfAccount){
                if(a.IsPersonAccount == false)
                { //Get all affliations related to the corresponding business account
                    for(Affiliation__c aff:[Select physician1__c,physician1__r.territory__c,hospital__c,Hospital__r.territory__c from Affiliation__c where hospital__c=:a.id])
                    {
                        if(aff.physician1__c!=null)
                        {
                            aftrUpdateTerr = aff.Hospital__r.territory__c;
                            phyIds.add(aff.physician1__c); // The list contains the physician ids of the corresponding affliation
                        }
                    }
                }
            }
        }
        if(phyIds!=null && phyIds.size()>0) // Find all the affliations of the physician
        {
            for(Account phys:[Select id,territory__c from Account where id IN:phyIds])
            {
                String territory ='';
                for(Affiliation__c aff:[Select physician1__c,physician1__r.territory__c,hospital__c,Hospital__r.territory__c from Affiliation__c where physician1__c=:phys.id])
                {
                    if(phys.id == aff.physician1__c)
                    {
                        if(territory!='' && territory!=null && aff.Hospital__r.territory__c!=null && !(territory.contains(aff.Hospital__r.territory__c)))
                            territory = territory+';'+aff.Hospital__r.territory__c;
                        else if(aff.Hospital__r.territory__c == null)
                            territory = territory;
                        else
                        {
                            if(phys.territory__c !=null && !(phys.territory__c.contains(aff.Hospital__r.territory__c)))
                                territory = aff.Hospital__r.territory__c;
                            else
                                territory = aff.Hospital__r.territory__c;
                        }
                    }
                }
                phys.territory__c = territory ;
                accPhysLst.add(phys);
            }
            update accPhysLst;
        }
    }
    catch(Exception e)
    {
        system.debug('Exception in person acc territory update'+e.getMessage());
    }
}

This was selected as the best answer
arag73877arag73877

is corrcet store the query in a list and the iterate through it you will not get the error.

sourabh shresthasourabh shrestha
Can you please provide the modifed code . I am new to this . It would be
really helpful for me.

--
Regards
Sourabh Shrestha
Dhaval PanchalDhaval Panchal

Hi,

 

Use below code. I am not able to compile this code because I donot have objects which you are using in trigger. So compile it and also check your business logic.

 

trigger updatePhysicianTerritory on Account (after update)
{

    List<account> accPhysLst=new List<account>();
    Set<ID> phyIds = new Set<ID>();
	Set<ID> setAccountId = new Set<ID>();
	List<Affiliation__c> lstAffiliation = new List<Affiliation__c>();
    try{		
		for(Account a:trigger,new){
			if(a.territory__c != Trigger.oldMap.get(a.Id).territory__c){
				setAccountId.add(a.Id);
			}
		}
		
		if(setAccountId.size()>0){
			lstAffiliation = [Select physician1__c,physician1__r.territory__c,hospital__c,Hospital__r.territory__c from Affiliation__c where hospital__c <> null And hospital__c In:setAccountId];
			if(lstAffiliation.size()>0){
				for(Affiliation__c aff:lstAffiliation){
					phyIds.add(aff.physician1__c); // The list contains the physician ids of the corresponding affliation
				}
			}
		}
		if(phyIds.size()>0){// Find all the affliations of the physician
			List<Account> lstPhys = [Select id,territory__c from Account where id IN:phyIds];
			List<Affiliation__c> lstAffPhy = [Select physician1__c,physician1__r.territory__c,hospital__c,Hospital__r.territory__c from Affiliation__c where physician1__c=:phys.id];
			if(lstPhys.size()>0 && lstAffPhy.size()>0){
				for(Account accPhy:lstPhys){
					for(Affiliation__c affPhy:lstAffPhy){
						if(accPhy.Id == affPhy.physician1__c){
							String territory ='';
							if(territory!='' && territory!=null && affPhy.Hospital__r.territory__c!=null && !(territory.contains(affPhy.Hospital__r.territory__c)))
								territory = territory+';'+affPhy.Hospital__r.territory__c;
							else if(affPhy.Hospital__r.territory__c == null)
								territory = territory;
							else
							{
								if(phys.territory__c !=null && !(phys.territory__c.contains(affPhy.Hospital__r.territory__c)))
									territory = affPhy.Hospital__r.territory__c;
								else
									territory = affPhy.Hospital__r.territory__c;
							}
							phys.territory__c = territory ;
							accPhysLst.add(phys);
						}
					}
				}
			}			
		}
		if(accPhysLst.size()>0) update accPhysLst;
    }
    catch(Exception e)
    {
        system.debug('Exception in person acc territory update'+e.getMessage());
    }
}