+ Start a Discussion
SalesforceCrm AccountCRMSalesforceCrm AccountCRM 

System.LimitException: Too many SOQL queries: 101 for triggers

When I run the test class for the one trigger ,the system throws an as :
System.LimitException: Too many SOQL queries: 101

Trigger.MergeLeadTrigger: line 276, column 1

Trigger.Accountduplicate: line 6, column 1

The Trigger which im running :
trigger CampaignMemberSyncStatus on CampaignMember (after update) {
    // Sync response status on Campaign Member to lead or contact

    // Look for status changes
    List<Id> relatedIDs = new List<ID>();
    List<Id> modifiedIDs = new List<ID>();
 
    // Get the list of IDs for campaignmembers whose status changed
    for(CampaignMember cm : Trigger.new) {
        if(cm.Response_Status__c != trigger.oldMap.get(cm.Id).Response_Status__c)
        {
            modifiedIDs.add(cm.Id);
            if(cm.ContactId != null) {
                relatedIDs.add(cm.ContactId);
            } else {
                relatedIDs.add(cm.LeadId);
            }
        }
    }
    if(modifiedIds.size() == 0) return;     /// No modified responses
    
    // Now pull the list of related leads and contacts
    Map<id,Lead> RelatedLeads;
    Map<id,Contact> RelatedContacts;
    RelatedLeads = new Map<ID,Lead>([Select id, Disqaulified_Reason__c, Status from Lead where Id in :relatedIDs]);
    RelatedContacts = new Map<ID,Contact>(
        [Select id, Status__c, Lead_Score__c, Campaign_Score__c, Account.Customer_Status__c, Admin_Nurture_Timeout__c from Contact where Id in :relatedIDs]);
    
    // Build lists of leads or contacts to update
    Map<ID,Lead> ModifiedLeads = new Map<ID,Lead>();
    Map<ID,Contact> ModifiedContacts = new Map<ID,Contact>();
    
    Map<String, CMStatusSettings__c> statusmapping = CMStatusSettings__c.getall();
    
    // Now update the stautus
    for(id cmid : modifiedIDs) {
        CampaignMember cm = trigger.newMap.get(cmid);
        Boolean wasupdated;
        if(cm.ContactId != null) {
            wasupdated = CampaignMemberFunctions.SyncStatus(cm, null, RelatedContacts.get(cm.ContactId), statusmapping);
            if(wasupdated) ModifiedContacts.put(cm.ContactID,RelatedContacts.get(cm.ContactId));
        } else {
            wasupdated = CampaignMemberFunctions.SyncStatus(cm, RelatedLeads.get(cm.LeadId), null, statusmapping);
            if(wasupdated) ModifiedLeads.put(cm.LeadId,RelatedLeads.get(cm.LeadId));
        }
    }
    if(ModifiedLeads.size()>0) update ModifiedLeads.values();
    if(ModifiedContacts.size()>0) update ModifiedContacts.values();
    
    
}
Test Class :
@isTest
public class TestCampaignMemberSync 
{

    static testMethod void TestStatusUpdates() 
    {
    Account genericaccount = new Account(Name = 'someaccount');
    insert genericaccount;

        Lead ld1 = new Lead(Company='colead1',LastName='colead1');
        Lead ld2 = new Lead(Company='colead2',LastName='colead2');
        Contact ct1 = new Contact(LastName='cocontact1', AccountID = genericaccount.Id);
        Contact ct2 = new Contact(LastName='cocontact2', AccountID = genericaccount.Id);
        Campaign cam = new Campaign(Name='campname');
        insert cam;
    insert ld1;
    insert ld2;
    insert ct1;
    insert ct2;
        CampaignMember mc1 = new CampaignMember(CampaignId = cam.Id, LeadId=ld1.Id, Buyer__c='a', user__c='b');
        CampaignMember mc2 = new CampaignMember(CampaignId = cam.Id, LeadId=ld2.Id, Buyer__c='a', user__c='b');
        CampaignMember mc3 = new CampaignMember(CampaignId = cam.Id, ContactId=ct1.Id, Buyer__c='a', user__c='b');
        CampaignMember mc4 = new CampaignMember(CampaignId = cam.Id, ContactId=ct2.Id, Buyer__c='a', user__c='b');
    List<CampaignMember> cms = new List<CampaignMember>();
    cms.add(mc1);cms.add(mc2);cms.add(mc3);cms.add(mc4);
    
    
        insert cms;
        List<Id> cmids = new List<id>();
    for(CampaignMember thiscm: cms)
    {
      cmids.add(thiscm.id);
    }
    
    ld1.admin_CMSourceId__c = mc1.id;
    update ld1;  // Needed later to simulate the button click on conversion
    ld2.admin_CMSourceId__c = mc2.id;
    update ld2; // Needed later to simulate the button click on conversion
    
    // Initialize the opp for later creation from CampaignMember
    Opportunity Opp = new Opportunity(CloseDate = Date.Today().addDays(30), AccountId = genericaccount.Id,
        Name='someopp', StageName='Selected' );
        
        Test.StartTest();
        mc1.Response_Status__c = 'Working';
        mc2.Response_Status__c = 'Disqualified';
        mc2.Disqualified_Reason__c = 'no budget';
        mc3.Response_Status__c = 'Working';
        mc4.Response_Status__c = 'Disqualified';
        mc4.Disqualified_Reason__c = 'no budget';
        mc4.Do_not_reassign_to_Landings__c = true;
        
        update cms;
       Test.StopTest();    
        
        List<CampaignMember> mcres = [Select Id, Response_Status__c, Contact.Admin_Nurture_Timeout__c , Contact.Status__c, Lead.Status, Do_not_reassign_to_Landings__c 
          from CampaignMember where Id = :cmids ];
        for(CampaignMember cm: mcres) {
          System.debug('Response ' + cm.Response_Status__c + ' lead status:' + cm.Lead.Status + ' contact status:' + cm.Contact.Status__c );
          System.debug('Nurture date:' + mcres[3].Contact.Admin_Nurture_Timeout__c);
        }
    //System.assertEquals(mcres[3].Do_not_reassign_to_Landings__c, mcres[3].Contact.Do_not_reassign_to_Landings__c);
 
     }
     
     
    static testMethod void TestConversionsNoOpp() 
    {
        Account genericaccount = new Account(Name = 'someaccount');
        insert genericaccount;

            Lead ld1 = new Lead(Company='colead1',LastName='colead1');
            Lead ld2 = new Lead(Company='colead2',LastName='colead2');
            ld2.OwnerId = UserInfo.getUserId();
            Campaign cam = new Campaign(Name='campname');
            insert cam;
            insert ld1;
            insert ld2;
            CampaignMember mc1 = new CampaignMember(CampaignId = cam.Id, LeadId=ld1.Id, Buyer__c='a', user__c='b');
            CampaignMember mc2 = new CampaignMember(CampaignId = cam.Id, LeadId=ld2.Id, Buyer__c='a', user__c='b');
            List<CampaignMember> cms = new List<CampaignMember>();
            cms.add(mc1);
            cms.add(mc2);
        
            insert cms;
            List<Id> cmids = new List<id>();
       
        
        ld1.admin_CMSourceId__c = mc1.id;
        update ld1;  // Needed later to simulate the button click on conversion
        ld2.admin_CMSourceId__c = mc2.id;
        update ld2; // Needed later to simulate the button click on conversion
        
        // Initialize the opp for later creation from CampaignMember
        Opportunity Opp = new Opportunity(CloseDate = Date.Today().addDays(30), AccountId = genericaccount.Id,
            Name='someopp', StageName='Selected' );
            
            mc1.Response_Status__c = 'Working';
            mc2.Response_Status__c = 'Disqualified';
            mc2.Disqualified_Reason__c = 'no budget';
             
            
            update cms;
            
            // We're checking CampaignMemberOpportunityInsertTrigger here
            Opp.admin_CMSourceId__c = mc2.Id;  // Set the source ID
            insert Opp;

            Test.StartTest();

            
            // Now let's do a lead convert
         Database.LeadConvert lc = new database.LeadConvert();
        lc.setLeadId(ld2.Id);
        lc.setConvertedStatus('Converted - Existing Opportunity');
        lc.setDoNotCreateOpportunity(true);
        lc.setOwnerId(UserInfo.getUserId());
        Database.LeadConvertResult lcr2 = Database.convertLead(lc);
            
              Test.StopTest();    
            try
            {
             }
            catch(Exception ex)
            {
              System.Debug('Exception during TestConversionsNoOpp StopTest (async error)' + ex.getMessage());
            }
     
         System.assert(lcr2.isSuccess());
            CampaignMember mc2b = [Select Id,Response_Status__c, Opportunity_Create_Date__c, Opportunity_Name__c, Buyer__c, User__c from CampaignMember where Id = :mc2.id];
        Lead convertedleadinfo = [Select Id, isConverted, Status from Lead where id = :ld2.Id];
        System.Debug('original leadid:' + ld2.Id + ' leadid result id:' + lcr2.getLeadId());
        System.Debug('Converted lead isconverted:' + convertedleadinfo.IsConverted + ' status:' + convertedleadinfo.Status);
           system.assertEquals('Converted - Existing Opportunity', mc2b.Response_Status__c);
     }
 }
The Error Lines Of other Triggers are
Error line  for MergeLead Trigger :

conrecords=[select id , Rep_Managing_Partner_del__c from Contact where id in: getconid1 and Rep_Managing_Partner_del__c !=null];

Error line  for AccountDuplicateTrigger :

  lstusr  =[SELECT Id,Name,Profile.Name FROM User WHERE Id=:UserInfo.getuserId()];
Any help very much appreciated.



 
Muhammad WasimMuhammad Wasim
All you have to do is to gather all the same type of inserts add them into a list and then insert that list into force.com, I can see many insert statements in your code. This is the only issue I think you have in your code. Bulkify your code to get rid of these errors. And do not use any SOQL inside a loop, if there is any in your code. 
BALAJI CHBALAJI CH
Hi,

The "System.LimitException: Too many SOQL queries: 101" error appears when you've hit the Execution Governors limit, which means you can run a total 100 SOQL queries in a context.  All the triggers fired will be counted in a single context or call. To fix the issue, you'll need to change your code in such a way that SOQL fired is less than 100.

Please find below links which are helpful.

https://help.salesforce.com/apex/HTViewSolution?id=000181404&language=en_US
https://help.salesforce.com/apex/HTViewSolution?id=000213152&language=en_US
https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_triggers_bestpract.htm
http://www.sfdc99.com/2013/11/23/the-three-most-common-governor-limits-and-why-youre-getting-them/

I would like to know you if that helps you.

Best Regards,
BALAJI
Amit Chaudhary 8Amit Chaudhary 8
This issue can be come because of below issue
1) Recursive Trigger
2) SOQL inside for loop

Please check below post. I hope that will help you
1) https://help.salesforce.com/apex/HTViewSolution?id=000181404&language=en_US
2) http://amitsalesforce.blogspot.in/2015/06/trigger-best-practices-sample-trigger.html
3) http://amitsalesforce.blogspot.in/2015/03/how-to-stop-recursive-trigger-in.html

Here are some best practices that will stop the error messages and/or help you avoid hitting the Governors Limit: 
 
1. Since Apex runs on a Multitenant structure, Apex runtime engine strictly enforces limits to ensure code doesn't monopolize shared resources. Learn about the Governors Limit.
2. Avoid SOQL queries that are inside FOR loop. 
3. Check out the Salesforce Developer Blog where you can find Best Practices for Triggers.
4. Review best practices for Trigger and Bulk requests on our Force.com Apex Code Developer's Guide. 
5. Be sure you're following the key coding principals for Apex Code in our Developer's Guide.

Trigger Best Practices for Trigger.

1) One Trigger Per Object
A single Apex Trigger is all you need for one particular object. If you develop multiple Triggers for a single object, you have no way of controlling the order of execution if those Triggers can run in the same contexts

2) Logic-less Triggers
If you write methods in your Triggers, those can’t be exposed for test purposes. You also can’t expose logic to be re-used anywhere else in your org.

3) Context-Specific Handler Methods
Create context-specific handler methods in Trigger handlers

4) Bulkify your Code
Bulkifying Apex code refers to the concept of making sure the code properly handles more than one record at a time.

5) Avoid SOQL Queries or DML statements inside FOR Loops
An individual Apex request gets a maximum of 100 SOQL queries before exceeding that governor limit. So if this trigger is invoked by a batch of more than 100 Account records, the governor limit will throw a runtime exception

6) Using Collections, Streamlining Queries, and Efficient For Loops
It is important to use Apex Collections to efficiently query data and store the data in memory. A combination of using collections and streamlining SOQL queries can substantially help writing efficient Apex code and avoid governor limits

7) Querying Large Data Sets
The total number of records that can be returned by SOQL queries in a request is 50,000. If returning a large set of queries causes you to exceed your heap limit, then a SOQL query for loop must be used instead. It can process multiple batches of records through the use of internal calls to query and queryMore

8) Use @future Appropriately
It is critical to write your Apex code to efficiently handle bulk or many records at a time. This is also true for asynchronous Apex methods (those annotated with the @future keyword). The differences between synchronous and asynchronous Apex can be found

9) Avoid Hardcoding IDs
When deploying Apex code between sandbox and production environments, or installing Force.com AppExchange packages, it is essential to avoid hardcoding IDs in the Apex code. By doing so, if the record IDs change between environments, the logic can dynamically identify the proper data to operate against and not fail

Let us know if this will help you

Thanks
Amit Chaudhary
ShantinathShantinath
The trigger that you have posted is not the issue. Main issue lies in other triggers getting fired as mentioned in your error message. The best way to start debugging is to start with 
Accountduplicate

trigger. 
SalesforceCrm AccountCRMSalesforceCrm AccountCRM
@Muhammad Wasim,@BALAJI CH,@Amit Chaudhary 8,@Shantinath :Thanks for the response.
Now the system throws an error when i just modified the test class :
System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, MergeLeadTrigger: execution of AfterInsert

caused by: System.DmlException: Insert failed. First exception on row 0; first error: INVALID_CROSS_REFERENCE_KEY, invalid cross reference id: []

Trigger.MergeLeadTrigger: line 430, column 1: []
Part of Test class :
@isTest
public class TestCampaignMemberSync 
{

    static testMethod void TestStatusUpdates() 
    {
    //Test.StartTest();
    
    Account genericaccount = new Account(Name = 'someaccount');
    insert genericaccount;
    
       /* Lead ld1 = new Lead(Company='colead1',LastName='colead1');*/
        Lead ld2 = new Lead(Company='colead2',LastName='colead2');
        Contact ct1 = new Contact(LastName='cocontact1', AccountID = genericaccount.Id);
        Contact ct2 = new Contact(LastName='cocontact2', AccountID = genericaccount.Id);
       /* Campaign cam = new Campaign(Name='campname');
        insert cam;*/
   /* insert ld1;*/
    insert ld2;
    insert ct1;
    insert ct2;
    
     Profile p = [SELECT id ,Name FROM Profile WHERE Name='Custom Partner Community user'];

        User u = new User(
            FirstName = 'test', 
            LastName= 'last_test', 
            Email='test323232@test.com', 
            Phone='111-111-1111', 
            //True_Market_Unit__c='1111', 
            alias = 'test', 
            EmailEncodingKey='UTF-8',
            ProfileId = p.Id,
            LanguageLocaleKey='en_US',
            LocaleSidKey='en_US', 
            TimeZoneSidKey='America/Los_Angeles',
            UserName = 'test323232@test.com'
            );
         u.ContactId=ct1.Id;
        insert u;
       System.runAs(u) {
             test.startTest();
             Lead ld1 = new Lead(Company='colead1',LastName='colead1',country='USA',Rep_Managing_Partner__c = u.id);
             insert ld1;
             Campaign cam = new Campaign(Name='campname');
             insert cam;
             
    
        CampaignMember mc1 = new CampaignMember(CampaignId = cam.Id, LeadId=ld1.Id, Buyer__c='a', user__c='b');
        CampaignMember mc2 = new CampaignMember(CampaignId = cam.Id, LeadId=ld2.Id, Buyer__c='a', user__c='b');
        CampaignMember mc3 = new CampaignMember(CampaignId = cam.Id, ContactId=ct1.Id, Buyer__c='a', user__c='b');
        CampaignMember mc4 = new CampaignMember(CampaignId = cam.Id, ContactId=ct2.Id, Buyer__c='a', user__c='b');
    List<CampaignMember> cms = new List<CampaignMember>();
    cms.add(mc1);cms.add(mc2);cms.add(mc3);cms.add(mc4);
    
    
        insert cms;
        List<Id> cmids = new List<id>();
    for(CampaignMember thiscm: cms)
    {
      cmids.add(thiscm.id);
    }
    
    ld1.admin_CMSourceId__c = mc1.id;
    update ld1;  // Needed later to simulate the button click on conversion
    ld2.admin_CMSourceId__c = mc2.id;
    update ld2; // Needed later to simulate the button click on conversion
    
    
    
    // Initialize the opp for later creation from CampaignMember
    Opportunity Opp = new Opportunity(CloseDate = Date.Today().addDays(30), AccountId = genericaccount.Id,
        Name='someopp', StageName='Selected' );
        
       // Test.StartTest();
        mc1.Response_Status__c = 'Working';
        mc2.Response_Status__c = 'Disqualified';
        mc2.Disqualified_Reason__c = 'no budget';
        mc3.Response_Status__c = 'Working';
        mc4.Response_Status__c = 'Disqualified';
        mc4.Disqualified_Reason__c = 'no budget';
        mc4.Do_not_reassign_to_Landings__c = true;
        
        update cms;
       Test.StopTest();    
        
        List<CampaignMember> mcres = [Select Id, Response_Status__c, Contact.Admin_Nurture_Timeout__c , Contact.Status__c, Lead.Status, Do_not_reassign_to_Landings__c 
          from CampaignMember where Id = :cmids ];
        for(CampaignMember cm: mcres) {
          System.debug('Response ' + cm.Response_Status__c + ' lead status:' + cm.Lead.Status + ' contact status:' + cm.Contact.Status__c );
          System.debug('Nurture date:' + mcres[3].Contact.Admin_Nurture_Timeout__c);
        }
    //System.assertEquals(mcres[3].Do_not_reassign_to_Landings__c, mcres[3].Contact.Do_not_reassign_to_Landings__c);
 
     }
    }
The line 430 for MergeLead Trigger is
        insert newcampaign;
Any help very much appreciated.