Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
ShadowlessKickShadowlessKick 

Help Bulkifying Trigger

I looked at the best practices page for bulkifying code here => http://wiki.developerforce.com/page/Apex_Code_Best_Practices  I cannot seem to get it right because I still get the error of too many SQL Queries.  Could someone please give me some direction as how to bulkify this trigger?

 

trigger UpdateProjectFromOpportunity on Opportunity (after update) {

for (opportunity theOpportunity : Trigger.new )  {  
    //Create an empty Tenrox Project List
List<tenrox__c> theTenroxProjects = new List<tenrox__c>();

For(tenrox__c theProject:[SELECT  id, KI_Project_Coordinator_pre_sales__c FROM tenrox__c WHERE opportunity__c = :theOpportunity.id AND KI_CAD_Project_Status__c  != 'Cancel' AND KI_CAD_Project_Status__c  != 'Complete' limit 25 ])
{
if (theOpportunity.Project_Coordinator__c != null ) {
theProject.KI_Project_Coordinator_pre_sales__c = theOpportunity.Project_Coordinator__c; 
}

theTenroxProjects.add(theProject);
} // End of For Loop for List

//Make sure there is something to update
if (!theTenroxProjects.isEmpty()) {
update theTenroxProjects;
} // End check for Records Processed

} // End of For Loop of Entire Trigger

} // End of Trigger

     

Best Answer chosen by Admin (Salesforce Developers) 
SLockardSLockard

You never want to have a SOQL query in a for loop. This should help you out:

trigger UpdateProjectFromOpportunity on Opportunity (after update) 
{
	//Create an empty Tenrox Project List
	List<tenrox__c> theTenroxProjects = new List<tenrox__c>();
	Map<Id, Opportunity> opps = new Map<Id, Opportunity>();
	
	for (opportunity theOpportunity : Trigger.new )  
	{  
		if (theOpportunity.Project_Coordinator__c != null ) 
		{
			opps.put(theOpportunity.Id, theOpportunity);
		}
	} // End of For Loop of Entire Trigger
	
	if (!opps.isEmpty())
	{
		theTenroxProjects = [SELECT  id, KI_Project_Coordinator_pre_sales__c FROM tenrox__c WHERE opportunity__c IN :opps.keySet() AND KI_CAD_Project_Status__c  != 'Cancel' AND KI_CAD_Project_Status__c  != 'Complete'];
	
		for(tenrox__c theProject: theTenroxProjects)
		{
			theProject.KI_Project_Coordinator_pre_sales__c = opps.get(theProject.opportunity__c).Project_Coordinator__c; 
		} // End of For Loop for List
		
		if (!theTenroxProjects.isEmpty())
		{
			update theTenroxProjects;
		}
	} // End check for Records Processed

} // End of Trigger

 Good luck!

All Answers

SLockardSLockard

You never want to have a SOQL query in a for loop. This should help you out:

trigger UpdateProjectFromOpportunity on Opportunity (after update) 
{
	//Create an empty Tenrox Project List
	List<tenrox__c> theTenroxProjects = new List<tenrox__c>();
	Map<Id, Opportunity> opps = new Map<Id, Opportunity>();
	
	for (opportunity theOpportunity : Trigger.new )  
	{  
		if (theOpportunity.Project_Coordinator__c != null ) 
		{
			opps.put(theOpportunity.Id, theOpportunity);
		}
	} // End of For Loop of Entire Trigger
	
	if (!opps.isEmpty())
	{
		theTenroxProjects = [SELECT  id, KI_Project_Coordinator_pre_sales__c FROM tenrox__c WHERE opportunity__c IN :opps.keySet() AND KI_CAD_Project_Status__c  != 'Cancel' AND KI_CAD_Project_Status__c  != 'Complete'];
	
		for(tenrox__c theProject: theTenroxProjects)
		{
			theProject.KI_Project_Coordinator_pre_sales__c = opps.get(theProject.opportunity__c).Project_Coordinator__c; 
		} // End of For Loop for List
		
		if (!theTenroxProjects.isEmpty())
		{
			update theTenroxProjects;
		}
	} // End check for Records Processed

} // End of Trigger

 Good luck!

This was selected as the best answer
ShadowlessKickShadowlessKick

Thank you VERY much!  That was a really clear example.  The trigger is now bulk worthy and I picked up on some new techniques.  When rewritten it exposed several other issues that the existing test script was not catching either.  

 

You should teach a class at Dreamforce with a real world example like that!