function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
muhammadb1.3970135761364219E12muhammadb1.3970135761364219E12 

Account Trigger that update all contacts - Too many SOQL queries: 101

Hi,

I have a trigger that runs after updating any account and it actually just updates a field (Relationship_category_r__c) in all the related contacts after few conditions.

Condition1: If we update the account type to "Member"
Condition2: If the contact doesn't have "Member" already in the (Relationship_category_r__c) field 
ACTION: Update the contact Relationship_Category_r__c field to "Member - staff"

Condition2: If we update the account type to "Member - past"
ACTION: Update all the contacts Relationship_Category_r__c field to "Member - past"

The trigger works absolutely find when the account has less than 25 to 50 contacts but it generates an error when we have an account more than 55 or so contacts

ERROR: Apex trigger UpdateAllContacts caused an unexpected exception, contact your administrator: UpdateAllContacts: System.LimitException: Too many SOQL queries: 101

======================================= TRIGGER ==============================

trigger UpdateAllContacts on Account (after update) {

    for(Account acc : Trigger.New){  
        List<Contact> listCon = [Select id, Relationship_Category_r__c from Contact where AccountId =: acc.id];
        for(Contact con : listCon)
            {
                if (acc.Type=='Member'){
                    if(con.Relationship_Category_r__c != 'Member'){
                        con.Relationship_Category_r__c = 'Member - staff';
                    }
                }
                else if (acc.Type=='Member - past'){
                    con.Relationship_Category_r__c = 'Member - past';
                }
            }
      try {
          update listCon;
          }
        catch (DmlException e) {}
    }
}

Any help will be greatly appreciated

Thanks
Best Answer chosen by muhammadb1.3970135761364219E12
muhammadb1.3970135761364219E12muhammadb1.3970135761364219E12
Instead of trigger, someone suggested to create a formula field which has actually resolved the issue

See the answer below

IF(ISPICKVAL(Account.Account_Type__c,'Member'), 'Member-Staff',ISPICKVAL(Account.Account_Type__c,'Member-past'),'Member-Past','Default value').

All Answers

Ramu_SFDCRamu_SFDC
The error is due to SOQL query within for loop List<Contact> listCon = [Select id, Relationship_Category_r__c from Contact where AccountId =: acc.id];

You need to bulkify this code by using Maps, Sets . Read through this blog post/tutorial on how to maintain the code within governor limits

http://www.sfdc99.com/2013/11/17/what-are-governor-limits/
muhammadb1.3970135761364219E12muhammadb1.3970135761364219E12
Dear Ramu,

Thanks for your reply, however I am unable to find anything regarding maps sets on the blog. Can you please tell me how can I remove the list from the loop as I won't have account ID before the loop ???

Ramu_SFDCRamu_SFDC
Hi Muhammadb1,

Here you go mate !! I tried to make use of your fields as far as possible. You might need to make some modifications if you get any errors

trigger Update_related_Contacts_On_Account_fieldchange on Account (after update) {
set<id> ids=new set<id>();
   
    for(Account acc:trigger.new){
        ids.add(acc.id);
    }
    map<id,List<contact>> acc_con_maps=new map<id,List<contact>>();
    List<Account> accts=new List<Account>([select id,(select id,Relationship_Category__c from contacts) from Account where id=:ids]);
    For(Account accts1:accts){
        acc_con_maps.put(accts1.id,accts1.contacts);
    }
   
    list<contact> constoupdate=new list<contact>();
    for(Account acc1:trigger.new){
        if(acc_con_maps.containsKey(acc1.Id)&&acc_con_maps.get(acc1.id)!=null){
            if(acc1.Type=='Member'){
               
                for(contact con:acc_con_maps.get(acc1.Id)){
                    if(con.Relationship_Category__c!='Member - Staff'){
                    con.Relationship_Category__c='Member - Staff';
                    constoupdate.add(con);
                }
                }
            }
            else if(acc1.type=='Member - Past'){
              
               for(contact con1:acc_con_maps.get(acc1.Id)){
                   if(con1.Relationship_Category__c!='Member - Past'){
                    con1.Relationship_Category__c='Member - Past';
                    constoupdate.add(con1);
                   }
                }
            }
           
        }
    }
    update constoupdate;
   
        }
muhammadb1.3970135761364219E12muhammadb1.3970135761364219E12
Dear Ramu,

That worked like a dream... Thank you very much :)
muhammadb1.3970135761364219E12muhammadb1.3970135761364219E12
Dear Ramu, 

The trigger was working fine until we had less than 100 users in any account. Now we have an account with 116 users and it's generating the same error as previously. I am copying the code here for reference and any advise would be highly appreciated



trigger Update_related_Contacts_On_Account_fieldchange on Account (after update) {
set<id> ids=new set<id>();
  
    for(Account acc:trigger.new){
        ids.add(acc.id);
    }
    map<id,List<contact>> acc_con_maps=new map<id,List<contact>>();
    List<Account> accts=new List<Account>([select id,(select id,Relationship_Category_r__c from contacts) from Account where id=:ids]);
    For(Account accts1:accts){
        acc_con_maps.put(accts1.id,accts1.contacts);
    }
  
    list<contact> constoupdate=new list<contact>();
    for(Account acc1:trigger.new){
        if(acc_con_maps.containsKey(acc1.Id)&&acc_con_maps.get(acc1.id)!=null){
            if(acc1.Type=='Member'){
             
                for(contact con:acc_con_maps.get(acc1.Id)){
                    if(con.Relationship_Category_r__c !='Member'){
                        con.Relationship_Category_r__c ='Member - Staff';
                    constoupdate.add(con);
                }
                }
            }
            else if(acc1.type=='Member - Past'){
             
               for(contact con1:acc_con_maps.get(acc1.Id)){
                   if(con1.Relationship_Category_r__c !='Member - Past'){
                    con1.Relationship_Category_r__c ='Member - Past';
                    constoupdate.add(con1);
                   }
                }
            }
          
        }
    }
    update constoupdate;
}

muhammadb1.3970135761364219E12muhammadb1.3970135761364219E12
Instead of trigger, someone suggested to create a formula field which has actually resolved the issue

See the answer below

IF(ISPICKVAL(Account.Account_Type__c,'Member'), 'Member-Staff',ISPICKVAL(Account.Account_Type__c,'Member-past'),'Member-Past','Default value').
This was selected as the best answer