If statement in trigger causes Too many SOQL Queries

Question

I've got an Opportunity after insert trigger and utility class that creates a Contact, Opportunity Contact Role and Campaign Member (whilst checking to see if the email already exists in which case use the existing Contact instead). The trigger and class have been written for use with imports so must be bulkified. If I use the trigger:

trigger Opportunity_ai on Opportunity (after insert) {
    Opty_Utils.createOptyContacts(trigger.new);
}

I have no issues, but I wanted to include an if to prevent Opportunity records without a last name or email going through (just so the utility class isn't run unnecessarily). If I use the trigger below I get "Too many SOQL Queries: 101":

trigger Opportunity_ai on Opportunity (after insert) {
    List<Opportunity> newOptys = New List<Opportunity>();
    for (Opportunity opty:Trigger.new){
        If(Opty.LastName__c != null && Opty.Email__c !=null){
            newOptys.add(opty);
        }
            Opty_Utils.createOptyContacts(newOptys);
    }
}

Why would adding the if statement to the trigger cause the one SOQL in my utility class to loop?

Answer 1

you just need to move the statement calling utility class method out of the for loop.. and hopefully your utility class method is not making any DML / SOQL inside loops..

trigger Opportunity_ai on Opportunity (after insert) {
    List<Opportunity> newOptys = New List<Opportunity>();
    for (Opportunity opty:Trigger.new){
        If(Opty.LastName__c != null && Opty.Email__c !=null){
            newOptys.add(opty);
        }
    }
    Opty_Utils.createOptyContacts(newOptys);
}

Answer 2

Is not the IF that is causing the issue, is the fact that you are including the createOptyContacts method inside the for loop, so basically for every record in the trigger context you are sending it individually to the method, you should do this operation outside the loop.