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
Mickey Stringer 5Mickey Stringer 5 

Trigger error - Too many query rows: 50001

Hi all,
I developed a trigger for our org that updates a checkbox based on a corresponding checkbox in related contacts. Not being a knowledgeable developer, I took an existing trigger we have working in production, stripped it down and modified it to work on this custom object. I got it working in the sandbox and after deploying to production, am unable to create new records in this object because of a query limit in the trigger.

Having read several forum posts throughout creating this new trigger, I understand the placement and scope of SOQL queries is very important in Apex. As far as placement, I mimicked what was i nthe original trigger and so trusted it wasn't being done in loops and such. As far as scope, as you see below the Person list is unqualified, but there are no Person records yet in production. The Contact list is qualified by the Person__r.Id existing in the Person list. 

I thought perhaps that having no Person records, and thus no Contact records with Person__r.Id returning a value, that might cause the list to return all Contacts, but I disabled the trigger, added a Person record, and then re-enabled it to no avail.

Posting code snippet where the lists occur. Do you all see anything improper? Thanks!
FYI, firstRunBefore is set to TRUE in the helper class
if(PDMPRegistrationTriggerHelperClass.firstRunBefore){
    PDMPRegistrationTriggerHelperClass.firstRunBefore = false;
    List<Id> personIds = new List<Id>();
    for(Person__c p : Trigger.new){
      personIds.add(p.Id);
    }

    // Get Contacts for all People
    // Prevents query within the loop below
    List<Contact> contacts = [SELECT Id, FirstName, LastName, PDMP_Registered__c, Person__r.Id
      FROM Contact WHERE Person__r.Id IN: personIds];

    System.debug('Number of Contacts: ' + Trigger.new.size());

    for(Person__c p : Trigger.new) {
      System.debug('Person: ' + p.Name);
      System.debug('PDMP Registration Status: ' + p.PDMP_Registered__c);


      // Get Contacts for this Person
      List<Contact> con = new List<Contact>();
      for(Contact loopCon : contacts){
        if(loopCon.Person__r.Id == p.Id){
          con.add(loopCon);
          System.debug('Contact: ' + loopCon.FirstName + ' ' + loopCon.LastName);
        }
      }
      if(con.size() == 0){
        System.debug('Contact: None');
      }

 
Best Answer chosen by Mickey Stringer 5
Abdul KhatriAbdul Khatri
I already mentioned about the Id concern on before insert trigger earlier.

I am not sure what and how you copy the things but presuming if you copy the body of the code only, then one thing I can think of the first line, remove that before insert
trigger PDMPRegistrationTrigger on Person__c (before update)
becuase looking at your code, the entire thing is based on Id which not going to work on before Insert.
 

All Answers

Abdul KhatriAbdul Khatri
Please try this
 
if(PDMPRegistrationTriggerHelperClass.firstRunBefore){
        
    	PDMPRegistrationTriggerHelperClass.firstRunBefore = false;
        
    	List<Id> personIds = new List<Id>();
        for(Person__c p : Trigger.new){
      		personIds.add(p.Id);
    	}
        
        if(personIds.isEmpty()) return;
        
	    // Get Contacts for all People
    	// Prevents query within the loop below
    	List<Contact> contacts = [SELECT Id, FirstName, LastName, PDMP_Registered__c, Person__r.Id FROM Contact WHERE Person__r.Id IN: personIds];

    	System.debug('Number of Contacts: ' + Trigger.new.size());

		for(Person__c p : Trigger.new) {
      		System.debug('Person: ' + p.Name);
      		System.debug('PDMP Registration Status: ' + p.PDMP_Registered__c);


      		// Get Contacts for this Person
      		List<Contact> con = new List<Contact>();
      		for(Contact loopCon : contacts){
                
        		if(loopCon.Person__r.Id == p.Id){
          			con.add(loopCon);
          		System.debug('Contact: ' + loopCon.FirstName + ' ' + loopCon.LastName);
        	}
      	}
            
      	if(con.size() == 0){
        	System.debug('Contact: None');
      	}    
    }

 
Abdul KhatriAbdul Khatri
What what event you are firing this triffer I meant
after insert
before insert
etc.

What is the relationship between Contact and the Person Object? I meant it is lookup or MasterDetail ( What is what in both the case?)
Waqar Hussain SFWaqar Hussain SF
The code look fine. Also you have handled the recursion of the trigger using firstRunBefore 

FYI: There is limitation of salesforce that you can query 50,000 records using SOQL in one transaction. Which includes all SOQL in a transaction. May be you will have more SOQL Queries in your trigger. 

total number of records retrieved by SOQL queries = 50,000

Are you sure you are getting issue with above query? How many records you are getting in the above query?

You can use Batch Apex, in which there is 50k limit counts per batch execution.
 
Mickey Stringer 5Mickey Stringer 5
It's a lookup from Contact to Person - one Person to many Contacts.
The trigger is before insert and update.
The error says the SOQL row limit is reached, but I can't figured out why. If I have one Person record, and two Contact records linked to it, the first query should return one row and the second query, two rows.
Abdul KhatriAbdul Khatri
I guess you are running this trigger on the Person__c Custom Object.

You mentioned that you are running this query "before insert" and "before update". Please note "before insert", the Id field is null so the following code would break on "before insert" trigger
List<Id> personIds = new List<Id>();
        for(Person__c p : Trigger.new){
      		personIds.add(p.Id);
    	}
Therefore personIds will be null in that case and there is possibility that your SOQL is going out of limits because of the that.

This is what my understanding is but if you can post the complete code including the trigger line in the code so better understand the issue and also if the line number it is throwing error to you from the debug log.
Mickey Stringer 5Mickey Stringer 5
That occurred to me, but my thought was that it wouldn't have worked in the sandbox in that case. Even if it didn't hit the query limit, it wouldn't actually have a Person ID to perform the field update.
I'll post the full code below, but will have to re-enable it and run a test in Production to get a line number. No line was referenced in the original SOQL limit error.
After pasting this, I'm noticing I've reused a few variable names (started writing this a while ago and didn't remember what I wrote!)... not sure that would cause the issue, but I'm going to change some of them first.
trigger PDMPRegistrationTrigger on Person__c (before insert, before update) {

  System.debug('-----START PDMP Registration TRIGGER-----');
  System.debug('First Run Before? ' + PDMPRegistrationTriggerHelperClass.firstRunBefore);
  String triggerType;
  String triggerTiming;
  if(Trigger.isBefore){triggerTiming='Before';} else if(Trigger.isAfter){triggerTiming='After';}
  if(Trigger.isInsert){triggerType='Insert';} else if(Trigger.isUpdate){triggerType='Update';}
  System.debug('Trigger: ' + triggerTiming + ' ' + triggerType);

  if(PDMPRegistrationTriggerHelperClass.firstRunBefore){
    PDMPRegistrationTriggerHelperClass.firstRunBefore = false;
    List<Id> personIds = new List<Id>();
    for(Person__c p : Trigger.new){
      personIds.add(p.Id);
    }

    // Get Contacts for all People
    // Prevents query within the loop below
    List<Contact> contacts = [SELECT Id, FirstName, LastName, PDMP_Registered__c, Person__r.Id
      FROM Contact WHERE Person__r.Id IN: personIds];

    System.debug('Number of Contacts: ' + Trigger.new.size());

    for(Person__c p : Trigger.new) {
      System.debug('Person: ' + p.Name);
      System.debug('PDMP Registration Status: ' + p.PDMP_Registered__c);


      // Get Contacts for this Person
      List<Contact> con = new List<Contact>();
      for(Contact loopCon : contacts){
        if(loopCon.Person__r.Id == p.Id){
          con.add(loopCon);
          System.debug('Contact: ' + loopCon.FirstName + ' ' + loopCon.LastName);
        }
      }
      if(con.size() == 0){
        System.debug('Contact: None');
      }

      // Update PDMP Registration Status
      boolean pdmpReg = PDMPReg(con);
      
      System.debug('-----UPDATE PDMP Registration Status-----');
      System.debug('PDMP Registration Status: ' + pdmpReg);

      System.debug('PDMP Registration Before: ' + p.PDMP_Registered__c);

      if(PDMPReg){
        p.PDMP_Registered__c = TRUE;
      } else {
        p.PDMP_Registered__c = FALSE;
      }

      System.debug('PDMP Registration Status Updated To: ' + p.PDMP_Registered__c);

      p.Trigger_Helper_Hidden__c = false;
    }
  }
  System.debug('-----END PDMP Registration TRIGGER-----');

  // Check Contacts for Registration Status
  private static boolean PDMPReg(List<Contact> c){
    System.debug('-----PDMP REGISTERED ?-----');
    boolean isReg = IsReg(c);
    System.debug('Is Registered: ' + isReg);
    return isReg;
  }

  /*private static boolean ConReg(List<Contact> c, String RegStatus){
  System.debug('-----Contact Registration-----');
  boolean isReg = IsReg(c, RegStatus);
  System.debug('Contact Registration: ' + isReg);
  return isReg;
  }*/

  private static boolean IsReg(List<Contact> c){
    if(c.size()>0){
      // If at least one Contact is registered, return true
      for(Contact con : c){
        if(con.PDMP_Registered__c){
          return true;
        }
      }
      return false;
    } else {
      return false;
    }

}
  
}

 
Abdul KhatriAbdul Khatri
I am sorry it is very hard to predict without having the Logs, If you can share the Debug Log that would be a valuable thing to trouble shoot your issue. Right now nothing seems wrong with your code thought but I may be missing a critical point here which can be traced through logs.
Mickey Stringer 5Mickey Stringer 5
So I got a debug log and found the error is thrown for the Contact list on line 20.
I just edited the one Person record I created while the trigger was inactive, and that saved without issue. So it seems like it might be an issue with a Befor Insert trigger running before the ID is generated for the new record. I just can't figure out why that's happening here and not with the trigger I based this on.
Abdul KhatriAbdul Khatri
I already mentioned about the Id concern on before insert trigger earlier.

I am not sure what and how you copy the things but presuming if you copy the body of the code only, then one thing I can think of the first line, remove that before insert
trigger PDMPRegistrationTrigger on Person__c (before update)
becuase looking at your code, the entire thing is based on Id which not going to work on before Insert.
 
This was selected as the best answer
Mickey Stringer 5Mickey Stringer 5
Yea I know, I just didn't understand why that would be the case if the other trigger is running before insert and before update.
So I changed it to after insert, after update (though I guess it could still run before update) and my test class fails now. Are there any common pitfalls I should be looking for to make sure this works after insert?
Thank you for your help so far. I really appreciate it
Abdul KhatriAbdul Khatri
It's not just changing the trigger type from before to after. You may get yourself in a lot of consequences. The issue you started with was Trigger error - Too many query rows: 50001 so please try to stick to this one so that we can resolve this issue first and then we look into the other issues. If you are able to get the debug log for this issue that would be very helpful. 

Do you have the original trigger and would like to share to see what we can understand you are doing wrong?
Mickey Stringer 5Mickey Stringer 5
Here's the debug log:
11:38:30.0 (247579)|CODE_UNIT_STARTED|[EXTERNAL]|01q0G000000vQuF|PDMPRegistrationTrigger on Person trigger event BeforeInsert
11:38:30.0 (1111702)|USER_DEBUG|[3]|DEBUG|-----START PDMP Registration TRIGGER-----
11:38:30.0 (20507065)|USER_DEBUG|[4]|DEBUG|First Run Before? true
11:38:30.0 (20565221)|USER_DEBUG|[9]|DEBUG|Trigger: Before Insert
11:38:33.862 (3862034496)|EXCEPTION_THROWN|[20]|System.LimitException: Too many query rows: 50001
11:38:33.862 (3862404798)|FATAL_ERROR|System.LimitException: Too many query rows: 50001

Trigger.PDMPRegistrationTrigger: line 20, column 1
11:38:33.862 (3862429541)|FATAL_ERROR|System.LimitException: Too many query rows: 50001

Trigger.PDMPRegistrationTrigger: line 20, column 1
11:38:33.862 (3864406105)|CODE_UNIT_FINISHED|PDMPRegistrationTrigger on Person trigger event BeforeInsert

Here's the beginning of the other trigger. I won't post the whole thing just yet, as it's rather long.
Delegator is a custom object that links to two Contact records through lookup relationships - one is the Delegator, one is the Delegate.
trigger SecurityRoleTrigger on Contact (before insert, before update) {

  System.debug('-----START SECURITY ROLE TRIGGER-----');
  System.debug('First Run Before? ' + SecurityRoleTriggerHelperClass.firstRunBefore);
  String triggerType;
  String triggerTiming;
  if(Trigger.isBefore){triggerTiming='Before';} else if(Trigger.isAfter){triggerTiming='After';}
  if(Trigger.isInsert){triggerType='Insert';} else if(Trigger.isUpdate){triggerType='Update';}
  System.debug('Trigger: ' + triggerTiming + ' ' + triggerType);

  if(SecurityRoleTriggerHelperClass.firstRunBefore){
    SecurityRoleTriggerHelperClass.firstRunBefore = false;
    List<Id> accountIds = new List<Id>();
    List<Id> contactIds = new List<Id>();
    for(Contact c : Trigger.new){
      accountIds.add(c.AccountId);
      contactIds.add(c.Id);
    }
    // Get Accounts for all contacts
    // Prevents query within the loop below
    List<Account> accounts = [SELECT Id, Name, Participation_Agreement_Executed__c, Facility_Patient_Panel__c, NPP_Updated__c, Type, State__c
      FROM Account WHERE Id IN: accountIds];

    // Get Delegators for all contacts
    // Prevents query within the loop below
    List<Delegator__c> delegators = [SELECT Id, Delegator_Approved__c, Delegate__r.Id, Delegator__r.License_Verified__c,
      Delegator__r.DEA_Verified__c, Delegator__r.CDS_Verified__c, Delegator__r.NPI_Verified__c, Delegator__r.FirstName, Delegator__r.LastName
      FROM Delegator__c WHERE Delegate__r.Id IN: contactIds];

    System.debug('Number of Contacts: ' + Trigger.new.size());

    for(Contact c : Trigger.new) {
      System.debug('Contact: ' + c.FirstName + ' ' + c.LastName);
      System.debug('Member Title: ' + c.crisp2_MemberTitle__c);
      System.debug('Security Role: ' + c.Security_Role__c);

 // Get Account for this Contact
      Account a = new Account();
      for(Account loopAcc : accounts){
        if(loopAcc.Id == c.AccountId){
          a = loopAcc;
        }
      }
      System.debug('Account: ' + a.Name);

      // Get Delegators for this Contact
      List<Delegator__c> del = new List<Delegator__c>();
      for(Delegator__c loopDel : delegators){
        if(loopDel.Delegate__r.Id == c.Id){
          del.add(loopDel);
          System.debug('Delegator: ' + loopDel.Delegator__r.FirstName + ' ' + loopDel.Delegator__r.LastName);
        }
      }
      if(del.size() == 0){
        System.debug('Delegator: None');
      }

Mickey Stringer 5Mickey Stringer 5
Abdul, thanks for your help. I started realizing for this particular record type, it doesn't make sense to have the 'before insert' trigger event anyway. Removing that got rid of the error, and everything is working smoothly now!