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

Mass Acount Transfer kills my trigger code (Too many SOQL queries)

I have a trigger set to fire before update to validate data on Account records.  I get around the SOQL query limit by going through all the records in the trigger,  getting record Id's and running a piece of SOQL to pull back a SET of Account objects and then using a loop to go around those objects and add an error to the update before it is made.


However,  this morning,  one of my users ran a mass update on accounts to transfer to them to a different user and I received the dreaded Too many SOQL queries error.  So,  I ran the same process on our full copy dev box with the log running and discovered that instead of firing the trigger once,  the updated records were broken down into odd sized batches (sometimes ones and twos,  sometimes much larger,  e.g 50).  This meant the trigger was fired 24 times,  which caused the error.


So,  my question is,  how am I supposed to code for mass updates if I cannot rely on the trigger firing just once?  Has anyone else seen this problem?

If you can, post a simpilified version of the code that reproduces the issues. Do you have any SOQL queries inside a for loop?

Here's the code,  only one query is made and the results are looped through.  The code seeks to find duplicate Agent_ID__c values before a record is saved (permitting the default value of zero) and notify the user of the record where the value is currently in use.


    public static void CheckForDuplicateAgentID (Account[] objOriginalAccounts, Account[] objUpdatedAccounts) {

        Set<Double> dblAgentID = new Set<Double>();
        List<Account> objAccounts = new List<Account>(); //Set<Account> is not allowed
        Boolean blnAdd;
        //We only want to do this work for Accounts that have had Agent_ID__c changed
        for(Integer i = 0; i < objUpdatedAccounts.size(); i++){
            blnAdd = false;
            //If the original account value is null,  this is an insert - so run the code anyway
            if(objOriginalAccounts == null){
                blnAdd = true;
                //If this trigger holds inserts AND updates,  this isn't going to work!  Is this a possiblity?  Not under the current business model
                if(objOriginalAccounts[i].Agent_ID__c != objUpdatedAccounts[i].Agent_ID__c){
                    blnAdd = true;

            if (blnAdd){
        //Get all Agent_ID__c
        for(Account objThisAccount:objAccounts){
            //AgentID of zero is permitted as a duplicate
            if(objThisAccount.Agent_ID__c > 0){
        System.debug('dblAgentID.size(): ' + dblAgentID.size());
        //Make a single soql call to search for Agent_ID__c that already exist
        Account[] objExistingAccounts = [SELECT a.Id, a.Agent_ID__c, a.Name FROM Account a WHERE a.Agent_ID__c in :dblAgentID];
        //Now loop through the result set
        for(Account objResultAccount:objExistingAccounts){
            System.debug('objResultAccount.Agent_ID__c: ' + objResultAccount.Agent_ID__c);
            //Do any of the accounts found match accounts from the trigger on Agent_ID__c?
            for(Account objSentAccount:objAccounts){
                System.debug('objSentAccount.Agent_ID__c:objResultAccount.Agent_ID__c = ' + objSentAccount.Agent_ID__c + ':' + objResultAccount.Agent_ID__c);
                //Is there a match on Agent_ID__c?
                 if(objSentAccount.Agent_ID__c == objResultAccount.Agent_ID__c){
                     //Okay,  we're comparing the account record we were sent with the account record we found
                     //If the two have the same ID that's good because it means that we're talking about the same account
                     //If the IDs differ,  we're talking about 2 different accounts. BAD,  notify the user
                    System.debug('objSentAccount.Id:objResultAccount.Id = ' + objSentAccount.Id + ':' + objResultAccount.Id);
                     if(objSentAccount.Id <> objResultAccount.Id){
                         objSentAccount.addError('The Agent ID provided (' + objSentAccount.Agent_ID__c + ') is already in use for the following agent: ' + objResultAccount.Name + '.  Try a different ID or set the other one to zero.');
                    //We got it,  exit this loop to look for the next objResultAccount


Thanks for looking!

Apologies,  the page seems to render a colon and the letter O as some stupid face-thing...

You might want to check out this thread as this appears to be the same bug I ran into. 


Jeff Douglas
Appirio, Inc.