3

I got 99% of the way through refactoring a trigger from "after" to "before" when I noticed one little thing I'd overlooked when judging whether or not to do it:

My trigger copies data from "holding fields" populated by ETL scripts into "real fields" (when those "real fields" have null values).

My old "after" trigger included this code, for the occasion when this "holding->real" copy might be a problem (in one case, the "real field" is a unique external ID; rather than SOQL-query ahead for duplicates, I just caught DUPLICATE_VALUE exceptions):

try { update csToUpdate.values(); }
catch ( DMLException e ) {
    for ( Integer i=0; i<e.getNumDml(); i++ ) {
        if ( StatusCode.DUPLICATE_VALUE==e.getDmlType(i) && cIDsThatWereEligibleForFillingInExternalId.contains(e.getDmlId(i)) ) {
            csToUpdate.get(e.getDmlId(i)).ExternalId__c = triggerNewCsMap.get(e.getDmlId(i)).ExternalId__c; // Roll back attempt to fill in external ID from "holding spot" field
        }
    }
    UPDATE csToUpdate.values(); // Try updating csToUpdate again, only this time with problematic records slightly less problematic.

It's easy to slip this in at the end of my "AFTER" helper because I've got explicit DML to put inside a "try{}" block.

Is there any way to error-handle a DMLException arising from the change to records' values that occurs at the end of a trigger's "BEFORE" context?

3

This really is more of an X-Y problem. What you should be doing is to simply set all of the values to whatever they're going to be; the parent level transaction should decide what to do in the event of an error, and should probably use Database.insert(records, false) to allow partial success.

The only way you can detect if there might be a duplicate value problem would be to query the database using SELECT ... FOR UPDATE to try and lock on existing values, but even that may occasionally fail because of race conditions (the values won't be seen as "in flight" until the end of your trigger). Once your trigger ends, the duplicate value check occurs, and no trigger you could write can detect this problem; it must be handled at the next-higher level.

If you still want to do this in the trigger, I'd recommend a temporary object to check uniqueness against. Your trigger would generate the unique values, query this temporary object to see if there's any duplicates (again, using FOR UPDATE), then insert those records. This gives you a better way to control the visibility of unique values in a way the trigger can control it.

  • As far as an "X-Y problem" & "setting all of the values to whatever they're going to be:" the business users want "central fields" updated from ETL loads "only if null" but all ETL data also in its own fields, and that's easiest to code Salesforce-side. I could move the duplicate-checking to the ETL tool. Or I might just go back to my old code, which worked but felt ugly as "after" when I was doing inside-a-single-record field value copying. I just wanted to double-check there wasn't a clever way to "catch" the DML at the end of a "before" context before I looked at other options. – k.. May 8 '18 at 20:42
  • 2
    @k.. Nothing you do in your trigger can catch system errors like that, because your code isn't actually running at the point where those errors are checked. The next higher level has to be responsible for handling those errors. And, realistically, a cascade insert-to-update is probably better if you want this problem to be self-contained. – sfdcfox May 8 '18 at 20:45
  • That was what I assumed in my gut ("your code isn't actually running") -- just wanted to run it by someone else to be sure. Thanks! – k.. May 8 '18 at 20:46
  • Also, I realized that of all the "field-to-field" copy operations I'm doing, only 1 has the need for special handling, so just as I can move it into the ETL tool, I can also put it by itself in an AFTER context and put all the "simpler" operations from the same business need in a "BEFORE" context where they feel like they "belong." – k.. May 8 '18 at 21:03
  • 2
    @k.. Assuming the other values can't fail validation for whatever reason, then that's a perfectly legitimate approach. I've done something like this in the past that involved creating 1:1 records where the other record would be created and then paired back to the original, for example. – sfdcfox May 8 '18 at 21:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.