Sign Up ›

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

How to Bulkify a SpecificTrigger?

I am getting an error
System.Exception: Too many SOQL queries: 101


I have the followiing trigger which I am attempting to change to avoid that error message. 

I have tried using a keyset with this but it has not worked out yet. The tricky part is that I am updating an opportunity from a task, and there are also triggers on opportunities.

trigger UpdateLatestDueDatefromTask on Task (after insert, after update) {


try{
    //the trigger will update the opportunity's latest_open_due_date__c field
    for (task ta : trigger.new){
        id Opportunityid = ta.WhatId;
        
        Opportunity oOpportunity = [select id, Latest_Open_Due_Date__c from Opportunity where id = :Opportunityid LIMIT 1];
        if (oOpportunity.Latest_Open_Due_Date__c <  ta.ActivityDate ) {
        	if (ta.Status != 'Completed' ) {
        		System.debug ('in completed');
        		oOpportunity.Latest_Open_Due_Date__c = ta.ActivityDate;
            	//update oOpportunity;
        	}
        }
        
        if (oOpportunity.Latest_Open_Due_Date__c == null) {
        	if (ta.Status != 'Completed'  ) {
        		oOpportunity.Latest_Open_Due_Date__c = ta.ActivityDate;
            	//update oOpportunity;
        	}
        }
        
       List <Task> ActiveTask = [select id, WhatId from Task where Whatid = :Opportunityid and status != 'Completed'];
          
        	if (ActiveTask.isEmpty()){
        	 	oOpportunity.Latest_Open_Due_Date__c = null;
             	///update oOpportunity;
        	}
    	update oOpportunity;
    }
}
catch (Exception e ){
            System.debug('Create trigger exception ' + e.getMessage());
            
      }
 }


THIS IS AMONG THE THINGS I HAVE BEEN TRYING:

trigger UpdateLatestDueDatefromTask on Task (after insert, after update) {

  //the trigger will update the opportunity's latest_open_due_date__c field
    for (task ta : trigger.new){
	try{    	
     List<Opportunity> updateOpportunities = new list<Opportunity>();
     
     List <Tasks> OpportunitiesWithTasks = [select id, ActivityDate, WhatId, (Select id, Latest_Open_due_Date__c from Opportunity) from Task 
     	Where Id in :Trigger.newMap.keySet()];
     for (Opportunity oOpportunity: [select id, WhatId, ActivityDate from Opportunity where id in :Trigger.NewMap.keySet()] ) {
			list <Opportunity> parentOpp = [select id, Latest_Open_Due_Date__c From Opportunity where id in :Trigger.NewMap.get (oTask.WhatId)];
			if (parentOpp.Latest_Open_Due_Date__c < oTask.WhatId) {
				parentOpp.Latest_Open_Due_Date__c = oTask.ActivityDate;
				
			}
    }
}

(The error I get is that it doesn't understand the relationship for opportunity.)

THANKS!


Best Answer chosen by LBarton
Deepak Kumar ShyoranDeepak Kumar Shyoran
Try this code it will bulkify your trigger by avoiding any SOQL/DML limit exception

trigger UpdateLatestDueDatefromTask on Task (after insert, after update) {


try{
    
	List<Id> oppIds = new List<Id>() ;
	List<Opportunity> oppToUpdate = new List<Opportunity>() ;
	
	
	for (task ta : trigger.new){
        oppIds.add(ta.WhatId) ;
	
	Map<Id,Opportunity> oppMap = new Map<Id,Opportunity>([Select id, Latest_Open_Due_Date__c from Opportunity where id in :oppIds ] ;
	Map<Id,Task> ActiveTaskMap = new<Id,Map>([select id, WhatId from Task where Whatid in :oppIds and status != 'Completed']);
	
	//the trigger will update the opportunity's latest_open_due_date__c field
    
	for (task ta : trigger.new){
      //  id Opportunityid = ta.WhatId;
        
        //Opportunity oOpportunity = [select id, Latest_Open_Due_Date__c from Opportunity where id = :Opportunityid LIMIT 1];
		Opportunity oOpportunity = oppMap.get(ta.WhatId) ;
		
        if (oOpportunity.Latest_Open_Due_Date__c <  ta.ActivityDate ) {
        	if (ta.Status != 'Completed' ) {
        		System.debug ('in completed');
        		oOpportunity.Latest_Open_Due_Date__c = ta.ActivityDate;
            	//update oOpportunity;
        	}
        }
        
        if (oOpportunity.Latest_Open_Due_Date__c == null) {
        	if (ta.Status != 'Completed'  ) {
        		oOpportunity.Latest_Open_Due_Date__c = ta.ActivityDate;
            	//update oOpportunity;
        	}
        }
        
       //List <Task> ActiveTask = [select id, WhatId from Task where Whatid = :Opportunityid and status != 'Completed'];
        List <Task> ActiveTask = ActiveTaskMap.get(ta.WhatId) ;
        	if (ActiveTask.isEmpty()){
        	 	oOpportunity.Latest_Open_Due_Date__c = null;
             	///update oOpportunity;
        	}
    	oppToUpdate.add(oOpportunity) ;
		//update oOpportunity;
    }
	update oppToUpdate ;
}
catch (Exception e ){
            System.debug('Create trigger exception ' + e.getMessage());
            
      }
 }

All Answers

Deepak Kumar ShyoranDeepak Kumar Shyoran
Try this code it will bulkify your trigger by avoiding any SOQL/DML limit exception

trigger UpdateLatestDueDatefromTask on Task (after insert, after update) {


try{
    
	List<Id> oppIds = new List<Id>() ;
	List<Opportunity> oppToUpdate = new List<Opportunity>() ;
	
	
	for (task ta : trigger.new){
        oppIds.add(ta.WhatId) ;
	
	Map<Id,Opportunity> oppMap = new Map<Id,Opportunity>([Select id, Latest_Open_Due_Date__c from Opportunity where id in :oppIds ] ;
	Map<Id,Task> ActiveTaskMap = new<Id,Map>([select id, WhatId from Task where Whatid in :oppIds and status != 'Completed']);
	
	//the trigger will update the opportunity's latest_open_due_date__c field
    
	for (task ta : trigger.new){
      //  id Opportunityid = ta.WhatId;
        
        //Opportunity oOpportunity = [select id, Latest_Open_Due_Date__c from Opportunity where id = :Opportunityid LIMIT 1];
		Opportunity oOpportunity = oppMap.get(ta.WhatId) ;
		
        if (oOpportunity.Latest_Open_Due_Date__c <  ta.ActivityDate ) {
        	if (ta.Status != 'Completed' ) {
        		System.debug ('in completed');
        		oOpportunity.Latest_Open_Due_Date__c = ta.ActivityDate;
            	//update oOpportunity;
        	}
        }
        
        if (oOpportunity.Latest_Open_Due_Date__c == null) {
        	if (ta.Status != 'Completed'  ) {
        		oOpportunity.Latest_Open_Due_Date__c = ta.ActivityDate;
            	//update oOpportunity;
        	}
        }
        
       //List <Task> ActiveTask = [select id, WhatId from Task where Whatid = :Opportunityid and status != 'Completed'];
        List <Task> ActiveTask = ActiveTaskMap.get(ta.WhatId) ;
        	if (ActiveTask.isEmpty()){
        	 	oOpportunity.Latest_Open_Due_Date__c = null;
             	///update oOpportunity;
        	}
    	oppToUpdate.add(oOpportunity) ;
		//update oOpportunity;
    }
	update oppToUpdate ;
}
catch (Exception e ){
            System.debug('Create trigger exception ' + e.getMessage());
            
      }
 }


This was selected as the best answer
ashish kumar 18ashish kumar 18
Hi,

You have to remove the SOQL from the loop.

Please go through the below mentioned url for your reference:
http://blog.jeffdouglas.com/2009/04/20/writing-bulk-triggers-for-salesforce/
PratikPratik (Salesforce Developers) 
Hi LBarton,

You can refer to Salesforce Best practices to bulkify trigger:
https://developer.salesforce.com/page/Best_Practice%3A_Bulkify_Your_Code

Thanks,
Prtaik
PratikPratik (Salesforce Developers) 

Hi LBarton,

+1

The code you posted above, (the code within For loop)

for (task ta : trigger.new){
        id Opportunityid = ta.WhatId;
       
        Opportunity oOpportunity = [select id, Latest_Open_Due_Date__c from Opportunity where id = :Opportunityid LIMIT 1];
        if (oOpportunity.Latest_Open_Due_Date__c <  ta.ActivityDate ) {
                if (ta.Status != 'Completed' ) {
                        System.debug ('in completed');
                        oOpportunity.Latest_Open_Due_Date__c = ta.ActivityDate;
                //update oOpportunity;
                }
        }
       
        if (oOpportunity.Latest_Open_Due_Date__c == null) {
                if (ta.Status != 'Completed'  ) {
                        oOpportunity.Latest_Open_Due_Date__c = ta.ActivityDate;
                //update oOpportunity;
                }
        }
       
       List <Task> ActiveTask = [select id, WhatId from Task where Whatid = :Opportunityid and status != 'Completed'];
         
                if (ActiveTask.isEmpty()){
                        oOpportunity.Latest_Open_Due_Date__c = null;
                ///update oOpportunity;
                }
        update oOpportunity;
    }


In this code you should put SOQL outside the For loop which will help to remove the error of SOQL limit.

Thanks,
Pratik
LBartonLBarton
Thanks so much everyone for your help, and especially to Pratik who provided code as well as advance.

I use what he had posted above and changed it a little and this is now in production as follows:
trigger UpdateLatestDueDatefromTask on Task (after insert, after update) {


try{
    
 List<Id> oppIds = new List<Id>() ;
 List<Opportunity> oppToUpdate = new List<Opportunity>() ;
 
 for (task ta : trigger.new){
   
    oppIds.add(ta.WhatId) ;
 
 Map<Id,Opportunity> oppMap = new Map<Id,Opportunity>([Select id, Latest_Open_Due_Date__c from Opportunity where id in :oppIds ]) ;
 Map<Id,Task> ActiveTaskMap = new Map<Id,Task>([select id, WhatId from Task where Whatid in :oppIds and status != 'Completed']);
 
 //the trigger will update the opportunity's latest_open_due_date__c field
        
     Opportunity oOpportunity = oppMap.get(ta.WhatId) ;
  
        if (oOpportunity.Latest_Open_Due_Date__c <  ta.ActivityDate ) {
         if (ta.Status != 'Completed' ) {
          oOpportunity.Latest_Open_Due_Date__c = ta.ActivityDate;
         }
        }
        
        if (oOpportunity.Latest_Open_Due_Date__c == null) {
         if (ta.Status != 'Completed'  ) {
          oOpportunity.Latest_Open_Due_Date__c = ta.ActivityDate;
          }
        }
        
        List <Task> ActiveTask = [select id, WhatId from Task where Whatid in :oppIds and status != 'Completed'];

         if (ActiveTask.isEmpty()){
           oOpportunity.Latest_Open_Due_Date__c = null;
            }
     oppToUpdate.add(oOpportunity) ;
    }
 update oppToUpdate ;
}
catch (Exception e ){
            System.debug('Create trigger exception ' + e.getMessage());
            
      }
 }
Deepak Kumar ShyoranDeepak Kumar Shyoran
Haha lol so funny.

As it's clearly seen above that it was me who provided the code which is in your production is provided by me not Pratik and you give all credit to Pratik.

I think this is not not fare.
LBartonLBarton
I apologize so much for my confusion and error. Thanks very much Deepak!  I appreciate your code. It has helped me out a lot!
Deepak Kumar ShyoranDeepak Kumar Shyoran
No Problem. Sometimes it happens. Please mark my post as a best solution as it help you so that it will available for others also as solution for such questions.
LBartonLBarton
OK, it is flagged as best answer. I had to change it a little as I mentioned.