Apex Code Development You need to sign in to do that
Don't have an account?
Lalit Apex Trigger and Error Too many SOQL queries:
Is there a solution to this problem, in our organization we have to do mass update on Custom object to change Owner, or load the record from files or re-calculate the price based on New item cost.
Apex trigger works fine for all the auto-calculation but while doing Mass-Maintenance or updating more than 20 records. it started giving error message like
SampleFormAfter: execution of AfterUpdate
caused by: System.DmlException: Update failed. First exception on row 0 with id a0J800000019TwKEAU; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, Price_Protection_Sales_Form: execution of BeforeUpdate
caused by: System.Exception: Too many SOQL queries: 21
Appreicate if you can help us with below question.
. Is there an easy and clean way to turn-off the Apex Trigger( Changing XML in live enviornment doesn't work nicely, User or Admin(other than SFDC developer can also do this vir UI)
. Is this possible to avoid invoking Trigger for some User(say sample or test which we can use for Mass Maintenance), some of our Mass-maintenance doesn't require trigger to be called.
. Can we change the Governon limit or any trick which can be used in Trigger to remain withing limits.
. Is Salesforce Batch Apex available or ready to use
Thanks
Lalit
if you are doing a select within your 'for' loop, then that is your problem - but don't worry, there is a better way... post what you have and I'll try to help if I can :)
Cheers, Ian
Thanks for your reply.
Yes I am doing a select in For loop. below are the some of the sample code we have.
One Sales Form(Parent) can contain multiple Sample request and we need to add the total field on Child form to auto-calculate Total amount requested in Sales Form(Parent)
public class SampleForma {public static void SampleForm(Sales_Forms__c[] salesform){
Double tot = 0;
for (Sales_Forms__c p :salesform){
for( Sample_Requested__c con :[ Select id, Total__c From Sample_Requested__c p where p.Sales_Form__c = :p.id]){ tot += con.Total__c; }
p.Total_Amount_of_Request__c= tot;
}
}
}
Another example is , in below example for loop will have only one record(will be executed once)
public class SampleRequest {
public static void SampleProgramUpd(Sample_Requested__c[] sampleform){Double tot = 0;
Double p_item_cost = 0;
string TraID1;
for (Sample_Requested__c p :sampleform){string TraID = p.Part__c;
for (Product2 con :[ Select id, Item_Cost__c From Product2 q where q.id = :TraID]){tot = con.Item_Cost__c;}
p.Total__c= tot*p.Quantity__c;
}
For typical mass update, User Updates 200-300 Sales Form(change Owner or price detail), or upload the changes from a CSV file.
My understading is, for such update Trigger will be invokved and since we have 300 records, trigger will be called 300 times and its tough to avoid Too Many SOQL query, please advise
Hi,
What you need to do is calculate all Ids upfront and use one query instead of one within a for loop. Then store the data in a collection, like a map and traverse it using a for loop
Set<ID> salesformIds = new Set<ID>();
for(Sales_Forms__c p : salesform)
{
salesformIds.add(p.Id);
}
if(salesformIds.size() > 0)
{
Map<ID,Sample_Requested__c> mapSalesform = new Map<ID,Sample_Requested__c>([Select Id, Total__c, Sales_Form__c from Sample_Requested__c where Sales_Form__c in :salesformIds]);
..........
..........
..........
}
You can traverse the above map and store calculated values in another map if you want using Sample_Requested__c.Sales_Form__c as unique key.
(Do note that the map size above cannot exceed 1000 as thats the max limit for a collection)
Thanks for your reply, but say for mass update we are updaing 400 Sales form record, my understanding was with each individual update a Trigger call will be made.
below map will work fine if I have say 50 sample parts in a single sales form and below code will invoke only one SOQL call rather than 50 earlier(which was my previous code doing), It will help. But we rarely have more than 3-4 Sample Part(child) for each sales form.
Is there a way to pass USER id(invoking the trigger) or any other parameter to avoid Trigger
Triggers can handle batch inserts/updates, you need to use the same idea within your trigger to gather all the pieces together such that you just hit the database once within your trigger...
Have a look at this - HTH
Ian
Thanks Ian for the reply.
Let me explore what best can I do for all the Triggers we have in place, and modification it needs.
It make sense to build more Logic in Trigger body (to collect all the ID's and collection record in Apex trigger itself) rather than calling Apex class for each update.