I have written a trigger to fill some lookup fields depending on a given number

it worked fine until you try to mass update.

here is the trigger

trigger getTradingAccountUPD on Case (before update) {
List<Contract> abc = new List<Contract>();
List<Account> mta = new List<Account>();

for(Case oldCase :trigger.old){
    if(oldCase.Trading_Account__c == null && oldCase.Master_trading_Account__c == null && oldCase.Customer__c == null){

for(Case nCase :trigger.new){
    if(nCase.Legacy_Account_Number__c != null){
        nCase.System_Notification__c ='';
        abc = [SELECT Id,AccountId FROM Contract WHERE Legacy_Account_Number__c =: nCase.Legacy_Account_Number__c LIMIT 1];
        if(abc.size()>0){
           
              nCase.Trading_Account__c = abc[0].Id;
              nCase.Master_Trading_Account__c = abc[0].AccountId;
              mta = [SELECT ID,ParentId FROM Account WHERE Id =: abc[0].AccountId LIMIT 1];
              if(mta[0].Id != null) {
                  nCase.Customer__c = mta[0].ParentId;    
                  }  
            }
         if(abc.size() == 0) {
             mta = [SELECT Id,ParentId FROM Account WHERE Legacy_Account_Number__c =: nCase.Legacy_Account_Number__c LIMIT 1];
             if(mta.size() >0) {
             nCase.Master_Trading_Account__c = mta[0].Id;
             if(mta[0].ParentId != null) {
                 nCase.Customer__c = mta[0].ParentId;
                 }
             }    
          }
          if((abc.size()==0) && (mta.size()==0)){
              nCase.System_Notification__c = '(NOT FOUND IN DATABASE)';
              }
        }
     }
   }
  }
}

 Okay I saw that the select query is in the loop so i moved my head around and came up with the following

trigger getTradingAccountUPD on Case (before update) {

List<Account> cus = new List<Account>();

Set<id> mta = new Set<id>();
Map<id,String> cases = new Map<id,String>();

 for(Case oldCase :trigger.old){
    if(oldCase.Trading_Account__c == null && oldCase.Master_Trading_Account__c == null && oldCase.Customer__c == null){
       for(Case ncase :trigger.New){
          cases.put(ncase.Id,ncase.Legacy_Account_Number__c);
          ncase.System_Notification__c = '';
       }
    }
 }
Set<Contract> ta = new Set<Contract>([SELECT Id,AccountId,Legacy_Account_Number__c FROM Contract WHERE Legacy_Account_Number__c IN : cases.values() Limit 1]);
If(!ta.isEmpty()){
  for(Case ncase :trigger.New){
    if(ncase.Legacy_Account_Number__c != null){
        for(Contract lan : ta){
            if(ncase.Legacy_Account_Number__c.equals(lan.Legacy_Account_Number__c)){
                ncase.Trading_Account__c = lan.id;
                mta.add(lan.AccountId);
            }
        }
    }
   }
 }
 if(!mta.isEmpty()){
     List<Account> acc = new List<Account>([SELECT Id,ParentId FROM Account WHERE Id IN : mta]);
     for(Case ncase :trigger.New){
       ncase.Master_Trading_Account__c = acc[0].Id;
       if(acc[0].ParentId != null){
           ncase.Customer__c = acc[0].ParentId;
           }
       }
  }      
 if(ta.isEmpty() && mta.isEmpty()){
     List<Account> amaster = new List<Account>([SELECT Id,ParentId,Legacy_Account_Number__c FROM Account WHERE Legacy_Account_Number__c IN : cases.values()]);
        if(!amaster.isEmpty()){
          for(Case ncase :trigger.New){
            ncase.Master_Trading_Account__c = amaster[0].Id;
            if(amaster[0].ParentId != null){
              ncase.Customer__c = amaster[0].ParentId;
            }
          }
        } 
        else if ((amaster.isEmpty()) &&(ta.isEmpty() && mta.isEmpty()) ){
          for(Case oldCase :trigger.old){
              if(oldCase.Trading_Account__c == null && oldCase.Master_Trading_Account__c == null && oldCase.Customer__c == null){
                 for(Case ncase :trigger.New){
                    ncase.System_Notification__c = 'NOT FOUND';
                 }
               }  
           } 
         }    
 }
    
           
}

 but now my test class runs into the same System.LimitException: Too many SOQL queries: 101 issue at

Set<Contract> ta = new Set<Contract>([SELECT Id,AccountId,Legacy_Account_Number__c FROM Contract WHERE Legacy_Account_Number__c IN : cases.values() Limit 1]);

Any Idea how can I avoid that issue, because there are regular updates with the data loader

some backgroud

the account number is an external id which will be pulled into the legacy_account_number_ c

this number is pointing to a Contract object ( unique ), depending on that contract I need to pull the

ID of the related Account Object from that Contract and if any the parent account of that account.

I just wonder if there is anything else I could render to avoid the SQL limits

Thanks in advance

my testclass looks like that

@isTest(SeeAllData=true) 
public class getTradingAccountTest {
static testMethod void test_getTradingAccount(){
 Test.startTest();
        Case cs = new Case();
        cs.email__c = 'test.test@gmail.com';
        cs.Preferred_Reply_Method__c = 'Letter';
        cs.Post_Code__c = '95050';
        cs.Phone__c = '408123123';
        cs.First_Line_of_Address__c = 'test address';
        cs.Tracking_Job_Number__c = '1234567';
        cs.Consignment_Number__c = '24242323';
        cs.description= 'test';
        cs.Account_Number__c = '38J010W';
        insert cs;
        
        Case ds = new Case();
        ds.email__c = 'test.test@gmail.com';
        ds.Preferred_Reply_Method__c = 'Letter';
        ds.Post_Code__c = '95050';
        ds.Phone__c = '408123123';
        ds.First_Line_of_Address__c = 'test address';
        ds.Tracking_Job_Number__c = '1234567';
        ds.Consignment_Number__c = '24242323';
        ds.description= 'test';
        ds.Account_Number__c = 'test0001';
        insert ds;
        
        Case es = new Case();
        es.email__c = 'test.test@gmail.com';
        es.Preferred_Reply_Method__c = 'Letter';
        es.Post_Code__c = '95050';
        es.Phone__c = '408123123';
        es.First_Line_of_Address__c = 'test address';
        es.Tracking_Job_Number__c = '1234567';
        es.Consignment_Number__c = '24242323';
        es.description= 'test';
        es.Account_Number__c = 'rolle roe';
        insert es;
        
        //for code coverage in the controller
        NewCaseController x = new NewCaseController();
        x.trackAccount(cs);
        x.trackAccount(ds);
        x.trackAccount(es);
        //end of code coverage in controller
        
        es.Legacy_Account_Number__c = '38J010W';
        update es;
        es.Legacy_Account_Number__c=null;
        es.Trading_Account__c=null;
        es.Master_Trading_Account__c=null;
        es.Customer__c=null;
        update es;
        es.Legacy_Account_Number__c='test0001';
        update es;
        es.Legacy_Account_Number__c=null;
        es.Trading_Account__c=null;
        es.Master_Trading_Account__c=null;
        es.Customer__c=null;
        update es;
        es.Legacy_Account_Number__c='nope';
        update es;
        
        Test.stopTest();
}
}