Apex : CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY & SELF_REFERENCE_FROM_TRIGGER

Question

I added a new field on Opportunity table to keep a count of records, as well as track the updates made on that record. This is needed for an external system we use. Opportinity table already has around 5000 records now. I would like to update the new field that I added with some sequence order initially. Say each record to have a unique sequence number like 1,2,3 etc(can't use the Id field for unique). The field "Identifier__c" was added for this purpose with data type number and unique contraint. For that I created a temporary class and trigger. But it is throwing the error.

Review all error messages below to correct your data. Apex trigger OpportunitySAPIdentifierHandler caused an unexpected exception, contact your administrator: OpportunitySAPIdentifierHandler: execution of BeforeUpdate caused by: System.DmlException: Update failed. First exception on row 0 with id 006E00000038FPSIA2; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, OpportunitySAPIdentifierHandler: execution of BeforeUpdate caused by: System.DmlException: Update failed. First exception on row 0 with id 006E00000038FPTIA2; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, OpportunitySAPIdentifierHandler: execution of BeforeUpdate caused by: System.DmlException: Update failed. First exception on row 0 with id 006E00000038FPSIA2; first error: SELF_REFERENCE_FROM_TRIGGER, Object (id = 006E00000038FPS) is currently in trigger OpportunitySAPIdentifierHandler, therefore it cannot recursively update itself: [] Class.AutoNumber.OpportunityAutoNumber: line 76, column 1 Trigger.OpportunitySAPIdentifierHandler: line 4, column 1: [] Class.AutoNumber.OpportunityAutoNumber: line 76, column 1 Trigger.OpportunitySAPIdentifierHandler: line 4, column 1: []: Class.AutoNumber.OpportunityAutoNumber: line 76, column 1

Below is the trigger and class used.

trigger Opportuniwith sIdentifierHandler on Opportunity (before insert,before update) {

        If(Trigger.isInsert || Trigger.isUpdate){
            AutoNumber.OpportunityAutoNumber(Trigger.new);
        } 

}

The class is

public with sharing class AutoNumber {


    public static void OpportunityAutoNumber (List<Opportunity> Opp) {

        if(Trigger.isUpdate || Trigger.isInsert) {

            for(Opportunity p: Opp) {

                List<Opportunity> oppList = new List<Opportunity>();
                oppList = [SELECT Id, createdDate, Identifier__c FROM Opportunity where Identifier__c = null and Id != :p.Id limit 100 FOR update];
                for (Opportunity oppL: oppList) {

                    if(oppL.Identifier__c == null) {

                        List<Opportunity> OpportunityList = new List<Opportunity>([select Identifier__c from Opportunity where Identifier__c != null order by Identifier__c desc limit 1]); 
                        if (!OpportunityList.isEmpty()) {  
                            Decimal maxval = OpportunityList.get(0).Identifier__c;
                            oppL.Identifier__c = maxval + 1;
                            update oppL;
                        }

                    }
                    // integer currentCounter = Integer.valueOf(opp.Identifier__c);
                    // oppr.Identifier__c = currentCounter + 1;
                    // update oppr;
                }
            }
        }   
    }

}

Answer 1

To prevent a recursive call, you should make sure your trigger only executes one time. Add a class with a static boolean variable. In the trigger, have a condition that checks the value of the boolean. Once the trigger executes, change the value to false.

trigger Opportuniwith sIdentifierHandler on Opportunity (before insert,before update) {
    if(checkRecursion.runOnce()){
       If(Trigger.isInsert || Trigger.isUpdate){
                AutoNumber.OpportunityAutoNumber(Trigger.new);
       } 
    }

    }

****Utility class****

public Class checkRecursion{

        private static boolean run = true;
        public static boolean runOnce(){
          if(run){
           run=false;
           return true;
          }else{
            return run;
          }
        }
    }

Answer 2

You have a few issues in this trigger.

1. Bulkification: SOQL and DML operations should be outside a for loop (you have them in both the inner and outer for loop)
2. for each individual opportunity you are querying all other opportunities which could include other opportunities inside the same trigger context

Since this appears to be a one time update, you are better off using something like data loader to extract your data, update your sequence then update your opportunity records.

Answer 3

Let me try to answer my own question based on the solution I received. The problem with error was recursive calls to the same trigger again as it was on update. So a check to avoid recursive stopped it.

public static Boolean stopRecursion = false;

 public static void OpportunityAutoNumber (List<Opportunity> Opp) {
       if( !stopRecursion ) {
        stopRecursion = true;
        if(Trigger.isUpdate || Trigger.isInsert) {

         // rest of your code

}

This helped solved the problem. Thank you guys for all the inputs and suggestions. It did help me to improve my code a lot.