Apex Trigger to Prevent Duplicates Keeps Giving an Error Message

Question

I wrote an APEX trigger to check for duplicates before a record is created or saved to ensure there are no duplicates. However, since the variables are empty (before record creation) I keep getting the following error message:

Apex trigger COIDuplicateTrigger caused an unexpected exception, contact your administrator: COIDuplicateTrigger: execution of BeforeInsert caused by: System.NullPointerException: Attempt to de-reference a null object: Trigger.COIDuplicateTrigger: line 7, column 1

APEX Trigger:

trigger COIDuplicateTrigger on COI__c (before insert,before update) {
// creates trigger on the COI object to occur before insert or before update of record
    map<Id,COI__c> existingCOImap = new  map<Id,COI__c>([Select Id, Name, Company__c From COI__c]);
// maps variable to store data queried from SOQL statement

    for(COI__c i : Trigger.new){
        if(i.Name == existingCOImap.get(i.Id).Name && i.Company__c == existingCOImap.get(i.Id).Company__c){
            i.addError('Error! Duplicate COI record found.');
        }
    }       
}
// loop to see if data from name AND company fields match a COI record. if TRUE then throws error message.

Any tips on how to work around this?

Thanks!

Mercury1986

Answer 1

By querying every COI__c object your trigger will become slower as the number of COI__c objects grows and eventually will fail entirely when there are more than 50,000 objects because of the "Total number of records retrieved by SOQL queries" governor limit. Not a good design choice. Also in a before insert the ID values have not yet been assigned.

A solution is to add a formula field - lets call it Key__c - that combines (appends) the fields that you want to check for uniqueness. (You can also ask Salesforce support to add an index to this field to speed access.)

Then your trigger becomes:

trigger COIDuplicateTrigger on COI__c (before insert, before update) {

    String message = 'Error! Duplicate COI record found.';

    Set<String> newKeys = new Set<String>();
    for(COI__c coi : Trigger.new) {
        String key = coi.Name + coi.Company__c;
        if (newKeys.contains(key)) coi.addError(message);
        else newKeys.add(key);
    }

    Set<String> existingKeys = new Set<String>();
    for (COI__c coi : [select Key__c from COI__c where Key__c in :newKeys]) {
        existingKeys.add(coi.Key__c);
    }

    for(COI__c coi : Trigger.new) {
        String key = coi.Name + coi.Company__c;
        if (existingKeys.contains(key)) coi.addError(message);
    }
}

Answer 2

No reason to write at trigger at all really (Unless you want to control the error message).

Just create a Unique Field and use a workflow to set it to the value of coi.Name + coi.Company__c

Then if a record is being inserted that is a duplicate an error message will be present that it is a duplicate value of xxx record with a link to the record.

Answer 3

As you already said, the error is because your query does not have the record you are trying to fetch which results in a null pointer exception on existingCOImap.get(i.Id) . The record is not in the database and hence your query map would not have it.

How about an easier way to do this avoiding code? use validation rules to avoid duplicates