+ Start a Discussion
VarunCVarunC 

a simple trigger causing exceptions

Hi .. I'm getting Max SOQL queries errors (Too many SOQL queries: 21) from my triggers.

My trigger is this:

trigger trgAccountCalculations on Account (before delete) {
if (trigger.isBefore && trigger.isDelete) {
for (Account a : [Select Id, Name,
(Select Id From Billings__r LIMIT 1)
From Account Where ID IN: trigger.old])
{
if (a.Billings__r.size() > 0)
trigger.oldMap.get(a.Id).addError('ERROR : Cannot delete this Account.');
}
}
}

 

How do u guys see this trigger to be broken with Governor Limit? I don't think I can bulkify it more :-( ...

 

Best Answer chosen by Admin (Salesforce Developers) 
Greg HGreg H

To bea clear, I am referring to SOQL statements that have sub-queries in them. I am not referring to SOQL statements that use dot notation in them.

It toook me a while but I was able to find the documentation about this. Here's the link:http://www.salesforce.com/us/developer/docs/apexcode/index_Left.htm#StartTopic=Content/apex_gov_limits.htm The piece specific to your topic is "In a SOQL query with parent-child relationship sub-queries, each parent-child relationship counts as an additional query."
-greg

All Answers

bob_buzzardbob_buzzard

Difficult to see anything wrong with that trigger.

 

Governor limits are for the whole transaction though - is this the only trigger that fires for your action, or are there a number of them?

VarunCVarunC

Thats is my issue, this issue is caused by a mass delete of accounts, but does not involve any other transaction for delete or anything.

 

The issue occurred in a managed package installaion in Client Org, and I received a mail with this exception, ...

 

The error mail is as expected Without any Info except the error text, No Line Number, no Trace Data information.

A_SmithA_Smith
It's possible the  customer also has a trigger on the same object and that your trigger and their trigger are intermixing.  You may just be the lucky one who is hitting the limit.  Are there any other triggers on this object in the customer org?
VarunCVarunC

No, client org does not have any other trigger on Account object. Is there any way I can have Full debug log sent in exception mails? Is there some setting?

 

I've checked & tested the development org where trigger was developed and pakcaged & tested with bulk operations via data laoder & NO ERROR received ...

Greg HGreg H

What if you use the Trigger.oldMap variable to get a set of unique Account Ids instead of the Trigger.old variable? Something like:

trigger trgAccountCalculations on Account (before delete) { for (Billings__c b : [SELECT Account__c FROM Billings__c WHERE Account__c in :Trigger.oldMap.keySet()]) { Trigger.oldMap.get(b.Account__c).addError('ERROR : Cannot delete this Account.'); } }

 

-greg

VarunCVarunC

This does not seems to be a valid for me, as this does not explain why I got the error of too many SOQL queries ... :( ... also, in future I may put another child query to account like (Select ID from payment__r) ... and restirct delete of an account on that too like i have on billing.

 

 

Greg HGreg H

I think the reason for the error is because of the relationship query. The system counts your query as two SOQL requests. I believe each subsequent child query counts against your total. What if you assigned your query results to a map instead of doing the SOQL For loop?
-greg

VarunCVarunC

oh ... I didn't knew that ..

 

So if I'm not wrong then you mean, if my query is like this:

 

Select Id,

    (Select Id From Object_1__r),

    (Select Id From Object_2__r)

From Account

 

Then the inline for loop will count it as 2 Seperate SOQL queries. But if I used a MAP or LIST then it would be counted as 1 single SOQL query. Am I right?

Greg HGreg H

The example you just wrote would result in three SOQL requests. Basically, any request made to an object will result in an SOQL request even if the code is written in a manner that combines the requests into one SOQL statement. This would also apply if the results are assigned to a map.

My suggestion of using a map was so that you could drop the SOQL request against the Account object. This would essentially eliminate one of the SOQL requests. Since your trigger is on the Account object, you already have access to the Account attributes. I'm suggesting that you only query the Billings__c object. Then later if you want to query other related objects you can. But I would assign each query result to a map so that there is truly one SOQL request for Billings__c and a second for the second object.
-greg

VarunCVarunC

This statement really got me worried :( ...


Greg H wrote:

Basically, any request made to an object will result in an SOQL request even if the code is written in a manner that combines the requests into one SOQL statement. This would also apply if the results are assigned to a map.


Are you sure .. any request to Fetch objects Either direct SOQL or SubQuery like SOQL child query would result in seperate SOQL Request .. this will greatly effect all my triggers which I've created everywhere in my application :( ...

 

I was seriously under the impression that by grouping them in single query (with Sub Child queries) I restrict them into 1 single SOQL instead of multiple SOQL requests ... :(

Greg HGreg H

To bea clear, I am referring to SOQL statements that have sub-queries in them. I am not referring to SOQL statements that use dot notation in them.

It toook me a while but I was able to find the documentation about this. Here's the link:http://www.salesforce.com/us/developer/docs/apexcode/index_Left.htm#StartTopic=Content/apex_gov_limits.htm The piece specific to your topic is "In a SOQL query with parent-child relationship sub-queries, each parent-child relationship counts as an additional query."
-greg

This was selected as the best answer
VarunCVarunC
Thanks greg for putting the pieces together for me ... Now I understood that the 20 SOQl limit can be reached I really didn't had the idea of my subqueries being counted as seperate queries :( .. .Thanks again for searching the topic ...
A_SmithA_Smith
Your sub query should still only be counting as one query.  So your trigger above has two queries in terms of the SOQL limit, which is why it's strange you are hitting this.  How are you going about deleting the accounts?  Through the UI?  Some sort of apex test?  What are you doing when you get this error?
VarunCVarunC

yes thats why I got totally lost when I debugged, the count i got was 2 ... Can you please explain me how is this count working like this?

 

The issue arised when account records deleted via Data Loader.

 

I mean i have other triggers on other Objects like Contact, but all are based on my asumption that a query with qubquery would still be counting into 1 :( ... and you explaination of the fact that how it works out to be 2 would greatly help me organize my Triggers in better way ...

A_SmithA_Smith
Do you have any delete triggers on any of the objects that also get deleted when an account is deleted?  IE contacts?
VarunCVarunC

See .. Account in my application is the Parent for all. It gets deleted it Delets its Child Records automatically ... but as far as I know ... that Delete of other child records is Cascaded delete, so No Trigger is executed in that case ..

 

There is No Other trigger on Account object. And there is No DML statement that gets executed in this trigger which can trigger other triggers. Though child Records Do have associated triggers on them .. but they should not be fired since the delete is cascaded .. right ?

A_SmithA_Smith

I'm pretty sure (not 100%) that triggers during a cascade delete are fired. 

 

I would write a simple apex test method in your org that queries for 200 accounts and performs a delete (make sure they have associated child records where you have before/after delete triggers).  Then you should find the culprit.

VarunCVarunC

Ok I tested this ... Cascaded delete Did Not fire any child object triggers.

Message Edited by vchaddha on 01-14-2010 09:03 PM