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
Mayank.msMayank.ms 

Trigger deployment issue

Hello Guys,

When I validate the trigger with test class on LIVE, getting error for Too many SQL Quries 101. Can you please help me to update the trigger.

Trigger: UpdateContactOwnerForTrial that wants to validate on LIVE.
trigger UpdateContactOwnerForTrial on Opportunity (after insert, after Update) {
    
     List<Contact> conList = new List<Contact>([select Id,ownerId from Contact where Id In (select ContactId from OpportunityContactRole where OpportunityId IN :Trigger.newMap.keySet())]); 
  	 List<Contact> contactsToUpdate = new List<Contact>{};
    
    for(Opportunity opp : trigger.new)
    {   
          for(Contact c: conList){
              c.ownerId = opp.ownerId;
              contactsToUpdate.add(c);
        }    
    }
   update contactsToUpdate; 

}

Test class
@isTest
public class TestUpdateContactOwnerForTrial {
	static testMethod void TestUpdateContactOwnerForTrialTest()
	{    
		Account a = new Account(Name = 'TestUpdateContactOwnerForTrial');
		a.Type = 'Customer';
		insert a;

		Contact newContact = new Contact(Lastname='testLastName', AccountId = a.Id );
		insert newContact;
	
        String opportunityName = 'TestUpdateContactOwnerForTrial';
        Opportunity o1 = new Opportunity(AccountId=a.Id, name=opportunityName, StageName='Closed Won', Accounting_Platform__c='QuickBooks Online (US)', CloseDate = Date.today() );
        insert o1;
        
		OpportunityContactRole ocr = new OpportunityContactRole(OpportunityId = o1.Id, ContactId = newContact.Id, Role='Decision Maker',Isprimary=true );
		insert ocr;
        
    }	
		
}

Error: 

User-added image
trigger billingSubscriptionEmail on Opportunity (before update) {


List<Addon_Opportunity__c> addOppList = new List<Addon_Opportunity__c>([SELECT Name,Amount__c,Quantity__c FROM Addon_Opportunity__c where Id__c IN :Trigger.newMap.keySet()]); 

List<Contact> conList = new List<Contact>([select Email from Contact where Id In (select ContactId from OpportunityContactRole where OpportunityId IN :Trigger.newMap.keySet())]); 

 Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
     Decimal planPrice=0 ;
    String[] toAddresses;
    if(addOppList!=null){
        for(Addon_Opportunity__c addon :addOppList){
                planPrice= planPrice+ addon.Amount__c;
            }    
  }
    for(Opportunity opp : trigger.new){
    
            if(opp.StageName=='Closed Won' && Trigger.oldMap.get(opp.Id).StageName!='Closed Won'){
                
                for(Contact c:conList){
                        if(c.Email.contains('webgility.com')){
                            toAddresses = new String[] {'test3@test.com','test1@test.com'};
                        }else{
                                 toAddresses = new String[] {'test4@test.com','test2@test.com'};
                        }     
                     String billingCycle;
                     String promo='';
                       
                        mail.setToAddresses(toAddresses );   
                        mail.setSubject('New Subscription - '+opp.Accounting_Platform__c+' - '+c.Email);   
                       if(opp.Billing_Cycle__c=='Monthly'){
                         billingCycle = 'Monthly';
                       }else{
                          billingCycle = 'Annual';  
                       }
                       
                      if(opp.promo_code__c!=null){
                          promo = opp.promo_code__c;
                      }else{
                          promo = 'None';
                      } 
                         Decimal P;
                       
                         if(opp.Recurring_Revenue__c==null) opp.Recurring_Revenue__c=0;
                         if(opp.One_Time_Fee__c==null) opp.One_Time_Fee__c=0;
                         P = (integer)opp.Recurring_Revenue__c +(integer)opp.One_Time_Fee__c-(integer)planPrice;
                         String template = 'Billing & Subscription Info:\n';
                         template+= '----------------------------------\n';
                       template+= 'Account Email: '+c.Email+'\n';
                         template+= 'Total Upfront Payment: $'+ opp.One_Time_Fee__c+'\n';  
                         template+= billingCycle+' Recurring Amount: $'+opp.Recurring_Revenue__c+'\n';
                         template+= 'Plan: '+opp.Plan__c+' ($'+P+')\n';
                         template+= 'Plan Tier: '+opp.Plan_Tier__c+'\n';
                         template+= '----------------------------------\n';
                         for(Addon_Opportunity__c addon :addOppList){
                             String qty='';
                             if(addon.Name=='Installation'){
                                addon.Name = 'Installation ,setup and traning';
                             }
                             if(addon.Name=='Add-on Users' || addon.Name=='Add-on Profile')
                             {
                                qty =  String.valueOf(addon.Quantity__c.format())+' ';
                             } 
                             template+= qty+addon.Name+': $'+addon.Amount__c;
                             template+= '\n';
                        }
                        template+= '\n';
                        template+= '----------------------------------\n';
                        template+= 'Recurring billing start date :'+opp.Purchase_Date__c+'\n';
                        template+= 'Promo Code :'+promo+'\n';
                        template+= 'SF Opportunity: '+'https://webgility.my.salesforce.com/'+Trigger.oldMap.get(opp.Id).Id;
                        List<String> args = new List<String>();
                   String formattedHtml = String.format(template, args);
                   
                    mail.setPlainTextBody(formattedHtml);
                         
                    } 
                   if(BillingEmailTriggerHandler.isFirstTime)
                        {
                          BillingEmailTriggerHandler.isFirstTime = false;  
                          Messaging.SendEmail(new Messaging.SingleEmailMessage[] {mail});  
                       }  
            }

    }

}
Issue is getting on the line no. 6
List<Contact> conList = new List<Contact>([select Email from Contact where Id In (select ContactId from OpportunityContactRole where OpportunityId IN :Trigger.newMap.keySet())]); 

Thanks 








 
Best Answer chosen by Mayank.ms
Amit Chaudhary 8Amit Chaudhary 8
Hi Kamran Siddiqui,

It look like you trigger become resursive. Please check below blog how to resolve this issue.
http://amitsalesforce.blogspot.in/2015/03/how-to-stop-recursive-trigger-in.html

you can create a class with a static Boolean variable with default value true. In the trigger, before executing your code keep a check that the variable is true or not. Once you check make the variable false.

you can also try below test class
@isTest
public class TestUpdateContactOwnerForTrial {
	static testMethod void TestUpdateContactOwnerForTrialTest()
	{    
		Account a = new Account(Name = 'TestUpdateContactOwnerForTrial');
		a.Type = 'Customer';
		insert a;

		Contact newContact = new Contact(Lastname='testLastName', AccountId = a.Id );
		insert newContact;
	
		Test.StartTest();
			String opportunityName = 'TestUpdateContactOwnerForTrial';
			Opportunity o1 = new Opportunity(AccountId=a.Id, name=opportunityName, StageName='Closed Won', Accounting_Platform__c='QuickBooks Online (US)', CloseDate = Date.today() );
			insert o1;
	 
			
			OpportunityContactRole ocr = new OpportunityContactRole(OpportunityId = o1.Id, ContactId = newContact.Id, Role='Decision Maker',Isprimary=true );
			insert ocr;
		Test.StopTest();
    }	
}


Please let us know if this will help you

Thanks,
Amit Chaudhary

All Answers

Stanley JacobsStanley Jacobs
Hi,

First, I'm not sure those code causing error "Too many SOQL Query". Do you have another trigger on Account or Contact?

Using developer console, you can check which SOQL get executed how many times. So you can understand the root cause. Here is step by step to check SOQL count execution:
1. Go to setup >> debug log >> new debug log for your user
2. Then click Filters
User-added image
Set Apex Code and Apex Profiling to Finest
3. Run your test class from developer console
4. Open your log and see this screenshot. You will have information how many times soql being executed.
User-added image
Mayank.msMayank.ms
Hi Stanley, Thanks for the reply.

I have followed step by step it and the result as below. But how to reslove it. You can see the no. of queries result

User-added image



 
Rahul Sangwan7341Rahul Sangwan7341
Hi Kamran,

Use Test.Start and Test.Stop in your class before inserting opportunity in test class. It will increase the SOQL limit. And i think there is loop in which trigger is running so you can use static varibale to stop its execution again and again.
Mayank.msMayank.ms
Hi Rahul, Thanks for the reply. I have added Test.Start and Test.Stop in the test for both the test class. Now the error occuring on Trigger UpdateContactOwnerForTrial and onther Trigger test class 'TestEmailServiceAssignmentTest'


User-added image
Rahul Sangwan7341Rahul Sangwan7341
I think it is calling trigger in loop. Can you use 1 static variable and based on that stop the recursive call to trigger.
Stanley JacobsStanley Jacobs
Rahul, you bring up a good point.

Kamran,

This soql executed 14 times.
User-added image

I'm not sure what is the logic behind. Could you locate the highlighted soql in trigger, and copy paste code here.

 
Mayank.msMayank.ms
trigger EmailServiceAssignment on Task (before insert ) {
    
    Set<String> whatIDs = new Set<String>();
    Map<String, Id> userMap = new Map<String, Id>();
    for(User currUser : [SELECT Id, Name FROM User WHERE Name in ('Stephanie DeFranco', 'Gaurav Singh', 'Andrew Cunningham','Anjali Mamnani')])
    {
        userMap.put(currUser.Name, currUser.Id);
    }
    for (Task t : Trigger.new) {
        whatIDs.add(t.whatID);
    }
    
    List<Opportunity> opps = [SELECT Id, StageName,Plan__c,Accounting_Platform__c, OwnerId,Opportunity.Account.OwnerId FROM Opportunity WHERE Id =: whatIDs];
    for(Task t:Trigger.New){
        
        for(Opportunity o :opps){

          if(t.Subject == 'Inbound Customer Request' && o.StageName=='Closed Won'){
                  
                  if(o.Accounting_Platform__c=='QuickBooks Online (US)'  || o.Accounting_Platform__c=='Xero (US)' ||  o.Plan__c=='Pro/Premier' || o.Plan__c=='International'){
                  
                     t.OwnerId = userMap.get('Andrew Cunningham');
                  }else if(o.Plan__c=='POS'|| o.Plan__c =='Enterprise' || o.Plan__c=='pro/premierHosting' || o.Plan__c=='EnterpriseHosting' || o.Plan__c=='NetSuite' || o.Accounting_Platform__c=='') {
                      t.OwnerId = userMap.get('Stephanie DeFranco'); 
                  } else if(o.Accounting_Platform__c=='Shiplark'){
                      t.OwnerId = userMap.get('Gaurav Singh');
                  }
            }else if(t.Subject == 'Inbound Customer Request'){
                    t.OwnerId = o.OwnerId; 
                
            }
            
        }            
    }
 

}
I am using below line in  2-3  triggers for users
 
for(User currUser : [SELECT Id, Name FROM User WHERE Name in ('Stephanie DeFranco', 'Gaurav Singh', 'Andrew Cunningham','Anjali Mamnani')])
    {
        userMap.put(currUser.Name, currUser.Id);
    }

 
Amit Chaudhary 8Amit Chaudhary 8
Hi Kamran Siddiqui,

It look like you trigger become resursive. Please check below blog how to resolve this issue.
http://amitsalesforce.blogspot.in/2015/03/how-to-stop-recursive-trigger-in.html

you can create a class with a static Boolean variable with default value true. In the trigger, before executing your code keep a check that the variable is true or not. Once you check make the variable false.

you can also try below test class
@isTest
public class TestUpdateContactOwnerForTrial {
	static testMethod void TestUpdateContactOwnerForTrialTest()
	{    
		Account a = new Account(Name = 'TestUpdateContactOwnerForTrial');
		a.Type = 'Customer';
		insert a;

		Contact newContact = new Contact(Lastname='testLastName', AccountId = a.Id );
		insert newContact;
	
		Test.StartTest();
			String opportunityName = 'TestUpdateContactOwnerForTrial';
			Opportunity o1 = new Opportunity(AccountId=a.Id, name=opportunityName, StageName='Closed Won', Accounting_Platform__c='QuickBooks Online (US)', CloseDate = Date.today() );
			insert o1;
	 
			
			OpportunityContactRole ocr = new OpportunityContactRole(OpportunityId = o1.Id, ContactId = newContact.Id, Role='Decision Maker',Isprimary=true );
			insert ocr;
		Test.StopTest();
    }	
}


Please let us know if this will help you

Thanks,
Amit Chaudhary
This was selected as the best answer
Rahul Sangwan7341Rahul Sangwan7341
Hi Kamran, 

Amit gives a gud example how to stop recursive call of trigger and i am sure it will solve your issue
Mayank.msMayank.ms
Hi Amit,

Thanks a lot. Its succesffuly uploaded on production with static variable class. You are genius :)

Thanks Rahul and Stanley for your help and support.