Apex Code Development You need to sign in to do that
Don't have an account?
JN22 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:
Trigger in error:
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;}
}
}
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.