function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
ZamieZamie 

System.LimitException: Too many query rows: 1001

Hi,

 

We are writing a select query in our trigger which is giving such exception but when we are running the query in API, error is not coming and sometimes we can find too less records for this exception.

 

How to solve this? Any help will be much appreciated.

 

 

ZamieZamie

 

trigger autoAttachTransactions on Opportunity (after insert) {
  
  List<Opportunity> lstOpp;
  String partnerId =  null;
  String endCustomerId = null;
  List<POS_Entry__c> posOpp;
  if(trigger.isAfter) {
    if(trigger.isInsert) {
      for(Opportunity opp:Trigger.new){
          //lstOpp = new List<Opportunity>();
          if(opp.Deal_Registration_Number__c!=null || opp.Deal_Registration_Number__c!=''){
              if(opp.Show_Transactions_By__c=='Both'){
                  partnerId = opp.Account.Id;
                  endCustomerId = opp.End_Customer_Name__r.Id;
                  //posOpp= [Select Opportunity__c from POS_Entry__c Where Opportunity__c='' AND (Sold_By__c =:partnerId OR Sold_To__c =:partnerId) AND (Shipped_To__c=:endCustomerId ) ];
                  for(POS_Entry__c pos: [Select Opportunity__c from POS_Entry__c Where Opportunity__c='' AND (Sold_By__c =:partnerId OR Sold_To__c =:partnerId) AND (Shipped_To__c=:endCustomerId ) AND (Deal_Registration_Number__c =:opp.Deal_Registration_Number__c)]){
                      pos.Opportunity__c = opp.Id;
                      posOpp.add(pos);
                  }
              }
              if(opp.Show_Transactions_By__c=='Partner'){
                  partnerId = opp.Account.Id;
                  //posOpp= [Select Opportunity__c from POS_Entry__c Where Opportunity__c='' AND (Sold_By__c =:partnerId OR Sold_To__c =:partnerId)];
                  for(POS_Entry__c pos: [Select Opportunity__c from POS_Entry__c Where Opportunity__c='' AND (Sold_By__c =:partnerId OR Sold_To__c =:partnerId) AND (Deal_Registration_Number__c =:opp.Deal_Registration_Number__c)]){
                      pos.Opportunity__c = opp.Id;
                      posOpp.add(pos);
                  }
              }
              if(opp.Show_Transactions_By__c=='End Customer'){
                  endCustomerId = opp.End_Customer_Name__r.Id;
                  //posOpp= [Select Opportunity__c from POS_Entry__c Where Opportunity__c='' AND Shipped_To__c=:endCustomerId ];
                  for(POS_Entry__c pos: [Select Opportunity__c from POS_Entry__c Where Opportunity__c='' AND Shipped_To__c=:endCustomerId AND (Deal_Registration_Number__c =:opp.Deal_Registration_Number__c)]){
                      pos.Opportunity__c = opp.Id;
                      posOpp.add(pos);
                  }
              }
              
              Update posOpp;
          }
      }
    }
  }
}

 Here is the code

 
kyle.tkyle.t

I suspect that your query is just too broad.  a trigger is limited to retrieving 1000 rows in a query, once you reach that mark, the next row retrieved throws an error (hence the 1001).  Try adding more constraints to your query... though it appears you have added some already.

 

If you are able to solve that issue you may run into another issue where you execute too many SOQL statements (the max is 20 in a trigger) because you are executing SOQL queries in a for loop.  If you ever insert more than 20 opportunities (using data loader) you will hit the SOQL query govenor limit.
uptime_andrewuptime_andrew

They main problem here is you are querying inside a for loop.

 

A better practise is to do the loop to build the list of Opportunities you need to query for, query on them AFTER the loop, then process the data.