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){

(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!


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.

  • This makes sense, not being able to mix DML operations. That's why I was hoping I'd have some way to modify the trigger objects and keep within the trigger context. I think my best bet might be to just addError on the before trigger and, assuming it isn't all or nothing, have an async method called from the after trigger that update the existing values. Thank you @David – ZAR Feb 27 '18 at 20:15
  • Just so you know, those errored records aren't going to make it to the after trigger, @ZAR, if you mark them errored in the before trigger. – David Reed Feb 27 '18 at 20:18
  • Right, I'll have to cache the field updates somewhere accessible to the after trigger - thanks! – ZAR Feb 27 '18 at 20:20
  • @DavidReed Since checking for duplicates has to happen before insert operation, how do we use Database.Insert in a trigger running 'before insert' ? . I have to do something similar to what you were trying to do and I tried doing it the way you suggested. However, no DML operation can happen in before context so I get an error. How did you implement your solution? – randomperson Jul 1 at 13:49
  • @randomperson Please open a new post with all the details of your challenge; I don't think I understand from the brief comment. – David Reed Jul 1 at 15:54

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.