function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion

Opportunity Trigger to insert Case - Test failure



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)
//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)

 for (Opportunity opp : 
    if ( (opp.approval_status__c == 'Approved by AD' ) &&
         (trigger.oldMap.get( != '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', 
       OwnerId = CaseUSers.get('Traffic List') ); 
       insert c; 
    else if ((opp.approval_status__c == 'Approved by AD' ) &&
         (trigger.oldMap.get( != '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', 
       OwnerId = CaseUSers.get('Traffic List')  ); 
       insert c; 

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.
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 = '';
        user1.Username = '';  
        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 =;
        Oppty1.AED__Campaign_Start_Date__c =;
        Oppty1.AED__Campaign_Close_Date__c =;
        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 =;
         testpbe1.product2id =;
         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 =;
        Oppty2.AED__Campaign_Start_Date__c =;
        Oppty2.AED__Campaign_Close_Date__c =;
        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 =;
         testpbe2.product2id =;
         testpbe2.IsActive = True;
         testpbe2.UnitPrice = 250;
         testpbe2.UseStandardPrice = false;
         insert testpbe2;
    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 =;
    oli1.OpportunityId =;
    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 =;
    oli2.OpportunityId =;    
    oli2.AED__Sales_Mode__c = 'CPA';
    oli2.AED__Country__c = 'IT';
    insert oli2;




Both triggers are tested 100%.


Thanks in advance for your help.






I don't see anything obvious, but the 21 queries will cover all the scope, so if you have triggers on case, account with select clauses, that will count toward the 21. 


So you might need to split the test into two separate tests, once for each opty which each then have their own scope, which would more closely mimic two separate executions by a user.


But one thing I did notice is that I think you are doing a DML insert statement on case within the loop - you should change that to add each new case to a list, and then execute one insert after the end of the loop - that might reduce the calls you are making, and without that change, this would not work in bulk.

Hi @Stefandl  and @BritishBoyinDC - Have you guys made this trigger work, I am looking at creating exactly the same thing, it would be nice to know how you resolved the issue you were having. Also, if you could post the final code. 
Thanks - Veronica