Why does my delete trigger cause a 'Too Many SOQL Queries' error?

Question

I have a trigger that is supposed to see if a field is empty and, if it is empty, it is supposed to check if there is an Account Team Member with a certain role. If that is true then it is supposed to delete that team member. I can only get 60% code coverage on it and I've been wondering why. I am new to writing code. My trigger and test class are below.

TRIGGER

trigger DeleteATM on Account(after insert, after update) {
    List<Id> lstId = new List<Id>();

   for(Account acc: Trigger.new){
       if (acc.Customer_Success_Manager__c== null) {
           List<AccountTeamMember> atm = [Select UserId from AccountTeamMember where      TeamMemberRole =: 'Customer Success Manager' AND AccountID=:acc.id AND UserID!=:acc.Customer_Success_Manager__c];
           delete atm;
       }
   }
}

TEST CLASS

@isTest
Public class RemoveCSMTest {
    Static testMethod void insertNewAccount() {
        Account Cre = new Account();
        Cre.Market__c = 'Houston';Cre.Business_Unit__c = '334';
        Cre.Name = 'Test CSM2';
        Cre.Customer_Success_Manager__c = '005A0000000SAC0';
        Insert cre;
        List<Account> a = [SELECT Id, Name, Customer_Success_Manager__c FROM Account WHERE Name = 'TestCSM2'];
        for (Account accs : a){
             accs.Customer_Success_Manager__c=NULL;
             Update accs;
        }
    }
}

Answer 1

You are running a SOQL statement within a loop. This is not best practice and will usually end up having governor limit issues like you are seeing.

You need to move your query outside of the loop and try to leverage a collection. See below I am using a map to hold the account team members.

EDIT

Changed code to delete ALL team members, not just a single team member.

trigger DeleteATM on Account(after insert, after update) {

    map<Id,list<AccountTeamMember>> account2TeamMemberMap = new map<Id,list<AccountTeamMember>>();
    for(AccountTeamMember mem : [Select Id, AccountId From AccountTeamMember Where TeamMemberRole = 'Customer Success Manager' and AccountId In : trigger.new]){
        if(account2TeamMemberMap.containsKey(mem.AccountId)){
             account2TeamMemberMap.put(mem.AccountId, new list<AccountTeamMember>());
        }
        account2TeamMemberMap.get(mem.AccountId).add(mem);
    }

    list<AccountTeamMember> members2Delete = new list<AccountTeamMember>();
    for(Account acc: Trigger.new){
        if (account2TeamMemberMap.containsKey(acc.Id) && acc.Customer_Success_Manager__c == null) {
            members2Delete.addAll(account2TeamMemberMap.get(acc.Id));
        }
    }
    delete members2Delete;
}

As @KeithC suggested, I would take a look at the Apex Coding Best Practices