+ Start a Discussion
PXForcePXForce 

Too many SOQL queries: 101

I have a trigger that runs on lead update (when a lead is converted to a contact.) . We use demand tools to dedupe leads to leads and then leads to contacts. When we run demand tools its giving me this error

 

UpdateContactOnLeadUpdate: execution of AfterUpdate

 

caused by: System.Exception: Too many SOQL queries: 101

 

Trigger.UpdateContactOnLeadUpdate: line 13, column 25

 

I am pasting the code for my trigger below. Line 13 is where I am getting the recordTypes . Its a single query. I am not sure why its causing this error. 

 

 

trigger UpdateContactOnLeadUpdate on Lead (after Update)
{
  List<String> conIds = new List<String>();
  List<String> leadIds = new List<String>();
  
  Map<String,Lead> leadMap = new map<String,Lead>();
  Map<String,Contact> contactMap = new map<String,Contact>();
  List<Key_Event__c> keyEventsToInsert = new List<Key_Event__c>();
  List<Lead_Key_Event__c> keyEventsToDelete = new List<Lead_Key_Event__c>();
  
  List<Contact> consToUpdate = new List<Contact>();
  
  RecordType[] recType = [Select Id, Name, SobjectType from RecordType 
                                            Where SOBjectType =: 'Key_Event__c'];line13
  
    For(Lead lead : Trigger.new)
    {
        //the lead is just converted
        if(lead.IsConverted == true && Trigger.oldMap.get(lead.Id).IsConverted == false)
        {
          leadIds.add(lead.Id);
          
          leadMap.put(lead.Id,lead);
          
          conIds.Add(lead.ConvertedContactId);
        }
    }
    
    //get the key events for the leads
    List<Lead_Key_Event__c> leadKeyEvents = [Select k.Auto_Demo_Field_1__c,k.Appt_Scheduled_Date__c, k.Auto_demo_Field_2__c,k.Lead_Confirmed_by_RM_NAM__c, 
                      k.Best_time_to_contact_prospects__c,k.Contact_Sales_Comments__c, 
                      k.CreatedById, k.CreatedDate, k.Id, k.IsDeleted, k.Key_Event_Type__c, 
                      k.LastModifiedById, k.LastModifiedDate, k.Lead__c, k.Lead_Created_Date__c, k.Name, 
                      k.Primary_Business_Pain__c, k.Purchase_decision_timeframe__c, 
                      k.Score__c, k.SystemModstamp, k.Trade_Show_Comments__c, k.Webinar_date__c, 
                      k.Webinar_request_date__c, k.Webinar_status__c from Lead_Key_Event__c k where Lead__c IN: leadIds Order By k.CreatedDate ASC];   
    
    
    
    
            //get the contact associated with the lead
    Contact[] contacts = [select Id , Last_KE_Date__c , CPM_Autodemo__c , White_Paper_Download__c,
              Contact_Sales_Request_Form__c,Purchase_Decision_Pending__c ,Recorded_Webinar__c,
              Webcast__c,Webinar__c, AccountId,pi__score__c
                         from Contact Where Id IN :conIds ];
                         
    //set the contact map                     
    For(Contact con : contacts)
    {
      contactMap.put(con.Id,con);
    }
    
    //run through all the lead key events and create corresponding key events for contacts/accounts
    For(Lead_Key_Event__c ke : leadKeyEvents)
    {
      Lead leadObject = leadMap.get(ke.Lead__c);
      Contact contactObject = contactMap.get(leadObject.ConvertedContactId);
    
      Key_Event__c keyEvent = new Key_Event__c();
      keyEvent.Contact__c = contactObject.Id;
      keyEvent.Account__c = contactObject.AccountId;
      keyEvent.Auto_Demo_Field_1__c = ke.Auto_Demo_Field_1__c;
      keyEvent.Auto_demo_Field_2__c = ke.Auto_demo_Field_2__c;
      keyEvent.Best_time_to_contact_prospects__c = ke.Best_time_to_contact_prospects__c;
      keyEvent.Contact_Sales_Comments__c = ke.Contact_Sales_Comments__c;
      keyEvent.Key_Event_Type__c = ke.Key_Event_Type__c;
      keyEvent.Lead_Created_Date__c = ke.Lead_Created_Date__c;
      keyEvent.Name = ke.Name;
      keyEvent.Primary_Business_Pain__c = ke.Primary_Business_Pain__c;
      keyEvent.Purchase_decision_timeframe__c = ke.Purchase_decision_timeframe__c;
      keyEvent.Score__c = ke.Score__c;
      keyEvent.Trade_Show_Comments__c = ke.Trade_Show_Comments__c;
      keyEvent.Webinar_date__c = ke.Webinar_date__c;
      keyEvent.Webinar_request_date__c = ke.Webinar_request_date__c;
      keyEvent.Webinar_status__c = ke.Webinar_status__c;
      keyEvent.Appt_Scheduled_Date__c = ke.Appt_Scheduled_Date__c;
      keyEvent.Lead_Confirmed_by_RM_NAM__c = ke.Lead_Confirmed_by_RM_NAM__c;
      
      String recTypeToSearch;
      
      if(keyEvent.Name == 'Auto Demo')
        recTypeToSearch = 'Key Event Auto Demo';
      if(keyEvent.Name == 'Auto Demo Click Through')
        recTypeToSearch = 'Key Event Auto Demo';
      if(keyEvent.Name == 'White Paper Download')
        recTypeToSearch = 'Key Event White Paper Download';
      if(keyEvent.Name == 'Contact Sales request form')
        recTypeToSearch = 'Contact Sales Request form layout';
      if(keyEvent.Name == 'Purchase Decision pending')
        recTypeToSearch = 'Purchase decision pending';
      if(keyEvent.Name == 'Recorded Webinar')
        recTypeToSearch = 'Recorded webinar';
      if(keyEvent.Name == 'Webcast Attendence')
        recTypeToSearch = 'Webcast attendence';
      if(keyEvent.Name == 'Webinar Request')
        recTypeToSearch = 'Webinar Request';
      if(keyEvent.Name == 'Trade Show')
        recTypeToSearch = 'TradeShow';
      if(keyEvent.Name == 'Webinar Attendence')
        recTypeToSearch = 'Webinar Attendence';
      if(keyEvent.Name == 'Webinar Registration')
        recTypeToSearch = 'Webinar Registration';
      if(keyEvent.Name == 'Appointment Scheduled')
        recTypeToSearch = 'Appointment Scheduled';
      if(keyEvent.Name == 'Appointment Attended')
        recTypeToSearch = 'Appointment Attended';
        
      
      For(RecordType rec : recType)
        {
            if(rec.Name == recTypeToSearch)
                keyEvent.RecordTypeId = rec.Id;
        }
  
      if(contactObject.Last_KE_Date__c == null || (contactObject.Last_KE_Date__c < keyEvent.Lead_Created_Date__c))
        contactObject.Last_KE_Date__c = keyEvent.Lead_Created_Date__c;
      
      if(leadObject.pi__score__c != null)
        {
          if(contactObject.pi__score__c == null)
            contactObject.pi__score__c = leadObject.pi__score__c;
          else
            contactObject.pi__score__c += leadObject.pi__score__c;
        }
      
      List<Contact> refinedContacts = new List<Contact>();
      For(Integer i = 0 ; i < consToUpdate.size() ; i++)
      {
        if(consToUpdate[i].Id != contactObject.id)
        {
          refinedContacts.add(consToUpdate[i]);
        }
      }
      consToUpdate = refinedContacts;
      
      consToUpdate.add(contactObject);
      keyEventsToInsert.add(keyEvent);
      keyEventsToDelete.add(ke);
      
    }
      
    if(keyEventsToInsert.size() > 0)
      insert keyEventsToInsert;  
      
    if(consToUpdate.size() > 0)
      update consToUpdate;
      
    if(keyEventsToDelete.size() > 0)
      delete keyEventsToDelete;
}

 

 

 

 

grigri9grigri9

It doesn't look like there are any issues with queries in that particular trigger. Most likely some other piece of code in the same apex transaction is not properly bulkified. Turn on the debug log when you run demand tools and see what's using up all those queries.

krishnagkrishnag

the problem is you should not have any soql queries in a loop.Take the lists u have created and keep them out of the loop and change the code accordingly.

PXForcePXForce

There are no soql queries in any loop. All the DML statements are outside the loop and just thrown once.

krishnagkrishnag

sorry my Bad i overlooked the code the code looks fine. there is somehwere it is getting looped so its hitting too many queries.

CoryCowgillCoryCowgill

I don't see anything wrong with your SOQL, it all looks bulkified to me.

 

Have you confirmed that your not recursively calling the trigger.

 

I see you have DML statements which are inserting/deleting objects at the end of the trigger. Do any of those triggers in turn update the Lead or Contacts objects which would cause this trigger to fire again?