+ Start a Discussion
JN22JN22 

System.LimitException: Too many SOQL queries: 101

Hello,

I am running into the error above when trying to run a test class.  The stack trace lists one of my triggers, but when I comment out the DML statements in that trigger (posted below), it comes up with a different trigger in the stack trace.  I am not a seasoned programmer, so I really don't know how to trace back where this problem is coming from.  Can anyone help or give me some guidance?  Thanks.

Trigger in Stack Trace:

trigger AccountTeamChanges on Account(after insert, after update) 
{
   //list to hold new account team members
     List<AccountTeamMember> acctMembers = new List<AccountTeamMember>();
     
     //list to hold new account sharing rules
     List<AccountShare> acctSharingRules = new List<AccountShare>();
     
     //misc
     Set<String> rmMemberAccts = new Set<String>();
     Map<ID, List<ID>> AccountTeamMap = new Map<ID, List<ID>>(); 
    
    List<Account> acc = new list<Account>();
    
     for(Account a : Trigger.new)
     {

        if(Trigger.isInsert)
        {
            //new Account - Client Advisor
            if(a.Client_Advisor__c != null){
            
            AccountTeamMember ca = new AccountTeamMember();
            ca.AccountId = a.Id;
            ca.TeamMemberRole = 'Client Advisor';
            ca.UserId = a.Client_Advisor__c;
            acctMembers.add(ca);
            
            AccountShare caSharingRule = new AccountShare();
            caSharingRule.AccountId = a.Id;
            acctSharingRules.add(caSharingRule);
            }

            //new Account - Market Director
            if(a.Market_Director__c != null){
            
            AccountTeamMember md = new AccountTeamMember();
            md.AccountId = a.Id;
            md.TeamMemberRole = 'Market Director';
            md.UserId = a.Market_Director__c;
            acctMembers.add(md);
            
            AccountShare mdSharingRule = new AccountShare();
            mdSharingRule.AccountId = a.Id;
            acctSharingRules.add(mdSharingRule);
            }

            //new Account - Industry Manager
            if(a.Industry_Manager__c != null){
            
            AccountTeamMember im = new AccountTeamMember();
            im.AccountId = a.Id;
            im.TeamMemberRole = 'Industry Manager';
            im.UserId = a.Industry_Manager__c;
            acctMembers.add(im);
            
            AccountShare imSharingRule = new AccountShare();
            imSharingRule.AccountId = a.Id;
            acctSharingRules.add(imSharingRule);
            }
            system.debug('@@@@@@@@@ ACCTMEMBERS TRIGGER.INSERT: ' + acctMembers);
        }

        //updated Account
        else if(Trigger.isUpdate)
        {
            
            //old Account record
            Account oldAcct = Trigger.oldMap.get(a.Id);
            
            //check to see if the team values have changed and verifies the
            //new values are not null

            system.debug('client advisor '+ a.Client_Advisor__c +'old value '+ oldAcct.Client_Advisor__c);

            if(oldAcct.Client_Advisor__c != a.Client_Advisor__c && a.Client_Advisor__c != null)
            {
                list<AccountTeamMember> oldadvisor = [SELECT Id, UserId, AccountId, TeamMemberRole
                                            FROM AccountTeamMember
                                            WHERE (TeamMemberRole='Client Advisor') AND AccountId =:a.id];
            if (oldadvisor .size() > 0){
            delete oldadvisor ;
			}
            
            //add old Client Advisor to remove list if one exists
                rmMemberAccts.add(oldAcct.Id);
                    if(AccountTeamMap.get(oldAcct.Id) == null){
                        AccountTeamMap.put(oldAcct.Id, new List<ID>{oldAcct.Client_Advisor__c});
                        }
                    else{
                        AccountTeamMap.get(oldAcct.Id).add(oldAcct.Client_Advisor__c);
                        }
                system.debug('Account teammap 1 : '+ AccountTeamMap);
                

                
                //add new processor to account team and update sharing rules
                AccountTeamMember ca = new AccountTeamMember();
                ca.AccountId = a.Id;
                ca.TeamMemberRole = 'Client Advisor';
                ca.UserId = a.Client_Advisor__c;
                acctMembers.add(ca);
                
                AccountShare caSharingRule = new AccountShare();
                caSharingRule.AccountId = a.Id;
                acctSharingRules.add(caSharingRule);
            }
            else if(oldAcct.Client_Advisor__c != a.Client_Advisor__c && a.Client_Advisor__c == null)
            {
                rmMemberAccts.add(a.Id);
                    if(AccountTeamMap.get(oldAcct.Id) == null){
                        AccountTeamMap.put(oldAcct.Id, new List<ID>{oldAcct.Client_Advisor__c});}
                    else{
                AccountTeamMap.get(oldAcct.Id).add(oldAcct.Client_Advisor__c);}

            }

            
            //check to see if the team values have changed and verifies the
            //new values are not null
            if(oldAcct.Market_Director__c != a.Market_Director__c && a.Market_Director__c != null)
            {
                list<AccountTeamMember> oldMD = [SELECT Id, UserId, AccountId, TeamMemberRole
                                            FROM AccountTeamMember
                                            WHERE (TeamMemberRole='Market Director') AND AccountId =:a.id];
            if (oldMD .size() > 0){
            delete oldMD;
			}

            //add old Market Director to remove list if one exists

                rmMemberAccts.add(oldAcct.Id);
                    if(AccountTeamMap.get(oldAcct.Id) == null){
                        AccountTeamMap.put(oldAcct.Id, new List<ID>{oldAcct.Market_Director__c});}
                    else{
                        AccountTeamMap.get(oldAcct.Id).add(oldAcct.Market_Director__c);}
                system.debug('Account teammap 2 : '+ AccountTeamMap);

                
                //add new processor to account team and update sharing rules
                AccountTeamMember md = new AccountTeamMember();
                md.AccountId = a.Id;
                md.TeamMemberRole = 'Market Director';
                md.UserId = a.Market_Director__c;
                acctMembers.add(md);
                
                AccountShare mdSharingRule = new AccountShare();
                mdSharingRule.AccountId = a.Id;
                acctSharingRules.add(mdSharingRule);
            }
            else if(oldAcct.Market_Director__c != a.Market_Director__c && a.Market_Director__c == null)
            {

                rmMemberAccts.add(a.Id);
                    if(AccountTeamMap.get(oldAcct.Id) == null){
                        AccountTeamMap.put(oldAcct.Id, new List<ID>{oldAcct.Market_Director__c});}
                    else{
                AccountTeamMap.get(oldAcct.Id).add(oldAcct.Client_Advisor__c);}

            }

            
            //check to see if the team values have changed and verifies the
            //new values are not null
            if(oldAcct.Industry_Manager__c != a.Industry_Manager__c && a.Industry_Manager__c != null)
            {
                list<AccountTeamMember> oldIM = [SELECT Id, UserId, AccountId, TeamMemberRole
                                            FROM AccountTeamMember
                                            WHERE (TeamMemberRole='Industry Manager') AND AccountId =:a.id];
            if (oldIM .size() > 0){
            delete oldIM;
			}

                //add old Industry Manager to remove list if one exists
                if(oldAcct.Industry_Manager__c != null)

                rmMemberAccts.add(oldAcct.Id);
                    if(AccountTeamMap.get(oldAcct.Id) == null){
                        AccountTeamMap.put(oldAcct.Id, new List<ID>{oldAcct.Industry_Manager__c});}
                    else{
                        AccountTeamMap.get(oldAcct.Id).add(oldAcct.Industry_Manager__c);}
                system.debug('Account teammap 2 : '+ AccountTeamMap);

                
                //add new processor to account team and update sharing rules
                AccountTeamMember im = new AccountTeamMember();
                im.AccountId = a.Id;
                im.TeamMemberRole = 'Industry Manager';
                im.UserId = a.Industry_Manager__c;
                acctMembers.add(im);
                
                AccountShare imSharingRule = new AccountShare();
                imSharingRule.AccountId = a.Id;
                acctSharingRules.add(imSharingRule);
            }
            else if(oldAcct.Industry_Manager__c != a.Industry_Manager__c && a.Industry_Manager__c == null)
            {

                rmMemberAccts.add(a.Id);
                    if(AccountTeamMap.get(oldAcct.Id) == null){
                        AccountTeamMap.put(oldAcct.Id, new List<ID>{oldAcct.Market_Director__c});}
                    else{
                AccountTeamMap.get(oldAcct.Id).add(oldAcct.Client_Advisor__c);}


            }
            system.debug('@@@@@@@@@ ACCTMEMBERS TRIGGER.UPDATE: ' + acctMembers);
        }
        
        //DML OPERATIONS
        //remove team members from account team if any exist
        
        acc.add(a);

    }

        system.debug('-->ACCOUNT MEMBERS: ' + acctMembers);
        //insert the new account team members if any exist
        if(acctMembers.size() > 0)
            upsert acctMembers;
        
        //insert account sharing rules if any exist
        if(acctSharingRules.size() > 0)
            upsert acctSharingRules;
            
        if (acc[0].Client_Advisor__c == null )
        {
            list<AccountTeamMember> atm1 = [SELECT Id, UserId, AccountId, TeamMemberRole
                                            FROM AccountTeamMember
                                            WHERE (TeamMemberRole='Client Advisor') AND AccountId =:acc[0].id];
            if (atm1.size() > 0){
            delete atm1;
			}
        }
        if (acc[0].Industry_Manager__c == null )
        {
            list<AccountTeamMember> atm1 = [SELECT Id, UserId, AccountId, TeamMemberRole
                                            FROM AccountTeamMember
                                            WHERE (TeamMemberRole='Industry Manager') AND AccountId =:acc[0].id];
            if (atm1.size() > 0){
            delete atm1;
			}
        }
        if (acc[0].Market_Director__c == null )
        {
            list<AccountTeamMember> atm1 = [SELECT Id, UserId, AccountId, TeamMemberRole
                                            FROM AccountTeamMember
                                            WHERE (TeamMemberRole='Market Director') AND AccountId =:acc[0].id];
            if (atm1.size() > 0){
            delete atm1;
			}
        }    
}

Test Class:


vodanh0nthvodanh0nth
You can see: https://developer.salesforce.com/page/Apex_Code_Best_Practices (Best Practice #2: Avoid SOQL Queries or DML statements inside FOR Loops)