Trigger+batch exception, Database.executeBatch cannot be called from a batch start, batch execute, or future method

Question

I am getting following error when I call the batch from the trigger.

System.AsyncException: Database.executeBatch cannot be called from a batch start, batch execute, or future method.

My trigger code is as below:

trigger Certification_Data_Trigger on Certification_Data__c (before insert,after insert,before update,after update,before delete,after delete) {
    if(Trigger.isAfter && !CertificationData_Helper.SKIP_TRIGGER_EXECUTION){
        if(Trigger.isUpdate){
            System.debug('in update');

            List<Certification_Data__c> lstCertificates = new List< Certification_Data__c>();

            for (Integer i=0;i<Trigger.new.size();i++) {
                    lstCertificates.add(Trigger.new[i]);
            }
            //Batch execution
            if (lstCertificates.size() > 0 && lstCertificates.size() != 1) 
                Database.executeBatch(new UpdateCertificationBatch(lstCertificates));
        }
    }
}

And batch code is as below:

    global class UpdateCertificationBatch implements Database.Batchable<sObject> {
   List<Certification_Data__c > lstCertificate = new list<Certification_Data__c >();
    public static Set < String > inActiveCertNameSet = new Set < String > {'Dev','Certified System Architect'};
    public static Boolean SKIP_TRIGGER_EXECUTION = false;

    //Constructor initialization
    global UpdateCertificationBatch (List<Certification_Data__c > lstCertification) {
        this.lstCertificate = lstCertification;
    }

    global Database.QueryLocator start(Database.BatchableContext BC) {
        system.debug('lstCertificate ='+lstCertificate );
        return DataBase.getQueryLocator([Select id, is_Active__c, Admin_Update_Date__c,Certification_Name__c,Cert_External_Id__c from Certification_Data__c ]);
    }

    //Execute Method.
    global void execute(Database.BatchableContext BC,List<Certification_Data__c > lstCertification) {

        List < Certification_Data__c > lstToUpdateAssociates = new List < Certification_Data__c > ();
        Set < String > associateAdminUpdateDateSet = new Set < String > ();
        String adminUpdateDate = '';

        adminUpdateDate = lstCertificate[0].admin_update_date__c;
        system.debug('adminUpdateDate ='+adminUpdateDate );

        for (Certification_Data__c cert : lstCertification){
            //adminUpdateDate matches then mark as active & mark the rest as inactive
            if (adminUpdateDate.equalsIgnoreCase(cert.Admin_Update_Date__c) && !inActiveCertNameSet.contains(cert.Certification_Name__c)) {
                cert.is_Active__c = true;
                lstToUpdateAssociates.add(cert);
            } else {
                cert.is_Active__c = false;
                lstToUpdateAssociates.add(cert);
            }
        }

         if (!lsttoUpdateAssociates.isEmpty()) {

            SKIP_TRIGGER_EXECUTION = true;
            Schema.SObjectField fld = Certification_Data__c.Fields.Cert_External_Id__c;
            Database.upsert( lsttoUpdateAssociates , fld, false);
            SKIP_TRIGGER_EXECUTION = false;
        }
    }
    global void finish(Database.BatchableContext BC) {
    }
    }

I am upsertting some certification records, but after upsertting, I am updating all the records with active/inactive status. The total no of records in the org is around 14000.

Answer 1

In your trigger, you're checking if CertificationData_Helper.SKIP_TRIGGER_EXECUTION is false, but in your batch, you're setting UpdateCertificationBatch.SKIP_TRIGGER_EXECUTION, which is a different variable. Presumably, CertificationData_Helper.SKIP_TRIGGER_EXECUTION is still false, so you end up calling Database.executeBatch from your trigger, which causes the error in question.

Your code design has several other flaws, too; you're passing in a list of records, but then you query every record in the database, you're using upsert instead of update, you're copying records from one list to another unnecessarily, you're using a batch process when you could be setting the data synchronously in your trigger, you're triggering on every DML event, even though you're only using one event, you're using equalsIgnoreCase instead of the much simpler ==, you're checking nonsense conditions, such as if (!lsttoUpdateAssociates.isEmpty()) { and if (lstCertificates.size() > 0, which will always be true, etc.

As far as I can tell, some odd 90% of your code isn't necessary. Perhaps you should consider why your code is doing all this work for absolutely no real purpose. Your trigger should end up being about five lines of code, and your batch shouldn't even be a batch. If you need asynchronous processing for some reason, you might want to switch to Queueable instead.

Answer 2

@sfdxfox, thanks for comments, those are really helpful. I have optimized the code, but still the same error is coming.

About functionality: the records which I am upserting(with admin_update_date i.e. today's date) marking true in is_active__c flag and the rest records active flag marking as false. So I have written after update trigger, so I can fetch all the records, compare the dates(ie.admin Update date upserted in excel while data loading) and set the active flag as true/false.

Note: I have to upsert the records using external id, because there can be new records while data loading

Please check my updated code:

Trigger:

trigger Certification_Data_Trigger on Certification_Data__c (after insert,before update,after update) {
    if(Trigger.isAfter && !CertificationData_Helper.SKIP_TRIGGER_EXECUTION){
        if(Trigger.isUpdate){

            List<Certification_Data__c> lstCertificates = new List< Certification_Data__c>();

            //to get list size
            for (Integer i=0;i<Trigger.new.size();i++) {
                   lstCertificates.add(Trigger.new[i]);
            }
            //Don't call the batch if only 1 reocrd is updated.
            if (lstCertificates.size() > 0 && lstCertificates.size() != 1) 
               Database.executeBatch(new UpdateCertificationBatch(lstCertificates[0].Admin_update_date__c));
        }
    }
}

And batch code

global class UpdateCertificationBatch implements Database.Batchable<sObject> {

    String strAdminUpdateDate;
    public static Set < String > inActiveCertNameSet = new Set < String > {'Dev','Certified System Architect'};
    public static Boolean SKIP_TRIGGER_EXECUTION = false;

    //Constructor initialization
    global UpdateCertificationBatch (String adminUpdateDateStr) {
        this.strAdminUpdateDate = adminUpdateDateStr;
    }

    global Database.QueryLocator start(Database.BatchableContext BC) {
        //Update all certifications, if Admin_Update_Date__c matches to admin UpdateDateStrthen mark active, else inactive 
        return DataBase.getQueryLocator([Select id, is_Active__c, Admin_Update_Date__c,Certification_Name__c,Cert_External_Id__c from Certification_Data__c ]);
    }

    //Execute Method.
    global void execute(Database.BatchableContext BC,List<Certification_Data__c > lstCertification) {

        for (Certification_Data__c cert : lstCertification){
            //adminUpdateDate matches then mark as active & mark the rest as inactive
            if (cert.Admin_Update_Date__c == strAdminUpdateDate && !inActiveCertNameSet.contains(cert.Certification_Name__c)) 
                cert.is_Active__c = true;
            else 
                cert.is_Active__c = false;
        }
        SKIP_TRIGGER_EXECUTION = true;
        Schema.SObjectField fld = Certification_Data__c.Fields.Cert_External_Id__c;
        Database.upsert( lstCertification, fld, false);
    }
    global void finish(Database.BatchableContext BC) {
         }
}