function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
BobBob 

Too many SOQL queries: 101 Trigger_CasePrimaryContact: line 7, column 1

I am having a issue with my trigger. When i try to do a mass update on account records I get this error message.  Too many SOQL queries: 101 Trigger_CasePrimaryContact: line 7, column 1 

my trigger code is below, any help with issue would be greatly appreciated. 
  
trigger Trigger_CasePrimaryContact on Account (before insert, before update) {


   for (Account a : Trigger.new) {
   
       AccountContactRole[] contactRoleArray =
       [select ContactID, Role from AccountContactRole where AccountId = :a.id AND Role='Asset Management Service Contact' ];
       if (contactRoleArray.size() > 0) {
         
        
           a.Account_Case_Contact__c = contactRoleArray[0].ContactID;
          
       }else{
      
           // IF NO CONTACT ROLES EXIST RETURN NULL...
           a.Account_Case_Contact__c = null;
         
       }
   }
    


}
Best Answer chosen by Bob
Kelly KKelly K

Take that query out of the for loop. With Apex, you should always plan to do DMLs or Queries outside of a for loop. I know they teach it to you that way in courses, but it's a very bad practice. 

Also, since you're dealing with a list on specific records, try getting used to working with Maps.

I would rewrite something like this:

trigger Trigger_CasePrimaryContact on Account (before insert, before update) {
	
	List<AccountContactRole> contactRoleArray = [SELECT AccountId, ContactId, Role FROM AccountContactRole WHERE AccountId IN trigger.new AND Role='Asset Management Service Contact'];
	Map<Id, AccountContactRole> accountToContactRoleMap = new Map<Id,AccountContactRole>();
	
	for(AccountContactRole contactRole : contactRoleArray) {
		accountToContactRoleMap.put(contactRole.AccountId, contactRole);
	}

   for (Account a : Trigger.new) {
          if (accountToContactRoleMap.get(a.Id) != null) {
           a.Account_Case_Contact__c = accountToContactRoleMap.get(a.Id).ContactID;
          
       } else{      
           // IF NO CONTACT ROLES EXIST RETURN NULL...
           a.Account_Case_Contact__c = null;         
       }
   }
}
 

I would caution though, that there could be more than one contact role that fits the asset management service contact, but the way this is written, it will pull the last one in the list of the query.
 

All Answers

RainnyCloudyRainnyCloudy
Try applying the trigger and Bulk requests best practices from the Apex developer guide:

https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_triggers_bestpract.htm
Kelly KKelly K

Take that query out of the for loop. With Apex, you should always plan to do DMLs or Queries outside of a for loop. I know they teach it to you that way in courses, but it's a very bad practice. 

Also, since you're dealing with a list on specific records, try getting used to working with Maps.

I would rewrite something like this:

trigger Trigger_CasePrimaryContact on Account (before insert, before update) {
	
	List<AccountContactRole> contactRoleArray = [SELECT AccountId, ContactId, Role FROM AccountContactRole WHERE AccountId IN trigger.new AND Role='Asset Management Service Contact'];
	Map<Id, AccountContactRole> accountToContactRoleMap = new Map<Id,AccountContactRole>();
	
	for(AccountContactRole contactRole : contactRoleArray) {
		accountToContactRoleMap.put(contactRole.AccountId, contactRole);
	}

   for (Account a : Trigger.new) {
          if (accountToContactRoleMap.get(a.Id) != null) {
           a.Account_Case_Contact__c = accountToContactRoleMap.get(a.Id).ContactID;
          
       } else{      
           // IF NO CONTACT ROLES EXIST RETURN NULL...
           a.Account_Case_Contact__c = null;         
       }
   }
}
 

I would caution though, that there could be more than one contact role that fits the asset management service contact, but the way this is written, it will pull the last one in the list of the query.
 

This was selected as the best answer
Kelly KKelly K
The other thing I would caution - is that query will run on any kind of created account record or updated record (system admin mass updates would cause it to run). When you start having a lot of code, you'll want to move this functionality to an apex class so you can better control when it fires that query and on what conditions it should update.
BobBob
KKorynta Thank you so much for helping with this code adjustment. I final code is as follows.
  
trigger Trigger_CasePrimaryContact on Account (before insert, before update) {
    
    List<AccountContactRole> contactRoleArray = [SELECT AccountId, ContactId, Role FROM AccountContactRole WHERE AccountId IN :Trigger.new AND Role='Asset Management Service Contact'];
    Map<Id, AccountContactRole> accountToContactRoleMap = new Map<Id,AccountContactRole>();
    
    for(AccountContactRole contactRole : contactRoleArray) {
        accountToContactRoleMap.put(contactRole.AccountId, contactRole);
    }

   for (Account a : Trigger.new) {
          if (accountToContactRoleMap.get(a.Id) != null) {
           a.Account_Case_Contact__c = accountToContactRoleMap.get(a.Id).ContactID;
          
       } else{      
           // IF NO CONTACT ROLES EXIST RETURN NULL...
           a.Account_Case_Contact__c = null;         
       }
   }
}
I changed the AccountId IN trigger.new to  
IN :Trigger.new
because the trigger was failing. I will look into creating a apex class to handle this soon, but thanks again for your help.  
 
Kelly KKelly K
Great! Glad I could help.