Trigger Error: too many SOQL queries

Question

I have developed following code for apex trigger and it will work when i am going to insert bulk data from the data loader,

Trigger: System.LimitException: Too many SOQL queries: 101

I read some post for bulkify and best practice to write apex trigger but in below code i mentioned i am not sure how can i bulkified it.

please guide me.

here is my code :

    List<SBQQ__QuoteLine__c> quoteLineList = new List<SBQQ__QuoteLine__c>();

for(opportunity oppAfter : trigger.New){
    List<Product2> prList = [select Id, Name from Product2 where Name =: oppAfter.Quote_line_Product_Name__c Limit 1];

    if(oppAfter.Coming_from_Portal__c == True){
        if(trigger.isAfter){
            SBQQ__Quote__c quote = new SBQQ__Quote__c();
            quote.SBQQ__Account__c = oppAfter.AccountId;
            quote.SBQQ__PriceBook__c = [Select Id from Pricebook2 where Name = 'Lista de Precios CPQ' Limit 1].Id;
            quote.Primary_Contact__c = [Select Id from Contact where AccountId =: oppAfter.AccountId Order By createdDate Limit 1].Id;

            insert quote;

            SBQQ__QuoteLine__c qliNew = new SBQQ__QuoteLine__c();
            qliNew.SBQQ__Product__c = [Select Id, Name from Product2 where Name =: 'DATA NETWORK SERVICES' Limit 1].Id;
            qliNew.SBQQ__Quote__c = quote.Id;
            insert qliNew;

            for(SBQQ__Quote__c q : [select Id from SBQQ__Quote__c where Id =: quote.Id]){
                if(oppAfter.Quote_line_Product_Name__c != Null){
                    SBQQ__QuoteLine__c qli = new SBQQ__QuoteLine__c();
                    for(Product2 p : prList){
                        qli.SBQQ__Product__c = p.Id;
                        if(oppAfter.Product_Special_Price_coming_from_portal__c != Null){
                            qli.SBQQ__SpecialPrice__c = Decimal.valueOf(oppAfter.Product_Special_Price_coming_from_portal__c);
                        }
                        qli.SBQQ__Quote__c = q.Id;
                        qli.SBQQ__SpecialPriceType__c = 'Custom';
                        if(oppAfter.Quote_line_TipoClearChanel_Velocidad__c != Null){
                            qli.TipoClearChanel__c = String.ValueOf(oppAfter.Quote_line_TipoClearChanel_Velocidad__c);    
                        }
                        quoteLineList.add(qli);
                    }
                }
            }
        }
    }
}
    if(quoteLineList.size() > 0)
        insert quoteLineList;
}

Answer 1

Certain corrections that will help:

• In For loop on Opportunity, take the product associated with Opp in Set and then outside of the for loop do the query of Product that fetches the data from Set. Set will ensure that we have unique and collected data at once.

  Set<String> productSet = new Set<String>();
  for(opportunity oppAfter : trigger.New){
      productSet.add(oppAfter.Quote_line_Product_Name__c); 
  }
  List<Product2> prList = [select Id, Name from Product2 where Name IN: productSet];
  

In the similar manner, what all is needed in queries in further part of code, take that in set and do the queries outside of for loop.

• Rather than inserting the quote in between, take the values in List or Map and use them further when inserting the Quote Line Items as well as when doing the for loop over the product. This way for within for that is, nested for can be avoided.

Answer 2

You need to populate a list of quotes. Then insert the list of quotes outside the FOR loop. Also, never use a SOQL query inside a FOR loop. Use maps to manipulate data. Here is a little help. I haven't tested it, I don't know if it compiles. I am also wondering why you don't use a lookup field to Product on the opportunity ?

 List<SBQQ__QuoteLine__c> quoteLineList = new List<SBQQ__QuoteLine__c>();
 List<SBQQ__Quote__c> quotesList = new List<SBQQ__Quote__c>();
 Id PriceBookId = [Select Id from Pricebook2 where Name = 'Lista de Precios CPQ' Limit 1].Id;
 Map<Opportunity,Id> opportunityAccount = new Map<Opportunity,Id> ();
 Map<Opportunity,Id> opportunityProduct = new Map<Opportunity,Id> ();
 Set<Id> accountIds = new Set<Id>(); 
 Map<Opportunity,Id> primaryContact = new Map<Opportunity,Id> ();
 Id datanetworkproductId = [Select Id, Name from Product2 where Name =: 'DATA NETWORK SERVICES' Limit 1].Id ;

 if(trigger.isAfter){
for(opportunity oppAfter : trigger.New){    

   if(oppAfter.Coming_from_Portal__c == true){
        oppsToUpdate.add(oppAfter);
        opportunityAccount.put(oppAfter,oppAfter.accountId);
        opportunityProduct.put(oppAfter,oppAfter.product__c ); // why don't you use the a lookup to Product rather than a name ?
        accountIds.add(oppAfter.accountId); 
        ProductIds.add(oppAfter.product__c);
   }

}
}
map<Id,Account> accounts = new Map<Id,Account>([select Id,PrimaryContact__c FROM Account WHERE AccountId IN : accountIds]);
for(Opportunity opp: opportunityAccount){
 if(accounts.containsKey(opp.AccountId)
    primaryContact.put(opp,accounts.get(opp.AccountId).PrimaryContact__c);
}
 List<Opportunity> oppsToUpdate = new List<Opportunity>();


 for(opportunity opp : oppsToUpdate){

quotesList.add(new SBQQ__Quote__c(
        SBQQ__Account__c = oppAfter.AccountId,
        SBQQ__PriceBook__c = PriceBookId;
        Primary_Contact__c = primaryContact.get(oppAfter)));

quotesList.add(quote);  

 }
insert quotesList ;

for(SBQQ__Quote__c quote : quotesList){         

        quoteLineList.add(new SBQQ__QuoteLine__c(
        SBQQ__Product__c = datanetworkproductId,
        BQQ__Quote__c = quote.Id));

  /**** add following code to adapt here ***/
 }
insert quoteLineList;