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
SFDCDevQASFDCDevQA 

System.LimitException: Too many SOQL queries: 101

I'm trying to write the test class on this trigger and have suddenly hit the System.LimitException.  Why is this hitting SOQL limits at line 3?  My starting line essentially.  It's part of a list so shouldn't that prevent the SOQL limit?

Thanks so much,
Amanda Howell

trigger CloneOpportunityRollover on Opportunity (after Insert, after Update) {
   
    List <Opportunity> opportunities = [select Id, Name, Type, LeadSource, Course_Type__c, Opportunity_Rolled__c, Opportunity_Rolled_From__c, AccountID, New_Subject__c, StageName, Term__c, CloseDate, Account.Academic_Term__c, Account.Winter_Course_Start_Date__c, Account.Spring_Course_Start_Date__c, Account.Summer_Course_Start_Date__c, Account.Fall_Course_Start_Date__c, (select Id, opportunity.term__c, opportunity.stagename, opportunity.course_type__c, opportunity.account.Academic_Term__c,unitprice, Quantity, PricebookEntryID, Description From OpportunityLineItems), (Select Id, Contactid, IsPrimary, Role From OpportunityContactRoles) from Opportunity where ID IN :Trigger.newMap.keySet()];
   
   
    List<Opportunity> opportunitiesToUpdate = new List<Opportunity>{};
    List<Opportunity> newOpportunities = new List<Opportunity>{};
    List<OpportunityLineItem> newOpportunityLineitems = new List<OpportunityLineItem>{};
    List<opportunitycontactrole> newOpportunityContactRoles = new List<opportunitycontactrole>{};


    for(Opportunity opp : opportunities){

        if (opp.StageName == 'Commitment' && Opp.Opportunity_Rolled__c == False) {
            Opportunity newOpp;
           
//          try {
           
                newOpp = opp.clone(false);
                if (Opp.StageName == 'Commitment' && Opp.Opportunity_Rolled__c == False && Opp.Course_Type__c == 'Standard') {
                               
                    if ( (opp.term__c == 'Fall' && (opp.account.Academic_Term__c == 'Semester' || opp.account.Academic_Term__c == 'Trimester' )) || (opp.term__c == 'Winter' && opp.account.Academic_Term__c == 'Quarter')) {
                        newOpp.Term__c = 'Spring';
                        newOpp.CloseDate = opp.account.Spring_Course_Start_Date__c;
                        newOpp.Name = opp.Name + ' Spring ' + opp.account.Spring_Course_Start_Date__c.year();
                    } else if ( (opp.term__c == 'Spring' && opp.account.Academic_Term__c == 'Semester') || (opp.term__c == 'Summer' && (opp.account.Academic_Term__c == 'Quarter' || opp.account.Academic_Term__c == 'Trimester')) )  {
                        newOpp.Term__c = 'Fall';
                        newOpp.CloseDate = opp.account.Fall_Course_Start_Date__c;
                        newOpp.Name = opp.Name + ' Fall ' + opp.account.Fall_Course_Start_Date__c.year();
                    } else if ( opp.term__c == 'Fall' && opp.account.Academic_Term__c == 'Quarter')  {
                        newOpp.Term__c = 'Winter';
                        newOpp.CloseDate = opp.account.Winter_Course_Start_Date__c;
                        newOpp.Name = opp.Name + ' Winter ' + opp.account.Winter_Course_Start_Date__c.year();
                    } else if ( opp.term__c == 'Spring' && (opp.account.Academic_Term__c == 'Quarter' || opp.account.Academic_Term__c == 'Trimester'))  {
                        newOpp.Term__c = 'Summer';
                        newOpp.CloseDate = opp.account.Summer_Course_Start_Date__c;
                        newOpp.Name = opp.Name + ' Summer ' + opp.account.Summer_Course_Start_Date__c.year();
                    }
                } else if (Opp.StageName == 'Commitment' && Opp.Opportunity_Rolled__c == False && Opp.Course_Type__c == 'Single Semester') {
                               
                    if ( (opp.term__c == 'Spring' && opp.account.Academic_Term__c == 'Semester') || ( opp.term__c == 'Summer' && opp.account.Academic_Term__c == 'Trimester') || (opp.term__c == 'Fall' && opp.account.Academic_Term__c == 'Quarter') ) {
                        newOpp.Term__c = 'Spring';
                        newOpp.CloseDate = opp.account.Spring_Course_Start_Date__c;
                        newOpp.Name = opp.Name + ' Spring ' + opp.account.Spring_Course_Start_Date__c.year();
                    } else if ( (opp.term__c == 'Fall' && opp.account.Academic_Term__c == 'Semester') || (opp.term__c == 'Spring' && (opp.account.Academic_Term__c == 'Quarter' || opp.account.Academic_Term__c == 'Trimester'))  )  {
                        newOpp.Term__c = 'Fall';
                        newOpp.CloseDate = opp.account.Fall_Course_Start_Date__c;
                        newOpp.Name = opp.Name + ' Fall ' + opp.account.Fall_Course_Start_Date__c.year();
                    } else if ( opp.term__c == 'Summer' && opp.account.Academic_Term__c == 'Quarter')  {
                        newOpp.Term__c = 'Winter';
                        newOpp.CloseDate = opp.account.Winter_Course_Start_Date__c;
                        newOpp.Name = opp.Name + ' Winter ' + opp.account.Winter_Course_Start_Date__c.year();
                    } else if (( opp.term__c == 'Winter' && opp.account.Academic_Term__c == 'Quarter') || ( opp.term__c == 'Fall' && opp.account.Academic_Term__c == 'Trimester'))  {
                        newOpp.Term__c = 'Summer';
                        newOpp.CloseDate = opp.account.Summer_Course_Start_Date__c;
                        newOpp.Name = opp.Name + ' Summer ' + opp.account.Summer_Course_Start_Date__c.year();
                    }
               }
                newOpp.Type = 'Renewal';
                newOpp.StageName = 'Prospecting';
                newOpp.Opportunity_Rolled__c = False;
                newOpp.Opportunity_Rolled_From__c = opp.ID;
                newOpportunities.add(newOpp);
                opp.Opportunity_Rolled__c = True;
                opportunitiesToUpdate.add(opp);
               
//          } catch (Exception e){
//              //ApexPages.addMessages(e);
//              opportunitiesToUpdate = new List<Opportunity>{};
//              newOpportunities = new List<Opportunity>{};
//              newOpportunityLineitems = new List<OpportunityLineItem>{};
//              newOpportunityContactRoles = new List<opportunitycontactrole>{};
//          }
    }}
   
    // setup the save point for rollback
    Savepoint sp = Database.setSavepoint();

//  try {
   
        insert newOpportunities;
        update opportunitiesToUpdate;
       
        Integer size = newOpportunities.size();
       
        for(Integer i = 0; i < size; i++){
           
            Opportunity opp = opportunitiesToUpdate.get(i);
            Opportunity newOpp = newOpportunities.get(i);
           
            List<OpportunityLineItem> lineItems = opp.OpportunityLineItems;
            //System.debug(lineItems.size=' + 'lineItems.size());
            for (OpportunityLineItem oli : lineItems) {
                 OpportunityLineItem newoli = oli.clone(false);
                 Decimal rounddown = oli.quantity *.8;
                    Decimal roundeddown = Rounddown.setScale(0, RoundingMode.DOWN);
                    system.debug(roundeddown);
                 Decimal roundup = oli.quantity *1.2;
                    Decimal roundedup = Roundup.setScale(0, RoundingMode.UP);
                    system.debug(roundedup);
                                                
                    if ( oli.opportunity.term__c == 'Fall' && oli.opportunity.account.Academic_Term__c == 'Semester' && oli.opportunity.course_type__c=='Standard' ) {
                        newOli.Quantity = roundeddown;
                    } else if ( oli.opportunity.term__c == 'Spring' && oli.opportunity.account.Academic_Term__c == 'Semester' && oli.opportunity.course_type__c=='Standard' )  {
                        newOli.Quantity = roundedup;
                    } else if ( oli.opportunity.term__c == 'Fall' && oli.opportunity.account.Academic_Term__c == 'Quarter' && oli.opportunity.course_type__c=='Standard' )  {
                        newOli.Quantity = roundeddown;
                    } else if ( oli.opportunity.term__c == 'Winter' && oli.opportunity.account.Academic_Term__c == 'Quarter' && oli.opportunity.course_type__c=='Standard' )  {
                        newOli.Quantity = roundeddown;
                    } else if ( oli.opportunity.term__c == 'Spring' && oli.opportunity.account.Academic_Term__c == 'Quarter' && oli.opportunity.course_type__c=='Standard' )  {
                        newOli.Quantity = roundeddown;
                    } else if ( oli.opportunity.term__c == 'Summer' && oli.opportunity.account.Academic_Term__c == 'Quarter' && oli.opportunity.course_type__c=='Standard' )  {
                        newOli.Quantity = roundedup;
                    } else if ( oli.opportunity.term__c == 'Fall' && oli.opportunity.account.Academic_Term__c == 'Trimester' && oli.opportunity.course_type__c=='Standard' )  {
                        newOli.Quantity = roundeddown;
                    } else if ( oli.opportunity.term__c == 'Spring' && oli.opportunity.account.Academic_Term__c == 'Trimester' && oli.opportunity.course_type__c=='Standard' )  {
                        newOli.Quantity = roundeddown;
                    } else if ( oli.opportunity.term__c == 'Summer' && oli.opportunity.account.Academic_Term__c == 'Trimester' && oli.opportunity.course_type__c=='Standard' )  {
                        newOli.Quantity = roundedup;
                    }
                 newoli.OpportunityID = newOpp.id;
                 newOpportunityLineitems.add(newoli);
            }
           
            List<opportunitycontactrole> roles = opp.opportunitycontactroles;
            //System.debug("roles.size=" + roles.size());
            for (opportunitycontactrole ocr : roles) {
                 opportunitycontactrole newocr = ocr.clone(false);
                 newocr.Opportunityid = newOpp.id;
                 newOpportunityContactRoles.add(newocr);
            }
        }

       
        insert newOpportunityLineitems;
        insert newopportunitycontactroles;


//  } catch (Exception e){
//      // roll everything back in case of error
//      Database.rollback(sp);
//      //ApexPages.addMessages(e);
//  }
}
Best Answer chosen by SFDCDevQA
logontokartiklogontokartik
Can you split your tests into multiple methods, its not really adviceable to add everything to one method, as it would blow up the limits as it did in your case. 

Create multiple methods of your tests and within every method inside Test.startTest() and Test.stopTest(), add your test code. that way you get new sets of limits for each test condition you are testing. 

Hope this helps.

All Answers

logontokartiklogontokartik
Can you please post your test class. I want to see if its test class thats causing issue.
Bhawani SharmaBhawani Sharma
Are you sure your trigger is not executing recursively and no other triggers are in picture?
Also you are not performing dml operations in loop.
SFDCDevQASFDCDevQA
Here's my test class.  I had to turn off a couple of other triggers it was catching on and was going to back later to look at but this time it's stating the trigger it is testing (that I posted) as having the issue at line 3 which is the first line essentially.  It didn't hit the system limit until I added test parts 5 and 6 to the test class.

@isTest(seeAllData=true)
private class TestCloneOpportunityRollover {

    static testMethod void TestCloneOpportunityRollover() {

        // setup an account
        Account a = new Account();
        a.Name = 'University of California';
        a.Type = 'University';
        a.Industry = 'Other';
        a.Academic_Term__c = 'Quarter';
        a.Spring_Course_Start_Date__c = Date.newInstance(2020,01,01);
        a.Summer_Course_Start_Date__c = Date.newInstance(2020,05,01);
        a.Fall_Course_Start_Date__c = Date.newInstance(2020,09,01);
        a.Winter_Course_Start_Date__c = Date.newInstance(2020,11,01);
        insert a;
       
        //get standard pricebook
        Pricebook2  standardPb = [select id, Name, isActive from Pricebook2 where IsStandard = true limit 1];

       
        Pricebook2 pbk1 = new Pricebook2();
        pbk1.Name='Test Pricebook Entry 1';
        pbk1.Description='Test Pricebook Entry 1';
        pbk1.isActive=true;
        insert pbk1;
       
  
        Product2 prd1 = new Product2() ;
        prd1.Name='Test Product Entry 1';
        prd1.Description='Test Product Entry 1';
        prd1.productCode = 'ABC';
        prd1.isActive = true;
        insert prd1;


        PricebookEntry pbe1 = new PricebookEntry();
        pbe1.Product2ID=prd1.id;
        pbe1.Pricebook2ID=standardPb.id;
        pbe1.UnitPrice=50;
        pbe1.isActive = true;
        insert pbe1;
           
        // create new opp record
        Opportunity opp = new Opportunity();
        Opp.CloseDate = Date.newInstance(2020,01,01);
        opp.StageName = 'Prospecting';
        opp.Name = 'Test Opportunity Clone';
        opp.Type = 'New Business';
        opp.LeadSource = 'Other';
        opp.New_Subject__c = 'Biology';
        opp.New_Textbook__c = 'Text1';
        opp.New_Course_Sequence__c = '1 of 1';
        opp.Opportunity_Rolled__c = False;
        opp.Pricebook2ID = pbe1.Pricebook2ID;
        opp.Term__c = 'Spring';
        opp.Course_Type__c = 'Standard';
        opp.AccountID= a.ID;
        insert opp;
       
       
       

        // create a line item for the opp
        OpportunityLineItem oli1 = new OpportunityLineItem();
        oli1.Quantity = 200;
        oli1.OpportunityID = opp.id;
        oli1.PricebookentryID = pbe1.id;
        oli1.UnitPrice = 10;
        insert oli1;
       
         // insert contact
        Contact[] cont = new Contact[]
        {
        new Contact(LastName = 'testcontact1', AccountID=a.id),
        new Contact(LastName = 'testcontact2', AccountID=a.id)
        };   
        insert cont;   
        // insert contact role    
        OpportunityContactRole[] ocr = new OpportunityContactRole[]
        {
        new OpportunityContactRole(Role ='Business User',OpportunityID=Opp.id ,Contactid = cont[0].id ,isprimary = True),
        new OpportunityContactRole(Role ='Business User',OpportunityID=Opp.id ,Contactid = cont[1].id ,isprimary = False)
        };
        insert ocr;   

        // Construct the standard controller
        ApexPages.StandardController con = new ApexPages.StandardController(opp);


            
        // Switch to test context
        Test.startTest();
        // Cause automatic copy via stage of commitment
        opp.StageName = 'Commitment';
        update opp;
       
        Opportunity newopp1 = [select id, Type, StageName, CloseDate, Term__c, Opportunity_Rolled_From__c from Opportunity WHERE Opportunity_Rolled_From__c = :opp.id];
        System.assertNotEquals(newopp1, null);
        System.assertEquals('Renewal', newopp1.type);
        System.assertEquals('Prospecting', newopp1.stagename);
        System.assertEquals(a.Summer_Course_Start_Date__c, newopp1.closedate);
        System.assertEquals(opp.id, newopp1.Opportunity_Rolled_From__c);
        System.assertEquals('Summer', newopp1.Term__c);
        // check that the line item was created
        List<OpportunityLineItem> newOli1 = [Select oli.Id From OpportunityLineItem oli where OpportunityID = :newopp1.id];
        System.assertEquals(newOli1.size(),1);
        List<OpportunityContactRole> newOCR1 = [Select ocr.Id From OpportunityContactRole ocr where OpportunityID = :newopp1.id];
        System.assertEquals(newOCR1.size(),2);
 
 
  // Switch to 2nd test
        // Using the Summer Term of the new Opp cause automatic copy via stage of commitment
  newopp1.StageName = 'Commitment';
  newopp1.New_Textbook__c = 'Text1';
        newopp1.New_Course_Sequence__c = '1 of 1'; 
        update newopp1;

        Opportunity newOpp2 = [select id, Type, StageName, CloseDate, Term__c, Opportunity_Rolled_From__c from Opportunity WHERE Opportunity_Rolled_From__c = :newopp1.id];
        System.assertNotEquals(newOpp2, null);
        System.assertEquals('Renewal', newopp2.type);
        System.assertEquals('Prospecting', newopp2.stagename);
        System.assertEquals(a.Fall_Course_Start_Date__c, newopp2.closedate);
        System.assertEquals(newopp1.id, newopp2.Opportunity_Rolled_From__c);
        System.assertEquals('Fall', newopp2.Term__c);
        // check that the line item was created
        List<OpportunityLineItem> newOLI2 = [Select oli.Id From OpportunityLineItem oli where OpportunityID = :newOpp2.id];
        System.assertEquals(newOLI2.size(),1);
        List<OpportunityContactRole> newOCR2 = [Select ocr.Id From OpportunityContactRole ocr where OpportunityID = :newOpp2.id];
        System.assertEquals(newOCR2.size(),2);
 
  // Switch to 3rd test
        // Using the Fall Term of the new Opp cause automatic copy via stage of commitment
  newopp2.StageName = 'Commitment';
  newopp2.New_Textbook__c = 'Text1';
        newopp2.New_Course_Sequence__c = '1 of 1'; 
        update newopp2;

        Opportunity newOpp3 = [select id, Type, StageName, CloseDate, Term__c, Opportunity_Rolled_From__c from Opportunity WHERE Opportunity_Rolled_From__c = :newopp2.id];
        System.assertNotEquals(newOpp3, null);
        System.assertEquals('Renewal', newopp3.type);
        System.assertEquals('Prospecting', newopp3.stagename);
        System.assertEquals(a.Winter_Course_Start_Date__c, newopp3.closedate);
        System.assertEquals(newopp2.id, newopp3.Opportunity_Rolled_From__c);
        System.assertEquals('Winter', newopp3.Term__c);
        // check that the line item was created
        List<OpportunityLineItem> newOLI3 = [Select oli.Id From OpportunityLineItem oli where OpportunityID = :newOpp3.id];
        System.assertEquals(newOLI3.size(),1);
        List<OpportunityContactRole> newOCR3 = [Select ocr.Id From OpportunityContactRole ocr where OpportunityID = :newOpp3.id];
        System.assertEquals(newOCR3.size(),2);
 
  // Switch to 4th test
        // Using the Winter Term of the new Opp cause automatic copy via stage of commitment
  newopp3.StageName = 'Commitment';
  newopp3.New_Textbook__c = 'Text1';
        newopp3.New_Course_Sequence__c = '1 of 1'; 
        update newopp3;

        Opportunity newopp4 = [select id, Type, StageName, CloseDate, Term__c, Opportunity_Rolled_From__c from Opportunity WHERE Opportunity_Rolled_From__c = :newopp3.id];
        System.assertNotEquals(newopp4, null);
        System.assertEquals('Renewal', newopp4.type);
        System.assertEquals('Prospecting', newopp4.stagename);
        System.assertEquals(a.Spring_Course_Start_Date__c, newopp4.closedate);
        System.assertEquals(newopp3.id, newopp4.Opportunity_Rolled_From__c);
        System.assertEquals('Spring', newopp4.Term__c);
        // check that the line item was created
        List<OpportunityLineItem> newOLI4 = [Select oli.Id From OpportunityLineItem oli where OpportunityID = :newopp4.id];
        System.assertEquals(newOLI4.size(),1);
        List<OpportunityContactRole> newOCR4 = [Select ocr.Id From OpportunityContactRole ocr where OpportunityID = :newopp4.id];
        System.assertEquals(newOCR4.size(),2);
 
  // Switch to 5th test
        // Using the Spring Term of the new Opp and changing course type to Single Semester then cause automatic copy via stage of commitment
  newopp4.course_type__c = 'Single Semester';
        update newopp4;
  newopp4.StageName = 'Commitment';
  newopp4.New_Textbook__c = 'Text1';
        newopp4.New_Course_Sequence__c = '1 of 1'; 
        update newopp4;

        Opportunity newopp5 = [select id, Type, StageName, CloseDate, Term__c, Opportunity_Rolled_From__c from Opportunity WHERE Opportunity_Rolled_From__c = :newopp4.id];
        System.assertNotEquals(newopp5, null);
        System.assertEquals('Renewal', newopp5.type);
        System.assertEquals('Prospecting', newopp5.stagename);
        System.assertEquals(a.Fall_Course_Start_Date__c, newopp5.closedate);
        System.assertEquals(newopp4.id, newopp5.Opportunity_Rolled_From__c);
        System.assertEquals('Fall', newopp5.Term__c);
        // check that the line item was created
        List<OpportunityLineItem> newOLI5 = [Select oli.Id From OpportunityLineItem oli where OpportunityID = :newopp5.id];
        System.assertEquals(newOLI5.size(),1);
        List<OpportunityContactRole> newOCR5 = [Select ocr.Id From OpportunityContactRole ocr where OpportunityID = :newopp5.id];
        System.assertEquals(newOCR5.size(),2);
 
  // Switch to 6th test
        // Change Term to Winter of the new Opp then cause automatic copy via stage of commitment
  newopp5.Term__c = 'Winter';
  newopp5.StageName = 'Commitment';
  newopp5.New_Textbook__c = 'Text1';
        newopp5.New_Course_Sequence__c = '1 of 1'; 
        update newopp5;

        Opportunity newopp6 = [select id, Type, StageName, CloseDate, Term__c, Opportunity_Rolled_From__c from Opportunity WHERE Opportunity_Rolled_From__c = :newopp5.id];
        System.assertNotEquals(newopp6, null);
        System.assertEquals('Renewal', newopp6.type);
        System.assertEquals('Prospecting', newopp6.stagename);
        System.assertEquals(a.Summer_Course_Start_Date__c, newopp6.closedate);
        System.assertEquals(newopp5.id, newopp6.Opportunity_Rolled_From__c);
        System.assertEquals('Summer', newopp6.Term__c);
        // check that the line item was created
        List<OpportunityLineItem> newOLI6 = [Select oli.Id From OpportunityLineItem oli where OpportunityID = :newopp6.id];
        System.assertEquals(newOLI6.size(),1);
        List<OpportunityContactRole> newOCR6 = [Select ocr.Id From OpportunityContactRole ocr where OpportunityID = :newopp6.id];
        System.assertEquals(newOCR6.size(),2);
       
        // Switch back to runtime context
        Test.stopTest();      

    }

}
logontokartiklogontokartik
Can you split your tests into multiple methods, its not really adviceable to add everything to one method, as it would blow up the limits as it did in your case. 

Create multiple methods of your tests and within every method inside Test.startTest() and Test.stopTest(), add your test code. that way you get new sets of limits for each test condition you are testing. 

Hope this helps.
This was selected as the best answer
SFDCDevQASFDCDevQA
That seemed to have been the issue.  Too much in one method.  With how little it seemingly was doing I didn't think that would matter but I guess it does. Thanks so much!