+ Start a Discussion
Tyler D.ax299Tyler D.ax299 

Preventing duplicate records in a detail table of a Master-Detail type relationship

I need to create a trigger to prevent duplicate records in the Detail table of a custom Master-Detail type relationship.  One field, called Name, must be unique for all records in the Detail record collection for each Master record. Thus, the trigger must prevent duplicate records for 2 fields, the Name field plus the Id of the Master record.  In the force.com Cookbook, page 118, there is an example, but it is for a single field and I don't understand how to modify it for 2 fields. Can anyone help?



Message Edited by Tyler D on 04-08-2009 09:10 AM

Tyler --


If I understand you right, you are checking only that Detail__c.name must be unique for a given Master__c.id


Your trigger should be written as a before update, before insert on Detail__c.  This trigger has access to the Master__c.id field.  However, if you are following the bulk trigger idiom, you could potentially need to check uniqueness on three Detail records in one trigger invocation for:


  • detail name='foo' on master id=1
  • detail name='fie' on master id=4
  • detail name='foo' on master id=8


You'll need several passes:


Step 1- fetch all the Master__c.id's belonging to each triggered Detail__c record (in Trigger.new). Put in a set.


Step 2- fetch into a List all Detail__c where Detail__c.master__c IN :<set built from step 1>. In my example above, this fetches all three records into the List.


Step 3 Reloop back through trigger.new and for each Detail__c, scan the  Step #2 List to see if for the current iterator Detail__c's Master__c.id, there are any Detail__c.Name in the List that match.


There are cleverer ways to do this such as replacing the List with a Map of <ID,List<Detail__c> which makes step3 more efficient.


You also need to be sure to not include in step 1 any Detail__c whose Name didn't change in the trigger (check Trigger.oldMap versus Trigger.newMap).