+ Start a Discussion
BobBob 

System.Exception: Too many SOQL queries: 101

I keep getting this error on one of my working triggers. Would anyone know how to stop the error System.Exception: Too many SOQL queries: 101 from occurring?

trigger Trigger_EventBeforeInsertUpdate on Event (before insert, before update) {
   
    /** Add **/
    Set<Id> whoIds = new Set<Id>();
    for(Event event: System.Trigger.New){
        whoIds.add(event.whoId);
    }
   
    Map<Id, Contact> contactMap = new Map<Id, Contact>([select Id,Name from Contact where Id in:whoIds]);
    Map<Id, Lead> leadMap = new Map<Id, Lead>([select Id,Name from Lead where Id in:whoIds]);
    /** Add **/
   
   
    for(Event event: System.Trigger.New){
        List<sObject> lobjects;
        string name;
        lobjects = [select Id, Name from Account where Id=:event.WhatId];
        if(lobjects.size() == 1)
            name = ((Account)lobjects[0]).Name;
        else{ 
            lobjects = [select Id, Name from Opportunity where Id=:event.WhatId];
            if(lobjects.size() == 1)
                name = ((Opportunity)lobjects[0]).Name;
            else{ 
                lobjects = [select Id, CaseNumber from Case where Id=:event.WhatId];
                if(lobjects.size() == 1)
                    name = ((Case)lobjects[0]).CaseNumber;
                else{
                    lobjects = [select Id, Name from Campaign where Id=:event.WhatId];
                    if(lobjects.size() == 1)
                    name = ((Campaign)lobjects[0]).Name;
                }
            }     
        }
       
        /**Add**/        
        if (contactMap.containskey(event.whoId) == true) {
            event.Description = 'Related To: ' + name + '\n Contact Name:' + contactMap.get(event.whoId).Name +'\n'+  event.Activity_Comment__c;
        } else if (leadMap.containskey(event.whoId) == true) {
            event.Description = 'Lead Name:' + leadMap.get(event.whoId).Name +'\n'+  event.Activity_Comment__c;
        }
                    
        System.debug('description is set to ' + event.Description);
    }
}
Vishant ShahVishant Shah
from what i see is you bring the account/opp/case/campaign name and populate it on the Event.

I dont think you need a trigger at all, you can do a workflow rule and a field update to set the name and description. Do it with click if you can saves you a lot of time and effort in the long term

Ta
Vish
sandeep varsandeep var

Bob,

Looks like the you are using multiple queries in the for loop depending on conditions, salesforce has limits on using soql queries. try to do soql query before the for loop and use the query values in the for loop. Use Maps or lists....

Thanks,
Sandeep.

Vinnie BVinnie B
A basic rule of SFDC triggers is that they must be bulkified.  A user on your system could easily be adding 200 events at once via a bulk uploading process.  I'm assuming that the IF / ELSE code could run one query for each of these.  You're over the 100 limit right there.

The general rule is to not have any queries inside the FOR loop that is going through all of the objects in the trigger.  If you need to run a trigger, do so before the main trigger loop.  Something like this:

  1) Create a map of all of the event objects being imported.  This may require a FOR loop of its own to go through all of the events to be added.
  2) Run a query to populate this map with all of the data you need for the events and other related objects.
  3) Do your main FOR loop of the trigger objects referencing the above map.

While you may need a few queries in steps 1 and 2 the point is that these will only be run once.  Putting a query inside that FOR loop will cause too many queries to be run.

One issue people run into is doing this but only testing the trigger with one or two objects at a time.  That might keep under the 100 limit but then a user will do a few at one time and your sunk.  You must assume that someone will be bulk uploading that object and test accordingly.

Hope this helps.

---

Below is a code snippet where I needed to get information on all of the accounts related to opportunities that were being created.  Note that the query in the second piece is run only once.

for(Opportunity newOpportunity : newOpportunities){  // First pass through main trigger loop
      accountIDs.add(newOpportunity.AccountID);
    }
   
Map<Id, Account> accountMap = new Map<Id, Account>  // Get data I need for all of the accounts involved
     ([SELECT Id, DAF__c, No_Acknowledgements__c
         FROM Account WHERE Id IN: accountIDs]);
   
for(Opportunity newOpportunity : newOpportunities){  // Second pass through main trigger loop
      Account anAccount = accountMap.get(newOpportunity.AccountID);

      ... < main things that the trigger does > ...

}