+ Start a Discussion
ChickenOrBeefChickenOrBeef 

Deployment error (System.LimitException: Too many SOQL queries: 101)

Hey everyone,

I successfully deployed my first trigger (and test class) a couple weeks ago. I'm now trying to deploy my second trigger (and test class), but I'm receiving this error when valdiating the deployment:

Failure Message: "System.LimitException: Too many SOQL queries: 101", Failure Stack Trace: "Trigger.OppIndustry: line 8, column 1"

From what I understand, my triggers should be bulkified. Perhaps it's a problem with one of my test class? Would someone be kind enough to look through my first trigger and class, and my second trigger and class, and tell me where the problem might be? Thanks!


Here is the first trigger I already deployed successfully:

trigger RenewalProcess on Opportunity (after insert, after update) {
   
   Set<String> allOpps = new Set<String>();
    for(Opportunity renewalOpp : Trigger.new) {
        if (renewalOpp.Renewed_Opportunity__c != null) {
            allOpps.add(renewalOpp.Renewed_Opportunity__c);
         }
    }

    List<Opportunity> potentialOpps = [SELECT Id FROM Opportunity WHERE Id IN :allOpps];

    Map<String,Opportunity> opportunityMap = new Map<String,Opportunity>();
        for (Opportunity o : potentialOpps) {
            opportunityMap.put(o.id,o);
        }
   
    List<Opportunity> firstOppsToUpdate = new List<Opportunity>();

        for (Opportunity renewalOpp : Trigger.new) {
             if (renewalOpp.Renewed_Opportunity__c != null) {
             Opportunity renewedOpp = opportunityMap.get(renewalOpp.Renewed_Opportunity__c);
             renewedOpp.Renewal_Process_Checkbox__c = TRUE;
             firstOppsToUpdate.add(renewedOpp);
           }
           
        }

  update firstOppsToUpdate;
     
     List<Opportunity> oppsToUpdate = new List<Opportunity>();
       
        for (Opportunity renewalOpp : Trigger.new) {
            if (renewalOpp.Renewed_Opportunity__c != null && renewalOpp.StageName.equals('Closed Won')) {
                Opportunity renewedOpp = opportunityMap.get(renewalOpp.Renewed_Opportunity__c);
                renewedOpp.Renewal_Status__c = 'Renewed';
                oppsToUpdate.add(renewedOpp);
            }
            else if(renewalOpp.Renewed_Opportunity__c != null && !renewalOpp.IsClosed) {
                Opportunity renewedOpp = opportunityMap.get(renewalOpp.Renewed_Opportunity__c);
                renewedOpp.Renewal_Status__c = 'In Negotiations';
                oppsToUpdate.add(renewedOpp);
            }
            else if(renewalOpp.Renewed_Opportunity__c != null && (renewalOpp.StageName.equals('Closed Lost') || renewalOpp.StageName.equals('Closed Stalled'))) {
                Opportunity renewedOpp = opportunityMap.get(renewalOpp.Renewed_Opportunity__c);
                renewedOpp.Renewal_Status__c = 'Did Not Renew';
                oppsToUpdate.add(renewedOpp);
            }
           
        }
   
    update oppsToUpdate;
   




Here is the test class I succesfully deployed with that trigger:

@isTest
public class TestRenewalProcess{
    static testMethod void testRenewals() {
       
        Test.startTest();
       
        Contact con1 = new Contact();
        con1.FirstName = 'John';
        con1.LastName = 'Hopkins';
        Insert con1;
       
        Contact con2 = new Contact();
        con2.FirstName = 'Bob';
        con2.LastName = 'Smith';
        Insert con2;
       
        Contact con3 = new Contact();
        con3.FirstName = 'Tom';
        con3.LastName = 'Hill';
        Insert con3;
       
        Opportunity opp1 = new Opportunity();
        opp1.Name = 'Unilever Annual';
        opp1.StageName = 'Closed Won';
        opp1.CloseDate = date.parse('1/30/13');
        opp1.Renewal__c = 'No';
        opp1.Approved__c = TRUE;
        opp1.Lead_Origin__c = 'Inbound Lead';
        opp1.SP__c = TRUE;
        opp1.Primary_Contact__c = con1.Id;
        insert opp1;
       
        Opportunity opp2 = new Opportunity();
        opp2.Name = 'Unilever Annual Renewal';
        opp2.StageName = 'Closed Won';
        opp2.CloseDate = date.parse('6/30/13');
        opp2.Renewal__c = 'Yes';
        opp2.Approved__c = TRUE;
        opp2.Lead_Origin__c = 'Inbound Lead';
        opp2.SP__c = TRUE;
        opp2.Primary_Contact__c = con1.Id;
        opp2.Renewed_Opportunity__c = opp1.Id;
        insert opp2;
       
        Opportunity opp3 = new Opportunity();
        opp3.Name = 'Viacom Annual';
        opp3.StageName = 'Closed Won';
        opp3.CloseDate = date.parse('4/30/13');
        opp3.Renewal__c = 'No';
        opp3.Approved__c = TRUE;
        opp3.Lead_Origin__c = 'Inbound Lead';
        opp3.Primary_Contact__c = con2.Id;
        opp3.SP__c = TRUE;
        insert opp3;
       
        Opportunity opp4 = new Opportunity();
        opp4.Name = 'Viacom Annual Renewal';
        opp4.StageName = 'Call Scheduled';
        opp4.CloseDate = date.parse('7/30/13');
        opp4.Renewal__c = 'Yes';
        opp4.Lead_Origin__c = 'Inbound Lead';
        opp4.Primary_Contact__c = con2.Id;
        opp4.Renewed_Opportunity__c = opp3.Id;
        insert opp4;
       
        Opportunity opp5 = new Opportunity();
        opp5.Name = 'Sony Annual';
        opp5.StageName = 'Closed Won';
        opp5.CloseDate = date.parse('4/30/13');
        opp5.Renewal__c = 'No';
        opp5.Approved__c = TRUE;
        opp5.Lead_Origin__c = 'Inbound Lead';
        opp5.Primary_Contact__c = con3.Id;
        opp5.SP__c = TRUE;
        insert opp5;
       
        Opportunity opp6 = new Opportunity();
        opp6.Name = 'Sony Annual Renewal';
        opp6.StageName = 'Closed Lost';
        opp6.CloseDate = date.parse('9/30/13');
        opp6.Renewal__c = 'Yes';
        opp6.Lead_Origin__c = 'Inbound Lead';
        opp6.Lost_Stalled_Detail__c = 'yo mama';
        opp6.Primary_Contact__c = con3.Id;
        opp6.Renewed_Opportunity__c = opp3.Id;
        opp6.Who_We_Lost_To__c = 'test';
        opp6.Amount = 5000;
        insert opp6;
       
        Test.stopTest();

}
}




Here is the trigger I'm trying to deploy now:

trigger OppIndustry on Opportunity (before insert, before update) {
   
    Set<String> relevantAccountIds = new Set<String>();
     for (Opportunity o : Trigger.new) {
            relevantAccountIds.add(o.AccountId);
        }
   
    List<account> accountsWithIndustry = [SELECT Id, Industry FROM Account WHERE Id IN :relevantAccountIds];
   
    map<string,string> accountsMap = new map<string,string>();
     for(Account a : accountsWithIndustry) {
        accountsMap.put(a.Id,a.Industry);
        }
   
    for(Opportunity oppInTrigger : Trigger.new) {
        String oppAccountIndustry = accountsMap.get(oppInTrigger.AccountId);
        IF(oppAccountIndustry != NULL && oppAccountIndustry != 'Agency') {
            oppInTrigger.Opportunity_Industries__c = oppAccountIndustry;
        }
    }
}




Here is the test class for that trigger I'm trying to deploy:

@isTest
public class TestOppIndustry{
    static testMethod void testOpps() {
       
        Test.startTest();
       
        Contact con1 = new Contact();
        con1.FirstName = 'John';
        con1.LastName = 'Hopkins';
        Insert con1;
       
        Account acc1 = new Account();
        acc1.Name = 'Unilever';
        acc1.Region__c = 'CSA';
        acc1.Industry = 'Consumer Goods';
        acc1.Status__c = 'Prospect';
        acc1.Website = 'www.unilever.com';
        acc1.FB_Page_1_Fans__c = 300;
        acc1.FB_Page_1_Link__c = 'www.facebook.com/unilever';
        insert acc1;
       
        Opportunity opp1 = new Opportunity();
        opp1.Name = 'Unilever Annual';
        opp1.StageName = 'Active Discussions';
        opp1.CloseDate = date.parse('1/30/13');
        opp1.Renewal__c = 'No';
        opp1.Lead_Origin__c = 'Inbound Lead';
        opp1.Primary_Contact__c = con1.Id;
        opp1.AccountId = acc1.Id;
        insert opp1;
       
        Test.stopTest();
    }
}
Best Answer chosen by ChickenOrBeef
ChickenOrBeefChickenOrBeef
Hey Ninja,

I looked around for something similar, and I found this class:

http://help.salesforce.com/apex/HTViewSolution?id=000133752&language=en_US

That did the trick! 

So I'm guessing I just need to call that class for every After trigger I create?

Thanks,
Greg

All Answers

sfdc_ninjasfdc_ninja
Looks to be an issue with trigger recursion.  Your before trigger is firing, then your after trigger, in your after trigger, you are calling updates on opportunities, which calls the before triggers again.  

As a general practice, you shopuld only have one trigger per object.  So instead of adding a second trigger, I would edit your current trigger with the use of context variables such as trigger.isAfter, trigger.isUpdate to combine your logic into a singlew trigger.  This will not solve your recursion issue, but its getting you going on best practices.

For the recursion, I would leverage a static variable to know if the code has already executed within the context.

I would create a class like below

public class TriggerContextUtility {

      private static boolean firstRun = true;

      public static boolean isFirstRun() {
            return firstRun;
      }

      public static void setFirstRunFalse(){
            firstRun = false;
      }
}


Then within your after trigger, you can make use of this class in this way


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

      if(TriggerContextUtility.isFirstRun()){
            //Add your logic and DML
      }
      TriggerContextUtility.TriggerContextUtility();

}


ChickenOrBeefChickenOrBeef
Hey Ninja,

It definitely seems like I have a lot more to learn before I can roll out more triggers. I had no idea about that best practice (updating one trigger instead of creating separate triggers). I'm also not experienced with creating classes, besides test classes of course.

So, a couple questions:

1) You said the best practice is to combine your triggers, but you said that wouldn't fix my recursion issue. Why is that?

2) I created the class you wrote in the developer console, and then I tried to edit my RenewalProcess trigger to include the edits you wrote (see below), but I'm getting this error: Method does not exist or incorrect signature TriggerContextUtility.TriggerContextUtility()

Here is the current version of the trigger (I bolded the parts I added):


trigger RenewalProcess on Opportunity (after insert, after update) {
   
   if(TriggerContextUtility.isFirstRun()){
   
   Set<String> allOpps = new Set<String>();
    for(Opportunity renewalOpp : Trigger.new) {
        if (renewalOpp.Renewed_Opportunity__c != null) {
            allOpps.add(renewalOpp.Renewed_Opportunity__c);
         }
    }

    List<Opportunity> potentialOpps = [SELECT Id FROM Opportunity WHERE Id IN :allOpps];

    Map<String,Opportunity> opportunityMap = new Map<String,Opportunity>();
        for (Opportunity o : potentialOpps) {
            opportunityMap.put(o.id,o);
        }
   
    List<Opportunity> firstOppsToUpdate = new List<Opportunity>();

        for (Opportunity renewalOpp : Trigger.new) {
             if (renewalOpp.Renewed_Opportunity__c != null) {
             Opportunity renewedOpp = opportunityMap.get(renewalOpp.Renewed_Opportunity__c);
             renewedOpp.Renewal_Process_Checkbox__c = TRUE;
             firstOppsToUpdate.add(renewedOpp);
           }
           
        }

  update firstOppsToUpdate;
     
     List<Opportunity> oppsToUpdate = new List<Opportunity>();
       
        for (Opportunity renewalOpp : Trigger.new) {
            if (renewalOpp.Renewed_Opportunity__c != null && renewalOpp.StageName.equals('Closed Won')) {
                Opportunity renewedOpp = opportunityMap.get(renewalOpp.Renewed_Opportunity__c);
                renewedOpp.Renewal_Status__c = 'Renewed';
                oppsToUpdate.add(renewedOpp);
            }
            else if(renewalOpp.Renewed_Opportunity__c != null && !renewalOpp.IsClosed) {
                Opportunity renewedOpp = opportunityMap.get(renewalOpp.Renewed_Opportunity__c);
                renewedOpp.Renewal_Status__c = 'In Negotiations';
                oppsToUpdate.add(renewedOpp);
            }
            else if(renewalOpp.Renewed_Opportunity__c != null && (renewalOpp.StageName.equals('Closed Lost') || renewalOpp.StageName.equals('Closed Stalled'))) {
                Opportunity renewedOpp = opportunityMap.get(renewalOpp.Renewed_Opportunity__c);
                renewedOpp.Renewal_Status__c = 'Did Not Renew';
                oppsToUpdate.add(renewedOpp);
            }
           
        }
   
    update oppsToUpdate;
      
   }
   
    TriggerContextUtility.TriggerContextUtility();

   
}
ChickenOrBeefChickenOrBeef
Hey Ninja,

I looked around for something similar, and I found this class:

http://help.salesforce.com/apex/HTViewSolution?id=000133752&language=en_US

That did the trick! 

So I'm guessing I just need to call that class for every After trigger I create?

Thanks,
Greg
This was selected as the best answer
sfdc_ninjasfdc_ninja
Excellent.  Glas to hear it.  Yes I geenrally try to use a static variable in this way for controlling recursion