+ Start a Discussion
JN22JN22 

System.LimitException: Too many SOQL queries: 101

Hello,

I am getting the above error when I run the test below and it seems to be related to the trigger below.  Can anyone help?

Rest of error message:  Trigger.AccountTeamChanges: line 268, column 1

Test Class:
@Istest(SeeAllData=true)
public class TestAccountPlan {

public static testMethod void testMyController1() {    
       
  Account acct1 = TestGeneralAcctCreate.createAcct(0);
    insert acct1;

    Account acct2 = TestStatusAcctCreate.createAcct(0);
  
    insert acct2;

        Profile ProDir2 = [SELECT Id
                          FROM Profile
                          WHERE Name='Client Delivery (Director)'];
               
            User Dir2 = new User(Alias = 'Dir2User',Country='United States',Email='Dir2User@testing.com',EmailEncodingKey='UTF-8', LastName='Testing1a', LanguageLocaleKey='en_US',LocaleSidKey='en_US',ProfileId = ProDir2.Id,TimeZoneSidKey='America/New_York', UserName='Dir2User@testing.com');
        insert Dir2;

        Profile ProTeamMgr2 = [SELECT Id
                              FROM Profile
                              WHERE Name='Client Delivery Team Manager'];
               
            User TM2 = new User(Alias = 'TM2User',Country='United States',Email='TM2User@testing.com',EmailEncodingKey='UTF-8', LastName='Testing2a', LanguageLocaleKey='en_US',LocaleSidKey='en_US',ProfileId = ProTeamMgr2.Id,TimeZoneSidKey='America/New_York', UserName='TM2User@testing.com');
        insert TM2;

        Profile ProCSM2 = [SELECT Id
                          FROM Profile
                          WHERE Name='Client Success Manager'];
               
            User CSM2 = new User(Alias = 'CSM2User',Country='United States',Email='CSM2User@testing.com',EmailEncodingKey='UTF-8', LastName='Testing3a', LanguageLocaleKey='en_US',LocaleSidKey='en_US',ProfileId = ProCSM2.Id,TimeZoneSidKey='America/New_York', UserName='CSM2User@testing.com');
        insert CSM2;

        Account acct2a = [SELECT id,Market_Director__c,Industry_Manager__c,Client_Advisor__c
                          FROM Account
                          WHERE id =: acct2.Id];
               acct2a.Market_Director__c = Dir2.Id;
               acct2a.Industry_Manager__c = TM2.Id;
               acct2a.Client_Advisor__c = CSM2.Id;
        
    Account acct3 = TestGeneralAcctCreate.createAcct(0);
    insert acct3;

    Account acct4 = TestGeneralAcctCreate.createAcct(0);
    insert acct4;

    Account acct5 = TestGeneralAcctCreate.createAcct(0);
    insert acct5;

//    Account acct6 = TestStatusAcctCreate.createAcct(0);
//    insert acct6;

    Opportunity opp1 = TestOppCreate3.createOpp(acct1.Id);
    insert opp1;

    Contract_Summary__c contsumm1 = TestContractCreate.createContSumm(opp1.Id);
    insert contsumm1;
  
        Contract_Summary__c contSumm2 = [SELECT id
                                         FROM Contract_Summary__c
                                         WHERE id =: contSumm1.id];
  
    Client_Status_Scorecard__c cssc1 = TestStatusSCCreate.createCSSC1(acct1.Id);
    insert cssc1;
    
        Client_Status_Scorecard__c cssc1a = [SELECT id
                                           FROM Client_Status_Scorecard__c
                                           WHERE id =: cssc1.id];
    
    Client_Status_Scorecard__c cssc2 = TestStatusSCCreate.createCSSC2(acct2.Id);
    insert cssc2;
    
    Client_Status_Scorecard__c cssc3 = TestStatusSCCreate.createCSSC3(acct3.Id);
    insert cssc3;
    
    Client_Status_Scorecard__c cssc4 = TestStatusSCCreate.createCSSC4(acct4.Id);
    insert cssc4;
    
    Client_Status_Scorecard__c cssc5 = TestStatusSCCreate.createCSSC5(acct5.Id);
    insert cssc5;
    
//    Client_Status_Scorecard__c cssc6 = TestStatusSCCreate.createCSSC6(acct6.Id);
//    insert cssc6;
    
    Account_Plan__c plan1 = TestAcctPlanCreate.createAP1(acct1.Id);

    ApexPages.StandardController AcctPlan = new ApexPages.standardController(acct1);
    AcctPlanController APControl1 = new AcctPlanController(AcctPlan);
  //  APControl1.ap.add(plan1);
    APControl1.ap2 = plan1;
    APControl1.testsf();
    APControl1.save();
    
    Test.startTest();
    update acct1;
    update acct2;
    update acct2a;
    update acct3;
    update acct4;
    update acct5;
//    update acct6;
    update opp1;
    update contsumm1;
    delete contsumm2;
    update cssc1;
//    update cssc2;
    update cssc3;
    update cssc4;
    update cssc5;
//    update cssc6;
    delete cssc1a;
    Test.stopTest();
  
}

}


Trigger in error:
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>();
    
     //iterate through records to find update processor values
     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;
            caSharingRule.OpportunityAccessLevel = 'Read';
            caSharingRule.CaseAccessLevel = 'Read';
            caSharingRule.AccountAccessLevel = 'Edit';
            caSharingRule.UserOrGroupId = a.Client_Advisor__c;
            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;
            mdSharingRule.OpportunityAccessLevel = 'Read';
            mdSharingRule.CaseAccessLevel = 'Read';
            mdSharingRule.AccountAccessLevel = 'Edit';
            mdSharingRule.UserOrGroupId = a.Market_Director__c;
            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;
            imSharingRule.OpportunityAccessLevel = 'Read';
            imSharingRule.CaseAccessLevel = 'Read';
            imSharingRule.AccountAccessLevel = 'Edit';
            imSharingRule.UserOrGroupId = a.Industry_Manager__c;
            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)
            {
                //Sample code added by SFDC support
                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;
                caSharingRule.OpportunityAccessLevel = 'Read';
                caSharingRule.CaseAccessLevel = 'Read';
                caSharingRule.AccountAccessLevel = 'Edit';
                caSharingRule.UserOrGroupId = a.Client_Advisor__c;
                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)
            {
                //Sample code added by SFDC support
                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;
                mdSharingRule.OpportunityAccessLevel = 'Read';
                mdSharingRule.CaseAccessLevel = 'Read';
                mdSharingRule.AccountAccessLevel = 'Edit';
                mdSharingRule.UserOrGroupId = a.Market_Director__c;
                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)
            {
                //Sample code added by SFDC support
                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;
                imSharingRule.OpportunityAccessLevel = 'Read';
                imSharingRule.CaseAccessLevel = 'Read';
                imSharingRule.AccountAccessLevel = 'Edit';
                imSharingRule.UserOrGroupId = a.Industry_Manager__c;
                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; //LINE 100
        
        //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;}
        }    
}


Vinnie BVinnie B
I don't have time to fully comprehend your code but the error message is pretty self-explanatory.  You may only run 100 queries in a given file.

From what I can tell your code isn't 'bulkified'.  Line 16 ("for(Account a : Trigger.new)") is where you're going through each individual account to be processed in that trigger.  If there are five objects being added, then the number of queries will be five times the number of queries in that for loop.  Given that a batch process will often do 200 records at a time, you want to avoid any queries in that loop at all.

JN22JN22
Thanks.  I assume I need to create a list before that FOR loop?