You need to sign in to do that
Don't have an account?
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.
Test class
Error:
List<Contact> conList = new List<Contact>([select Email from Contact where Id In (select ContactId from OpportunityContactRole where OpportunityId IN :Trigger.newMap.keySet())]);
Thanks
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:
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
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
Please let us know if this will help you
Thanks,
Amit Chaudhary
All Answers
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
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.
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
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.
Kamran,
This soql executed 14 times.
I'm not sure what is the logic behind. Could you locate the highlighted soql in trigger, and copy paste code here.
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
Please let us know if this will help you
Thanks,
Amit Chaudhary
Amit gives a gud example how to stop recursive call of trigger and i am sure it will solve your issue
Thanks a lot. Its succesffuly uploaded on production with static variable class. You are genius :)
Thanks Rahul and Stanley for your help and support.