Too many query rows - exceeded 50001

Question

I'm getting this error in a query in an after update trigger on the contact object. There are 30,123 contacts in our production org. The query is:

List<AggregateResult> results = [SELECT max(National_Serial_Number_Import__C) FROM Contact];

The field is a custom decimal value on the contact object. The query runs once each time the trigger executes. There are a lot of questions about this limit here - I've reviewed the answers and still not seeing what the issue is with my code. Is it that the code also updates a contact? Maybe I should move that code out to a batch?

Also, this code was running fine in production until we ran an upsert which added a few hundred contact records. We went from just under 30,000 contacts to just over 30,000.

For reference, the trigger code is here:

trigger AddNationalSerialNumber on Contact (after update) {

    //List<AggregateResult> results = [SELECT max(National_Serial_Number_Import__C) FROM Contact];
    //Decimal nsn = (Decimal)results[0].get('expr0');
    //Integer nationalSerialNumber = nsn.IntValue();
    Decimal nsn = 0;
    Integer nationalSerialNumber = 0;

    try {
        List<AggregateResult> results = [SELECT max(National_Serial_Number_Import__C) FROM Contact];
        nsn = (Decimal)results[0].get('expr0');
        nationalSerialNumber = nsn.IntValue();
    } catch(Exception e) {
        //ToDo Handle exception
    }

    for (Contact newContact : Trigger.new) {

        // Access the "old" record by its ID in Trigger.oldMap
        Contact oldContact = Trigger.oldMap.get(newContact.Id);

        // Check if we have an existing National Serial Number
        Decimal existingnsn = 0;
        Integer ExistingNationalSerialNumber = 0;

        try {
            existingnsn = newContact.National_Serial_Number_Import__c;
            ExistingNationalSerialNumber = existingnsn.IntValue();
        } catch(Exception e) {
            //ToDo  Handle exception
        }

        if(oldContact.Member_Status__c == 'Interim'
             && newContact.Member_Status__c == 'Undergraduate'
             && ExistingNationalSerialNumber == 0) {

            nationalSerialNumber++;
            Contact c = new Contact();
            c.id = newContact.id; 
            c.National_Serial_Number_Import__c = nationalSerialNumber;   
            update c;

            ChapterSpot_Queue__c CSQueue = new ChapterSpot_Queue__c();
            CSQueue.Contact__c = newContact.id;
            CSQueue.ContactID__c = newContact.id;
            CSQueue.National_Serial_Number__c = nationalSerialNumber;
            insert CSQueue;

        }
    }

}

Answer 1

Your trigger is likely running into an error because it is recursing. You have an after trigger, which then updates the same contact, which then fires the trigger off again. That's 30K plus query rows in the first part of the transaction and then another 30K when it recurses.

Also, another issue in your code worth calling out is that you have update and insert DML statements inside of a for loop and this could create issues when contacts are created or updated in bulk. You should move your contacts for update to a list of contacts, and perform one update DML at the end of your trigger and outside of the for loop. Do the same for the inserts, in a separate list.

Since you are updating the same contact that is in the trigger, you are better off using a before trigger, rather than firing off a separate update DML. Alternatively, you can put a static variable flag in place, set it before the first contact update and check for that flag in your trigger to prevent a second query. One of these two solutions should eliminate the query rows issues, but you are correct that you are likely going to be better off in the long run performing this operation in a scheduled batch.

Answer 2

Here are some updates to your code that should help with the issue you are experiencing.

First remove your trigger code and add it to a class and reference that class in the trigger. This is a standard best practice ensuring one trigger per object.

trigger AddNationalSerialNumber on Contact (before update, after update) 
{

    if(Trigger.isBefore && Trigger.isUpdate)
    {
        ContactHelper.AddNationalSerialNumber(Trigger.new, Trigger.OldMap)
    }

}

In the new class create a list to hold new ChapterSpot_Queue__c records so that you can do a bulk insert later on. Also, since this is moved to a before update, you can assign the National Serial Number to the contact and completely eliminate the update you were doing.

public class ContactHelper 
{

    public void AddNationalSerialNumber(List<Contact> newRecords, Map<Id, Contact> oldRecords)
    {
        //List<AggregateResult> results = [SELECT max(National_Serial_Number_Import__C) FROM Contact];
        //Decimal nsn = (Decimal)results[0].get('expr0');
        //Integer nationalSerialNumber = nsn.IntValue();
        Decimal nsn = 0;
        Integer nationalSerialNumber = 0;
        List<ChapterSpot_Queue__c> csqList = new List<ChapterSpot_Queue__c>();

        try {
            List<AggregateResult> results = [SELECT max(National_Serial_Number_Import__C) FROM Contact];
            nsn = (Decimal)results[0].get('expr0');
            nationalSerialNumber = nsn.IntValue();
        } catch(Exception e) {
            //ToDo Handle exception
        }

        for (Contact newContact : newRecords) {

            // Access the "old" record by its ID in Trigger.oldMap
            Contact oldContact = oldRecords.get(newContact.Id);

            // Check if we have an existing National Serial Number
            Decimal existingnsn = 0;
            Integer ExistingNationalSerialNumber = 0;

            try {
                existingnsn = newContact.National_Serial_Number_Import__c;
                ExistingNationalSerialNumber = existingnsn.IntValue();
            } catch(Exception e) {
                //ToDo  Handle exception
            }

            if(oldContact.Member_Status__c == 'Interim'
                 && newContact.Member_Status__c == 'Undergraduate'
                 && ExistingNationalSerialNumber == 0) {

                nationalSerialNumber++;
                newContact.National_Serial_Number_Import__c = nationalSerialNumber;   

                ChapterSpot_Queue__c CSQueue = new ChapterSpot_Queue__c();
                CSQueue.Contact__c = newContact.id;
                CSQueue.ContactID__c = newContact.id;
                CSQueue.National_Serial_Number__c = nationalSerialNumber;
                csqList.add(CSQueue);

            }
        }

        if(CSQueue.size() > 0)
        {
            insert CSQueue;
        }
    }
}