+ Start a Discussion
Olver_BassovOlver_Bassov 

Too many SOQL queries: 101

Hi I'm having the error on my trigger

CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY:CreateOppForNewSubTriggger: System.LimitException: Too many SOQL queries: 101:--

The trigger is 
 
trigger ZuoraModoProductUpdate on Zuora__SubscriptionProductCharge__c (before insert, before update) {

    Set<Id> zsub_ids = new Set<Id>();
    for(Zuora__SubscriptionProductCharge__c zspc: trigger.new)
    {
        zsub_ids.add(zspc.Zuora__Subscription__c);
    }
    
    List<Zuora__Subscription__c> zsubs = [SELECT Id, Opportunity__c FROM Zuora__Subscription__c WHERE Id IN :zsub_ids];
    
    Map<Id, Zuora__Subscription__c> id_zsubs = new Map<Id, Zuora__Subscription__c>();
    Set<Id> opp_ids = new Set<Id>();
    for(Zuora__Subscription__c zsub : zsubs)
    {
        id_zsubs.put(zsub.Id, zsub);
        opp_ids.add(zsub.Opportunity__c);
    }
    
    List<Opportunity> opps = [SELECT Id, Modo_Web__c FROM Opportunity WHERE Id IN :opp_ids];
    
    Map<Id, Opportunity> id_opps = new Map<Id, Opportunity>();
    for(Opportunity opp : opps)
    {
        id_opps.put(opp.Id, opp);
    }
    
    List<Opportunity> update_opps = new List<Opportunity>();

    for(Zuora__SubscriptionProductCharge__c zspc: trigger.new)
    {
        if(zspc.Zuora__ProductName__c != null && zspc.Zuora__ProductName__c.toLowerCase().contains('modo')) 
        {
            if(zspc.Zuora__Subscription__c != null)
            {
                Zuora__Subscription__c zsub = id_zsubs.get(zspc.Zuora__Subscription__c);
                if(zsub.Opportunity__c != null)
                {
                    Opportunity opp = id_opps.get(zsub.Opportunity__c);
                    opp.Modo_Web__c = true;
                    update_opps.add(opp);
                }
            }
        }
    }
    update update_opps;
}

I believe it's something in the for loop but I can't finde where if someone could give me a light it will be much appreciated.

Thanks in advance.
 
Best Answer chosen by Olver_Bassov
Rahul BorgaonkarRahul Borgaonkar
This could be a chain of a updates which is executing various triggers. You need to check where it is starting and find out if any SOQL is put in a loop. Also check debug log and find No of SOQL number progressing and when it is suddenly raising, some time there is a large difference in SOQL number.

These are some tips to find how this error is occuring.

Regards,

Rahul

All Answers

Sanjay.GeorgeSanjay.George
Hi Olver,

Your loops look good. Can you confirm if you have a trigger on Opportunity that updates Zuora__SubscriptionProductCharge__c?

In your case I believe there might also be other triggers on the same object which might be eating up your 100 SOQL limit.
Rahul BorgaonkarRahul Borgaonkar
Hi,
Could you please check if Zuora__SubscriptionProductCharge__c is inserted / updated in loop in CreateOppForNewSubTriggger? It could be a cascading effect of that insert/update?

Regards,

Rahul

 
gyani19901.3956550919266765E12gyani19901.3956550919266765E12
Hi ,
your loops look good , so just insert the validation for update i.e. when old value and new value are same your code not run and second thing insert the list size validation before update the list.


Regards
Gyanender Singh
Olver_BassovOlver_Bassov
I'm afraid yes.

 
trigger CreateOppForNewSubTriggger on Zuora__SubscriptionProductCharge__c (after insert) {

    System.debug('########## INFO: Trigger creating an Opportunity for Subscriptions created from the web, without a Quote.');
    
    List<Zuora__SubscriptionProductCharge__c> spcList1 = [SELECT Id, Zuora__Subscription__c FROM Zuora__SubscriptionProductCharge__c WHERE Id IN :Trigger.New];
    
    Set<String> subSfdcIdsSet = new Set<String>();
    
    for (Zuora__SubscriptionProductCharge__c spc : spcList1) {
        subSfdcIdsSet.add(spc.Zuora__Subscription__c);
    }
    
    System.debug('########## INFO: subSfdcIdsSet = ' + subSfdcIdsSet);
    
    List<Zuora__Subscription__c> subList = [SELECT Id, Zuora__External_Id__c FROM Zuora__Subscription__c WHERE Zuora__Version__c = 1 AND Id IN :subSfdcIdsSet];
    
    System.debug('########## INFO: subList = ' + subList);
    
    Set<String> subZuoraIdsSet = new Set<String>();
    
    for (Zuora__Subscription__c sub : subList) {
        subZuoraIdsSet.add(sub.Zuora__External_Id__c);
    }
    
    System.debug('########## INFO: subZuoraIdsSet = ' + subZuoraIdsSet);
    
    List<zqu__Quote__c> quoteList = [SELECT Id, zqu__ZuoraSubscriptionID__c FROM zqu__Quote__c WHERE zqu__Status__c ='Sent to Z-Billing' AND zqu__ZuoraSubscriptionID__c IN :subZuoraIdsSet];
    
    for (zqu__Quote__c quote : quoteList) {
        if (subZuoraIdsSet.contains(quote.zqu__ZuoraSubscriptionID__c)) {
            subZuoraIdsSet.remove(quote.zqu__ZuoraSubscriptionID__c);
        }
    }
    
    System.debug('########## INFO: subZuoraIdsSet = ' + subZuoraIdsSet);
    
    Map<ID, Zuora__Subscription__c> sfdcSubMap = new Map<ID, Zuora__Subscription__c>([SELECT Id, CurrencyIsoCode, Zuora__Account__c, Zuora__Account__r.Name, Zuora__ContractEffectiveDate__c, web_order_id__c FROM Zuora__Subscription__c WHERE Zuora__External_Id__c IN :subZuoraIdsSet]);
    System.debug('########## INFO: sfdcSubMap.keySet() = ' + sfdcSubMap.keySet());
    //List<Zuora__Subscription__c> sfdcSubSet = [SELECT Id, CurrencyIsoCode, Zuora__Account__c FROM Zuora__Subscription__c WHERE Zuora__External_Id__c IN :subZuoraIdsSet];
    //System.debug('########## INFO: sfdcSubSet = ' + sfdcSubSet);
    
    List<Zuora__SubscriptionProductCharge__c> spcList = [SELECT Id, Zuora__ExtendedAmount__c, Zuora__Subscription__c FROM Zuora__SubscriptionProductCharge__c WHERE Zuora__Subscription__c IN :sfdcSubMap.keySet()];
    //List<Zuora__SubscriptionProductCharge__c> spcList = [SELECT Id, Zuora__ExtendedAmount__c, Zuora__Subscription__c FROM Zuora__SubscriptionProductCharge__c WHERE Zuora__Subscription__c IN :sfdcSubSet];
    System.debug('########## INFO: spcList = ' + spcList);
    
    //Set<String> accountIdSet = new Set<String>();
    List<Opportunity> oppToCreateList = new List<Opportunity>();
    
    System.debug('Subs to consider are of size: ' + sfdcSubMap.values().size());
    System.debug('Subs to consider are: ' + sfdcSubMap.values());
    
    //for (Zuora__Subscription__c sub : sfdcSubSet) {
    for (Zuora__Subscription__c sub : sfdcSubMap.values()) {
        System.debug('########## INFO: sub.Id = ' + sub.Id);
        
        //accountIdSet.add(sub.Zuora__Account__c);
        
        Decimal total = 0;
        for (Zuora__SubscriptionProductCharge__c spc : spcList) {
            System.debug('########## INFO: spc.Zuora__Subscription__c = ' + spc.Zuora__Subscription__c);
        
            if (spc.Zuora__Subscription__c == sub.Id) {
                total += spc.Zuora__ExtendedAmount__c;
            }
        }
        System.debug('########## INFO: total = ' + total);
        
        Opportunity oppToCreate = new Opportunity(Name = sub.Zuora__Account__r.Name + ' - WEB' + sub.web_order_id__c, AccountId = sub.Zuora__Account__c, CurrencyIsoCode = sub.CurrencyIsoCode, End_User_Account__c = sub.Zuora__Account__c, CloseDate = sub.Zuora__ContractEffectiveDate__c, StageName = 'Qualified Opportunity', Amount = total, Opportunity_Lead_Source__c='Web Purchase', Transaction_Type__c='Foundry Web');
        
        oppToCreateList.add(oppToCreate);
    }
    
    Database.SaveResult[] srList = Database.insert(oppToCreateList, true);
    
    Map<String, String> oppIdToAccountIdMap = new Map<String, String>();
    Map<String, String> oppNameToOppIdMap = new Map<String, String>(); // Waly added this
    
    integer i = 0;
    
    for (Database.SaveResult sr : srList) {
        if (sr.isSuccess()) {
            System.debug('########## INFO: Successfully inserted Opportunity with Id: ' + sr.getId());
            
            oppIdToAccountIdMap.put(sr.getId(), oppToCreateList.get(i).AccountId);
            oppNameToOppIdMap.put(oppToCreateList.get(i).Name, sr.getId()); // Waly added this
            
        } else {
            // Operation failed, so get all errors                
            for(Database.Error err : sr.getErrors()) {                  
                System.debug('########## ERROR: '+ err.getStatusCode() + ': ' + err.getMessage());
                System.debug('########## ERROR: Opportunity fields that affected this error: ' + err.getFields());
            }
        }
        i++;
    }
    
    
    
    System.debug('########## INFO: oppIdToAccountIdMap = ' + oppIdToAccountIdMap);
    //==============================================
    //Waly added this
    System.debug('########## INFO: oppNameToOppIdMap = ' + oppNameToOppIdMap);
    
    List<Zuora__Subscription__c> subsToUpdate = new List<Zuora__Subscription__c>();
    for(Zuora__Subscription__c sub : sfdcSubMap.values()){
        sub.Opportunity__c = oppNameToOppIdMap.get(sub.Zuora__Account__r.Name + ' - WEB' + sub.web_order_id__c);
        subsToUpdate.add(sub);
    }
    //========================================================
    
    List<Contact> contactList = [SELECT Id, AccountId FROM Contact WHERE AccountId IN :oppIdToAccountIdMap.values()];
    
    System.debug('########## INFO: contactList = ' + contactList);
    
    Map<String, String> accountIdToContactIdMap = new Map<String, String>();
    
    for (Contact cont : contactList) {
        accountIdToContactIdMap.put(cont.AccountId, cont.Id);
    }
    
    List<OpportunityContactRole> ocrToCreateList = new List<OpportunityContactRole>();
    
    List<Opportunity> oppToUpdateList = new List<Opportunity>();
    
    for (String oppId : oppIdToAccountIdMap.keySet() ) {
        String contactId = accountIdToContactIdMap.get(oppIdToAccountIdMap.get(oppId));
        if (contactId != null ) {
            OpportunityContactRole ocr = new OpportunityContactRole(OpportunityId = oppId, ContactId = contactId, IsPrimary = true, Role = 'Sales Contact');
            ocrToCreateList.add(ocr);
            
            Opportunity opp = new Opportunity(Id = oppId, StageName = 'Purchase Order Received');
            oppToUpdateList.add(opp);
        } else {
            System.debug('########## ERROR: No Contact found for Account ' + oppIdToAccountIdMap.get(oppId));
        }
    }
    
    insert ocrToCreateList;
    
    update oppToUpdateList;
    
    update subsToUpdate; // Waly added this
    
}



Any idea on how I can fix this without modifying completely ? All suggestions are welcome.

Thank you
Rahul BorgaonkarRahul Borgaonkar
This could be a chain of a updates which is executing various triggers. You need to check where it is starting and find out if any SOQL is put in a loop. Also check debug log and find No of SOQL number progressing and when it is suddenly raising, some time there is a large difference in SOQL number.

These are some tips to find how this error is occuring.

Regards,

Rahul
This was selected as the best answer