+ Start a Discussion
cfangercfanger 

Need Help Bulkifying Single Object Trigger

trigger EDC_Account_Number_Trigger on EDC_Account_Number__c (before insert, before update, after insert, after update) {

//Description: Associates all EDC Account Number Records with their previous and next iterations according to
//the EDC Account with the closest end date in both the past and future
    for(edc_account_number__c EDC:trigger.new)
        {
            list<edc_account_number__c> EDCListPrevious = [Select Id, name,start_date__c,end_date__c from edc_account_number__c
            where name = :EDC.name and End_Date__c <= :EDC.End_Date__c and ID != :EDC.id ORDER BY End_Date__c DESC];
            
            if(EDCListPrevious.isEmpty())
                {                   
                }
            Else
                {
                    if(trigger.isbefore)
                        {                       
                            EDC.Previous_EDC_Account_Number_Record__c = EDCListPrevious[0].id;
                        }
                    if(trigger.isafter)     
                        {
                            EDC_Account_Number__c EDCPrevious = [Select Id, name,Next_EDC_Account_Number_Record__c 
                            from EDC_Account_Number__c where Id = :EDCListPrevious[0].id];
                    
                            EDCPrevious.Next_EDC_Account_Number_Record__c = EDC.id;
                            update EDCPrevious;             
                        }
                }
            list<edc_account_number__c> EDCListNext = [Select Id, name,start_date__c,end_date__c from edc_account_number__c
            where name = :EDC.name and End_Date__c >= :EDC.End_Date__c and ID != :EDC.id ORDER BY End_Date__c ASC];
                
            if(EDCListNext.isEmpty())
                {
                }
            Else
                {
                    if(trigger.isbefore)
                        {
                            EDC.Next_EDC_Account_Number_Record__c = EDCListNext[0].id;                      
                        }
                }
        }
}

 I know that this trigger works on a single record but I am struggling as to how I should bulkify it. I appreciate any assistance as this is an area that I struggle with on a regular basis. I am fairly new to APEX development so I will take any feedback I can get. Thanks.

Best Answer chosen by Admin (Salesforce Developers) 
Platy ITPlaty IT

That makes it much easier to deal with then.  Here's how you can handle this-

 

First, you'll need a new Apex class (called "VariableClass" in the exampl below) to hold a new static variable like this- "public static Boolean isSysUpdate = false;"  The purpose of this is to track when updates being made to the EDC_Account_Number__c records are being made by your trigger, so it doesn't go into an infinite loop of updates setting off updates.

 

Then the following code could be used to update the Previous EDC Id-

trigger EDC_Account_Number_Trigger on EDC_Account_Number__c (after delete, after insert, after undelete, 
after update) {
	//To track the names of EDC records in the trigger
	Set<String> setEDCName = new Set<String>();
	
	
	for (EDC_Account_Number__c edc : (Trigger.isDelete ? Trigger.old : Trigger.new)){
		//Make sure trigger wasn't called by the DML update that happens below....
		if (!VariableClass.isSysUpdate){
			//Store the name to query for all EDCs with the same name later...
			setEDCName.add(edc.Name);
		}
	}
	
	if (setEDCName.size() > 0){
		//Gather all related EDC records ordered by End Date in a List so they can be updated later
		List<EDC_Account_Number__c> lstUpdate = [Select Id, Name, Previous_EDC_Account_Number_Record__c, Next_EDC_Account_Number_Record__c
			from EDC_Account_Number__c where Name IN: setEDCName Order by Name, End_Date__c];
		//To track the last name that was examined in the loop below
		String strLastName;
		Id idLastId;
		for (EDC_Account_Number__c edc : lstUpdate){
			//Does this have the same Name as the last record in the loop?
			if (edc.Name == strLastName){
				//Then Previous will be idLastId;
				edc.Previous_EDC_Account_Number_Record__c = idLastId;
			} else {
				//Otherwise, update strLastName;
				strLastName = edc.Name;
			}
			idLastId = edc.Id;
		}
		//Set this static variable to true, so the update doesn't fire this same logic again (and again and again...)
		VariableClass.isSysUpdate = true;
		update lstUpdate;
	}//end if there are EDC records to update
}

To be complete, I included "after delete" and "after undelete" in this logic.  To update the Next EDC Number field, you could either repeat the logic above but order the query by End_Date__c DESC (that's an extra query, but the easiest way to do it) or write some fancier logic where you track the Previous Id and Id related together in a Map during that first lstUpdate loop, and then loop through lstUpdate again and use that map to grab pull the Next Id from that Map (slightly more elegant because it skips a query, but more of a pain to write).  

All Answers

Platy ITPlaty IT

cfanger,

 

First one note on the trigger context, your trigger includes "before insert" but also references the EDC Id number, which won't yet be populated on the before insert.  To get this working for a bulk insert, that will be an issue because one record being inserted may end up being the Previous Account Number for a different record being inserted.  I think the best way to go for this logic will be to restrict it to the after context- that way your query to find the potential Previous EDC will include all the new records being inserted.  

 

For bulkifying, the trick usually is to gather information your logic will need in Sets, Maps or Lists and then use those collections to enact logic that includes queries or DML updates outside of loops.  So Sets to gather Ids or Strings that you later use to as filters in a query, or Lists to gather records you'll be updating so you can do a single update on that List instead of separate updates on each record.

 

Before I even suggest some bulk-proofing that could be done here, can I ask how many of these EDC records are likely to have the same Name?  Just want to make sure that the logic you implement to bulk-proof doesn't put you in danger of a query that returns more records than the governor limits allow.  So long as it's a small number of records, there's a relatively easy way to implement this that I've done for similar scenarios before.

cfangercfanger

Platy IT,

 

Thank you for your response. I will change the trigger context to be after insert, after update. I was not aware of the issues caused by setting it as before insert, before update.

 

There will not be very many EDC records with the same name. I would say at most there may be 10 entries of a single EDC account. I don't anticipate the number of reoccurring EDC records to be increasing much at any point in the future so I do not think I am in danger of hitting governor limits here.

 

I appreciate any help, thanks.

Platy ITPlaty IT

That makes it much easier to deal with then.  Here's how you can handle this-

 

First, you'll need a new Apex class (called "VariableClass" in the exampl below) to hold a new static variable like this- "public static Boolean isSysUpdate = false;"  The purpose of this is to track when updates being made to the EDC_Account_Number__c records are being made by your trigger, so it doesn't go into an infinite loop of updates setting off updates.

 

Then the following code could be used to update the Previous EDC Id-

trigger EDC_Account_Number_Trigger on EDC_Account_Number__c (after delete, after insert, after undelete, 
after update) {
	//To track the names of EDC records in the trigger
	Set<String> setEDCName = new Set<String>();
	
	
	for (EDC_Account_Number__c edc : (Trigger.isDelete ? Trigger.old : Trigger.new)){
		//Make sure trigger wasn't called by the DML update that happens below....
		if (!VariableClass.isSysUpdate){
			//Store the name to query for all EDCs with the same name later...
			setEDCName.add(edc.Name);
		}
	}
	
	if (setEDCName.size() > 0){
		//Gather all related EDC records ordered by End Date in a List so they can be updated later
		List<EDC_Account_Number__c> lstUpdate = [Select Id, Name, Previous_EDC_Account_Number_Record__c, Next_EDC_Account_Number_Record__c
			from EDC_Account_Number__c where Name IN: setEDCName Order by Name, End_Date__c];
		//To track the last name that was examined in the loop below
		String strLastName;
		Id idLastId;
		for (EDC_Account_Number__c edc : lstUpdate){
			//Does this have the same Name as the last record in the loop?
			if (edc.Name == strLastName){
				//Then Previous will be idLastId;
				edc.Previous_EDC_Account_Number_Record__c = idLastId;
			} else {
				//Otherwise, update strLastName;
				strLastName = edc.Name;
			}
			idLastId = edc.Id;
		}
		//Set this static variable to true, so the update doesn't fire this same logic again (and again and again...)
		VariableClass.isSysUpdate = true;
		update lstUpdate;
	}//end if there are EDC records to update
}

To be complete, I included "after delete" and "after undelete" in this logic.  To update the Next EDC Number field, you could either repeat the logic above but order the query by End_Date__c DESC (that's an extra query, but the easiest way to do it) or write some fancier logic where you track the Previous Id and Id related together in a Map during that first lstUpdate loop, and then loop through lstUpdate again and use that map to grab pull the Next Id from that Map (slightly more elegant because it skips a query, but more of a pain to write).  

This was selected as the best answer