+ Start a Discussion
Shruti NigamShruti Nigam 

System.LimitException: Too many SOQL queries: 101 Apex script unhandled trigger exception by user/organization:

Hi all,
Need help on this apex class getting sql governor limit.
 
public class salesprice {

    
    public static void price(Id recordsales)
    {	
     	try
     	{
        	Account ac = [Select id,percent_increase1__c,FB_NET_ARR1__c from Account where id =:  recordsales ];
     		for( Opportunity  ap : [Select id,Type from Opportunity where AccountId  =: ac.Id ])
            {
         		if(ap.Type == 'Renewal')
            	{
                	List<OpportunityLineItem> ab = [Select id,ProductCode from opportunityLineitem where opportunityId =: ap.Id ];
                	if(ab[0].ProductCode == '002')
                    {
                		ab[0].UnitPrice = ac.percent_increase1__c * ac.FB_NET_ARR1__c;
     	   				update ab;
                    }
            	}
            }     
     }
     catch(Exception e)
     {
         System.debug(e);
     }
    }
}

Trigger to call above class:
trigger updateproduct on Account (after insert,after update) {
     
    for(Account a: Trigger.new)
    {
   			 salesprice.price(a.Id);
    }
}

Thanks in advance
Best Answer chosen by Shruti Nigam
Ramesh DepaiahRamesh Depaiah

@shruthi,
Try below code and you please read salesforce limitation before writing apex triggers 

https://developer.salesforce.com/forums/?id=906F0000000DBl8IAG

Trigger: Used set/List to collect ids(bulkify) and then pass it to helper calss

trigger updateproduct on Account (after insert,after update) {
    
    set<id> ids=new  set<id>();
    for(Account a: Trigger.new)
    {
   			 ids.add(a.Id);
    }
    if(ids.size()>0)
    {
        salesprice.price(ids);

    }    
    
}
Class:  Used Maps to hold record data
public class salesprice {
public static void price(set<id> accIds)
    {	
        try
        {
            Map<id,Opportunity> mapOpps=new Map<id,Opportunity>();
            for( Opportunity  ap : [Select id,Type,AccountId,Account.percent_increase1__c,Account.FB_NET_ARR1__c from Opportunity where AccountId  IN: accIds and Type =:'Renewal' ])
            {
                mapOpps.put(ap.id,ap);
                
            }  
            if(mapOpps.size()>0){
                List<OpportunityLineItem> olItems = [Select id,ProductCode,opportunityId from opportunityLineitem where opportunityId IN: mapOpps.keySet() ];  
                for(OpportunityLineItem item:olItems) {
                    Opportunity  ac=  mapOpps.get(item.opportunityId);
                    if(item.ProductCode == '002')
                    {
                        if(ac!=null){
                            item.UnitPrice = ac.Account.percent_increase1__c * ac.Account.FB_NET_ARR1__c;                            
                        }
                    }
                }                
                Database.update(olItems,false);                
            }
        }
        catch(Exception e)
        {
            System.debug(e);
        }
    }
}

Please let me know if it works for you

Thanks
Ramesh

All Answers

Neha AggrawalNeha Aggrawal
Hi Shruti,

You are using SOQL query within a for loop. 
Instead try something like:
public class salesprice {

    
    public static void price(Id recordsales)
    {	
Set<Id> OppSet = new Set<Id>();
     	try
     	{
        	Account ac = [Select id,percent_increase1__c,FB_NET_ARR1__c from Account where id =:  recordsales ];
     		for( Opportunity  ap : [Select id,Type from Opportunity where AccountId  =: ac.Id ])
            {
         		if(ap.Type == 'Renewal')
                        OppSet.add(ap.Id);
                 }
                	List<OpportunityLineItem> ab = [Select id,ProductCode from opportunityLineitem where opportunityId in: OppSet ];
                 //Don't you need a for loop here as well
                	if(ab[0].ProductCode == '002')
                    {
                		ab[0].UnitPrice = ac.percent_increase1__c * ac.FB_NET_ARR1__c;
     	   				update ab;
                    }
            	}
            }     
     }
     catch(Exception e)
     {
         System.debug(e);
     }
    }
}

Let me know if this helps.
Thanks.
Ramesh DepaiahRamesh Depaiah

@shruthi,
Try below code and you please read salesforce limitation before writing apex triggers 

https://developer.salesforce.com/forums/?id=906F0000000DBl8IAG

Trigger: Used set/List to collect ids(bulkify) and then pass it to helper calss

trigger updateproduct on Account (after insert,after update) {
    
    set<id> ids=new  set<id>();
    for(Account a: Trigger.new)
    {
   			 ids.add(a.Id);
    }
    if(ids.size()>0)
    {
        salesprice.price(ids);

    }    
    
}
Class:  Used Maps to hold record data
public class salesprice {
public static void price(set<id> accIds)
    {	
        try
        {
            Map<id,Opportunity> mapOpps=new Map<id,Opportunity>();
            for( Opportunity  ap : [Select id,Type,AccountId,Account.percent_increase1__c,Account.FB_NET_ARR1__c from Opportunity where AccountId  IN: accIds and Type =:'Renewal' ])
            {
                mapOpps.put(ap.id,ap);
                
            }  
            if(mapOpps.size()>0){
                List<OpportunityLineItem> olItems = [Select id,ProductCode,opportunityId from opportunityLineitem where opportunityId IN: mapOpps.keySet() ];  
                for(OpportunityLineItem item:olItems) {
                    Opportunity  ac=  mapOpps.get(item.opportunityId);
                    if(item.ProductCode == '002')
                    {
                        if(ac!=null){
                            item.UnitPrice = ac.Account.percent_increase1__c * ac.Account.FB_NET_ARR1__c;                            
                        }
                    }
                }                
                Database.update(olItems,false);                
            }
        }
        catch(Exception e)
        {
            System.debug(e);
        }
    }
}

Please let me know if it works for you

Thanks
Ramesh
This was selected as the best answer