Getting 101 SOQL limit exceeded error

Question

I have two triggers : one on parent object(Opportunity) to update fields on child(opportunity_partner__c)and the other is on child(opportunity_partner__c) to update fields on Opportunity. I'm getting too many SOQL 101 error. Please advise what is wrong. When i looked into log files, the child object trigger is firing multiple times. Can anyone tell me how to solve this?

Child Trigger: trigger MFE_Impartner_Ptnr2OptySync on Opportunity_Partner__c (after insert, after update) {

 public static boolean isOptyRecusrsive = true;

ISecG_Trigger_Settings__c objTriggerSetting = ISecG_Trigger_Settings__c.getInstance();
if(objTriggerSetting.Enable_OptyAssocSyncTrigger__c && isOptyRecusrsive){

Set<Id> Ids= new Set<Id>();

for(Opportunity_Partner__c records : Trigger.new)
{
    Ids.add(records.id);
}

List<Opportunity_Partner__c> optyPtnrList = new List<Opportunity_Partner__c>([select Opportunity__r.rvpe__IsDealRegistration__c, Opportunity__r.PCT_Review_Status__c, PCT_Review_Status__c,Partner_Account__c,Is_Deal_Registration__c,Partner_Account__r.id,Partner_Account__r.Partner_Type__c,Partner_Account__r.rvpe__RVAccount__c, RV_Member_Associate__c, Opportunity__c,RV_Member__c, Opportunity__r.rvpe__RVAccount__c, Partner_Opty_ID__c, Opportunity__r.id, Partner_Quote_ID__c, Disti_Locked__c, Program_Benefit__c, Deal_Registration_Type__c, Deal_Registration_Status__c, Product_Type__c, Approved_Product_Types__c, Partner_Forecast_Amount__c, DMP_Relationship__c, Displacement_Competitor__c,Partner_Win_Probability__c, Partner_Expected_Close_Date__c, Partner_Expiration_Date__c,Opportunity__r.Disti_Locked_Status__c, Opportunity__r.RV_Member_Assoc__c, Opportunity__r.RV_Account_Asscoiate__c, Opportunity__r.rvpe__RVMember__c, Opportunity__r.Program_Benefit__c, Opportunity__r.Deal_Registration_Type__c, Opportunity__r.Deal_Registration_Status__c, Opportunity__r.Submitted_Product_Type__c, Opportunity__r.Approved_Product_Types__c,  Opportunity__r.Partner_Opty_ID__c, Opportunity__r.Partner_Quote_ID__c, Opportunity__r.Partner_Forecast_Amount__c, Opportunity__r.Transaction_Type__c, Opportunity__r.Displacement_Competitor__c, Opportunity__r.Partner_Win_Probability__c, Opportunity__r.DMP_Relationship__c, Opportunity__r.Partner_Expected_Close_Date__c, Opportunity__r.Partner_Deal_Expiration_Date__c from Opportunity_Partner__c where id in: ids]);
List<Opportunity> lstOpty = new List<Opportunity>();
Map<Id, Opportunity> optyMapToUpd = new Map<Id, Opportunity>();

for(Opportunity_Partner__c newOptyPtnr : optyPtnrList)
{
    if(newOptyPtnr.Partner_Account__c != null)
    {
        system.debug('Should not enter this');
        Boolean optyUpdFlg = false;
        Opportunity opty = newOptyPtnr.Opportunity__r;
        if(newOptyPtnr.Partner_Account__r.Partner_Type__c != null && newOptyPtnr.Partner_Account__r.Partner_Type__c == 'Reseller' && opty.rvpe__RVAccount__c==null)
        {
            opty.rvpe__RVAccount__c = newOptyPtnr.Partner_Account__r.rvpe__RVAccount__c;
            optyUpdFlg = true; 
        }

        if(newOptyPtnr.Partner_Account__r.Partner_Type__c != null && newOptyPtnr.Partner_Account__r.Partner_Type__c == 'Distributor' && opty.RV_Account_Asscoiate__c==null){
            opty.RV_Account_Asscoiate__c = newOptyPtnr.Partner_Account__r.rvpe__RVAccount__c;
            optyUpdFlg = true;
            if(newOptyPtnr.Disti_Locked__c != null && opty.Disti_Locked_Status__c == null)
                opty.Disti_Locked_Status__c = newOptyPtnr.Disti_Locked__c;

        }

        if(newOptyPtnr.Is_Deal_Registration__c != null && opty.rvpe__IsDealRegistration__c == null){
            opty.rvpe__IsDealRegistration__c = newOptyPtnr.Is_Deal_Registration__c;
            optyUpdFlg = true;
        }
        if(newOptyPtnr.RV_Member_Associate__c != null && opty.RV_Member_Assoc__c == null)
        {
            opty.RV_Member_Assoc__c = newOptyPtnr.RV_Member_Associate__c;
            optyUpdFlg = true;
        }

        if(newOptyPtnr.RV_Member__c != null && opty.rvpe__RVMember__c == null)
        {
            opty.rvpe__RVMember__c = newOptyPtnr.RV_Member__c;
            optyUpdFlg = true;
        }
        if(newOptyPtnr.PCT_Review_Status__c != null && opty.PCT_Review_Status__c != newOptyPtnr.PCT_Review_Status__c){
            opty.PCT_Review_Status__c = newOptyPtnr.PCT_Review_Status__c;
            optyUpdFlg = true;
        }

        if(newOptyPtnr.Program_Benefit__c != null && opty.Program_Benefit__c == null){
            opty.Program_Benefit__c = newOptyPtnr.Program_Benefit__c;
            if(newOptyPtnr.Program_Benefit__c == 'Deal Registration')
                opty.rvpe__IsDealRegistration__c = true;
            optyUpdFlg = true;
        }
        if(newOptyPtnr.Deal_Registration_Type__c != null && opty.Deal_Registration_Type__c == null)
        {
            opty.Deal_Registration_Type__c = newOptyPtnr.Deal_Registration_Type__c;
            optyUpdFlg = true;
        }
        if(newOptyPtnr.Deal_Registration_Status__c != null && opty.Deal_Registration_Status__c == null)
        {
            opty.Deal_Registration_Status__c = newOptyPtnr.Deal_Registration_Status__c;
            optyUpdFlg = true;
        }
        if(newOptyPtnr.Product_Type__c != null && opty.Submitted_Product_Type__c == null)
        {
            opty.Submitted_Product_Type__c = newOptyPtnr.Product_Type__c;
            optyUpdFlg = true;
        }
        if(newOptyPtnr.Approved_Product_Types__c != null && opty.Approved_Product_Types__c == null)
        {
            opty.Approved_Product_Types__c = newOptyPtnr.Approved_Product_Types__c;
            optyUpdFlg = true;
        }
        if(newOptyPtnr.Partner_Opty_ID__c != null && opty.Partner_Opty_ID__c == null)
        {
            opty.Partner_Opty_ID__c = newOptyPtnr.Partner_Opty_ID__c;
            optyUpdFlg = true;
        }
        if(newOptyPtnr.Partner_Quote_ID__c != null && opty.Partner_Quote_ID__c == null)
        {
            opty.Partner_Quote_ID__c = newOptyPtnr.Partner_Quote_ID__c;
            optyUpdFlg = true;
        }
        if(newOptyPtnr.Partner_Forecast_Amount__c != null && opty.Partner_Forecast_Amount__c == null)
        {
            opty.Partner_Forecast_Amount__c = newOptyPtnr.Partner_Forecast_Amount__c;
            optyUpdFlg = true;
        }

        if(newOptyPtnr.Displacement_Competitor__c != null && opty.Displacement_Competitor__c == null)
        {
            opty.Displacement_Competitor__c = newOptyPtnr.Displacement_Competitor__c;
            optyUpdFlg = true;
        }
        if(newOptyPtnr.Partner_Win_Probability__c != null && opty.Partner_Win_Probability__c == null)
        {
            opty.Partner_Win_Probability__c = newOptyPtnr.Partner_Win_Probability__c;
            optyUpdFlg = true;
        }
        if(newOptyPtnr.DMP_Relationship__c != null && opty.DMP_Relationship__c == null)
        {
            opty.DMP_Relationship__c = newOptyPtnr.DMP_Relationship__c;
            optyUpdFlg = true;
        }
        if(newOptyPtnr.Partner_Expected_Close_Date__c != null && opty.Partner_Expected_Close_Date__c == null)
        {
            opty.Partner_Expected_Close_Date__c = newOptyPtnr.Partner_Expected_Close_Date__c;
            optyUpdFlg = true;
        }
        if(newOptyPtnr.Partner_Expiration_Date__c != null && opty.Partner_Deal_Expiration_Date__c == null)
        {
            opty.Partner_Deal_Expiration_Date__c = newOptyPtnr.Partner_Expiration_Date__c;
            optyUpdFlg = true;
        }

        if(optyUpdFlg == true){
            if(!optyMapToUpd.containsKey(opty.Id)){
                optyMapToUpd.put(opty.Id, opty);
            }
            //lstOpty.add(opty);
        }

    }

}

if(optyMapToUpd.size()>0 && isOptyRecusrsive){
    isOptyRecusrsive = false;
    update optyMapToUpd.values();
}
} 

}

Parent Trigger: https://paste.ofcode.org/36wZg6MYRtKjtFHMpfRczpC

Answer 1

You have to handle recursion in this case, your trigger runs multiple times when the Opportunity any and the Opportunity_Partner__c records are updated. You need to prevent recursion using the static variables instead of initializing the variable mapOpty inside the trigger, please create another class in which create two static Set<Id> to prevent recursion like this:

public with sharing class StaticVariableHelper {

    public static Set<Id> preventRecOppIdSet = new Set<Id>();
    public static Set<Id> preventRecOppPartnerIdSet = new Set<Id>();
}

Then add the following line in Opportunity trigger inside the for loop where you have queried your Opportunity records:

if( !preventRecOppIdSet.contains( opp.Id ) ) {
    preventRecOppIdSet.add( opp.Id );
    //Your logic goes here
}

Do the same thing in your Opportunity Partner trigger in the for loop:

if( !preventRecOppPartnerIdSet.contains( newOptyPtnr.Id ) ) {
    preventRecOppPartnerIdSet.add( newOptyPtnr.Id );
    //Your logic goes here
}

This should prevent the recursion in both of your triggers.