Avoid 'Too many soql queries: 101' error in before update trigger

Question

I have a trigger thats suppose to execute during before update context and set a value in checkbox to true or false. Now this code seems to work, however if I am updating thousands of records I am getting the Too Many SOQL queries error when I use the data loader. Is there something in my code thats missing?

Class:

public class DonationTriggerHandler {
    static EmailTemplate emailTemplate = [SELECT Id, Name FROM EmailTemplate WHERE DeveloperName = 'Thank_you_HTML_Contact'];

    //Do Bussiness Logic for Opportunity Update
    public static void updateOpportunity(List<Opportunity> oppList){
        List<Opportunity> updateOpportunity = new List<Opportunity>();
        //Get Mail Template        
        for(Opportunity newOppRec: oppList){
            System.debug(newOppRec);
            //Check if the Opportunity has been Posted and then Execute Email Code
            if (newOppRec.StageName.equalsIgnoreCase('Posted') && newOppRec.Thank_You_email_sent__c != true){
                //Get the affiliated Accounts        
                List<Affiliated_Entities__c> affAccounts = [SELECT Person_Account__c FROM Affiliated_Entities__c WHERE Organisation__c =: newOppRec.AccountId];
                for(Affiliated_Entities__c affAcount: affAccounts ){
                    //Get the Person Accounts which needs to be emailed.
                    List<Account> personAccounts = [SELECT PersonContactId, npe01__Preferred_Email__pc, PersonEmail, npe01__WorkEmail__pc, npe01__AlternateEmail__pc FROM Account WHERE Id =: affAcount.Person_Account__c];
                    //Send Thank You Email.
                    if (sendEmails(newOppRec, personAccounts)) { 
                        newOppRec.Thank_You_email_sent__c = true;
                        updateOpportunity.add(newOppRec);
                    } 
                }
            }
        }
    }        

    //Do Bussiness Logic for Opportunity Insert
    public static void insertOpportunity(List<Opportunity> newOppList){
        List<Opportunity> updateOpportunity = new List<Opportunity>();
        //Get Mail Template        
        for(Opportunity newOppRec: newOppList){
            //Check if the Opportunity has been Posted and then Execude Email Code
            if (newOppRec.StageName.equalsIgnoreCase('Posted') && newOppRec.Thank_You_email_sent__c != true){
                //Get the affiliated Accounts        
                List<Affiliated_Entities__c> affAccounts = [SELECT Person_Account__c FROM Affiliated_Entities__c WHERE Organisation__c =: newOppRec.AccountId];
                for(Affiliated_Entities__c affAcount: affAccounts ){
                    //Get the Person Accounts which needs to be emailed.
                    List<Account> personAccounts = [SELECT PersonContactId, npe01__Preferred_Email__pc, PersonEmail, npe01__WorkEmail__pc, npe01__AlternateEmail__pc FROM Account WHERE Id =: affAcount.Person_Account__c];
                    //Send Thank You Email.
                    if (sendEmails(newOppRec, personAccounts)) { 
                        newOppRec.Thank_You_email_sent__c = true;
                        updateOpportunity.add(newOppRec);
                    } 
                }               
            }
        }
    }

    //Send the emails to thank donors of the account   
    private static boolean sendEmails(
        Opportunity newOppRec, List<Account> personAccounts
    ){ 
        Boolean success = false;
        //Create A list to of mails to send off
        Messaging.SingleEmailMessage[] emailList = new Messaging.SingleEmailMessage[]{};
        //Loop through the Affiliate Person Accounts to send a mail to each
        for(Account personAccount : personAccounts){
            //Check which email to use
            if (personAccount.npe01__Preferred_Email__pc.equals('Personal') &&
                personAccount.PersonEmail != null) {
                    Contact ct = [SELECT Id, Email FROM Contact WHERE Id =: personAccount.PersonContactId];
                    Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
                    mail.setTargetObjectId(ct.Id);
                    mail.setWhatId(newOppRec.Id);
                    mail.setSaveAsActivity(true);
                    mail.setTemplateId(emailTemplate.Id);
                    mail.setToAddresses(new String[]{ct.Email});
                    emailList.add(mail);
                } else if (personAccount.npe01__Preferred_Email__pc.equals('Work') &&
                           personAccount.npe01__WorkEmail__pc != null) {
                               Contact ct = [SELECT Id, Email FROM Contact WHERE Id =: personAccount.PersonContactId];
                               ct.Email = personAccount.npe01__WorkEmail__pc;
                               update ct;
                               Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
                               mail.setTargetObjectId(ct.Id);
                               mail.setWhatId(newOppRec.Id);
                               mail.setSaveAsActivity(true);
                               mail.setTemplateId(emailTemplate.Id);
                               mail.setToAddresses(new String[]{ct.Email});
                               emailList.add(mail);
                           } else if (personAccount.npe01__Preferred_Email__pc.equals('Alternate') &&
                                      personAccount.npe01__AlternateEmail__pc != null) {
                                          Contact ct = [SELECT Id, Email FROM Contact WHERE Id =: personAccount.PersonContactId];
                                          ct.Email = personAccount.npe01__AlternateEmail__pc;
                                          update ct;
                                          Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
                                          mail.setTargetObjectId(ct.Id);
                                          mail.setWhatId(newOppRec.Id);
                                          mail.setSaveAsActivity(true);
                                          mail.setTemplateId(emailTemplate.Id);
                                          mail.setToAddresses(new String[]{ct.Email});
                                          emailList.add(mail);
                                      } else {
                                          //Email any available email
                                          if (personAccount.PersonEmail != null) {
                                              Contact ct = [SELECT Id, Email FROM Contact WHERE Id =: personAccount.PersonContactId];                                                  
                                              Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
                                              mail.setTargetObjectId(ct.Id);
                                              mail.setWhatId(newOppRec.Id);
                                              mail.setSaveAsActivity(true);
                                              mail.setTemplateId(emailTemplate.Id);
                                              mail.setToAddresses(new String[]{ct.Email});
                                              emailList.add(mail);
                                          } else if (personAccount.npe01__WorkEmail__pc != null) {
                                              Contact ct = [SELECT Id, Email FROM Contact WHERE Id =: personAccount.PersonContactId];
                                              ct.Email = personAccount.npe01__WorkEmail__pc;
                                              update ct;
                                              Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
                                              mail.setTargetObjectId(ct.Id);
                                              mail.setWhatId(newOppRec.Id);
                                              mail.setSaveAsActivity(true);
                                              mail.setTemplateId(emailTemplate.Id);
                                              mail.setToAddresses(new String[]{ct.Email});
                                              emailList.add(mail);
                                          } else if (personAccount.npe01__AlternateEmail__pc != null) {
                                              Contact ct = [SELECT Id, Email FROM Contact WHERE Id =: personAccount.PersonContactId];
                                              ct.Email = personAccount.npe01__AlternateEmail__pc;
                                              update ct;
                                              Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
                                              mail.setTargetObjectId(ct.Id);
                                              mail.setWhatId(newOppRec.Id);
                                              mail.setSaveAsActivity(true);
                                              mail.setTemplateId(emailTemplate.Id);
                                              mail.setToAddresses(new String[]{ct.Email});
                                              emailList.add(mail);
                                          }
                                      } 
        }
        if(!emailList.isEmpty()){
            System.debug('DonationEmailList: '+ emailList);
            try{                
                //Send Emails
                List<Messaging.SendEmailResult> msg = Messaging.sendEmail(emailList);
                success = true;
                System.debug(msg);         
            } catch(Exception e){
                System.debug('Exception caught:::'+e.getMessage());
                success = false;
            }
        }
        return success;
    }    
}

Trigger:

trigger DonationUpdate on Opportunity (before update,before insert) {
//check if trigger is fired after update
    if(Trigger.isBefore && Trigger.isUpdate){        
        System.debug('Update');
        //Run The rules in the APEX Class for Update on the Opportunity Record
        DonationTriggerHandler.updateOpportunity(Trigger.new);
    }
    //Is trigger fired on create
    if(Trigger.isBefore && Trigger.isInsert) {
        //Run The rules in the APEX Class for Create on the Opportunity Record
        System.debug('Insert');
        DonationTriggerHandler.insertOpportunity(Trigger.new);
    }

}

Answer 1

Look at these lines

//Send the emails to thank donors of the account   
private static boolean sendEmails(Opportunity newOppRec){ 
...
List<Affiliated_Entities__c> affAccounts = [SELECT Person_Account__c FROM Affiliated_Entities__c WHERE Organisation__c =: newOppRec.AccountId];
        //Create A list to of mails to send off
...
EmailTemplate emailTemplate = [SELECT Id, Name FROM EmailTemplate WHERE DeveloperName = 'Thank_you_HTML_Contact'];   
...
List<Account> personAccounts = [SELECT PersonContactId, npe01__Preferred_Email__pc, PersonEmail, npe01__WorkEmail__pc, npe01__AlternateEmail__pc FROM Account WHERE Id =: affAcount.Person_Account__c];

...
Contact ct = [SELECT Id, Email FROM Contact WHERE Id =: personAccount.PersonContactId];

It is inside the for loop and being called by method sendEmails. This is the reason.

The email template is something which is repeating. You can put it as static variable like:

public class DonationTriggerHandler {

  static EmailTemplate emailTemplate; 

  //Do Bussiness Logic for Opportunity Update
    public static void updateOpportunity(List<Opportunity> oppList){
            ...
            emailTemplate = [SELECT Id, Name FROM EmailTemplate WHERE DeveloperName = 'Thank_you_HTML_Contact'];   
            ...
     } 


}