Trigger recursion giving me a bad day

Salesforce trigger recursion is always fun. Especially when the problem is occurring in a subscribers org with your managed package triggers. This is a follow on to my previous post Preventing trigger recursion and handling a Workflow field update but with an added twist.

As per the previous post, I needed a trigger recursion mechanism that could prevent an infinite loop but still handle subsequent changes made to the Opportunity by workflows. Using the hashCode of the Opportunity worked. Then I got the following error from the managed package trigger after insert:

System.DmlException: Upsert failed. First exception on row 0; first error: DUPLICATE_VALUE, duplicate value found: Namespace__OpportunityIdDynamicPropertyIdUnique__c duplicates value on record with id: a0uc0000003RsMh: []

This was odd, how could the after insert trigger be falling over on existing records that used the Opportunity Id in the composite key? The Opportunity had only just been inserted.

The subscriber org in this case had created their own after insert trigger on Opportunity that created additional OpportunityLineItem records. This resulted in a sequence of events that went something like:

1. DML Insert Opportunity
2. Subscribers After Insert Trigger on Opportunity
   1. DML Insert OpportunityLineItems
   2. Subscribers After Update Trigger on Opportunity
   3. Managed Package After Update Trigger on Opportunity
3. Managed Package After Insert Trigger on Opportunity

Note how the subscribers after insert/update triggers went first and the resulting changes to the Opportunity.Amount from the new OpportunityLineItem records updated the Opportunity records. As a result, the managed packages After Update trigger fired before the After Insert trigger.

The sequence of events again, to hopefully provide some clarity:

1. Opportunity is inserted
2. Subscriber after insert trigger code inserts OpportunityLineItems for the Opportunity
3. Managed package Opportunity After Update trigger fires and inserts records related to the Opportunity
4. Managed package Opportunity After Insert trigger fires and attempts to insert records without checking for existing records (because it is an insert, so there shouldn't be any related records yet - which doesn't hold up in practice).
5. The insertion of the related records fails as they were already created by the Opportunity After Update trigger that occured when the OpportunityLineItems were inserted.

I'd made the incorrect assumption in the After Insert trigger that I didn't need to check for existing records with a lookup to the Opportunity as it couldn't exist yet without completing the insertion transaction.

In hindsight it is clear enough, but it wasn't much fun to figure out from the observed behavior. It's also something I'm sure I've encountered previously. Once I find my previous notes on this problem I'll link them here. I'd previously encountered this in A Tale of Two Triggers.

So, repeating the moral of the story from my previous post in the hopes I'll remember it:

Don't assume that the After Insert trigger will occur before the After Update trigger.