Create Trigger to conditionally before inserting new records, criteria below

Question

I am trying create a Trigger in my Salesforce Org, where I want to create a new record in an object only if no records already exists, and if records already exist then the end date should be null.

If there are records with end date values then the new record can be created. hope this is not too confusing. I have created the following code. This code creates a new record even though the end date has a date in the existing record.

trigger CreateGADeclarationRecord on GA_Declaration__c (before insert) {
    // Query existing records with blank End Date
    List<GA_Declaration__c> existingRecords = [
        SELECT Id, End_Date__c
        FROM GA_Declaration__c
        WHERE End_Date__c = NULL OR End_Date__c = ''
    ];

    if (existingRecords.isEmpty()) {
        insert newRecord;
    } else {
        // Prevent the insert
        for (GA_Declaration__c newRecord : Trigger.New) {
            newRecord.addError('Cannot create a new record. Existing records found with non-blank End Date.');
        }
    }
}

The End_Date__c = '' in the where clause throws an error, so I removed it while testing, and only used the null criteria. I think the problem is in the select statement, it is not correctly identifying the already existing records.

Answer 1

You have several problems. First, you're looking for records where End_Date__c is null, and then adding an error if there are any records. This is different than your stated intent that you want to allow a new record only if all records have a null End_Date__c. Further, you're already in an insert operation, so you don't need to insert again. Rather, you just need to not add an error. You also haven't considered the possibility that two or more records inserted at approximately the same time may cause a problem. As such, you need to also consider row locking, which means using an after insert trigger.

Here's my proposed not-ready-for-production revision:

trigger CreateGADeclarationRecord on GA_Declaration__c (after insert) {
    // Check for an existing record
    GA_Declaration__c[] existingRecords = [
        SELECT  Id
        FROM    GA_Declaration__c
        WHERE   End_Date__c <> NULL AND
                Id <> :Trigger.new
        LIMIT   1
        FOR UPDATE
    ];
    // Check for bulk insert of records
    Integer matchingRecords = 0;
    for(GA_Declaration__c newRecord: Trigger.new) {
        if(newRecord.End_Date__c != null) {
            matchingRecords++;
        }
    }
    for(Integer i = Trigger.size - 1; i >= 0; i--) {
        if(
            !existingRecords.isEmpty() && Trigger.new[i].End_Date__c != null || // Preexisting non-blank date
            matchingRecords > 1 && Trigger.new[i].End_Date__c != null // More than 1 record in this batch
        ) { 
            Trigger.new[i].addError('Cannot create a new record. Existing records found with non-blank End Date.');
            matchingRecords--;
        }
    }
}

You should, however, be using some kind of trigger framework.

Answer 2

try below code:

trigger CreateGADeclarationRecord on GA_Declaration__c (before insert) {
    List<GA_Declaration__c> existingRecords = [SELECT Id, End_Date__c FROM GA_Declaration__c];
    
    Boolean canCreateNewRecord = existingRecords.isEmpty();

    if (!canCreateNewRecord) {
        canCreateNewRecord = true;
        for (GA_Declaration__c record : existingRecords) {
            if (record.End_Date__c != null) {
                canCreateNewRecord = false;
                break;
            }
        }
    }

    if (!canCreateNewRecord) {
        for (GA_Declaration__c rec : Trigger.new) {
            rec.addError('A new record cannot be created in GA_Declaration__c because there are existing records with a non-null End_Date__c.');
        }
    }
}