Apex trigger runs OK while editing Account, fails in DataLoader

Question

The trigger in question reassigns cases from the updated account to another account given a criteria defined by a single custom field in those cases.

Basically, if "Case.ReassignTo__c" has a value, i have to reassign that case to an Account that has the value of "Case.ReassignTo__c" in its "Field__c" field.

This is the trigger:

trigger ReassignCasesOnField on Account (after update) {

    List<Case> caseList= new List<Case>();

    for (Account a : Trigger.new) {
        if (a.IsPersonAccount && a.Field__c != null){ 
            Case[] casesToReassign = [select id from Case where ReassignToField__c =: Integer.valueOf(a.Field__c) and LastModifiedDate = TODAY];

            for (Case ca : casesToReassign){
                ca.AccountId = a.Id;
                ca.ReassignTo__c = null;
                caseList.add(ca);
            }
        }
    }

    update caseList;
}

I'm thinking the batch size of the DataLoader has something to do since I'm getting a

CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY:ReassignCasesOnField: System.LimitException: Too many SOQL queries: 101

but I guess that's a symptom and not the main issue.

I've tried to get the cases into a map, populating it inside the for loop and then updating the cases, to no avail, I get the same error in the SOQL.

Answer 1

You first need to collect a list of accounts before you run your query for cases, then run a single query on cases. Your trigger should look more or less like what's below.

It's unfortunate that you have two different sObject Types or you could have used a SOQL for loop. But as you don't, you're stuck using a separate query and two loops. The fact that you have this integer.valueOf() relationship complicates your query. However, you're not taking advantage of the accountId in your existing query. I've shown two possible different queries. You may want to test which one works best for you.

trigger ReassignCasesOnField on Account (after update) {

    List<Case> caseList= new List<Case>();
    list<Id>aIds = new list<id>():
    list<integer>cacctsfldc = new list<integer>();
    list<case>casesToReassign = new list<case>(); 
    list<account>accts = new list<account>():

    for (Account a : Trigger.new) {
        if (a.IsPersonAccount && a.Field__c != null){ 
            cacctsfldc.add(integer.valueOf(a.field__c));
        }
    }

    //casesToReassign = [select id, accountId, ReassignToField__c, LastModifiedDate FROM Case WHERE ReassignToField__c IN: cacctsfldc 
    AND LastModifiedDate = system.today()]; 
    // is there a reason you're not using AND accountId IN: aIds ?

    casesToReassign = [select id, ReassignToField__c, LastModifiedDate FROM Case WHERE 
    ReassignToField__c IN: string.valueOf(accts.field__c) AND LastModifiedDate = system.today() AND ];
    // is there a reason you're not using AND accountId IN: Accts ?

    If need to reassign Account to a different one than returned in query, then would use another for loop here
    //For(Account a:Trigger.new){
            for (Case ca  casesToReassign){
                // ca.AccountId = a.Id; <-- not necessary if use in query
                ca.ReassignTo__c = null;
                caseList.add(ca);
            }
       // }


    update caseList;
}

Answer 2

So when you updating record from UI. This code is working because you are inserting single record at a time. So you didn't get any error.

But when you inserting multiple records(more than 100 FROM Dataloader). Then you will receive

Too many SOQL queries: 101

means above for loop will iterate more than 100 times. Since you have written SOQL inside for loop means your SOQL will be performed more than 100 times.

We all know in a single transaction we can use only 100 SOQL that's the reason you hitting this limitation.

Like my above code, using that you can prevent the SOQL inside for loop.

Always your code should work with multiple records

Noted:- I am not tested this code. SO before using code plz check syntax etc.

Below code will solve your 101 Error.

trigger ReassignCasesOnField on Account (after update) {

    List<Case> caseList= new List<Case>();
    map<Integer, Id> mapIntToAccountId = new map<Integer, Id>();
    for (Account a : Trigger.new) {
        if (a.IsPersonAccount && a.Field__c != null){ 
            mapIntToAccountId.put(Integer.valueOf(a.Field__c), a.Id);
        }
    }
    for(Case objCase: [select id, ReassignToField__c from Case where ReassignToField__c IN: mapIntToAccountId.keyset() and LastModifiedDate = TODAY])
    {
       if(mapIntToAccountId.containsKey(objCase.ReassignToField__c))
       {
            objCase.AccountId = mapIntToAccountId.get(objCase.ReassignToField__c);
            objCase.ReassignTo__c = null;
            caseList.add(objCase);
       }
    }
    update caseList;
}

Check best practices https://developer.salesforce.com/page/Best_Practice%3A_Bulkify_Your_Code