Database DML method executes twice in any record has been failed

Question

I've been trying to leverage custom roll up functionality and found that Database methods is perfoming dml twice. First time it's inserting/updating all records and second time only ones that can be processed. Is it correct? I'm afraid that I'm missing important part in documentation

The optional allOrNone parameter specifies whether the operation allows partial success. If you specify false for this parameter and a record fails, the remainder of the DML operation can still succeed. This method returns a result object that can be used to verify which records succeeded, which failed, and why.

Since I've placed static boolean to check if trigger has been run, I'm loosing data.

If we create test based on the following example

@isTest
public class Test_accountTrigger {
    @isTest
    static void Test_saveResult() {
        Account[] accts = new List<Account>{
            new Account(Name='Account1'),
            new Account()};
        Database.SaveResult[] srList = Database.insert(accts, false);
    }
}

Given trigger will be executed twice

trigger accountTrigger on Account (before insert) {
    system.debug('accountTrigger.sise: ' + trigger.new.size());
}

Debug

22:25:31.1 (362723328)|USER_DEBUG|[2]|DEBUG|accountTrigger.sise: 2
22:25:31.1 (498071498)|USER_DEBUG|[2]|DEBUG|accountTrigger.sise: 1

I'm trying to update multiple child records within ETL and inside Salesforce update correspondent Parent records with flag allOrNone set to false. If any of those fails I'm adding error to child record. Since I have static boolean check, my first attempt of updating parent records is reverted and second is never happened.

Answer 1

Our very own @cropredy has a blog post about this very issue. You should read that post, and don't forget to bookmark his blog!

That post was very helpful in finding the documentation that covers this behavior:

Bulk DML Exception Handling

Exceptions that arise from a bulk DML call (including any recursive DML operations in triggers that are fired as a direct result of the call) are handled differently depending on where the original call came from:

• When errors occur because of a bulk DML call that originates directly from the Apex DML statements, or if the allOrNone parameter of a Database DML method was specified as true, the runtime engine follows the “all or nothing” rule: during a single operation, all records must be updated successfully or the entire operation rolls back to the point immediately preceding the DML statement.

• When errors occur because of a bulk DML call that originates from the SOAP API with default settings, or if the allOrNone parameter of a Database DML method was specified as false, the runtime engine attempts at least a partial save:

  1. During the first attempt, the runtime engine processes all records. Any record that generates an error due to issues such as validation rules or unique index violations is set aside.
  2. If there were errors during the first attempt, the runtime engine makes a second attempt that includes only those records that did not generate errors. All records that didn't generate an error during the first attempt are processed, and if any record generates an error (perhaps because of race conditions) it is also set aside.
  3. If there were additional errors during the second attempt, the runtime engine makes a third and final attempt which includes only those records that didn’t generate errors during the first and second attempts. If any record generates an error, the entire operation fails with the error message, “Too many batch retries in the presence of Apex triggers and partial failures.”

He also notes from Additional Considerations in Triggers and Order of Execution (emphasis added):

When a DML call is made with partial success allowed, more than one attempt can be made to save the successful records if the initial attempt results in errors for some records. For example, an error can occur for a record when a user-validation rule fails. Triggers are fired during the first attempt and are fired again during subsequent attempts. Because these trigger invocations are part of the same transaction, static class variables that are accessed by the trigger aren't reset. DML calls allow partial success when you set the allOrNone parameter of a Database DML method to false or when you call the SOAP API with default settings. For more details, see Bulk DML Exception Handling.

Answer 2

When the allOrNone=false operation fails, SF does a rollback of the entire failed transaction (including what happened in other triggers and rolling back DML limits) before it goes ahead and tries again using the Account which can succeed. So, the fact that the trigger runs twice shouldn't be too much of a worry (except in CPU time limits, perhaps).

Try this code to illustrate:

@isTest public class Test_accountTrigger {
    @isTest
    static void Test_saveResult() {
        Account[] accts = new List<Account>{
            new Account(Name='Account1'),
            new Account()};
        Database.SaveResult[] srList = Database.insert(accts, false);
        System.debug([SELECT Name FROM Lead]);
    } 
}

trigger accountTrigger on Account (before insert) {
    System.debug('accountTrigger.size: ' + trigger.new.size());
    System.debug('Limits.getDmlStatements(before)' + Limits.getDmlStatements());
    for(Account a : Trigger.new) {
        if(a.Name != null) {
            insert new Lead(LastName = a.Name, Company = a.Name);
        }
    }
    System.debug('Limits.getDmlStatements(after)' + Limits.getDmlStatements());
}

When you run this, the debug output is:

[2]|DEBUG|accountTrigger.size: 2
[3]|DEBUG|Limits.getDmlStatements(before)1
[9]|DEBUG|Limits.getDmlStatements(after)2
[2]|DEBUG|accountTrigger.size: 1
[3]|DEBUG|Limits.getDmlStatements(before)1
[9]|DEBUG|Limits.getDmlStatements(after)2
[9]|DEBUG|(Lead:{Name=Account1, Id=00Q58000007DikUEAS})

So, the Lead only gets inserted once and the resulting DML usage is as if the trigger only ran once.

My two pieces of advice to you are:

• Use Declarative Lookup Rollup Summaries instead of writing your own
• If you have to control trigger recursion, do not use static variables. This is just one of the ways your trigger might need to run twice within the scope of those static variables. Instead, write your trigger to look at the data and figure out for itself whether it needs to run again

Answer 3

Another illustration to back Aidan's point and bit of more information.

I run the following code:

1. Changed test class to a public class and public method:-

   public class Test_accountTrigger {
   
       public static void Test_saveResult() {
           Account[] accts = new List<Account>{
               new Account(Name='zzAccount1'),
               new Account()
           };
           Database.SaveResult[] srList = Database.insert(accts, false);
       }
   }
   

2. Changed the trigger to obtain useful information

   trigger accountTrigger on Account (before insert, after insert) {
   
       if (Trigger.isbefore) {
           system.debug('accountTrigger.size before insert: ' + trigger.new.size());
           system.debug('Id of record before insert = '+[select id,name from account where id in :trigger.new]);
   
       }
       if (Trigger.isafter) {
           system.debug('accountTrigger.size after insert: ' + trigger.new.size());
           system.debug('Id of record after insert = '+[select id,name from account where id in :trigger.new]);
   
       }    
   
   
   }
   

3. Finally, called the class method from anonymous execute like this:

   Test_accountTrigger.Test_saveResult();

The debug results were:

As you notice:

1. The system attempts the insert first time for both the records
2. As a matter of fact, it inserts the non-null account while the null one throuws an error and the system never commits the DB. NOTE: The system produces the ID for the first account
3. In the second attempt, it only inserts the accounts that were previosuly successfull but rolledback. I recon Salesforce uses a logic that insert and commit for those records that have an ID already assigned with them. Note: The system uses the same ID as step 2

Further exploration-------- Let us now add another 3rd valid account in our list for insertion and view the data log.

I changed the class to:

public class Test_accountTrigger {

     public static void Test_saveResult() {
        Account[] accts = new List<Account>{
            new Account(Name='ppAccount1'),
            new Account(),
            new Account(Name='ppAccount2')
        };
        Database.SaveResult[] srList = Database.insert(accts, false);
    }
}

I ran the anonomous class and here is the debug log:

As you see, same behavior!