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
r1985r1985 

System.Limit Exception Too many query rows

Hi,

 

I wrote a following trigger for updating a custom object field whenever a stage of  any opportunity is changed. But while doing mass update it is throwing Limit error crossed 500001 rows. Kindly help me in modifying  the code to avoid the error.

 

trigger stageupdate on Opportunity (before update) {
List<Trade_In_Equipments__c> ls=[select id,Opportunity__c,Equipment__c,Unique_check__c from Trade_In_Equipments__c];
List<Trade_In_Equipments__c> ls1=new List<Trade_In_Equipments__c>();
List<Trade_In_Equipments__c> ls2=new List<Trade_In_Equipments__c>();
  
    for(Opportunity op:Trigger.new)
    {
     try{   
  /* If the opportunity stage value is not equal to new value and the new value is closed lost, we are adding opportunity id and equipment id to uniquecheck field, so that other opportunity
can add this equipment */      

    if((!Trigger.oldmap.get(op.Id).stagename.equals(op.stagename)) && (op.stagename.equals('Closed Lost')))
        {
       
            for(Trade_In_Equipments__c tr:ls)

            {
            if(tr.Opportunity__c==op.Id){
             tr.Unique_check__c=(string)tr.Opportunity__c+(string)tr.Equipment__c;
                   
           
        ls1.add(tr);
       
        }

 

}

       
 /* If the old opportunity stage value is closed lost and the new value is not closed lost, we are adding equipment id to uniquecheck field, so that other opportunity cannot add this equipment.
In case if other opportunity already had the equipment added to them and in open stage, this will throw error 'Please delete the Trade_In_Equipments before changing the stage' */
   

  

           if((Trigger.oldmap.get(op.Id).stagename.equals('Closed Lost')) && (!op.stagename.equals('Closed Lost')))
            {
       
                    for(Trade_In_Equipments__c tr:ls)

               {
       
               String q=(string)tr.Equipment__c;

            
                     tr.Unique_check__c=q.substring(0,15);                   
               ls2.add(tr);

        } 

        }

 
   }
 update ls1;    
  update ls2;                   }
     
       catch(Exception e)
{
System.debug('Error');
op.addError('Stage cannot be changed without deleting the associated trade in equipments');
  }
}
  }

Best Answer chosen by Admin (Salesforce Developers) 
Platy ITPlaty IT

You only have one query in your trigger, so that must be what's hitting the limit.  You're querying for all Trade_In_Equipments__c records, when really all you need are the ones related to Opportunities in your trigger.  Try changing that query to this and you shouldn't hit that limit:

[select id,Opportunity__c,Equipment__c,Unique_check__c from Trade_In_Equipments__c where Opportunity__c IN: Trigger.new]

 

All Answers

Platy ITPlaty IT

You only have one query in your trigger, so that must be what's hitting the limit.  You're querying for all Trade_In_Equipments__c records, when really all you need are the ones related to Opportunities in your trigger.  Try changing that query to this and you shouldn't hit that limit:

[select id,Opportunity__c,Equipment__c,Unique_check__c from Trade_In_Equipments__c where Opportunity__c IN: Trigger.new]

 

This was selected as the best answer
r1985r1985
Thanks Jessie. This solved my problem.