How to Prevent Overlapping Date Records in Salesforce Using Bulkified Apex Triggers

In plain words: An overlapping date trigger prevents duplicate schedules, tax rates, or pricing tiers from coexisting. If someone tries to insert or update a record whose date window collides with an existing active record for the same state or category, the trigger blocks the save and displays a clear error message.

Enforcing data integrity often involves business logic that out-of-the-box Salesforce duplicate rules cannot handle. A frequent requirement in tax calculations, subscription billing, and employee scheduling is preventing overlapping date periods for matching attributes (such as State and Tax Type). By using a bulkified before insert, before update Apex trigger, you can validate date ranges and block conflicting records prior to database commit.

1. Understanding the Overlap Logic

Checking only for identical start or end dates will miss records where a new period partially overlaps or completely surrounds an existing period. Two date ranges (ValidFrom_A to ValidTo_A) and (ValidFrom_B to ValidTo_B) overlap if and only if:

Overlap Rule: New_Valid_From ≤ Existing_Valid_To AND New_Valid_To ≥ Existing_Valid_From

This single mathematical condition handles all overlap scenarios: inner ranges, outer ranges, leading overlaps, trailing overlaps, and exact matches.

2. Bulkified Apex Trigger Implementation

To avoid hitting SOQL row and CPU time limits, we collect the criteria (VS_State__c and vs_Tax_Type__c) and date boundaries first, query only matching records, and compare values in memory.

Step 1: Create the Trigger Handler Class (TaxRateDuplicateHandler.cls)

public with sharing class TaxRateDuplicateHandler {
    public static void validateDateOverlaps(List<Tax_Rate__c> newRecords, Map<Id, Tax_Rate__c> oldMap) {
        Set<String> stateSet = new Set<String>();
        Set<String> taxTypeSet = new Set<String>();
        Date minFromDate;
        Date maxToDate;
        List<Tax_Rate__c> recordsToValidate = new List<Tax_Rate__c>();

        // 1. Collect filter criteria and validate dates on incoming records
        for (Tax_Rate__c record : newRecords) {
            if (record.Valid_From__c == null || record.Valid_To__c == null) {
                continue;
            }
            if (record.Valid_From__c > record.Valid_To__c) {
                record.addError('Valid To date must be greater than or equal to Valid From date.');
                continue;
            }

            recordsToValidate.add(record);
            stateSet.add(record.VS_State__c);
            taxTypeSet.add(record.vs_Tax_Type__c);

            if (minFromDate == null || record.Valid_From__c < minFromDate) {
                minFromDate = record.Valid_From__c;
            }
            if (maxToDate == null || record.Valid_To__c > maxToDate) {
                maxToDate = record.Valid_To__c;
            }
        }

        if (recordsToValidate.isEmpty()) {
            return;
        }

        // 2. Query only existing records that match the states, tax types, and date boundaries
        Set<Id> currentRecordIds = oldMap != null ? oldMap.keySet() : new Set<Id>();
        List<Tax_Rate__c> existingRecords = [
            SELECT Id, VS_State__c, vs_Tax_Type__c, Valid_From__c, Valid_To__c 
            FROM Tax_Rate__c 
            WHERE Id NOT IN :currentRecordIds 
              AND VS_State__c IN :stateSet 
              AND vs_Tax_Type__c IN :taxTypeSet 
              AND Valid_From__c <= :maxToDate 
              AND Valid_To__c >= :minFromDate
            WITH USER_MODE
        ];

        // 3. In-memory comparison against existing database records
        for (Integer i = 0; i < recordsToValidate.size(); i++) {
            Tax_Rate__c current = recordsToValidate[i];

            for (Tax_Rate__c existing : existingRecords) {
                if (current.VS_State__c == existing.VS_State__c && 
                    current.vs_Tax_Type__c == existing.vs_Tax_Type__c && 
                    current.Valid_From__c <= existing.Valid_To__c && 
                    current.Valid_To__c >= existing.Valid_From__c) {
                    
                    current.addError('A record already exists for State ' + current.VS_State__c + 
                        ' and Tax Type ' + current.vs_Tax_Type__c + ' between ' + 
                        existing.Valid_From__c.format() + ' and ' + existing.Valid_To__c.format() + '.');
                    break;
                }
            }

            // 4. In-memory comparison against other records in the same batch (Bulk Insert Protection)
            for (Integer j = i + 1; j < recordsToValidate.size(); j++) {
                Tax_Rate__c sibling = recordsToValidate[j];
                if (current.VS_State__c == sibling.VS_State__c && 
                    current.vs_Tax_Type__c == sibling.vs_Tax_Type__c && 
                    current.Valid_From__c <= sibling.Valid_To__c && 
                    current.Valid_To__c >= sibling.Valid_From__c) {
                    
                    sibling.addError('Conflict detected: Another record in this batch shares an overlapping date range.');
                }
            }
        }
    }
}

Step 2: Create the Apex Trigger (TaxRateTrigger.trigger)

trigger TaxRateTrigger on Tax_Rate__c (before insert, before update) {
    if (Trigger.isBefore && (Trigger.isInsert || Trigger.isUpdate)) {
        TaxRateDuplicateHandler.validateDateOverlaps(Trigger.new, Trigger.oldMap);
    }
}

360 Duplicate Prevention Architecture Card:

• Trigger Timing: Must run in before insert and before update to halt database insertion before commit.
• Query Selectivity: Filter by criteria fields (VS_State__c, vs_Tax_Type__c) and date bounds (Valid_From__c <= :maxToDate) to prevent 50,000 SOQL row exceptions.
• Self-Exclusion: Exclude Trigger.oldMap.keySet() so updating existing records does not flag themselves as duplicates.
• Batch Self-Validation: Iterating over sibling records in Trigger.new ensures bulk API imports cannot insert conflicting dates in the same transaction.

3. Developer Pitfalls & Best Practices

Developer Trap: Unbounded SOQL & Nested Loops
Querying every record in the table without a WHERE clause and evaluating them using a nested loop (for (obj1 : allRecords) { for (obj2 : Trigger.new) }) creates O(n*m) complexity. This quickly triggers Apex CPU time limit exceeded and Too many query rows: 50001 errors.

• Keep Business Logic Out of Triggers: Always route execution to a dedicated Trigger Handler class to allow clean unit testing and easy maintenance.
• Enforce User Mode Security: Add WITH USER_MODE to your SOQL query to respect Object-Level and Field-Level permissions automatically.
• Provide Descriptive Error Messages: Use addError() on the specific record with dynamic date strings so users know the exact conflicting date range.

Summary

Preventing duplicate date ranges in Salesforce requires combining the universal overlap formula with selective SOQL queries and batch self-comparison. By adopting a dedicated Trigger Handler architecture and filtering records by category and date boundaries, you ensure strict data integrity without risking governor limit bottlenecks.