Handling FIELD_INTEGRITY_EXCEPTION with try catch and logging errors

Question

I have got an apex trigger that sometimes errors with

System.DmlException: Update failed. First exception on row 0 with id 00Q0800000fhnM2EAI; first error: FIELD_INTEGRITY_EXCEPTION

The reasons for the DmlException vary, they can be the above or things like:

• The Phone number must start with "+" or "00" and may only contain digits, space characters, "+", and "-".: []

• Please complete "Country" or "State" field.: []

• The existing country/territory doesn't recognize the state value for field: Billing State/Province Code: [BillingStateCode]

• Please enter State in Physical Address: [BillingState]

• There's a problem with this state, even though it may appear correct. Please select a state from the list of valid states.: Billing State/Province: [BillingState]

These look like user input errors so I decided to handle the exceptions gracefully. Here is my apex trigger:

trigger trgUpdateVCountField on V__c (after insert, before delete) {  
    
    List<Lead> leadsToUpdate = new List<Lead>{};
    List<Account> accountsToUpdate = new List<Account>{};
        
    if(trigger.isDelete) {
        
        for (V__c vis : trigger.old) {       
            
            if(vis.Lead__c != null) {          
                Lead leadToUpdate = [SELECT Id, LV__V_Count__c FROM Lead WHERE Id = :vis.Lead__c LIMIT 1];
                double calculatedVCount = [SELECT COUNT() FROM LV__V__c WHERE LV__Lead__C = :leadToUpdate.Id LIMIT 50000];
                calculatedVCount = calculatedVCount - 1;                
                if(calculatedVCount != leadToUpdate.LV__V_Count__c) {
                    leadToUpdate.LV__V_Count__c = calculatedVCount;
                    leadsToUpdate.add(leadToUpdate);
                }
            }
            
            if (vis.Account__c != null) {
                Account accountToUpdate = [SELECT Id, LV__V_Count__c FROM Account WHERE Id = :vis.Account__c LIMIT 1];
                double calculatedVCount = [SELECT COUNT() FROM LV__V__c WHERE LV__Account__C = :accountToUpdate.Id LIMIT 50000];
                calculatedVCount = calculatedVCount - 1;
                if(calculatedVCount != accountToUpdate.LV__V_Count__c) {
                    accountToUpdate.LV__V_Count__c = calculatedVCount;
                    accountsToUpdate.add(accountToUpdate);
                }
            }      
        }
    }
    else {
        for (V__c vis : trigger.new) {       
            if(vis.Lead__c != null) {          
                Lead leadToUpdate = [SELECT Id, LV__V_Count__c FROM Lead WHERE Id = :vis.Lead__c LIMIT 1];
                double calculatedVCount = [SELECT COUNT() FROM LV__V__c WHERE LV__Lead__C = :leadToUpdate.Id LIMIT 50000];
                if(calculatedVCount != leadToUpdate.LV__V_Count__c) {
                    leadToUpdate.LV__V_Count__c = calculatedVCount;
                    leadsToUpdate.add(leadToUpdate);
                }
                
            }
            
            if (vis.Account__c != null) {
                Account accountToUpdate = [SELECT Id, LV__V_Count__c FROM Account WHERE Id = :vis.Account__c LIMIT 1];
                double calculatedVCount = [SELECT COUNT() FROM LV__V__c WHERE LV__Account__C = :accountToUpdate.Id LIMIT 50000];
                if(calculatedVCount != accountToUpdate.LV__V_Count__c) {
                    accountToUpdate.LV__V_Count__c = calculatedVCount;
                    accountsToUpdate.add(accountToUpdate);
                }
            }      
        }
    }    

    if(!leadsToUpdate.isEmpty()) { update leadsToUpdate; } // LINE THAT THROWS THE EXCEPTIONS
    if(!accountsToUpdate.isEmpty()) { update accountsToUpdate; }
}

The error gets thrown at the update leadsToUpdate; line. I could wrap that with a try catch but I am unsure on what to put inside the catch section. I would like to log the error with as much detail as possible but I am not sure where or how to do that, I am struggling to find any examples of this.

Answer 1

No need for try/catch. You can allow partially succeed update with allOrNone parameter equals to false like:

Database.update(leadsToUpdate, false);

Now you can save your results to List<Database.SaveResult>, hence, you can iterate through this list and retrieve results with isSuccess() equals false. With getErrors() you can retrieve exceptions as you described in the beginning of your question (docs):

List<Database.SaveResult> ans = Database.update(leadsToUpdate, false);
for (Database.SaveResult result_i : ans) {
    if (!result_i.isSuccess()) {
        for (Database.Error error_i : result_i.getErrors()) {
            System.debug(error_i); //here you can process the error
        }
    }
}

UPDATE for the comment:

You can use a custom sobject Error_Log__c. To create it, go to Setup -> Object Manager -> New. Also you can create custom field Error_Message__c on it. I prefer using custom sobjects for logging such as the one mentioned in your comment, because usually I need only specific info about failed transaction.

So the final code after you created the Error_Log__c custom sobject will be:

List<Error_Log__c> logs = new List<Error_Log__c>();
List<Database.SaveResult> ans = Database.update(leadsToUpdate, false);
for (Database.SaveResult result_i : ans) {
    if (!result_i.isSuccess()) {
        for (Database.Error error_i : result_i.getErrors()) {
            logs.add(
                new Error_Log__c(
                    Error_Message__c = error_i.getStatusCode() + ': ' + error_i.getMessage()
                )
            );
        }
    }
}
insert logs;

Another approach, you can use standard EventLogFile and trailhead unit about it. But for more efficient usage you'll probably need to purchase Event Monitoring.

Answer 2

Your trigger has a couple of issues, the most important being that you do several queries from within a for loop. Besides that the counts will be incorrect if you delete several V__c records related to the same Lead or Account in the same transaction.

By running the trigger after insert and after delete you can mostly use the same code in both cases.

Below is an (untested) version of the trigger that would only run four queries even if 200 records are being inserted at once.

Like Mariia I use the Database.update function to catch the errors, but I build a map of the related visibility records to which I add the error message so it will be shown back to the user.

trigger trgUpdateVCountField on V__c (after insert, after delete) {  
    
    List<Lead> leadsToUpdate = new List<Lead>{};
    List<Account> accountsToUpdate = new List<Account>{};
    
    Set<Id> leadIds = new Set<Id>();
    Set<Id> accountIds = new Set<Id>();
    Map<Id, V__c> visByRelatedId = new Map<Id, V__c>();
    for (V__c vis: Trigger.isDelete ? Trigger.old : Trigger.new){
        if (vis.Lead__c != null){
            leadIds.add(vis.Lead__c)
            visByRelatedId.put(vis.Lead__c, vis);
        }
        if (vis.Account__c != null){
            accountIds.add(vis.Account__c);
            visByRelatedId.put(vis.Account__c, vis);
        }
    }


    Map<Id, Lead> leadsById = new Map<Id, Lead>([SELECT Id from Lead where Id in :leadIds]);
    for (Lead ld: leadsById.values()){
        // initialize count to 0 in case none are left;
        ld.LV__V_Count__c = 0;
    }
    for (AggregateResult agg: [SELECT LV__Lead__c leadId, count(Id) amount from LV__V__c where LV__Lead__c in :leadsById.keySet() GROUP BY LV__Lead__c]){
        Lead ld = leadsById.get(agg.get('leadId'));
        ld.LV__V_Count__c = (Decimal)agg.get('amount');
    }
    leadsToUpdate.addAll(leadsById.values());
    Map<Id, Account> accountsById = new Map<Id, Account>([SELECT Id from Account where Id in :accountIds]);
    for (Account acc: accountsById.values()){
        // initialize count to 0 in case none are left;
        acc.LV__V_Count__c = 0;
    }
    for (AggregateResult agg: [SELECT LV__Account__C leadId, count(Id) amount from LV__V__c where LV__Account__C in :accountsById.keySet() GROUP BY LV__Account__C]){
        Account acc = accountsById.get(agg.get('leadId'));
        acc.LV__V_Count__c = (Decimal)agg.get('amount');
    }
    accountsToUpdate.addAll(accountsById.values());


    Integer index = 0;
    for (Database.SaveResult sr: Database.update(leadsToUpdate, false)){
        if (!sr.isSuccess()){
            V__c vis = visByRelatedId.get(leadsToUpdate[index].Id);
            for (Database.Error err: sr.getErrors()){
                System.debug(LoggingLevel.ERROR, err.getMessage());
                vis.addError(err.getMessage());
            }
        }
        ++index;
    }
    index = 0;
    for (Database.SaveResult sr: Database.update(accountsToUpdate, false)){
        if (!sr.isSuccess()){
            V__c vis = visByRelatedId.get(accountsToUpdate[index].Id);
            for (Database.Error err: sr.getErrors()){
                System.debug(LoggingLevel.ERROR, err.getMessage());
                vis.addError(err.getMessage());
            }
        }
        ++index;
    }
}