System.Exception: Too many SOQL queries: 101 [duplicate]

Question

I am getting a Too many SOQL queries: 101 error on a trigger when I am trying to deploy another trigger.

I am posting the trigger which is causing the error.

trigger rollupAccountShowings on Account (before insert, before update) {
   if (trigger.isinsert) {
      for (Account a:Trigger.new){
          a.No_of_Viewings__c = 0;
       }
   } else {
     for (account a : [select 
                 id,
                 (select id from pb__Showings__r)
                 from account 
                 where id in:trigger.new LIMIT 1]) {

         trigger.newMap.get(a.id).No_of_Viewings__c = a.pb__Showings__r.size();
     }
}

Can anyone correct me where I have gone wrong and suggest me the changes.

Thanks

Answer 1

The code in your trigger is fine.

The trigger or class that the Too many SOQL queries: 101 error is thrown on may or may not be the cause of the problem. It just means that the code in question made the 101st SOQL call, which hit the SOQL limit.

As the comments on your question state, the most common cause of the 101 error is that you have a SOQL query in a FOR LOOP. If you can't find the loop thats causing the problem then one way to debug this issue is to turn on debug logging and then reproduce the problem. If you search the debug logs for SOQL_EXECUTE_BEGIN you should find 101 references (once you haven't hit the max log size).

The SOQL queries will be displayed something like what is shown below.

03:57:09.030 (30409851)|SOQL_EXECUTE_BEGIN|[70]|Aggregations:0|select Id, Email from User where Id = :tmpVar1

03:57:09.033 (33956239)|SOQL_EXECUTE_END|[70]|Rows:1

You should find that the vast majority of your SOQL statements are caused by the same query. Once you know which query it is you can find it in your code and then remove it from the loop it is in.

Answer 2

This trigger code is fine.

Your underlying issue resides within another block of code in the org. Does the error message give you a class name and a line number where the Limit exception was thrown? If so, you can use that to trace it back through the code to the source of the problem.

Do you have test classes which have @SeeAllData="true" or are of an old enough API version where they can see the org data?

Do you have classes in your org which execute any queries within any type of loop context? for, do {} while etc.

I suspect that you've got apex code which has visibility to your org data and in the past, there wasn't enough data (or data with the proper criteria) to hit this limit.

Answer 3

As said before, this trigger is fine. The error probably originates from another block of code, which certainly causes your trigger to fire.

I think you should check for child objects of Account. If you delete or modify a child record, it is possible that this change will modify some roll-up summary fields in the parent object (Account). Consequently, the trigger on Account will fire on update and the Select statement will count within SOQL limit. This mechanism may happen multiple times and that may cause the error.

A possible solution is to add another condition before the Select query in your Account trigger. If one of your roll-up summary fields has its value changed (old != new), don't go into the select statement.

Answer 4

You are hitting the governor limits in your trigger. This will happen whenever you have more than 100 SOQL queries. The following page will help you fix the issue:

https://developer.salesforce.com/page/Best_Practice%3A_Bulkify_Your_Code