+ Start a Discussion
Chinna SfdcChinna Sfdc 

Too many SOQL queries- 101 :Trigger

Hi All,

Facing the issues like getting a System.LimitException: Too many SOQL queries: 101 when trying to update my opportunities. Can someone please help me on this code, so we don't run into this error anymore and how to avoid it again with future Triggers.
Please check my below Trigger and class:
Trigger:
Trigger AccountPartnerType on Account (after update){
    AccountPartnerTypecls acpt = new AccountPartnerTypecls();
    if(AccountPartnerTypecls.Aptcalc)
    acpt.AccountPartnerTypecalc(Trigger.new,trigger.newmap,trigger.oldmap);
}
Class:
public class AccountPartnerTypecls{
                public static Boolean Aptcalc = true;
                public static Boolean Aptcalc1 = false; 
                public static Boolean Aptcalc2 = false;  
                public void AccountPartnerTypecalc(List<Account> aclist,map<id,Account> newmap,map<id,Account> oldmap){
                                set<id> accidset = new set<id>(); 
                                set<string> ulprntidset = new set<string>();   
                                string dulprnt;
                                map<string,List<Account>>  accmap = new  map<string,List<Account>>();
                                system.debug('*********'+aclist.size());           
                                for(Account acc: aclist){
                                                accidset.add(acc.id);
                                                // if(acc.GE_Global_Ultimate_Reporting_Account__c!=Null)  //GE_Digital_Ultimate_Parent_ID__c
                                                //Ultimate_Parent_ID__c
                                                ulprntidset.add(acc.GE_Digital_Ultimate_Parent_ID__c);
                                                dulprnt= acc.GE_Digital_Ultimate_Parent_ID__c;
                                                system.debug('*********'+dulprnt);           
                                }
                                system.debug('*********'+dulprnt); 
                                List<Account> hiracclist = new List<Account>();
                                List<Account> aclist1 = [select id, name,Partner_Status__c,Is_Partner__c,Account_Type__c,Parentid,GE_Digital_Ultimate_Parent_ID__c from Account where  GE_Digital_Ultimate_Parent_ID__c  =:dulprnt  AND Parentid!=null AND id NOT IN:accidset ];
                                system.debug('*********'+aclist1.size()); 
                                for(Account a : aclist1 ){
                                                if(accmap.containskey(a.GE_Digital_Ultimate_Parent_ID__c))
                                                accmap.get(a.GE_Digital_Ultimate_Parent_ID__c).add(a);
                                                else{
                                                                List<Account> alist = new List<Account>();
                                                                alist.add(a);
                                                                accmap.put(a.GE_Digital_Ultimate_Parent_ID__c,alist);
                                                }
                                }
                                for(Account acc: [select id, name,Partner_Status__c,Is_Partner__c,Account_Type__c,Parentid,GE_Digital_Ultimate_Parent_ID__c from Account where ID IN :accidset ]){
                                                string acctype='';
                                                if(accmap.get(acc.GE_Digital_Ultimate_Parent_ID__c)!=null){
                                                                /* if(accmap.get(acc.GE_Digital_Ultimate_Parent_ID__c).contains(acc)){
                                                                accmap.get(acc.GE_Digital_Ultimate_Parent_ID__c).set(0, acc);
                                                                accmap.get(acc.GE_Digital_Ultimate_Parent_ID__c).remove(0);
                                                                }*/
                                                                for(Account acchr : accmap.get(acc.GE_Digital_Ultimate_Parent_ID__c)){
                                                                                //acchr.Is_Partner__c= acc.Is_Partner__c;
                                                                                if(acc.Is_Partner__c)
                                                                                acchr.Account_Type__c = 'Partner';
                                                                                else
                                                                                acchr.Account_Type__c= '';
                                                                                hiracclist.add(acchr);
                                                                }
                                                                if(acc.Is_Partner__c){
                                                                                if(acc.parentid!=null)
                                                                                Aptcalc1 =true;
                                                                                acc.Account_Type__c= 'Partner';
                                                                                hiracclist.add(acc);
                                                                }
                                                                else{
                                                                                if(acc.parentid!=null)
                                                                                Aptcalc2 =true;
                                                                                acc.Account_Type__c= '';
                                                                                hiracclist.add(acc);
                                                                }
                                                }
                                                else{
                                                                if(acc.Is_Partner__c){
                                                                                acc.Account_Type__c= 'Partner';
                                                                                hiracclist.add(acc);
                                                                }
                                                                else{
                                                                                acc.Account_Type__c= ' ';
                                                                                hiracclist.add(acc);
                                                                }
                                                }
                                }
                                Account a=  [select id, name,Partner_Status__c,Is_Partner__c,Account_Type__c,Parentid,GE_Digital_Ultimate_Parent_ID__c from Account where ID  =:dulprnt];
                                if(Aptcalc1){
                                                a.Account_Type__c= 'Partner';
                                                hiracclist.add(a);
                                }
                                else if(Aptcalc2){
                                                a.Account_Type__c= '';
                                                hiracclist.add(a);
                                }
                                Aptcalc = false;         
                                update  hiracclist;
                }
}
 
Thanks
 
Nishad KNishad K
Hi,

Here you have a  logical problem that is you are trying to update same object Account  so  the trigger  will execute  recursively. you have to prevent recursive trigger execution 

Change your trigger to: 
 
Trigger AccountPartnerType on Account (after update){
    if(updation.isfutureupdate!=true){
           AccountPartnerTypecls acpt = new AccountPartnerTypecls();
           if(AccountPartnerTypecls.Aptcalc)
           acpt.AccountPartnerTypecalc(Trigger.new,trigger.newmap,trigger.oldmap);
     }
}


Add the below class to prevent recursive execution of trigger 
public class updation
{
public static boolean isfutureupdate=false;
}


Here you prevent future updates so it will not hit governer limit  

Regards,
 
Chinna SfdcChinna Sfdc
Hi Nishad,

Thanks your valid reply.So can i create a new class ( prevent recursive execution of trigger)? is there any chance to adding my current class"AccountPartnertypecls".

Thanks
Nishad KNishad K
Use the below code:

Trigger:
 
Trigger AccountPartnerType on Account (after update){
    if(updation.isfutureupdate!=true){
           AccountPartnerTypecls acpt = new AccountPartnerTypecls();
           if(AccountPartnerTypecls.Aptcalc)
           acpt.AccountPartnerTypecalc(Trigger.new,trigger.newmap,trigger.oldmap);
           updation.isfutureupdate=true;
     }
}
class:
1.
public class AccountPartnerTypecls{
                public static Boolean Aptcalc = true;
                public static Boolean Aptcalc1 = false; 
                public static Boolean Aptcalc2 = false;  
                public void AccountPartnerTypecalc(List<Account> aclist,map<id,Account> newmap,map<id,Account> oldmap){
                                set<id> accidset = new set<id>(); 
                                set<string> ulprntidset = new set<string>();   
                                string dulprnt;
                                map<string,List<Account>>  accmap = new  map<string,List<Account>>();
                                system.debug('*********'+aclist.size());           
                                for(Account acc: aclist){
                                                accidset.add(acc.id);
                                                // if(acc.GE_Global_Ultimate_Reporting_Account__c!=Null)  //GE_Digital_Ultimate_Parent_ID__c
                                                //Ultimate_Parent_ID__c
                                                ulprntidset.add(acc.GE_Digital_Ultimate_Parent_ID__c);
                                                dulprnt= acc.GE_Digital_Ultimate_Parent_ID__c;
                                                system.debug('*********'+dulprnt);           
                                }
                                system.debug('*********'+dulprnt); 
                                List<Account> hiracclist = new List<Account>();
                                List<Account> aclist1 = [select id, name,Partner_Status__c,Is_Partner__c,Account_Type__c,Parentid,GE_Digital_Ultimate_Parent_ID__c from Account where  GE_Digital_Ultimate_Parent_ID__c  =:dulprnt  AND Parentid!=null AND id NOT IN:accidset ];
                                system.debug('*********'+aclist1.size()); 
                                for(Account a : aclist1 ){
                                                if(accmap.containskey(a.GE_Digital_Ultimate_Parent_ID__c))
                                                accmap.get(a.GE_Digital_Ultimate_Parent_ID__c).add(a);
                                                else{
                                                                List<Account> alist = new List<Account>();
                                                                alist.add(a);
                                                                accmap.put(a.GE_Digital_Ultimate_Parent_ID__c,alist);
                                                }
                                }
                                for(Account acc: [select id, name,Partner_Status__c,Is_Partner__c,Account_Type__c,Parentid,GE_Digital_Ultimate_Parent_ID__c from Account where ID IN :accidset ]){
                                                string acctype='';
                                                if(accmap.get(acc.GE_Digital_Ultimate_Parent_ID__c)!=null){
                                                                /* if(accmap.get(acc.GE_Digital_Ultimate_Parent_ID__c).contains(acc)){
                                                                accmap.get(acc.GE_Digital_Ultimate_Parent_ID__c).set(0, acc);
                                                                accmap.get(acc.GE_Digital_Ultimate_Parent_ID__c).remove(0);
                                                                }*/
                                                                for(Account acchr : accmap.get(acc.GE_Digital_Ultimate_Parent_ID__c)){
                                                                                //acchr.Is_Partner__c= acc.Is_Partner__c;
                                                                                if(acc.Is_Partner__c)
                                                                                acchr.Account_Type__c = 'Partner';
                                                                                else
                                                                                acchr.Account_Type__c= '';
                                                                                hiracclist.add(acchr);
                                                                }
                                                                if(acc.Is_Partner__c){
                                                                                if(acc.parentid!=null)
                                                                                Aptcalc1 =true;
                                                                                acc.Account_Type__c= 'Partner';
                                                                                hiracclist.add(acc);
                                                                }
                                                                else{
                                                                                if(acc.parentid!=null)
                                                                                Aptcalc2 =true;
                                                                                acc.Account_Type__c= '';
                                                                                hiracclist.add(acc);
                                                                }
                                                }
                                                else{
                                                                if(acc.Is_Partner__c){
                                                                                acc.Account_Type__c= 'Partner';
                                                                                hiracclist.add(acc);
                                                                }
                                                                else{
                                                                                acc.Account_Type__c= ' ';
                                                                                hiracclist.add(acc);
                                                                }
                                                }
                                }
                                Account a=  [select id, name,Partner_Status__c,Is_Partner__c,Account_Type__c,Parentid,GE_Digital_Ultimate_Parent_ID__c from Account where ID  =:dulprnt];
                                if(Aptcalc1){
                                                a.Account_Type__c= 'Partner';
                                                hiracclist.add(a);
                                }
                                else if(Aptcalc2){
                                                a.Account_Type__c= '';
                                                hiracclist.add(a);
                                }
                                Aptcalc = false;         
                                update  hiracclist;
                }
}

2.
 
public class updation
{
public static boolean isfutureupdate=false;
}

Let me know the outcomes,
 
Nishad KNishad K
Okey,
Trigger AccountPartnerType on Account (after update){
    if(updation.isfutureupdate!=true){
           AccountPartnerTypecls acpt = new AccountPartnerTypecls();
           if(AccountPartnerTypecls.Aptcalc)
           acpt.AccountPartnerTypecalc(Trigger.new,trigger.newmap,trigger.oldmap);
           updation.isfutureupdate=true;
     }
}
public class updation
{
public static boolean isfutureupdate=false;
}
I add "updation.isfutureupdate=true;" after "acpt.AccountPartnerTypecalc(Trigger.new,trigger.newmap,trigger.oldmap); "
Are u try to update way data loader bulk updation?