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
viktorVviktorV 

Too many SOQL queries: 21

I don't understand how this limit works. I have three active triggers (all on tasks), all contains some queries.

I'd like to create some new triggers on opportunities and lineItems but no matters how simple my code is, I always get the query limit error.

What could I make wrong? My triggers doesn't come from any other trigger, so the queries cannot sum up

 

thanks for the answers

bob_buzzardbob_buzzard

Can you post some of the code that is experiencing the issues?  It sounds like you may have some queries embedded in loops etc which are blowing limits when you receive a bulk change.
viktorVviktorV

The simplest code I tried out and gave the error was an Opportunity update if a fields value was 'A'

So the only query was in the update method:

 

//opportunityID contains only one element

for(Opportunity u: toUpdate = [SELECT Id, customField__c FROM Opportunity WHERE Id IN : opportunityID]) {

   u.customField__c+='updated';

}

try{ update toUpdate; } catch ....

 

Of course, the solution needs a more complex code but I don't understand why the simplest code I could write gave me the error.
bob_buzzardbob_buzzard

What object/event is this trigger on - i.e. is it before/after insert/update, which sobject does it apply.

 

It would also be useful to see the full code - snippets often hide valuable details.
viktorVviktorV

The code is missing now (I did a lot of changes and attempts to avoid the error and for now, it's just a batch of expressions)

I will rewrite it from the beginning and post it later!

 

Otherwise the error exists on any mix of before/after insert/update on Opportunities
viktorVviktorV

I've already got the error for the next code:

 

 

trigger defaultProduct on Opportunity (after insert)
{
        
    List<Id> opportunityId=new List<Id>();
    List<Id> pbeID=new List<Id>();
    for(Opportunity t:trigger.new){
            opportunityId.add(t.Id);
            if (t.Product__c == 'XXXX') {
                List<PricebookEntry> entry = 
                  [SELECT Id FROM PricebookEntry WHERE ProductCode = 'XXXX-def' AND CurrencyIsoCode = :t.CurrencyIsoCode AND Pricebook2Id IN:[SELECT Id FROM Pricebook2 WHERE Name LIKE 'XXXX%BASE%']];
                //there is only one record in the entry list
                for(PricebookEntry e:entry){
                    pbeID.add(e.Id);
                }
                
                //then I'd like to add a pricebook to opportunity then insert a new line item
                
            }
    }
            

}

Many thanks for looking through it!

 
Jeremy.NottinghJeremy.Nottingh

The Trigger you just posted has 2 SOQL queries inside a FOR loop. So if this Trigger runs on 11 records, you will have 22 queries, and you'll hit your limit. 

 

Even if you never need to insert 11 Opportunities, this Trigger might run at the same time as other Triggers, and all the queries add up. Bulkify this Trigger so that it will only ever run 2 queries, and do the same for any other Triggers that are running alongside it, and your problem will be solved, I hope.

 

Jeremy
bob_buzzardbob_buzzard

There's good tips for this in the wiki:

 

http://wiki.developerforce.com/index.php/Best_Practice:_Bulkify_Your_Code
viktorVviktorV

As I read, my leading FOR loop just solves the bulkify problem (based on Bobs URL).

On the other hand, the error comes up when I'm inserting just one opportunity, for me that means the trigger should run only once.

Please tell me the running order of embedded queries.

1. The inner runs first, and then the outer runs on the inners result (as on a list)

2. the outer runs first and than the inner runs as many times as many records the outer gave.

I think the first, so for one inserted opportunity I should get 2 queries, but if the second order is right, (based on my DB) I should only get 4 queries...still not 21

 

My other triggers run on tasks and no tasks are created with a simple opportunity insert, there shouldn't be any connection between this and those triggers (and those triggers are also created with the outer FOR loop:  

for(Task t:trigger.new){ ... }
viktorVviktorV

I made a working trigger which adds a default product according to a field of the new opportunity.

The error doesn't come up! :)

 

 

trigger defaultProduct on Opportunity (after insert)
{
    Id oppID;
    Id peID;
    Id pbID;
    Decimal price; 
    for(Opportunity t:trigger.new){
            oppID=t.Id;
            if (t.Product__c == 'XXXX') {
                List<PricebookEntry> entry = 
                  [SELECT Id, Pricebook2Id, UnitPrice FROM PricebookEntry WHERE ProductCode = 'XXXX-def' AND CurrencyIsoCode = :t.CurrencyIsoCode AND Pricebook2Id IN:[SELECT Id FROM Pricebook2 WHERE Name LIKE 'XXXX%BASE%']];
                for(PricebookEntry e:entry){
                    peID=e.Id;
                    pbID=e.Pricebook2Id;
                    price=e.UnitPrice;
                }
                Opportunity toUpdate = [SELECT Id, Pricebook2Id FROM Opportunity WHERE Id= :oppID];
                toUpdate.Pricebook2Id=pbID;
                try{
                    update toUpdate;
                }catch(DMLException e){
                    system.debug('Opportunity was not properly updated.  Error: '+e);
                }
                OpportunityLineItem lineItem = new
                    OpportunityLineItem(OpportunityId=oppID, PricebookEntryId=peID, Quantity=1, UnitPrice=price);
                try{
                    insert lineItem;
                }catch(DMLException e){
                    system.debug('OpportunityLineItem was not added.  Error: '+e);
                }
            }
    }
}

 

 

 

I'll try the recreate my other opportunity trigger which also gives the SOQL query error...
Jeremy_nJeremy_n

You're still doing 3 queries and 2 DML statements for each Opportunity this runs on. It may not give you an error right now, but it won't take too much inserting to put you over the limits. Try to put all of your queries and inserts and updates outside of that FOR loop, and getting in under the governors should be much easier.

 

Believe me, it's possible. Let us know if you need further assistance.

 

Jeremy
viktorVviktorV

thank you for the advice Jeremy!