Workflow/trigger triggering an error on bulk updates - System.LimitException: Too many SOQL queries - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Curtis AveryCurtis Avery 

Workflow/trigger triggering an error on bulk updates - System.LimitException: Too many SOQL queries

Hi,

I have taken over SF admin tasks and yesterday set up a simple workflow which, when a checkbox is ticked will update an order status. The aim is to allow a user to look at a list view of filtered orders, mass-tick the checkbox and have all the order statuses updated and record the date of the change in another field. However, if I try updating more than 8-10 records via a bulk update (the failure number varies) I get the error below:

"There were custom validation error(s) encountered while saving the affected record(s). The first validation error encountered was \"Apex trigger SendOrderEmailTemplateTrigger caused an unexpected exception, contact your administrator: SendOrderEmailTemplateTrigger: System.LimitException: Too many SOQL queries: 101\".

The chap who dealt with SF dev work changed to a different division a while back and while someone will be able to look into this at some point (my apex knowledge is limited to knowing how it's spelt) I was wondering if anyone might know what in the trigger below is preventing mass updates of the type mentioned?

Even if it may all be chinese to me it'll be great to have an idea of what the cause may be...

Grateful for any responses received!




trigger SendOrderEmailTemplateTrigger on Order (after update) {
    
    //  templateId   must be ID of an Email template
    //  targetObjId must be a Contact, User, Lead Id -- also used in merge fields of template recipient.xxxx
    //  whatId    must be an SObject that is used in the merge fields of the template relatedTo.xxxx
    //  fromId    if non null, use current user, otherwise, use this ID (most likely an org wide no reply id)
    //  bcc      not permitted when using templates
    
    
    if(Trigger.isAfter && Trigger.isUpdate){
        
        
        
        for (Order ord : Trigger.new) {
            //if there is an opportunity related to this order then then make opportunitt effective date same as order effective date
            if(ord.OpportunityId != null) 
            {
                //get the opportunity details by opportunity ID in Order Object
                Opportunity oRecord = [SELECT Id, EffectiveDate__c
                                       FROM Opportunity where Id = :ord.OpportunityId];
                
                System.debug(ord.OpportunityId);
                
                //update opportunity EffectiveDate by Order Effective Date
                if(oRecord != null && oRecord.EffectiveDate__c != ord.EffectiveDate){
                    oRecord.EffectiveDate__c = ord.EffectiveDate;
                    Update oRecord;
                }
            } 
            
            Default_Values__c custSettingRef = Default_Values__c.getInstance();
            string apiUserId = custSettingRef.xcompanyx_API_UserID__c;
            
            system.debug('apiUserId- ' + apiUserId + ' CreatedById- '+ ord.CreatedById);
            if(ord.4VVV_Account_Number__c == null && ord.StatusCode == 'A' || (ord.CreatedById == apiUserId && ord.Status == 'QC Pass - To be Activated')){
                // Access the "old" record by its ID in Trigger.oldMap
                Order oldOrder = Trigger.oldMap.get(ord.Id);
                
                List<OrderItem> orderItems = [Select Id,Product2Id from OrderItem where OrderId = :ord.Id];
                
                Boolean orderWithobscured= false;
                
                
                for(OrderItem oi: OrderItems){
                    
                    Product2 pr = [Select Id,Name,Is_obscured__c from Product2 where Id= :oi.Product2Id Limit 1];
                    
                    if(pr.Is_obscured__c)
                    {
                        orderWithobscured = true;
                        break;
                    }
                    
                }
                
                
                Contact con = [Select Id,Email from Contact where ID = :ord.BillToContactId Limit 1];
                system.debug('Contact - ' + con);
                if(con.Email != null)
                {
                    ID orgWideEmailId = [Select Id from OrgWideEmailAddress where Address='noreply@xcompanyx.co.uk'].id;
                    
                    Id templateId;
                    string smsText;
                    
                    string customerServicePhoneNumber = custSettingRef.Customer_Service_Phone_Number__c ;
                    string websiteURL = custSettingRef.Website_URL__c;
                    
                    String[] toRecipients= new String[0];
                    toRecipients.add(con.Id);
                    
                    List<Task> welcomeEmailTask = [SELECT Id, WhatId, Subject FROM Task WHERE (Subject LIKE '%Totally brilliant obscured is just one step away%' OR Subject LIKE '%Welcome To xcompanyx%' OR Subject LIKE '%xcompanyx Line Only%') AND WhatID = :ord.ID];
                    
                    Messaging.SingleEmailMessage email = new Messaging.SingleEmailMessage();
                    // 1. Welcome Email -This is welcome email for customer
                    if((ord.Status == 'New - To Be Provisioned' || ord.Status == 'QC Pass - To be Activated') && welcomeEmailTask.size() == 0 ){
                        templateId = [select id from EmailTemplate where DeveloperName = 'Welcome_Email'].id;
                        smsText = 'Here comes xcompanyx! We’re sorting out the technical stuff and will be in touch. Questions? Call ' + customerServicePhoneNumber + '.';
                    }
                    else if(ord.Status != oldOrder.Status && ord.Status == 'Order Completed - Pending Add To Ability' && orderWithobscured){
                        templateId = [select id from EmailTemplate where DeveloperName = 'Order_Completed_Pending_Add_To_Ability'].id;
                        //if(ord.Delivery_Date__c != null){    
                        //    smsText = 'xcompanyx obscured is on its way! Expect your super duper router on ' + ord.Delivery_Date__c.format() + '. Need a hand? Call ' + customerServicePhoneNumber + '.';
                        //}
                    }
                    else if(ord.Status != oldOrder.Status && ord.Status == 'First Bill Failed Direct Debit'){
                        templateId = [select id from EmailTemplate where DeveloperName = 'First_Bill_Failed_Direct_Debit'].id;
                        if(ord.Affiliate__c != null){    
                            smsText = 'Oh no! We haven’t been able to collect payment so everything’s on hold. Re-enter your details at ' + websiteURL + '/?utm_source=' + ord.Affiliate__c + ' and we’ll get things moving again.';   
                        }
                    }
                    else if(ord.Status != oldOrder.Status && ord.Status == 'Order Checked But Cannot Be Supplied'){
                        templateId = [select id from EmailTemplate where DeveloperName = 'Order_Checked_But_Cannot_Be_Supplied'].id;
                        smsText = 'Oh no! xcompanyx doesn’t reach as far as where you live so we can’t complete your order. All payments will be refunded - sorry for any inconvenience.';       
                    }               
                    else if(ord.Status != oldOrder.Status && ord.Status == 'Customer Cancellation - Close' && orderWithobscured){
                        templateId = [select id from EmailTemplate where DeveloperName = 'Order_Cancellation_Confirmation'].id;
                        if(ord.Affiliate__c != null){
                            smsText = 'Don’t go! We’re so sad you’ve decided to cancel. Big savings and super service is waiting for you right now at ' + websiteURL + '/?utm_source=' + ord.Affiliate__c + '.';       
                        }
                    }
                    else if(ord.Status != oldOrder.Status && ord.Status == 'Order Processed - Awaiting Committed Date' && orderWithobscured){
                        templateId = [select id from EmailTemplate where DeveloperName = 'Order_Pocessed_Awaiting_Committed_Date'].id;
                    }              
                    //RS: 10062019 -- Trigger Email Again if CBA || ABC || Time Slot changes 
                    else if(ord.Status != oldOrder.Status && ord.Status == 'Committed Date Received' || 
                            (ord.Status == 'Committed Date Received' && 
                             (ord.CBA_Committed_Date__c!=oldOrder.CBA_Committed_Date__c 
                              || ord.ABC_Committed_Date__c!=oldOrder.ABC_Committed_Date__c
                              || ord.Time_Slot__c!=oldOrder.Time_Slot__c)))
                    {
                        if(ord.Type_Transfer__c == 'New Installation With New CLI' || ord.Type_Transfer__c == 'New Installation With Existing CLI')
                            templateId = [select id from EmailTemplate where DeveloperName = 'New_installation_Committed_Date_Received'].id;
                        else
                            templateId = [select id from EmailTemplate where DeveloperName = 'Line_Rental_Committed_Date_Received'].id;
                    }
                    else if(ord.Status != oldOrder.Status && ord.Status == 'Issue - Order Delay' && orderWithobscured){
                        templateId = [select id from EmailTemplate where DeveloperName = 'Order_Delayed_OR_Installation_Date'].id;
                    }
                    else if(ord.Status != oldOrder.Status && ord.Status == 'Order Cancelled By Losing Org - Investigate' && orderWithobscured){
                        templateId = [select id from EmailTemplate where DeveloperName = 'Order_Delayed_Cancelled_by_Losing_Org'].id;
                    }                 
                    
                    
                    system.debug('Email TemplateID ' + templateId); 
                    if(templateId != null){
                        email.setToAddresses(toRecipients);
                        email.setTargetObjectId(con.Id);
                        email.setWhatId(ord.Id);
                        email.setorgWideEmailAddressId(orgWideEmailId);
                        email.setTemplateId(templateId);
                        email.setSaveAsActivity(true);      // save email as activity on the targetObjId (i.e. Contact). Note activity can't be saved on Users
                        try{
                            Messaging.sendEmail(new Messaging.SingleEmailMessage[] {email});
                        }
                        catch(Exception  ex){
                            string sMessage = 'ERROR: ' + 'An exception has occurred. Please contact your System Administrator quoting the following message -- '+ ex.getTypeName() + ':'+ex.getMessage() + ':' + ex.getLineNumber() + ':' + ex.getStackTraceString();
                            EmailNotification.SendExceptionDetail(sMessage);
                        }
                        
                        Account orderAccount = [SELECT Phone FROM Account WHERE ID = :ord.AccountID];
                        
                        if(orderAccount.Phone != null && smsText != null)
                        {
                            FastSMS.sendSMS(smsText, '44' + orderAccount.Phone.substring(1,orderAccount.Phone.length()));
                        }
                    }
                }
            }
        }
    }
}
Guy KeshetGuy Keshet
Hi Curtis
I'm afaraid you will reuiqre a salesforce developer to fix this issue

your trigger is firing individual SoQL queireis per order - tht's not good practise precaisly for your scenario when several records are updated at the same time. what happens is that the trigger loops over each updated record and perfroms the queries one by one, so that overall you break the SOQL goivernonr limits

Instead, your developer will need to bulkify the code: the SOQL queries will need to be taken outsoide the for loops, executed once per queries acros all records in the trigger and stored in a map/list variable, and than in the for loop you'd use the data ffrom memory (the variable) instead of firing queries

for example -
the following query should happen only once, outside the loop:  
templateId = [select id from EmailTemplate where DeveloperName = 'Order_Delayed_OR_Installation_Date'].id; 

the following loop should run ither with no ID, or using IN antrigger.new.<id field>: 
SELECT Id, WhatId, Subject FROM Task WHERE (Subject LIKE '%Totally brilliant obscured is just one step away%' OR Subject LIKE '%Welcome To xcompanyx%' OR Subject LIKE '%xcompanyx Line Only%') AND WhatID = :ord.ID
Curtis AveryCurtis Avery
Hello Guy,

Thank you for that reply. I won't pretend I understand how this needs to be done but it allows me to at least speak to they chap who'll be lumbered with doing it and indicate what the issue is!
Ashok PatelAshok Patel
Hello Curtis,

You are writing all your Soql query inside the for loop and you are writing all of the business logic inside for loop . This is the reason you are getting this System.LimitException: Too many SOQL queries: 101\".

Please Refer below best practices for apex trigger :

1) One Trigger Per Object
A single Apex Trigger is all you need for one particular object. If you develop multiple Triggers for a single object, you have no way of controlling the order of execution if those Triggers can run in the same contexts

2) Logic-less Triggers
If you write methods in your Triggers, those can’t be exposed for test purposes. You also can’t expose logic to be re-used anywhere else in your org.

3) Context-Specific Handler Methods
Create context-specific handler methods in Trigger handlers

4) Bulkify your Code
Bulkifying Apex code refers to the concept of making sure the code properly handles more than one record at a time.

5) Avoid SOQL Queries or DML statements inside FOR Loops
An individual Apex request gets a maximum of 100 SOQL queries before exceeding that governor limit. So if this trigger is invoked by a batch of more than 100 Account records, the governor limit will throw a runtime exception

6) Using Collections, Streamlining Queries, and Efficient For Loops
It is important to use Apex Collections to efficiently query data and store the data in memory. A combination of using collections and streamlining SOQL queries can substantially help writing efficient Apex code and avoid governor limits

7) Querying Large Data Sets
The total number of records that can be returned by SOQL queries in a request is 50,000. If returning a large set of queries causes you to exceed your heap limit, then a SOQL query for loop must be used instead. It can process multiple batches of records through the use of internal calls to query and queryMore

8) Use @future Appropriately
It is critical to write your Apex code to efficiently handle bulk or many records at a time. This is also true for asynchronous Apex methods (those annotated with the @future keyword). The differences between synchronous and asynchronous Apex can be found

9) Avoid Hardcoding IDs
When deploying Apex code between sandbox and production environments, or installing Force.com AppExchange packages, it is essential to avoid hardcoding IDs in the Apex code. By doing so, if the record IDs change between environments, the logic can dynamically identify the proper data to operate against and not fail.

Few more Best Practices for Triggers
There should only be one trigger for each object.
Avoid complex logic in triggers. To simplify testing and resuse, triggers should delegate to Apex classes which contain the actual execution logic. See Mike Leach's excellent trigger template for more info.
Bulkify any "helper" classes and/or methods.
Trigers should be "bulkified" and be able to process up to 200 records for each call.
Execute DML statements using collections instead of individual records per DML statement.
Use Collections in SOQL "WHERE" clauses to retrieve all records back in single query
Use a consistent naming convention including the object name (e.g., AccountTrigger)

Also go through the below links:

http://blog.jeffdouglas.com/2010/10/21/force-com-programming-best-practices/

http://amitsalesforce.blogspot.com/2015/06/trigger-best-practices-sample-trigger.html

http://www.iterativelogic.com/salesforce-apex-trigger-best-practices/

https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_triggers_bestpract.htm

https://developer.salesforce.com/page/Apex_Code_Best_Practices

https://developer.salesforce.com/page/Trigger_Frameworks_and_Apex_Trigger_Best_Practices

Please do let me know if it helps you.
Deepali KulshresthaDeepali Kulshrestha
Hi Curtis,

I believe your developer is not following the best coding practices. He is using a SOQL query for each record updated.

Querying data is costly and querying data for every record is not a good practice.

The maximum limit to use SOQL query is 100 and your developer is using SOQL for every record. So if there are more than 100 records then the SOQL limit will reach and thus it is showing an error.

Let me know if you have any other developer to fix this issue or you can ask me to fix it for you.

I hope you find the above solution helpful. If it does, please mark as Best Answer to help others too.

Thanks and Regards,
Deepali Kulshrestha
www.kdeepali.com
Curtis AveryCurtis Avery
Thanks for your replies folks. It looks like the issues are pretty much as I suspected from reading similar cases. I don't have the skills to fix the code so now I need to wait until someone with apex skills is available!