+ Start a Discussion
jneilan22jneilan22 

System.LimitException: Too many SOQL queries: 101

Hello,

 

I have a simple trigger on the Opportunity object that fires when an Opportunity is edited and forces an edit/save on the OpportunityLineItems associated with the Opportunity.  When I try to Data Load some bulk changes, I get the following error:

 

System.LimitException: Too many SOQL queries: 101

 

I'm fairly new to Apex code.  I believe the problem is that I am running SOQL inside a FOR loop, but I'm not quite sure how to resolve it.  My trigger is below.  Any help would be appreciated.  Thanks.

 

 

trigger UpdateDeliverables on Opportunity (after insert, after update) {
   // Forces Edit/Save on Deliverables in order to fire Workflow field Update to update quantity field with Q__c value    

FOR(Opportunity opp : Trigger.new)  {

    LIST<OpportunityLineItem> oppLine = [SELECT Id
    FROM OpportunityLineItem
    WHERE OpportunityId = :opp.Id
    AND Flat_Price__c != TRUE];
    
    IF(oppLine.size() > 0)
    update oppLine;
    }

}

Best Answer chosen by Admin (Salesforce Developers) 
RajivRajiv

Hi you can use this code :

 

 

trigger UpdateDeliverables on Opportunity (after insert, after update) {

List<Id> oppIds = new List<Id>();
for(opportunity opp : Trigger.New){
oppIds.add(opp.Id);

}

List<OpportunityLineItem> Deliverables = new List<OpportunityLineItem>();

List<OpportunityLineItem> oppline = [Select OpportunityId from OpportunityLineItem where OpportunityId IN:oppIds];

for(integer i=0; i<oppline.size()>0; i++){
Deliverables.add(oppline[i]);

}
if(Deliverables.size() > 0)
update Deliverables;

 

If it works mark as a solution.

All Answers

symantecAPsymantecAP

Hi

 

Please go thru the second part on this page which talks abt writing your code outside for loop

 

http://wiki.developerforce.com/page/Apex_Code_Best_Practices

 

 

This should help

WorkhardWorkhard

Hi,

Try the modified code as below

trigger UpdateDeliverables on Opportunity (after insert, after update)
{
   // Forces Edit/Save on Deliverables in order to fire Workflow field Update to update quantity field with Q__c value    
    list<id> op=new list<id>();
    LIST<OpportunityLineItem> oppLine=new LIST<OpportunityLineItem> ();
    FOR(Opportunity opp : Trigger.new)  
    {
    op.add(opp.id);
    }
    for(OpportunityLineItem oppL:[SELECT Id FROM OpportunityLineItem WHERE OpportunityId in :op AND Flat_Price__c != TRUE])
    {
    oppLine.add(oppL);
    }
    
    update oppLine;
    

}

jneilan22jneilan22

I tried your code, but I still get the same error.

jneilan22jneilan22

Thank you for the reference.  I tried to put together a revised code based on the 2nd section you mentioned, but now I get the following error:

 

Error: Compile Error: Invalid field OpportunityLineItem for SObject Opportunity at line 37 column 41

 

The revised trigger code is below:

 

 

trigger UpdateDeliverables on Opportunity (after insert, after update) {

    List<Opportunity> Opps = [
        SELECT Id
        FROM Opportunity
        WHERE Id IN :Trigger.newMap.keySet()];
        
     List<OpportunityLineItem> Deliverables = new List<OpportunityLineItem>{};
     
         for(Opportunity o: Opps) {
         
             for(OpportunityLineItem d: o.OpportunityLineItem) {
             IF(Deliverables.size() > 0)
             Deliverables.add(d);
             }
        }
        update Deliverables;
    }

RajivRajiv

Hi you can use this code :

 

 

trigger UpdateDeliverables on Opportunity (after insert, after update) {

List<Id> oppIds = new List<Id>();
for(opportunity opp : Trigger.New){
oppIds.add(opp.Id);

}

List<OpportunityLineItem> Deliverables = new List<OpportunityLineItem>();

List<OpportunityLineItem> oppline = [Select OpportunityId from OpportunityLineItem where OpportunityId IN:oppIds];

for(integer i=0; i<oppline.size()>0; i++){
Deliverables.add(oppline[i]);

}
if(Deliverables.size() > 0)
update Deliverables;

 

If it works mark as a solution.

This was selected as the best answer
jneilan22jneilan22

Thank you Rajiv, your code worked!  I did have to change the i<oppline.size()>0 as it was not recognizing the "0", so instead I made it i<oppline.size() && oppline.size() >0.  Other than that everything seems to work.  Thanks again!

mmrrmmrr

Can you please help me with the same error in my trigger, I am not able to help myself....

 

trigger AD on Contact (after insert, after update) {      
    
       for (Contact c:Trigger.New){
    
    List <Opportunity> oplist=[select id, Ad_Comments_Notes__c, Ad__c from Opportunity where Ad__c=:c.id];             
    List <Opportunity> olist= new list <Opportunity>();
    
    if(oplist.size()>0){
        for (Opportunity op:oplist){
                
         op.Ad_Comments_Notes__c = c.Description;
         olist.add(op); 
        } 
        update olist;
     }
    }
 }

 

Thanks in advance.

amit_sfdcT-2amit_sfdcT-2

Hi,

 

Below are the reason for this error:

 

1. You are using SOQL inside the "for" loop

 

2. Avoid complex logic in triggers. To simplify testing and resuse, triggers should delegate to Apex classes which contain the actual execution logic.

 

3. There should only be one trigger for each object.

 

4. No DML statements inside loops.

 

Follow above best practices to hit any Governor limits.

 

Regards,

Amit Kumar Gupta

Salesforce.com