Apex Code Development You need to sign in to do that
Don't have an account?
mallikam execution of BeforeInsert caused by: System.Exception: Too many SOQL queries: 21
Hi,
I have a trigger with the following code, everything seems to be working fine until I got the above exception through email this morning. The code looks simple and correct to me an so I am having hard time figuring out why the query could have run into governor limits exception..
trigger Executive on Case (before insert, before update) { Map<Id, Case[]> contactCaseMap = new Map<Id, Case[]>(); For (Case ncase:Trigger.new) { Id cid = ncase.contactId; If (cid != NULL) { if(contactCaseMap.get(cid) == null) contactCaseMap.put(cid, new Case[]{}); contactCaseMap.get(cid).add(ncase); } } For(Contact c:[select Executive__c from Contact where Id IN :contactCaseMap.keySet()]) { // code is // running into exception at this // query
Thanks!
MM,
Is there a query written inside the SOQL FOR loop?
Could not make out clearly from your code, so wanted to confirm!
Cool_D
Thanks Anand and CD for the reply! And CD, here is the complete code:
For(Contact c:[select Executive__c from Contact where Id IN :contactCaseMap.keySet()]) {// exception here for(Case ncase:contactCaseMap.get(c.id)) { ncase.Executive__c = c.Executive__c; } } }
I dont have a query inside For loop but in FOR statement itself and thats where I am getting exception. I think Anand is right!
Hi,
I think you understood it wrongly.
If i am reading your code correctly, you are using a SOQL FOR loop which is different from query inside a FOR Loop!
In this case, the exception is not because of this loop but it is occuring as you are executing this inside the outer Trigger.New loop.
Your query is absolutely fine and is the right way of writing it. Once you pur it outside the outer loop, the code would work like charm!!
Cool_D
PS: The same concept is explained in the link posted above.
But it shows me where the exception was triggered and it was triggered at this particular line..
For(Contact c:[select Executive__c from Contact where Id IN :contactCaseMap.keySet()])
And moreover the outer for loop ends right above this SOQL for statement, meaning the query is already outside of the Trigger.new for loop?! Here is the complete code from the beginning:
trigger Executive on Case (before insert, before update) { Map<Id, Case[]> contactCaseMap = new Map<Id, Case[]>(); For (Case ncase:Trigger.new) { Id cid = ncase.contactId; If (cid != NULL) { if(contactCaseMap.get(cid) == null) contactCaseMap.put(cid, new Case[]{}); contactCaseMap.get(cid).add(ncase); } } For(Contact c: [select Executive__c from Contact where Id IN :contactCaseMap.keySet()]) { for(Case ncase:contactCaseMap.get(c.id)) { ncase.Executive__c = c.Executive__c; } } }
Well, code looks fine to me!
Did you try using an inline SOQL instead of the SOQL FOR loop?
If not, then try that out. But this query seems fine as it is already outside the loop.
Cool_D
Thanks CD for the confirmation. I have seperated the query from the FOR statement for now, lets see. I had hard time debugging this one because even I did not see any issues with code either..!!
CD,
I ran into the same problem again. I changed the code to below:
trigger Executive on Case (before insert, before update) { Map<Id, Case[]> contactCaseMap = new Map<Id, Case[]>(); For (Case ncase:Trigger.new) { Id cid = ncase.contactId; If (cid != NULL) { if(contactCaseMap.get(cid) == null) contactCaseMap.put(cid, new Case[]{}); contactCaseMap.get(cid).add(ncase); } } List<Contact> ExecutiveOfContact = [select Executive__c from Contact where Id IN :contactCaseMap.keySet()]; For(Contact c: ExecutiveOfContact) { for(Case ncase:contactCaseMap.get(c.id)) { ncase.Executive__c = c.Executive__c; } } }
and I got the same error again today:
Executive: execution of BeforeInsert
caused by: System.Exception: Too many SOQL queries: 21
Trigger.Executive: line 12, column 36
I really dont understand what exactly is causing the problem!! I dont understand if it is the FOR statement or if it is the SOQL statement itself that is causing this..any help?
thanks!
Hi There,
I would re-write the code like this-
trigger Executive on Case (before insert, before update) { Set<String> contactSet = new Set<String>(); For (Case ncase:Trigger.new) { If (ncase.contactId != NULL) { contactSet.add(ncase.contactId); } } Map<ID, Contact> ExecutiveOfContact = new Map<ID, Contact>([select id, Executive__c from contact where Id IN :contactSet]); for(Case ncase:Trigger.new) { ncase.Executive__c = ExecutiveOfContact.get(ncase.contactId).Executive__c; } }
Can you please try running this?
Cool_D
Thanks a lot, CD!! Now this works like a charm! :)
This looks certainly less problematic and straight forward than the original code but I dont know if I can find out if this is a correct choice over the previous one immediately..the problem with my original approach was that I could not reproduce the exception. It was running into the governor limit exception only under certain circumstances on production, I could not figure out what it was. But I think I will deploy this on production and see if this code runs me into an exception in future...thats the best I can do for now?!
Well, the query was always looking fine.
Only thing was ... there were some extra loops which could have been avoided.
hope this does'nt give exceptions for you!!
Cool_D
MM,
Can you please check if there is any workflow/field update, which might run the trigger again? Try seeing the debug log.
I am sure, code is fine, no issues. Certainly, there would be some other aspect of it which is being missed out!
Also, see if you can put any checks, like checking if contact value has changed as only then this logic needs to run, right?
Cool_D