1

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

2

3 Answers 3

1

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
}
5
  • i get error Invalid field sfcloud__Price_Book__c for SObject Subscriber__c at line "priceBookIds.add(ss.sfcloud__Price_Book__c);"
    – Vml
    Oct 10, 2014 at 12:38
  • My mistake - got the object wrong for that. I'll update the answer.
    – Keith C
    Oct 10, 2014 at 12:56
  • I've updated the answer. There may be other mistakes - I can't compile this - so it would be best if you dealt with any further problems yourself.
    – Keith C
    Oct 10, 2014 at 13:03
  • I have seen you code, now no error but when i tried to insert data in object i get System.NullPointerException: Attempt to de-reference a null object at line "for(PricebookEntry pbe1 : pbe) ". Help if possible from your side Thanks Keith.
    – Vml
    Oct 10, 2014 at 13:20
  • @VimalMakwana This isn't a community that gives line by line debugging support. This How do I start to debug my own Apex code? offers a number of techniques to help you work out what is wrong yourself.
    – Keith C
    Oct 10, 2014 at 13:24
2

You are doing query in for loop. That is why you are getting this error. You need to bulkify your code using List, Map and Set.

For more details check following link which contains Apex Code Best Practices and Bulkify your Code

https://developer.salesforce.com/page/Apex_Code_Best_Practices

1
  • Yes. i have tried all things for that but didn't get result. please can you look at my trigger. Thanks Ankush
    – Vml
    Oct 10, 2014 at 12:06
0

You need to query your Bundle and Price-books outside of the loop and hold them in a collection. I would recommend a map.

Here is how you would do it for your bundle object

map<Id,sfcloud__Product_Bundle__c> bundleMap = new map<Id,sfcloud__Product_Bundle__c>();
for(Subscriber__c sub : trigger.new){
     bundleMap.put(sub.Product_Bundle__c, null);
}
bundleMap.remove(null);
bundleMap.putAll([Select Id, Name,Vision__c,Telemedicine__c,Dental__c, Fulfillment__c, sfcloud__Price_Book__c,sfcloud__Price_Book__r.id from sfcloud__Product_Bundle__c where Id In : bundleMap.keyset()];

Then within your loop, you now have access to Bundle object related to a particular Subscriber and can access it like this

for(Subscriber__c sub : trigger.new)  {        
       if(ss.Product_Bundle__c == null){
            continue;
       }
       sfcloud__Product_Bundle__c spb = bundleMap.get(sub.Product_Bundle__c);
}

You need to use the same concept to get the Pricebook Entries as well. Utilize a collection to gather the data out side of your loop, and then reference that collection to get the pertinent record inside of your loop. This saves your queries and will help avoid governor limits.

And as everyone else suggested, I would really try to study the Apex coding best practices. you can find this technique along with many others

https://developer.salesforce.com/page/Apex_Code_Best_Practices

1
  • i did not get you point please see my trigger i paste in my question. Thanks sfdc_ninja
    – Vml
    Oct 10, 2014 at 12:50

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .