[SalesForce] System.LimitException: Too Many SOQL Queries: 101 in Trigger

Following is my trigger I get the following error

System.LimitException: Too Many SOQL Queries: 101 in Trigger

Here is my trigger

trigger SubscriberUpdateTrigger on Subscriber__c (after Insert, after Update, after undelete) {

    Set<sfcloud__Product_Bundle__c> listBundleToUpdate = new Set<sfcloud__Product_Bundle__c>();
    sfcloud__Product_Bundle__c pb = new sfcloud__Product_Bundle__c();        

    List<Subscriber__c> subsList = [select id,Subscriber__c.Product_Bundle__c,Active_Subscriber__c,Subscriber_Price_Book__r.id,Subscriber_Price_Book__c,Subscriber_Contact__r.Id,Subscriber_Contact__r.Is_Subscriber__c from Subscriber__c where id IN :Trigger.newMap.keySet()];

    for(Subscriber__c ss : subsList)    
    {        
            if(ss.Product_Bundle__c != null){

                sfcloud__Product_Bundle__c spb = [Select id,Vision__c,Telemedicine__c,Dental__c, Fulfillment__c, name, sfcloud__Price_Book__c,sfcloud__Price_Book__r.id from sfcloud__Product_Bundle__c where id =: ss.Product_Bundle__c];
                System.debug('Total Bundle :'+spb);                
                List<PricebookEntry> pbe = [Select Id, Name, ProductCode, Product2Id, UnitPrice From PricebookEntry where Pricebook2Id =: spb.sfcloud__Price_Book__c];
                System.debug('Total PriceEntry:'+pbe.size());                

                 for(PricebookEntry pbe1 : pbe){    

                    if(pbe1.ProductCode == 'Fulfillment'){
                        pb.Id = spb.Id;
                        pb.Fulfillment__c = True;
                    }
                    if(pbe1.ProductCode == 'Discount_Vision'){
                        pb.Id = spb.Id;
                        pb.Vision__c = True;
                    }
                    if(pbe1.ProductCode == 'Discount_Dental'){
                        pb.Id = spb.Id;
                        pb.Dental__c = True;
                    }
                    if(pbe1.ProductCode == 'Telemed_Standard'){    
                        pb.Id = spb.Id;
                        pb.Telemedicine__c = True;
                    } 
                }
                listBundleToUpdate.add(pb);

            }
            System.debug('List'+listBundleToUpdate);

        if(!listBundleToUpdate.isEmpty()){
            List<sfcloud__Product_Bundle__c> subs = new List<sfcloud__Product_Bundle__c>(listBundleToUpdate);
            UPDATE subs;
        }
    }
}

Thanks,
Vimal

Best Answer

Here is how to move the two queries out of the loop.

This is a common pattern, where you first loop over the trigger objects to collect the set of related object IDs and then do a set oriented query on those into maps. Then within the main processing loop you can lookup those results from the maps rather than querying for individual items.

You should also take care to do the final update outside of the loop.

trigger SubscriberUpdateTrigger on Subscriber__c (after Insert, after Update, after undelete) {

    Set<Id> productBundleIds = new Set<Id>();
    for (Subscriber__c ss : Trigger.new) {
        if (ss.Product_Bundle__c != null) {
            productBundleIds.add(ss.Product_Bundle__c);
        }
    }
    Map<Id, sfcloud__Product_Bundle__c> productBundles = new Map<Id, sfcloud__Product_Bundle__c>([
            Select id,Vision__c,Telemedicine__c,Dental__c, Fulfillment__c, name, sfcloud__Price_Book__c,sfcloud__Price_Book__r.id
            from sfcloud__Product_Bundle__c
            where Id in :productBundleIds
            ]);

    Set<Id> priceBookIds = new Set<Id>();
    for (sfcloud__Product_Bundle__c spb : productBundles.values()) {
        if (spb.Product_Bundle__c != null) {
            productBundleIds.add(spb.sfcloud__Price_Book__c);
        }
    }
    Map<Id, List<PricebookEntry>> priceBooks = new Map<Id, List<PricebookEntry>>();
    for (PricebookEntry pbe : [
            Select Id, Name, ProductCode, Product2Id, UnitPrice, Pricebook2Id
            From PricebookEntry
            where Pricebook2Id in :priceBookIds
            ]) {
        List<PricebookEntry> l = priceBooks.get(pbe.Pricebook2Id);
        if (l == null) {
            l = new List<PricebookEntry>();
            priceBooks.put(pbe.Pricebook2Id, l);
        }
        l.add(pbe);
    }

    for (Subscriber__c ss : Trigger.new) {        
        if (ss.Product_Bundle__c != null) {
            sfcloud__Product_Bundle__c spb = productBundles.get(ss.Product_Bundle__c);             
            List<PricebookEntry> pbe = priceBooks.get(spb.sfcloud__Price_Book__c);

            // Rest of loop logic

        }
    }

    // Do one update here
}
Related Topic