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
HarmpieHarmpie 

APEX trigger: limit SOQL queries, how to solve this?

Hi,
 
I am trying to make a trigger that will update fields on a related object. The functionality of the trigger is working fine, however, it contains 2 SOQL queries. These SOQL queries give me trouble. The involved objects (Productline__c) will be userted daily, 10000's of them. This fires the trigger, but when the batchsize of the dataloader is set to 200, exceptions will start occurring, because I am exceeding the 20 SOQL query limit. When I put the batchsize on 10, no exceptions occur based on the SOQL limit, but I will run into the API call limit. How can i circumvent this catch-22?
 
Here's the code:
Code:
// -------------------------------------------------------------------------------------------
// This trigger makes sure that changes to order amounts/quantities (based on productlines
// in the order are aggregated at Budget level. This is necessary because Master detail detail
// relations are unsupported by SFDC, as a consequence Roll-up will not work.
// -------------------------------------------------------------------------------------------
trigger productLineSaved on Productline__c (after insert, after update, after delete, 
after undelete) {

 if(!Trigger.isInsert) {
  integer z = Trigger.old.size();
  
  for (integer i=0; i<z; i++) {
   // Set old numbers to zero
   Double qtyOld = 0;
   Double amOld =  0;
   
   if(Trigger.isUpdate) {
    // Retrieve old values in case of an update
    Productline__c plOld = Trigger.old[i];
    qtyOld = plOld.Total_Quantity_Ordered__c;
    amOld =  plOld.Total_amount__c;
   }
   
   Productline__c plNew;
   if(!Trigger.isDelete) {
    plNew = Trigger.new[i];
   } else {
    plNew = Trigger.old[i];
   }
  
   // Fetch the Budget which we are updating.
   CI_Order__c ord = [SELECT Id,Budget__c FROM CI_Order__c WHERE Id = :plNew.Order__c  LIMIT 1];
   Budget__c bud = [SELECT Id,Total_Amount_Orders__c,Total_Quantity_Orders__c FROM Budget__c WHERE Id = :ord.Budget__c  LIMIT 1]; 
  
   // Make calculation for the figures on Budget level
   Double budgetOrderAmountBefore = 0;
   Double budgetOrderQuantityBefore = 0; 
   budgetOrderAmountBefore = bud.Total_Amount_Orders__c; 
   budgetOrderQuantityBefore = bud.Total_Quantity_Orders__c;
   if(!Trigger.isDelete) {
    bud.Total_Amount_Orders__c = (budgetOrderAmountBefore - amOld) + plNew.Total_amount__c;
    bud.Total_Quantity_Orders__c = (budgetOrderQuantityBefore - qtyOld) + plNew.Total_Quantity_Ordered__c;
   } else {
    // In case of a delete the calculation is a bit different
    bud.Total_Amount_Orders__c = (budgetOrderAmountBefore - plNew.Total_amount__c);
    bud.Total_Quantity_Orders__c = (budgetOrderQuantityBefore - plNew.Total_Quantity_Ordered__c);
   }
   update bud;
  }
 }
 if(Trigger.isInsert) {
  integer z = Trigger.new.size();
  
  for (integer i=0; i<z; i++) {
   // Set old numbers to zero
   Double qtyOld = 0;
   Double amOld =  0;

   Productline__c plNew;
   plNew = Trigger.new[i];
  
   // Fetch the Budget which we are updating.
   CI_Order__c ord = [SELECT Id,Budget__c FROM CI_Order__c WHERE Id = :plNew.Order__c  LIMIT 1];
   Budget__c bud = [SELECT Id,Total_Amount_Orders__c,Total_Quantity_Orders__c FROM Budget__c WHERE Id = :ord.Budget__c  LIMIT 1]; 
  
   // Make calculation for the figures on Budget level
   Double budgetOrderAmountBefore = 0;
   Double budgetOrderQuantityBefore = 0; 
   budgetOrderAmountBefore = bud.Total_Amount_Orders__c; 
   budgetOrderQuantityBefore = bud.Total_Quantity_Orders__c;

   bud.Total_Amount_Orders__c = (budgetOrderAmountBefore - amOld) + plNew.Total_amount__c;
   bud.Total_Quantity_Orders__c = (budgetOrderQuantityBefore - qtyOld) + plNew.Total_Quantity_Ordered__c;

   update bud;
  }
 }
}

 


Message Edited by Harmpie on 11-06-2008 01:01 AM
JimRaeJimRae
Look at using maps for your selected items, outside of your for loop.  Looks like you are geting CI_Order__c with an id called plNew.Order__c.  if you make the ord variable a set, and create a set of plNew.Order__c ids you could use an in query to get all of the objects with one call.  Same for your bud variable.
Then loop through the maps you create and do any calculations and updates.  Place all of the updated objects in a new set  and update the entire set with one update call.
Do the same thing in both sections (insert and update) of your code, and you should get down to 4 soql queries, and 2 dml executions, regardless of the batch size.
 
HarmpieHarmpie
Thanks Jim. Fixed it thanks to your help.