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
SFDCDevQASFDCDevQA 

System.LimitException: Too many SOQL queries: 101

Can anyone please help me understand why this is hitting SOQL limits?  I repurposed a previously working trigger that I had to do pretty much the same thing again but with a little bit more if then statements and a couple oldmap.get statements.  The prior trigger would allow you to import opportunities and update a date field and that would make the trigger go off with no problems.  But now this one doing pretty much the same thing with a checkbox is hitting SOQL statements when I try to do the import update to a special checkbox field.  Any ideas?

Thanks so much,
Amanda

trigger CloneOpportunityRollover on Opportunity (after Update) {
    
    List <Opportunity> opportunities = [select Id, Name, Type, LeadSource, Roll_Manually__c, Textbook__c, Description, Subject__c, Term_Use__c, Opportunity_Rolled__c,
    Opportunity_Rolled_From__c, AccountID, StageName, Term__c, CloseDate, Account.Academic_Calendar__c, Account.Winter_Course_Start_Date__c, Opportunity_Type__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.Term_Use__c, opportunity.account.Academic_Calendar__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>{};
    Integer CurrentYear = Date.Today().Year();
    
    
    

    for(Opportunity opp : opportunities){
    Integer CloseDateMonth = Opp.CloseDate.Month();
        if (((trigger.oldMap.get(Opp.id).StageName != null && opp.StageName == 'Commitment' &&  opp.Stagename != trigger.oldMap.get(Opp.id).StageName)  || opp.Roll_Manually__c == True) && Opp.Opportunity_Rolled__c == False && (Opp.Opportunity_Type__c == 'Hayden-McNeil' ||
        Opp.Opportunity_Type__c == 'Sapling Learning' || Opp.Opportunity_Type__c == 'Late Nite Labs' || Opp.Opportunity_Type__c == 'Nature Ed' ||
        Opp.Opportunity_Type__c == 'i>clicker')) {
            Opportunity newOpp;
            
            
//          try {
            
                newOpp = opp.clone(false);
                if (Opp.Term_Use__c == 'Used Each Term') {
                                
                    if (opp.term__c == 'Fall' && opp.account.Academic_Calendar__c == 'Semester') {
                        newOpp.Term__c = 'Spring';
                        if (opp.account.Spring_Course_Start_Date__c > opp.closedate){
                        newOpp.CloseDate = opp.account.Spring_Course_Start_Date__c;
                        } else {newopp.CloseDate = date.newInstance(opp.CloseDate.Year()+1, 1, 1) ;}
                    } else if (opp.term__c == 'Winter' && (opp.account.Academic_Calendar__c == 'Quarter' || opp.account.Academic_Calendar__c == 'Trimester')) {
                        newOpp.Term__c = 'Spring';
                        if (opp.account.Spring_Course_Start_Date__c > opp.closedate){
                        newOpp.CloseDate = opp.account.Spring_Course_Start_Date__c;
                        } else if (opp.closedate.month()==1 || opp.closedate.month()==2){
                        newOpp.CloseDate = date.newInstance(opp.CloseDate.Year(), 3, 1) ;
                        } else {newopp.CloseDate = date.newInstance(opp.CloseDate.Year()+1, 3, 1) ;}
                    } else if ( opp.term__c == 'Spring' && opp.account.Academic_Calendar__c == 'Semester' )  {
                        newOpp.Term__c = 'Fall';
                        if (opp.account.Fall_Course_Start_Date__c > opp.closedate){
                        newOpp.CloseDate = opp.account.Fall_Course_Start_Date__c;
                        } else {newopp.CloseDate = date.newInstance(opp.CloseDate.Year(), 9, 1) ;}
                    } else if ( opp.term__c == 'Fall' && (opp.account.Academic_Calendar__c == 'Quarter' || opp.account.Academic_Calendar__c == 'Trimester'))  {
                        newOpp.Term__c = 'Winter';
                        if (opp.account.Winter_Course_Start_Date__c > opp.closedate){
                        newOpp.CloseDate = opp.account.Winter_Course_Start_Date__c;
                        } else {newopp.CloseDate = date.newInstance(opp.CloseDate.Year()+1, 1, 1);}
                    } else if ( opp.term__c == 'Spring' && (opp.account.Academic_Calendar__c == 'Quarter' || opp.account.Academic_Calendar__c == 'Trimester'))  {
                        newOpp.Term__c = 'Summer';
                        if (opp.account.Summer_Course_Start_Date__c > opp.closedate){
                        newOpp.CloseDate = opp.account.Summer_Course_Start_Date__c;
                        } else {newopp.CloseDate = date.newInstance(opp.CloseDate.Year(), 5, 1);}
                    } else if (opp.term__c == 'Summer') {
                        newOpp.Term__c = 'Summer';
                        if (opp.account.Summer_Course_Start_Date__c > opp.closedate){
                        newOpp.CloseDate = opp.account.Summer_Course_Start_Date__c;
                        } else {newopp.CloseDate = date.newInstance(opp.CloseDate.Year()+1, 5, 1) ;}
                    }
                } else if (Opp.Term_Use__c == 'Single Term') {
                                
                    if ( opp.term__c == 'Spring' && opp.account.Academic_Calendar__c == 'Semester') {
                        newOpp.Term__c = 'Spring';
                        if (opp.account.Spring_Course_Start_Date__c > opp.closedate){
                        newOpp.CloseDate = opp.account.Spring_Course_Start_Date__c;
                        } else {newopp.CloseDate = date.newInstance(opp.CloseDate.Year()+1, 1, 1);}
                    } else if (opp.term__c == 'Fall' && (opp.account.Academic_Calendar__c == 'Quarter' || opp.account.Academic_Calendar__c == 'Trimester'))  {
                        newOpp.Term__c = 'Spring';
                        if (opp.account.Spring_Course_Start_Date__c > opp.closedate){
                        newOpp.CloseDate = opp.account.Spring_Course_Start_Date__c;
                        } else {newopp.CloseDate = date.newInstance(opp.CloseDate.Year()+1, 3, 1);}
                    } else if (opp.term__c == 'Fall' && opp.account.Academic_Calendar__c == 'Semester')  {
                        newOpp.Term__c = 'Fall';
                        if (opp.account.Fall_Course_Start_Date__c > opp.closedate){
                        newOpp.CloseDate = opp.account.Fall_Course_Start_Date__c;
                        } else {newopp.CloseDate = date.newInstance(opp.CloseDate.Year()+1, 9, 1);}
                    } else if (opp.term__c == 'Spring' && (opp.account.Academic_Calendar__c == 'Quarter' || opp.account.Academic_Calendar__c == 'Trimester'))  {
                        newOpp.Term__c = 'Fall';
                        if (opp.account.Fall_Course_Start_Date__c > opp.closedate){
                        newOpp.CloseDate = opp.account.Fall_Course_Start_Date__c;
                        } else {newopp.CloseDate = date.newInstance(opp.CloseDate.Year(), 9, 1);}
                    } else if ( opp.term__c == 'Summer' && (opp.account.Academic_Calendar__c == 'Quarter' || opp.account.Academic_Calendar__c == 'Trimester'))  {
                        newOpp.Term__c = 'Summer';
                        if (opp.account.Summer_Course_Start_Date__c > opp.closedate){
                        newOpp.CloseDate = opp.account.Summer_Course_Start_Date__c;
                        } else {newopp.CloseDate = date.newInstance(opp.CloseDate.Year()+1, 5, 1);}
                    } else if (( opp.term__c == 'Winter' && (opp.account.Academic_Calendar__c == 'Quarter' || opp.account.Academic_Calendar__c == 'Trimester')) || ( opp.term__c == 'Fall' && opp.account.Academic_Calendar__c == 'Trimester'))  {
                        newOpp.Term__c = 'Summer';
                        if (opp.account.Summer_Course_Start_Date__c > opp.closedate){
                        newOpp.CloseDate = opp.account.Summer_Course_Start_Date__c;
                        } else if (opp.closedate.month()==1 || opp.closedate.month()==2){
                        newOpp.CloseDate = date.newInstance(opp.CloseDate.Year(), 5, 1) ;
                        } else {newopp.CloseDate = date.newInstance(opp.CloseDate.Year()+1, 5, 1) ;}
                    } else if (opp.term__c == 'Summer' && opp.account.Academic_Calendar__c == 'Semester') {
                        newOpp.Term__c = 'Summer';
                        if (opp.account.Summer_Course_Start_Date__c > opp.closedate){
                        newOpp.CloseDate = opp.account.Summer_Course_Start_Date__c;
                        } else {newopp.CloseDate = date.newInstance(opp.CloseDate.Year()+1, 5, 1) ;}
                    }
               }
                newOpp.Type = 'Renewal';
                newOpp.Name = opp.Name;
                newOpp.StageName = 'In Closing';
                newOpp.Roll_Manually__c = False;
                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_Calendar__c == 'Semester' && oli.opportunity.Term_Use__c=='Used Each Term'  ) {
                        newOli.Quantity = roundeddown;
                    } else if ( oli.opportunity.term__c == 'Spring' && oli.opportunity.account.Academic_Calendar__c == 'Semester' && oli.opportunity.Term_Use__c=='Used Each Term') {
                        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);
//  }
}
nitesh gadkarinitesh gadkari
Hi Amanda,

This occuring due to your code hitting governor limits of 100 soql queries in triggers in case you have not bullkified the code.

Please ,refer to this knowledge article 
https://help.salesforce.com/apex/HTViewSolution?urlname=System-LimitException-Too-many-SOQL-queries-101&language=en_US

Hope this helps you.

Regards
Nitesh
SaranSaran
HI,

It seems that you have used update DML to update the list of opportunity(Next to that try line). since it is an after update trigger. the trigger will be again fired for the list of opportunity.

The trigger become Recursive. So that it is throwing an error.!!!

Hope this might help you!!!
SFDCDevQASFDCDevQA
The thing is this same code just with a few less qualifiers has been working for years without hitting soql limits.  It has to have the update in there or it creates a loop where it endlessly triggers.  I don't understand why this one is hitting soql limits when the other one never did and they are for the same purpose.