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

Question

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

score 1 · Answer 1 · answered Apr 09 '14 at 13:27

Couple of things that jump out at me here:

First and foremost is the main cause of your error: a SOQL query within a loop. You need to bulkify your trigger to perform a single query, rather than querying once for each Account record in your update batch.
You have a DML operation (update listCon;) within that same loop, which will exacerbate the problem if you happen to have any SOQL queries in your Contact triggers. This update should also be bulkified, so that you are making as few inserts/updates as possible (in this case you can limit it to a single update call).
You are updating all Contact records queried regardless of whether or not they've been updated by your code. This can lead to unnecessarily long processing times, and can easily be prevented by keeping track of which records should be updated in a second list.
This may be by design, but you're suppressing any errors in your update call without even attempting to deal with them. There are many different ways to go about this depending on your implementation so I won't delve into that, but in most circumstances you would probably want to at least be aware that your update to those records was failing so you can correct the issue.

After making a few corrections to your code we are left with the following which uses a single SOQL query, single DML statement, and won't update any Contact records that haven't been modified by the trigger:

trigger UpdateAllContacts on Account (after update) {
    List<Contact> updateCons = new List<Contact>();
    for ( Contact con : [select Id, AccountId, Relationship_Category_r__c from Contact where AccountId in :Trigger.NewMap.keySet()] ) {
        Account parentAcc = Trigger.NewMap.get(con.AccountId);
        if ( parentAcc.Type == 'Member' ) {
            if ( con.Relationship_Category_r__c != 'Member' ) {
                con.Relationship_Category_r__c = 'Member - staff';
                updateCons.add(con);
            }
        } else if ( parentAcc.Type == 'Member - past ' ) {
            con.Relationship_Category_r__c = 'Member - past';
            updateCons.add(con);
        } 
    }
    update updateCons;
}

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

1 Answers1