+ Start a Discussion
Ilene JonesIlene Jones 

Mixed_DML_Operation: Trigger on User when isActive changes

In the trigger below, I am attempting to update a custom object called Instructors__c when a user changes.  I'm looking for specific criteria, if they've become inactive, and if they're in a specific department, otherwise I move on.  The issue however is that my update is throwing a MIXED_DML_OPERATION error.  I found references to the @future option, however I need to pass an object to test against with at least 4 elements, the User id, EmployeeNumber, Department and isActive flag.

I did try putting this into a separate function with @future using this as a reference: http://salesforce-evershine-knowledge.blogspot.com/2012/09/unsupported-parameter-type-in-future.html.  That solution had it's own set of issues and errors, namely passing in the information via the objects.  

Any thoughts would be greatly appreciated.

// Run this only on update
    if(Trigger.isUpdate == true) {
        
        // When we deactivate a user, check to see if they are in instructor department
        // if they are, take their exit date and add it to the instructor object
        // and set them to inactive on the instructor object as well.        

    // Department name that instructors belong to
   String department='Instruction';

        //MASS ERROR MESSAGE
        String ErrorMsg = '';

        List<Instructor__c> instructorsToUpdate = new List<Instructor__c>();
        // Loop through each user, in case there is more than one user passed in
        for(User theUser : trigger.new) {            

            // If we have an active user, and that user happens to be in instruction
            if(theUser.isActive == false && theUser.Department  == department) {

                try {
                    // Get the instructor from the instructor and update their record
                    Instructor__c theInstructor = [SELECT Id, TeacherActive__c, Employee_Departure_Date__c FROM Instructor__c WHERE TeacherEmployeeID__c = :theUser.EmployeeNumber LIMIT 1];
                    // Make sure we have an object before trying to work with or update it.
                    if(theInstructor != null) {
                        theInstructor.TeacherActive__c = false;
                        theInstructor.Employee_Departure_Date__c = theUser.Exit_Date__c;
                        // Optimization
                        instructorsToUpdate.add(theInstructor);
                        ErrorMsg = ErrorMsg + 'Instructor: '+theInstructor.Name+', departure: '+theInstructor.Employee_Departure_Date__c+'\n';
                    }
                } catch(System.QueryException e) {
                    System.debug('Failed to find Instructor: ' + e);
                    ErrorMsg += 'Croaked while attempting find Instructor in Instructors__c using employee number: '+ theUser.EmployeeNumber + '<br/>Error: '+ e + '<br /><br/>'; 
                }
            }
        }
        // Do the update on the list, not the individual for batch/optimization
        update instructorsToUpdate;


Error Message text: 

Error: Invalid Data.
Review all error messages below to correct your data.
Apex trigger User_AI caused an unexpected exception, contact your administrator: User_AI: execution of AfterUpdate caused by: System.DmlException: Update failed. First exception on row 0 with id a0UR0000003jSkcMAE; first error: MIXED_DML_OPERATION, DML operation on setup object is not permitted after you have updated a non-setup object (or vice versa): Instructor__c, original object: User: []: Trigger.User_AI: line 42, column 1

Best Answer chosen by Ilene Jones
Ankit AroraAnkit Arora
Here is the list of setup object (a "setup" object is one that must be edited from the setup or builder area of the platform. These object include the User object, Ogranization object, Email templates and so on.) and explaination : http://www.salesforce.com/us/developer/docs/apexcode/index_Left.htm#StartTopic=Content/apex_dml_non_mix_sobjects.htm?SearchType=Stem

So I understand your case, that if you send the id and then uqery it then the actual value might get changed before the future method is executed. I think there are multiple options to solve it, say if I creat a list or map where I put all the values along with the Id then you can get the new values (by querying) and old value from the list/map. Make sense?

All Answers

Ankit AroraAnkit Arora
I hope till now you must be aware of the fact that DML over setup and non-setup objects can't be done in same thread or context. To solve this @future is the option, which you are already familiar with. Let me know what exactly the problem you are facing, as if you can't pass the sObject id then you can send the Id of the record and then query it?
Ilene JonesIlene Jones

I'm still fairly new, even though I'm doing complex things with Salesforce, so this may seem trivial to someone with a little more experience in the area.  I've read about @Future, and tried to work with it, however couldn't pass all of the information that I think I need to, so I went back to the drawing board.  

One basic understanding that I do not have is -- what IS a setup object or a non-setup object?  Are setup objects the default Salesforce objects and non-setup objects the custom objects?  I've never seen them referred to as such, so this is not clear to me.

If I send the ID of the object (in this case User), how do I know what state the isActive flag is currently in?  Will it be the new value or the old value?  If it will be the new value (after insert), then all of this is moot, and I can just query based on the User.Id to get what I need, right?

Thanks for the info and confirmation.

Ankit AroraAnkit Arora
Here is the list of setup object (a "setup" object is one that must be edited from the setup or builder area of the platform. These object include the User object, Ogranization object, Email templates and so on.) and explaination : http://www.salesforce.com/us/developer/docs/apexcode/index_Left.htm#StartTopic=Content/apex_dml_non_mix_sobjects.htm?SearchType=Stem

So I understand your case, that if you send the id and then uqery it then the actual value might get changed before the future method is executed. I think there are multiple options to solve it, say if I creat a list or map where I put all the values along with the Id then you can get the new values (by querying) and old value from the list/map. Make sense?
This was selected as the best answer