+ Start a Discussion
Jay reddyJay reddy 

Too many soql queries error: 101 Trigger

Hi,

Could someone help me on this soql 101 queries error issue. I'm hitting the error for this query "oldCRList=[Select Id from OpportunityContactRole where ContactId in : ContactId  and OpportunityId in : OppId];" . I know querying inside the for loop is the root cause for this issue but could someone help me on this, please.
 
trigger OpportunityPrimaryContactRole on Opportunity (after insert, after update) { 
    if(CheckRecursive.executeOpportunityPrimaryContactRoleTrigger == false){return;}
    
    
    List<Opportunity> NewOpptyLst = new List<Opportunity> ();
    Map<id,Opportunity> OpptyOldMap = new Map<id,Opportunity>();
    NewOpptyLst = Trigger.new;
    OpptyOldMap = Trigger.OldMap;
    
    
    List<OpportunityContactRole> newCRList = new List<OpportunityContactRole>();
    List<OpportunityContactRole> oldCRList = new List<OpportunityContactRole>();
    
    
    Set<Id> OppId = new Set<Id>();
    Set<Id> ContactId = new Set<Id>();     

    List<OpportunityContactRole> CRList = [ Select Id from OpportunityContactRole where ContactId in : ContactId  and OpportunityId in : OppId];
    
    
    for(Opportunity o: NewOpptyLst)
    {//Checks if the Opportunity is being inserted
        if(Trigger.isInsert)
        {    
            if(o.Buyer_Name__c != null)
            {   //Creates the new OCR       
                newCRList.add(new OpportunityContactRole (ContactId=o.Buyer_Name__c, OpportunityId=o.Id, Role='Decision Maker',IsPrimary=TRUE));                        
            } 
        }
        else 
        {
       
            if(o.Buyer_Name__c != null && OpptyOldMap.get(o.Id).Buyer_Name__c != null)
                { //Gets the Contact and Opportunity Id from the prior values and adds to this set         
                    try
                    {
                        Opportunity oldOppObj=OpptyOldMap.get(o.Id);
                        OppId.add(OldoppObj.id);
                        ContactId.add(oldOppObj.Buyer_Name__c); 
                        System.debug('Opp Size:' + OppId.size());
                        if (OppId.size()>0) 
                        oldCRList=[Select Id from OpportunityContactRole where ContactId in : ContactId  and OpportunityId in : OppId]; 
                        
                        
                        
                        if(oldOppObj.Buyer_Name__c != o.Buyer_Name__c)
                        {
                            if (oldCRList.size()>0)                         
                            delete oldCRList;                            
                            newCRList.add(new OpportunityContactRole (ContactId=o.Buyer_Name__c, OpportunityId=o.Id, Role='Decision Maker',IsPrimary=True));                                                

                        }
                        else
                        {  
                            if (oldCRList.size()==0)
                            {    System.debug('- Update during no single contact role and having buyer name');                         
                            newCRList.add(new OpportunityContactRole (ContactId=o.Buyer_Name__c, OpportunityId=o.Id, Role='Decision Maker',IsPrimary=True));                            
                            }
                            else
                            {   System.debug('- Update during no primary contact role and having buyer name');                        
                                if (oldCRList.size()==1) 
                                {
                                  for (OpportunityContactRole old:oldCRList)
                                   {
                                   old.Role = 'Decision Maker';
                                   old.IsPrimary=True;
                                   update oldCRList;
                                   }
                                }
                            }
                            
                        }
                    } 
                    catch(Exception e)
                    {  System.debug(e);
                    } 
                }   
        }
    }
    try
    { 
        if(newCRList.size()>0) insert newCRList;
    }   
   catch(Exception e)
    {        trigger.new[0].addError('A technical error has occurred creating the Opportunity Contact Role.');
    }

}

 
Nayana KNayana K
public class OpportunityHandler
{
	public OpportunityHandler()
	{}
	
	public void onAfterInsert(List<Opportunity> lstNewOpp)
	{
		createOCRs(new Map<Id, Opportunity>(), lstNewOpp);
	}
	
	public void onAfterUpdate(Map<Id, Opportunity> mapOldOpp, List<Opportunity> lstNewOpp)
	{
		createOCRs(mapOldOpp, lstNewOpp);
	}
	
	private void createOCRs(Map<Id, Opportunity> mapOldOpp, List<Opportunity> lstNewOpp)
	{
		Set<Id> setIdOpp = new Set<Id>();
		Set<Id> setIdCon = new Set<Id>();
		Set<String> setOppConIdsComboToInsert = new Set<String>();
		Set<String> setOppConIdsComboToDelete = new Set<String>();
		List<OpportunityContactRole> lstOCRToDelete = new List<OpportunityContactRole>();
		List<OpportunityContactRole> lstOCRToInsert = new List<OpportunityContactRole>();
		Id idCon, idOpp;
		String strPair;
		
		for(Opportunity objOpp : lstNewOpp)
		{
			if((Trigger.isInsert) || (Trigger.isUpdate && objOpp.Buyer_Name__c != mapOldOpp.get(objOpp.Id).Buyer_Name__c)))
			{
				if(Trigger.isUpdate && mapOldOpp.get(objOpp.Id).Buyer_Name__c != NULL)
				{
					idCon = mapOldOpp.get(objOpp.Id).Buyer_Name__c;
					setIdCon.add(idCon);
					setIdOpp.add(objOpp.Id);
					setOppConIdsComboToDelete.add(objOpp.Id + '__' + idCon);
				}
				if(objOpp.Buyer_Name__c != NULL)
				{
					setOppConIdsComboToInsert.add(objOpp.Id + '__' + objOpp.Buyer_Name__c);
				}
			}
		}
		
		
		for(OpportunityContactRole objOCR : [SELECT Id, ContactId, OpportunityId 
											FROM OpportunityContactRole 
											WHERE OpportunityId IN: setIdOpp AND ContactId IN: setIdCon AND Role = 'Decision Maker'])
		{
			strPair = objOCR.OpportunityId + '__' + objOCR.ContactId;
			if(setOppConIdsComboToDelete.contains(strPair))
			{
				lstOCRToDelete.add(objOCR.Id);
			}
		}
		
		if(!lstOCRToDelete.isEmpty())
		{
			delete lstOCRToDelete;
		}
		
		for(String strCombo : setOppConIdsComboToInsert)
		{
			idCon = strCombo.substringAfter('__');
			idOpp = strCombo.substringBefore('__');
			lstOCRToInsert.add(new OpportunityContactRole (ContactId = idCon, OpportunityId = idOpp, Role='Decision Maker', IsPrimary = TRUE)); 
		}
		
		if(!lstOCRToInsert.isEmpty())
		{
			insert lstOCRToInsert;
		}
	}
}
 
trigger OpportunityPrimaryContactRole on Opportunity (after insert, after update) { 
	
	OpportunityHandler objHandler = new OpportunityHandler();
	
	if(Trigger.isAfter && Trigger.isInsert)
	{
		objHandler.onAfterInsert(Trigger.New);
	}
	else if(Trigger.isAfter && Trigger.isUpdate)
	{
		objHandler.onAfterUpdate(Trigger.oldMap, Trigger.New);
	}
}