Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
BroncoBoyBroncoBoy 

Struggling Writing a Bulk Safe Event Trigger

I'm trying to avoid getting a "Too many SOQL queries" -error in a trigger I'm writing.  Does anyone have a suggestion on how I can remove the queries from for-loop within the trigger below using maps or something else? Thank you in advance for your help!

Here's my code:

trigger UpdateEventDescription on Event (before insert, before update)
{
Map<Id, User> usersMap = new Map<Id, User>([SELECT Cost_Center__c FROM User WHERE IsActive = true]);
private String iwEmailAddress;
private String bdcEmailAddress;

    if (trigger.isInsert)
    {
     for (Event e: Trigger.new)
        {
         String descrip = e.Description;
         String ownCC = usersMap.get(e.OwnerId).Cost_Center__c;
         if(e.Email_Description_to_BDC__c || e.Email_Description_to_IW__c)//if either checkbox (Email_Description_to_BDC__c or e.Email_Description_to_IW__c) is are checked, proceed
         {         
          if(e.Email_Description_to_BDC__c && ownCC != '' && ownCC != null)
          {       
           //Retrieve the BDC-users email address based on the event-owner's cost center
           bdcEmailAddress = [SELECT Cost_Center__c, Email FROM User WHERE Cost_Center__c =:ownCC AND (ProfileId = '1234' OR ProfileId = '1234') LIMIT 1].Email;  //is there a way to removed this from the for loop so that I can avoid the too many queries error?
          }          
          if(e.Email_Description_to_IW__c && ownCC != '' && ownCC != null)
          {
           //Retrieve the IW-users email address based on the event-owner's cost center
           iwEmailAddress =  [SELECT Cost_Center__c, Email FROM User WHERE Cost_Center__c =:ownCC AND (ProfileId = '5678' OR ProfileId = '8940') LIMIT 1].Email;
          }
          //Will add code here to update the description
         }        
        } 
    }
}
Best Answer chosen by BroncoBoy
Boris BachovskiBoris Bachovski
Try something like the following, basically go through all the events in the trigger once in order to map the cost centres to emails, and then go through again an use the map:

trigger UpdateEventDescription on Event (before insert, before update)
{
  Map<Id, User> usersMap = new Map<Id, User>([SELECT Cost_Center__c FROM User WHERE IsActive = true]);
  private String iwEmailAddress;
  private String bdcEmailAddress;

    if (trigger.isInsert)
    {
      Map <String, String> costCentreToFirstProfileEmail = new Map <String, String> ();
      Map <String, String> costCentreToSecondProfileEmail = new Map <String, String> ();

      for (Event e: Trigger.new)
      {
        String ownCC = usersMap.get(e.OwnerId).Cost_Center__c;
        costCentreToFirstProfileEmail.put(ownCC, null);
        costCentreToSecondProfileEmail.put(ownCC, null);
      }

      // You can combine the following 2 queries and loops and work them out differently based on your logic
      for (User user : [SELECT Cost_Center__c, Email FROM User WHERE Cost_Center__c IN :costCentreToFirstProfileEmail.keySet() AND (ProfileId = '1234' OR ProfileId = '1234')])
      {
        costCentreToFirstProfileEmail.put(user.Cost_Center__c, Email);
      }

      for (User user : [SELECT Cost_Center__c, Email FROM User WHERE Cost_Center__c IN :costCentreToSecondProfileEmail.keySet() AND (ProfileId = '5678' OR ProfileId = '8940')])
      {
        costCentreToSecondProfileEmail.put(user.Cost_Center__c, Email);
      }

     for (Event e: Trigger.new)
        {
         String descrip = e.Description;
         String ownCC = usersMap.get(e.OwnerId).Cost_Center__c;
         if(e.Email_Description_to_BDC__c || e.Email_Description_to_IW__c)//if either checkbox (Email_Description_to_BDC__c or e.Email_Description_to_IW__c) is are checked, proceed
         {         
          if(e.Email_Description_to_BDC__c && ownCC != '' && ownCC != null)
          {       
           //Retrieve the BDC-users email address based on the event-owner's cost center
           bdcEmailAddress = costCentreToFirstProfileEmail.get(ownCC);
          }          
          if(e.Email_Description_to_IW__c && ownCC != '' && ownCC != null)
          {
           //Retrieve the IW-users email address based on the event-owner's cost center
           iwEmailAddress =  costCentreToSecondProfileEmail.get(ownCC);
          }
          //Will add code here to update the description
         }        
        } 
    }
}
I haven't tested this so you might get some compiler errors but it should give you an idea of the approach.

All Answers

Boris BachovskiBoris Bachovski
Try something like the following, basically go through all the events in the trigger once in order to map the cost centres to emails, and then go through again an use the map:

trigger UpdateEventDescription on Event (before insert, before update)
{
  Map<Id, User> usersMap = new Map<Id, User>([SELECT Cost_Center__c FROM User WHERE IsActive = true]);
  private String iwEmailAddress;
  private String bdcEmailAddress;

    if (trigger.isInsert)
    {
      Map <String, String> costCentreToFirstProfileEmail = new Map <String, String> ();
      Map <String, String> costCentreToSecondProfileEmail = new Map <String, String> ();

      for (Event e: Trigger.new)
      {
        String ownCC = usersMap.get(e.OwnerId).Cost_Center__c;
        costCentreToFirstProfileEmail.put(ownCC, null);
        costCentreToSecondProfileEmail.put(ownCC, null);
      }

      // You can combine the following 2 queries and loops and work them out differently based on your logic
      for (User user : [SELECT Cost_Center__c, Email FROM User WHERE Cost_Center__c IN :costCentreToFirstProfileEmail.keySet() AND (ProfileId = '1234' OR ProfileId = '1234')])
      {
        costCentreToFirstProfileEmail.put(user.Cost_Center__c, Email);
      }

      for (User user : [SELECT Cost_Center__c, Email FROM User WHERE Cost_Center__c IN :costCentreToSecondProfileEmail.keySet() AND (ProfileId = '5678' OR ProfileId = '8940')])
      {
        costCentreToSecondProfileEmail.put(user.Cost_Center__c, Email);
      }

     for (Event e: Trigger.new)
        {
         String descrip = e.Description;
         String ownCC = usersMap.get(e.OwnerId).Cost_Center__c;
         if(e.Email_Description_to_BDC__c || e.Email_Description_to_IW__c)//if either checkbox (Email_Description_to_BDC__c or e.Email_Description_to_IW__c) is are checked, proceed
         {         
          if(e.Email_Description_to_BDC__c && ownCC != '' && ownCC != null)
          {       
           //Retrieve the BDC-users email address based on the event-owner's cost center
           bdcEmailAddress = costCentreToFirstProfileEmail.get(ownCC);
          }          
          if(e.Email_Description_to_IW__c && ownCC != '' && ownCC != null)
          {
           //Retrieve the IW-users email address based on the event-owner's cost center
           iwEmailAddress =  costCentreToSecondProfileEmail.get(ownCC);
          }
          //Will add code here to update the description
         }        
        } 
    }
}
I haven't tested this so you might get some compiler errors but it should give you an idea of the approach.
This was selected as the best answer
BroncoBoyBroncoBoy
I think this will work!  Thank you!!
-Bronco