Hello,

I'm having problems with a test on two triggers. Both are working correctly but when I execute the apex test I'm receiving the message: 

System.LimitException: Too many SOQL queries: 21

Trigger.NewCaseFromOpp: line 4, column 32

The trigger is: 

trigger NewCaseFromOpp on Opportunity (after update) {

//Query for Case record types
     List<RecordType> rtypes = [Select Name, Id From RecordType where sObjectType='Case' and isActive=true];
     
//Create a map between the Record Type Name and Id for easy retrieval
     Map<String,String> CampaignsRecordTypes = new Map<String,String>{};
     for(RecordType rt: rtypes)
     CampaignsRecordTypes.put(rt.Name,rt.Id);
     
     
//Query for Users 

    List<User> users = [Select Name, Id From User];
    
//Create a map between the User Name and Id for easy retrieval

     Map<String,String> CaseUsers = new Map<String,String>{};
     for(User  usr: Users)
     CaseUsers.put(usr.Name,usr.Id);

 
 for (Opportunity opp : Trigger.new) 
 { 
    if ( (opp.approval_status__c == 'Approved by AD' ) &&
         (trigger.oldMap.get(opp.id).approval_status__c != 'Approved by AD' ) && 
         (opp.Implementation_DirectTrack__c == 'Yes') )
    { 
       Case c = new Case ( 
       Opportunity__c = opp.Id, 
       Status = 'New', 
       Origin = 'Web', 
       Type = 'Internal Case', 
       Reason = 'Campaign  Setup', 
       Subject = 'New Direct Track Implementation', 
       RecordTypeId=CampaignsRecordTypes.get('Traffic'),
       OwnerId = CaseUSers.get('Traffic List') ); 
       insert c; 
    } 
    else if ((opp.approval_status__c == 'Approved by AD' ) &&
         (trigger.oldMap.get(opp.id).approval_status__c != 'Approved by AD' ) && 
         (opp.Implementation_DirectTrack__c == 'No'))
    { 
       Case c = new Case ( 
       Opportunity__c = opp.Id, 
       Status = 'New', 
       Origin = 'Web', 
       Type = 'Internal Case', 
       Reason = 'Campaign  Setup', 
       Subject = 'New Affiliation Campaign', 
       RecordTypeId=CampaignsRecordTypes.get('Traffic'),
       OwnerId = CaseUSers.get('Traffic List')  ); 
       insert c; 
    } 
    else
    {
    } 
 } 
}

The goal of this trigger is inserting a case (depending on some conditions on Opportunity) populating some fields and assigning to a specific Record Type and Owner.

The test is:

/**
 * This class tests the trigger named AddCampaign.
 */
@isTest
private class TriggerCampaignAndCaseTest {

    static testMethod void TriggerCampaignAndCaseTest() {       
                    
//Data Prep
        
        User user = [Select Id,Name,ProfileId,TimeZoneSidKey,LocaleSidKey,EmailEncodingKey,LanguageLocaleKey  from User limit 1];
        User user1 = new User ();
        user1.LastName = 'Tester';
        user1.ProfileId = user.ProfileId;
        user1.TimeZoneSidKey = user.TimeZoneSidKey;
        user1.LocaleSidKey = user.LocaleSidKey;
        user1.EmailEncodingKey = user.EmailEncodingKey;
        user1.LanguageLocaleKey = user.LanguageLocaleKey;
        user1.alias = 'tst';
        user1.Email = 'tester@test.com';
        user1.Username = 'tester@test.com';  
        insert user1;
        
//Create Account, Opportunity, Product, etc.
        Account acct1 = new Account(name='test Account One1');
        acct1.Type = 'Advertiser';
        insert acct1;
        
        
//Create Opportunity on Account
        Opportunity Oppty1 = new Opportunity(name='test Oppty One1');
        Oppty1.AccountId = acct1.Id;
        Oppty1.StageName = 'Test';
        Oppty1.CloseDate = Date.today();
        Oppty1.AED__Campaign_Start_Date__c = Date.today();
        Oppty1.AED__Campaign_Close_Date__c = Date.today()+1;
        Oppty1.AED__Invoice_Client_Name__c = acct1.Id;
        Oppty1.AED__Country__c = 'Spain';
        Oppty1.Campaign_Name_Agreement__c = 'test Oppty One1' ;
        Oppty1.Implementation_DirectTrack__c = 'Yes';
        Oppty1.Implementation_DT_Status__c = '1. Create Campaign';
        Oppty1.Approval_Status__c = 'Waiting for Approval';
       
        insert Oppty1; 

// Create Products 
         Product2 testprod1 = new Product2 (name='test product one1');
         testprod1.productcode = 'test pd code1one';
         insert testprod1;      

// Get Pricebook
         Pricebook2 testpb = [select id from Pricebook2 where IsStandard = true];            
        
// Add to pricebook
         PricebookEntry testpbe1 = new PricebookEntry ();
         testpbe1.pricebook2id = testpb.id;
         testpbe1.product2id = testprod1.id;
         testpbe1.IsActive = True;
         testpbe1.UnitPrice = 250;
         testpbe1.UseStandardPrice = false;
         insert testpbe1;  
   
//Create Opportunity on Account
        Opportunity Oppty2 = new Opportunity(name='test Oppty One2');
        Oppty2.AccountId = acct1.Id;
        Oppty2.StageName = 'Test';
        Oppty2.CloseDate = Date.today();
        Oppty2.AED__Campaign_Start_Date__c = Date.today();
        Oppty2.AED__Campaign_Close_Date__c = Date.today()+1;
        Oppty2.AED__Invoice_Client_Name__c = acct1.Id;
        Oppty2.AED__Country__c = 'Spain';
        Oppty2.Campaign_Name_Agreement__c = 'test Oppty One2' ;
        Oppty2.Implementation_DirectTrack__c = 'No';
        Oppty2.Approval_Status__c = 'Waiting for Approval';
               
        insert Oppty2;              
                               
// Create Products          
         Product2 testprod2 = new Product2 (name='test product two2');
         testprod2.productcode = 'test pd code2two';
         insert testprod2;

// Add to pricebook
         PricebookEntry testpbe2 = new PricebookEntry ();
         testpbe2.pricebook2id = testpb.id;
         testpbe2.product2id = testprod2.id;
         testpbe2.IsActive = True;
         testpbe2.UnitPrice = 250;
         testpbe2.UseStandardPrice = false;
         insert testpbe2;
      
    test.starttest();   
    
    Oppty1.Approval_Status__c = 'Approved by AD';
    Oppty2.Approval_Status__c = 'Approved by AD';
    
    update Oppty1;
    update Oppty2;
         
                //And now you want execute the startTest method to set the context 
                //of the following apex methods as separate from the previous data 
                //preparation or DML statements.  
       
        
          // add the line item which should call the trigger
          // with this line item it should fail out quickly 
          // As Auto Schedule is false
    OpportunityLineItem oli1 = new OpportunityLineItem();
    oli1.Quantity = 1;
    oli1.TotalPrice = 1;
    oli1.PricebookEntryId = testpbe1.id;
    oli1.OpportunityId = Oppty1.id;
    oli1.AED__Sales_Mode__c = 'CPA';
    oli1.AED__Country__c = 'IT';
    insert oli1;   
      
          // add the line item which should call the trigger
          // Auto Schedule is true so it should build the schedule.
    OpportunityLineItem oli2 = new OpportunityLineItem();
    oli2.Quantity = 1;
    oli2.TotalPrice = 1;
    oli2.PricebookEntryId = testpbe2.id;
    oli2.OpportunityId = Oppty2.id;    
    oli2.AED__Sales_Mode__c = 'CPA';
    oli2.AED__Country__c = 'IT';
    insert oli2;
            
    test.stoptest();
   
    }

}

Both triggers are tested 100%.

Thanks in advance for your help.

Stefano