[SalesForce] Apex Trigger to Prevent Duplicates Keeps Giving an Error Message

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

Best Answer

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);
    }
}
Related Topic