Trigger: Catch duplicate and update existing record

Question

I'm trying to write a trigger that can not only detect a duplicate but can update the existing record (similar to an upsert operation).

For several reasons, we are unable to actually use upsert. However, we can define a duplicate by a concatenation of field values.

Example, say we have a custom junction between opportunity and contacts (and are NOT using the standard OpportunityContactRole). We have four fields on ContactOpportunity__c:

1. id
2. OpportunityId
3. ContactId
4. Role

We currently have a record:

{
  id: oc123,
  OpportunityId: opp123,
  ContactId: con123,
  Role: 'Like a Boss'
}

We now are trying to insert the following record:

{
  id: null //Not assigned since is new record pre-insert
  OpportunityId: opp123,
  ContactId: con123,
  Role: 'Like an assistant'
}

I would want the new Contact_Opportunity__c to be blocked and instead update the current Contact_Opportunity__c to be:

{
  id: oc123,
  OpportunityId: opp123,
  ContactId: con123,
  Role: 'Like an assistant'
}

How can I best achieve this?

I tried the setting the new insert id to the old id:

map<id, contact_opportunity__c> mapConOpp = new map<id, contact_opportunity__c>();
set<id> oppIds = new set<id>();
for(obj o:Trigger.new){
  mapConOpp.put((contact_opportunity__c)o);
  oppIds.add(o.OpportunityId);
}

(for contact_opportunity__c existing_co : [SELECT opportunityid, contactid, role 
                                  FROM contact_opportunity__c
                                  WHERE opportunityid in :oppIds]){
  for(contact_opportunity__c new_co : mapConOpp.values()){
    //check for duplicate
    if(new_co.opportunityid == existing_co.opportunityid &&
       new_co.contactid == existing_co.contactid){
      //coerce id to update existing record
      new_co.id = existing_co.id
    }
  }
}

So setting the new object's id to the existing does not hold. I also tried removing the new object and adding the existing object to the trigger context - nope.

1. So how can I turn this insert into an update?
2. If not possible, how can I just block this one insert while still allowing the rest to go through (addError stops the whole DML, I believe?)

Thank you all!

Answer 1

You can't "transform" one DML operation into another. You can add errors to specific records that have duplicates.

The behavior when you do add those errors is actually dependent upon the code that initiated the original DML operation. Basic DML statements (insert, update, etc.) are all all-or-nothing; the whole transaction is rolled back if there's an error on any record.

However, the Database.insert method and its ilk take a Boolean parameter allOrNone. If that parameter is set to false, errors only impact the specific record with the error attached to it, while other records succeed or fail on their own merits. It's then incumbent upon the caller to inspect the list of result objects returned and take action upon the errors that took place.

You could also skip the errors, allow the transaction to go through, and fire an asynchronous job (future or Queueable) to perform a merge. However, before doing that, I would look really hard at the factors stopping you from using upsert, because that would in essence be a terribly inefficient reimplementation of upsert.