+ Start a Discussion
BarryPlumBarryPlum 

Need help making trigger bulk safe

I started to write it properly, but I ran into some issues and fell back to some bad practices to get the trigger working.  It works, tests fine, but I just got a DML governance limit email on it.  My suspicion is that email to case inserted enough cases to exceed the limit.

 

Purpose and requirements: Tracking case changes and measuring time that a case is in any status.  Created a custom object called csh__c that holds some case values.  When a case is created and any time those values change, a record is created in the new object with the values of the fields.  There is also a custom field on the case object that holds the last record id that was created.  This allows us to use our visualization software to make a gantt chart like bar that shows the life-cycle of a case.

 

My problem came while trying to figure out how to handle getting the Id of the custom record created back into the case.  For the updates, it was simple, the trigger.new had the case id in it already, I could just update the trigger and it works.  The inserts were more complicated, so I had do use an after insert to make sure I got the case id.  Which meant I needed to do yet another DML statement to update each case.

 

I think I was close, but I'm trying to decide whether I should break it into two triggers and deal with them separately or continue down the path of refining this one trigger.

 

Here's the code, with the original stuff commented out that would have made it bulk safe:

 

trigger cshTrigger on Case (after insert, before update) {

	// create array of case history records for populating
	// csh__c[] cshs = new csh__c[]{};

	// create a string to hold the id of the lead or contact
	String leadcontactid;
	
	// Start iteration thorough cases
	for (Case c : Trigger.new) {

		// check for lead AND contact, set value for contact if it exists
		if(c.ContactId != Null) {
			leadcontactid = String.valueOf(c.ContactId);
		} else {
			leadcontactid = String.valueOf(c.Lead_Name__c);
		}

		// check to see if this is an insert
		if (Trigger.isInsert){
		csh__c newCsh = new csh__c(cid__c = c.id);
		newCsh.accountid__c = c.AccountId;
		newCsh.leadcontactid__c = leadcontactid;
		newCsh.userid__c = System.Userinfo.getUserId();
		newCsh.bugnumber__c = c.BugNumber__c;
		newCsh.priority__c = c.Priority;
		newCsh.impact__c = c.Impact__c;
		newCsh.urgency__c = c.Urgency__c;
		newCsh.casetype__c = c.Type;
		newCsh.status__c = c.Status;
		newCsh.statusdetail__c = c.Status_Detail__c;
		newCsh.closedreason__c = c.Case_Closed_Reason__c;
		newCsh.requesttype__c = c.Request_Type__c;
		newCsh.product__c = c.Product__c;
		newCsh.productversion__c = c.Version__c;
		// cshs.add(newCsh);
		insert newCsh;
		Case updateCase = [select Id from Case where Id=:c.id];
		updateCase.pcshid__c = newCsh.id;
		update updateCase;
		} else {
			// not an insert, check if any fields changed
			for (case oc : trigger.old){
				if (c.Priority != oc.Priority || 
					c.Impact__c != oc.Impact__c ||
					c.Urgency__c != oc.Urgency__c ||
					c.Type != oc.Type ||
					c.Status != oc.Status ||
					c.Status_Detail__c != oc.Status_Detail__c ||
					c.Case_Closed_Reason__c != oc.Case_Closed_Reason__c ||
					c.Request_Type__c != oc.Request_Type__c ||
					c.Product__c != oc.Product__c ||
					c.Version__c != oc.Version__c ||
					c.AccountId != oc.AccountId ||
					c.ContactId	!= oc.ContactId ||
					c.Lead_Name__c != oc.Lead_Name__c ||
					c.bugnumber__c != oc.bugnumber__c
					) {
						csh__c newCsh = new csh__c(cid__c = c.id);
						newCsh.pid__c = c.pcshid__c;
						newCsh.accountid__c = c.AccountId;
						newCsh.leadcontactid__c = leadcontactid;
						newCsh.userid__c = System.Userinfo.getUserId();
						newCsh.bugnumber__c = c.BugNumber__c;
						newCsh.priority__c = c.Priority;
						newCsh.impact__c = c.Impact__c;
						newCsh.urgency__c = c.Urgency__c;
						newCsh.casetype__c = c.Type;
						newCsh.status__c = c.Status;
						newCsh.statusdetail__c = c.Status_Detail__c;
						newCsh.closedreason__c = c.Case_Closed_Reason__c;
						newCsh.requesttype__c = c.Request_Type__c;
						newCsh.product__c = c.Product__c;
						newCsh.productversion__c = c.Version__c;
						// cshs.add(newCsh);
						insert newCsh;
						c.pcshid__c = newCsh.id;
				}
			}
		}
	}
/*	//System.debug(cshs);
	// insert array
	insert cshs;

	// create a map of ids from the insert to update the new cases
	Map<ID,ID> pids = new Map<ID,ID>();
	for (csh__c ch : cshs) {
		pids.put(ch.cid__c,ch.Id);
	}
	System.debug(pids);

	// update the cases for the case history records
	for (case cu : Trigger.new){
		if (Trigger.isInsert){
			Case updateCase = [select Id from Case where Id=:cu.id];
			updateCase.pcshid__c = pids.get(cu.id);
			update updateCase;
			
		} else {
		cu.pcshid__c = pids.get(cu.id);
		}
	} */
	System.debug(Trigger.new);
}

 

 

Thanks in advance for your help!

 

Barry

jkucerajkucera

From a quick scan - Take the insert, SOQL, & update out of the loop.  Instead throw the records in a list and then make 1 insert.  Then it looks like you want 1 query to get affected records to update.  Then loop through that list and put the updates into another list, and finally update outside of the loop.