+ Start a Discussion
Jean Grey 10Jean Grey 10 

Mixed DML Operation Error on Trigger

I'm getting this error, but I'm confused because I'm only doing one DML when I insert the User History list. 

My trigger:
trigger userHistory on User (before update) {
    system.debug('--------------------------------------------start of trigger: userHistory');
    system.debug('Number of Queries used in this apex code so far: ' + Limits.getQueries());
    system.debug('Number of rows queried in this apex code so far: ' + Limits.getDmlRows());
    system.debug('Number of script statements used so far : ' +  Limits.getDmlStatements());
    system.debug('CPU usage so far: '+Limits.getCpuTime());    
    
	list<User> userList =new list<User>();
    userList=trigger.new;
    system.debug('userList '+userList);
	public List<User_History__c> histList =new List<User_History__c>();
	if(Trigger.isUpdate){
        for(User u:userList){
            // FirstName
            if(trigger.newmap.get(u.id).FirstName !=trigger.oldmap.get(u.id).FirstName){ 
                User_History__c h = new User_History__c();
                h.User__c=u.Id;
                h.Field__c='FirstName';                
                h.OldValue__c=trigger.oldmap.get(u.id).FirstName;
                h.NewValue__c=trigger.newmap.get(u.id).FirstName;
                histList.add(h);
            }
            // LastName
            if(trigger.newmap.get(u.id).LastName !=trigger.oldmap.get(u.id).LastName){ 
                User_History__c h = new User_History__c();
                h.User__c=u.Id;
                h.Field__c='LastName';                
                h.OldValue__c=trigger.oldmap.get(u.id).LastName;
                h.NewValue__c=trigger.newmap.get(u.id).LastName;
                histList.add(h);
            }
            // ProfileId
            if(trigger.newmap.get(u.id).Profile__c !=trigger.oldmap.get(u.id).Profile__c){ 
                User_History__c h = new User_History__c();
                h.User__c=u.Id;
                h.Field__c='Profile';                
                h.OldValue__c=trigger.oldmap.get(u.id).Profile__c;
                h.NewValue__c=trigger.newmap.get(u.id).Profile__c;
                histList.add(h);
            }
            // Role.Name
            if(trigger.newmap.get(u.id).UserRole__c !=trigger.oldmap.get(u.id).UserRole__c){ 
                User_History__c h = new User_History__c();
                h.User__c=u.Id;
                h.Field__c='UserRole';
                h.OldValue__c = trigger.oldmap.get(u.id).UserRole__c;
                h.NewValue__c = trigger.newmap.get(u.id).UserRole__c;
                histList.add(h);
            }
            // ManagerId
            if(trigger.newmap.get(u.id).ManagerId !=trigger.oldmap.get(u.id).ManagerId){ 
                User_History__c h = new User_History__c();
                h.User__c=u.Id;
                h.Field__c='ManagerId';                
                h.OldValue__c=trigger.oldmap.get(u.id).Manager.Name;
                h.NewValue__c=trigger.newmap.get(u.id).Manager.Name;
                histList.add(h);
            }
            // Quota
            if(trigger.newmap.get(u.id).This_Month_s_Quota__c !=trigger.oldmap.get(u.id).This_Month_s_Quota__c){ 
                User_History__c h = new User_History__c();
                h.User__c=u.Id;
                h.Field__c='Quota';
                decimal oldQuota = trigger.oldmap.get(u.id).This_Month_s_Quota__c;
                h.OldValue__c=oldQuota.format();
                decimal newQuota = trigger.newmap.get(u.id).This_Month_s_Quota__c;
                h.NewValue__c=newQuota.format();
                histList.add(h);
            }            
        }
    }
    try{
        userHistoryList insertHistLit = new userHistoryList();
        userHistoryList.insertList(histList);
        system.debug('histList '+histList);
    }
    catch(DMLException e1){
                system.debug(e1);
    }
    try{
        insert histList;
    }
    catch(DMLException e2){
                system.debug(e2);
    }
    system.debug('--------------------------------------------end of trigger: userHistory');
    system.debug('Number of Queries used in this apex code so far: ' + Limits.getQueries());
    system.debug('Number of rows queried in this apex code so far: ' + Limits.getDmlRows());
    system.debug('Number of script statements used so far : ' +  Limits.getDmlStatements());
    system.debug('Final CPU Usage: '+Limits.getCpuTime());
    system.debug('Final heap size: ' +  Limits.getHeapSize());
}

My class:
public class userHistoryList {

    public static void insertList(List<User_History__c> historyList){
        insert historyList;
    }
}
The error:
12:44:21:054 USER_DEBUG [94]|DEBUG|System.DmlException: Insert failed. First exception on row 0; first error: MIXED_DML_OPERATION, DML operation on setup object is not permitted after you have updated a non-setup object (or vice versa): User_History__c, original object: User: []

Can anyone help? Thanks in advance!
 
Best Answer chosen by Jean Grey 10
Raj VakatiRaj Vakati
try this code
 
trigger userHistory on User (before update) {
    system.debug('--------------------------------------------start of trigger: userHistory');
    system.debug('Number of Queries used in this apex code so far: ' + Limits.getQueries());
    system.debug('Number of rows queried in this apex code so far: ' + Limits.getDmlRows());
    system.debug('Number of script statements used so far : ' +  Limits.getDmlStatements());
    system.debug('CPU usage so far: '+Limits.getCpuTime());    
    
	list<User> userList =new list<User>();

//list<User_History__c> userHis = new List<User_History__c>() ; 
    userList=trigger.new;
    system.debug('userList '+userList);
	public List<User_History__c> histList =new List<User_History__c>();
	if(Trigger.isUpdate){
        for(User u:userList){
            // FirstName
            if(trigger.newmap.get(u.id).FirstName !=trigger.oldmap.get(u.id).FirstName){ 
                User_History__c h = new User_History__c();
                h.User__c=u.Id;
                h.Field__c='FirstName';                
                h.OldValue__c=trigger.oldmap.get(u.id).FirstName;
                h.NewValue__c=trigger.newmap.get(u.id).FirstName;
                histList.add(h);
            }
            // LastName
            if(trigger.newmap.get(u.id).LastName !=trigger.oldmap.get(u.id).LastName){ 
                User_History__c h = new User_History__c();
                h.User__c=u.Id;
                h.Field__c='LastName';                
                h.OldValue__c=trigger.oldmap.get(u.id).LastName;
                h.NewValue__c=trigger.newmap.get(u.id).LastName;
                histList.add(h);
            }
            // ProfileId
            if(trigger.newmap.get(u.id).Profile__c !=trigger.oldmap.get(u.id).Profile__c){ 
                User_History__c h = new User_History__c();
                h.User__c=u.Id;
                h.Field__c='Profile';                
                h.OldValue__c=trigger.oldmap.get(u.id).Profile__c;
                h.NewValue__c=trigger.newmap.get(u.id).Profile__c;
                histList.add(h);
            }
            // Role.Name
            if(trigger.newmap.get(u.id).UserRole__c !=trigger.oldmap.get(u.id).UserRole__c){ 
                User_History__c h = new User_History__c();
                h.User__c=u.Id;
                h.Field__c='UserRole';
                h.OldValue__c = trigger.oldmap.get(u.id).UserRole__c;
                h.NewValue__c = trigger.newmap.get(u.id).UserRole__c;
                histList.add(h);
            }
            // ManagerId
            if(trigger.newmap.get(u.id).ManagerId !=trigger.oldmap.get(u.id).ManagerId){ 
                User_History__c h = new User_History__c();
                h.User__c=u.Id;
                h.Field__c='ManagerId';                
                h.OldValue__c=trigger.oldmap.get(u.id).Manager.Name;
                h.NewValue__c=trigger.newmap.get(u.id).Manager.Name;
                histList.add(h);
            }
            // Quota
            if(trigger.newmap.get(u.id).This_Month_s_Quota__c !=trigger.oldmap.get(u.id).This_Month_s_Quota__c){ 
                User_History__c h = new User_History__c();
                h.User__c=u.Id;
                h.Field__c='Quota';
                decimal oldQuota = trigger.oldmap.get(u.id).This_Month_s_Quota__c;
                h.OldValue__c=oldQuota.format();
                decimal newQuota = trigger.newmap.get(u.id).This_Month_s_Quota__c;
                h.NewValue__c=newQuota.format();
                histList.add(h);
            }            
        }
    }
    try{
        userHistoryList insertHistLit = new userHistoryList();
        userHistoryList.insertList(histList);
        system.debug('histList '+histList);
    }
    catch(DMLException e1){
                system.debug(e1);
    }
    try{
ID jobID = System.enqueueJob(new AsyncExecutionExample(histList));
//        insert histList;


    }
    catch(DMLException e2){
                system.debug(e2);
    }
    system.debug('--------------------------------------------end of trigger: userHistory');
    system.debug('Number of Queries used in this apex code so far: ' + Limits.getQueries());
    system.debug('Number of rows queried in this apex code so far: ' + Limits.getDmlRows());
    system.debug('Number of script statements used so far : ' +  Limits.getDmlStatements());
    system.debug('Final CPU Usage: '+Limits.getCpuTime());
    system.debug('Final heap size: ' +  Limits.getHeapSize());
}


public class AsyncExecutionExample implements Queueable {
public List<User_History__c > uhl ;
public AsyncExecutionExample (List<User_History__c > uh){
uhl = uh ;
}
 public void execute(QueueableContext context) {
        
        insert uhl ;        
    }

}

 

All Answers

Rahul.MishraRahul.Mishra
Hi,

Can you change the user trigger and call the controller with future method, below is the code for same:
 
Trigger:


trigger userHistory on User (before update) {

   userHistoryControler.creatHistory(trigger.new);
}


class :

public with sharing class userHistoryControler{
    
    //this is the method that is triggered by a User Insert, After Insert
    @future
    public static void creatHistory(List<User> lstUser) {
	
	for(User u:lstUser){
            // FirstName
            if(trigger.newmap.get(u.id).FirstName !=trigger.oldmap.get(u.id).FirstName){ 
                User_History__c h = new User_History__c();
                h.User__c=u.Id;
                h.Field__c='FirstName';                
                h.OldValue__c=trigger.oldmap.get(u.id).FirstName;
                h.NewValue__c=trigger.newmap.get(u.id).FirstName;
                histList.add(h);
            }
            // LastName
            if(trigger.newmap.get(u.id).LastName !=trigger.oldmap.get(u.id).LastName){ 
                User_History__c h = new User_History__c();
                h.User__c=u.Id;
                h.Field__c='LastName';                
                h.OldValue__c=trigger.oldmap.get(u.id).LastName;
                h.NewValue__c=trigger.newmap.get(u.id).LastName;
                histList.add(h);
            }
            // ProfileId
            if(trigger.newmap.get(u.id).Profile__c !=trigger.oldmap.get(u.id).Profile__c){ 
                User_History__c h = new User_History__c();
                h.User__c=u.Id;
                h.Field__c='Profile';                
                h.OldValue__c=trigger.oldmap.get(u.id).Profile__c;
                h.NewValue__c=trigger.newmap.get(u.id).Profile__c;
                histList.add(h);
            }
            // Role.Name
            if(trigger.newmap.get(u.id).UserRole__c !=trigger.oldmap.get(u.id).UserRole__c){ 
                User_History__c h = new User_History__c();
                h.User__c=u.Id;
                h.Field__c='UserRole';
                h.OldValue__c = trigger.oldmap.get(u.id).UserRole__c;
                h.NewValue__c = trigger.newmap.get(u.id).UserRole__c;
                histList.add(h);
            }
            // ManagerId
            if(trigger.newmap.get(u.id).ManagerId !=trigger.oldmap.get(u.id).ManagerId){ 
                User_History__c h = new User_History__c();
                h.User__c=u.Id;
                h.Field__c='ManagerId';                
                h.OldValue__c=trigger.oldmap.get(u.id).Manager.Name;
                h.NewValue__c=trigger.newmap.get(u.id).Manager.Name;
                histList.add(h);
            }
            // Quota
            if(trigger.newmap.get(u.id).This_Month_s_Quota__c !=trigger.oldmap.get(u.id).This_Month_s_Quota__c){ 
                User_History__c h = new User_History__c();
                h.User__c=u.Id;
                h.Field__c='Quota';
                decimal oldQuota = trigger.oldmap.get(u.id).This_Month_s_Quota__c;
                h.OldValue__c=oldQuota.format();
                decimal newQuota = trigger.newmap.get(u.id).This_Month_s_Quota__c;
                h.NewValue__c=newQuota.format();
                histList.add(h);
            }            
        }
    
    try{
        userHistoryList insertHistLit = new userHistoryList();
        userHistoryList.insertList(histList);
        system.debug('histList '+histList);
    }
    catch(DMLException e1){
                system.debug(e1);
    }
    try{
        insert histList;
    }
    catch(DMLException e2){
                system.debug(e2);
    }
}}

Mark solved if it does helps you

 
Jean Grey 10Jean Grey 10
Thank you, I tried the future method on my class but I get the error "Future methods do not support parameter type of List"
Raj VakatiRaj Vakati
try this code
 
trigger userHistory on User (before update) {
    system.debug('--------------------------------------------start of trigger: userHistory');
    system.debug('Number of Queries used in this apex code so far: ' + Limits.getQueries());
    system.debug('Number of rows queried in this apex code so far: ' + Limits.getDmlRows());
    system.debug('Number of script statements used so far : ' +  Limits.getDmlStatements());
    system.debug('CPU usage so far: '+Limits.getCpuTime());    
    
	list<User> userList =new list<User>();

//list<User_History__c> userHis = new List<User_History__c>() ; 
    userList=trigger.new;
    system.debug('userList '+userList);
	public List<User_History__c> histList =new List<User_History__c>();
	if(Trigger.isUpdate){
        for(User u:userList){
            // FirstName
            if(trigger.newmap.get(u.id).FirstName !=trigger.oldmap.get(u.id).FirstName){ 
                User_History__c h = new User_History__c();
                h.User__c=u.Id;
                h.Field__c='FirstName';                
                h.OldValue__c=trigger.oldmap.get(u.id).FirstName;
                h.NewValue__c=trigger.newmap.get(u.id).FirstName;
                histList.add(h);
            }
            // LastName
            if(trigger.newmap.get(u.id).LastName !=trigger.oldmap.get(u.id).LastName){ 
                User_History__c h = new User_History__c();
                h.User__c=u.Id;
                h.Field__c='LastName';                
                h.OldValue__c=trigger.oldmap.get(u.id).LastName;
                h.NewValue__c=trigger.newmap.get(u.id).LastName;
                histList.add(h);
            }
            // ProfileId
            if(trigger.newmap.get(u.id).Profile__c !=trigger.oldmap.get(u.id).Profile__c){ 
                User_History__c h = new User_History__c();
                h.User__c=u.Id;
                h.Field__c='Profile';                
                h.OldValue__c=trigger.oldmap.get(u.id).Profile__c;
                h.NewValue__c=trigger.newmap.get(u.id).Profile__c;
                histList.add(h);
            }
            // Role.Name
            if(trigger.newmap.get(u.id).UserRole__c !=trigger.oldmap.get(u.id).UserRole__c){ 
                User_History__c h = new User_History__c();
                h.User__c=u.Id;
                h.Field__c='UserRole';
                h.OldValue__c = trigger.oldmap.get(u.id).UserRole__c;
                h.NewValue__c = trigger.newmap.get(u.id).UserRole__c;
                histList.add(h);
            }
            // ManagerId
            if(trigger.newmap.get(u.id).ManagerId !=trigger.oldmap.get(u.id).ManagerId){ 
                User_History__c h = new User_History__c();
                h.User__c=u.Id;
                h.Field__c='ManagerId';                
                h.OldValue__c=trigger.oldmap.get(u.id).Manager.Name;
                h.NewValue__c=trigger.newmap.get(u.id).Manager.Name;
                histList.add(h);
            }
            // Quota
            if(trigger.newmap.get(u.id).This_Month_s_Quota__c !=trigger.oldmap.get(u.id).This_Month_s_Quota__c){ 
                User_History__c h = new User_History__c();
                h.User__c=u.Id;
                h.Field__c='Quota';
                decimal oldQuota = trigger.oldmap.get(u.id).This_Month_s_Quota__c;
                h.OldValue__c=oldQuota.format();
                decimal newQuota = trigger.newmap.get(u.id).This_Month_s_Quota__c;
                h.NewValue__c=newQuota.format();
                histList.add(h);
            }            
        }
    }
    try{
        userHistoryList insertHistLit = new userHistoryList();
        userHistoryList.insertList(histList);
        system.debug('histList '+histList);
    }
    catch(DMLException e1){
                system.debug(e1);
    }
    try{
ID jobID = System.enqueueJob(new AsyncExecutionExample(histList));
//        insert histList;


    }
    catch(DMLException e2){
                system.debug(e2);
    }
    system.debug('--------------------------------------------end of trigger: userHistory');
    system.debug('Number of Queries used in this apex code so far: ' + Limits.getQueries());
    system.debug('Number of rows queried in this apex code so far: ' + Limits.getDmlRows());
    system.debug('Number of script statements used so far : ' +  Limits.getDmlStatements());
    system.debug('Final CPU Usage: '+Limits.getCpuTime());
    system.debug('Final heap size: ' +  Limits.getHeapSize());
}


public class AsyncExecutionExample implements Queueable {
public List<User_History__c > uhl ;
public AsyncExecutionExample (List<User_History__c > uh){
uhl = uh ;
}
 public void execute(QueueableContext context) {
        
        insert uhl ;        
    }

}

 
This was selected as the best answer
Glyn Anderson (Slalom)Glyn Anderson (Slalom)
While you're only doing one DML operation inside the trigger - remember that you're in a trigger because a DML operation was performed.  So your trigger's DML is actually the second (at least the second) DML operation in the transaction.  The last DML operation was an update on User (causing your trigger to run).  So you can't do DML on any non-setup object (like User_History__c).  Both Rahul and Raj have the right idea - you have to do your DML asynchronously, either with an @future method or a Queueable class.  The problem with Rahul's @future is not that it can't take a list -- it just can't take anything non-primitive -- no sObject types or Apex classes; but a list of Ids (for example) would be fine.  In this case, you can use the trick of serializing your User_History__c records into a JSON string and passing that to the @future method.  It can recreate the original list of records and insert them.  I have provided example code below, along with a suggestion for simplifying your trigger.  This will also let you add tracking on new User fields by simply adding an entry to a Map.  Let me know if you have any questions.  The trigger:

<pre>
trigger UserHistory on User (before update)
{
    Map<String,String> fieldLabelsByFieldName = new Map<String,String>
    {   'FirstName'             => 'FirstName'
    ,   'LastName'              => 'LastName'
    ,   'Profile'               => 'Profile'
    ,   'UserRole'              => 'UserRole'
    ,   'ManagerId'             => 'ManagerId'
    ,   'This_Month_s_Quota__c' => 'Quota'
    };

    List<User_History__c> userHistories = new List<User_History__c>();
    for ( User user : Trigger.new )
    {
        for ( String fieldName : fieldLabelsByFieldName.keySet() )
        {
            Object newValue = user.get( fieldName );
            Object oldValue = Trigger.oldMap.get( user.Id ).get( fieldName );
            if ( newValue == oldValue ) continue;

            userHistories.add
            (   new User_History__c
                (   User__c = user.Id
                ,   Field__c = fieldLabelsByFieldName.get( fieldName )
                ,   NewValue__c =
                    (   newValue instanceof Decimal
                    ?   ((Decimal) newValue).format()
                    :   String.valueOf( newValue )
                    )
                ,   OldValue__c =
                    (   oldValue instanceof Decimal
                    ?   ((Decimal) oldValue).format()
                    :   String.valueOf( oldValue )
                    )
                )
            );
        }
    }

    UserHistoryHelper.insertUserHistory_future( JSON.serialize( userHistories ) );
}
</pre>

The helper class:

<pre>
public class UserHistoryHelper
{
    @future
    public static insertUserHistory_future( String jsonUserHistories )
    {
        insert (List<User_History__c>)
        (   JSON.deserialize
            (   jsonUserHistories
            ,   List<User_History__c>.class
            )
        );
    }
}
</pre>
 
Jean Grey 10Jean Grey 10
Thank you all for your help! @Raj V the code works perfectly!

Glyn I appreciate this approach and it does seem much simpler, but the Profile field kept throwing an error when I tested.