You need to sign in to do that
Don't have an account?

MIXED_DML_OPERATION error in Trigger on User
Ok, so the driving force this this piece of code was to reassign leads for Users that are made inactive to a Queue to reassigned to other sales reps. Seems like a relatively common situation, right?
Seemed like a pretty simple trigger to right as well, even for a beginner like myself. Unfortunately I appear to be wrong in that assumption.
From my reading it seems that we cannot update other records (Leads, Contacts, Accouts, etc.) when a User's profile is updated. Is that really the case or am I missing something terribly obvious here?
trigger ReassignSalesLeadstoSupervisorforInactive on User (after update) { //Get Id for Sales Supervisors Queue Group sup = [SELECT Id FROM Group WHERE Name='Sales Supervisors' AND Type='Queue']; Set<Id> inactive = new Set<Id>(); List<Lead> reassign = new List<Lead>(); //Determine if user is a Sales User that has just been made Inactive for(user u : trigger.new){ if(u.Department == 'Sales' && Trigger.oldMap.get(u.id).IsActive == True && u.IsActive == False){ inactive.add(u.id); } } System.debug('****Users****'+inactive); //Find the inactive user's leads and reassign them to the Sales Supervisors Queue for(Lead l : [SELECT Id,OwnerId FROM Lead WHERE OwnerId IN :inactive]){ l.OwnerId = sup.id; reassign.add(l); } System.debug('****Leads****'+reassign); update reassign; }
David,
You are correct, you can't do DML on other objects when you have a trigger on a Setup object (like user).
The most popular workaround to this is to break your code into a separate class that acts with a @future keyword (making the transaction asynchronous). Then in your trigger, you pass your list of IDs to the other class and they get updated separately.
Something like this:
trigger ReassignSalesLeadstoSupervisorforInactive on User (after update) {
//Get Id for Sales Supervisors Queue
Group sup = [SELECT Id FROM Group WHERE Name='Sales Supervisors' AND Type='Queue'];
Set<Id> inactive = new Set<Id>();
//Determine if user is a Sales User that has just been made Inactive
for(user u : trigger.new){
if(u.Department == 'Sales' && Trigger.oldMap.get(u.id).IsActive == True && u.IsActive == False){
inactive.add(u.id);
}
}
System.debug('****Users****'+inactive);
LeadReassign.reassignleads(inactive);
}
public class LeadReassign{
@future
public static void reassignleads(Set<ID> inactive){
List<Lead> reassign = new List<Lead>();
//Find the inactive user's leads and reassign them to the Sales Supervisors Queue
for(Lead l : [SELECT Id,OwnerId FROM Lead WHERE OwnerId IN :inactive]){
l.OwnerId = sup.id;
reassign.add(l);
}
System.debug('****Leads****'+reassign);
update reassign;
}
}
All Answers
David,
You are correct, you can't do DML on other objects when you have a trigger on a Setup object (like user).
The most popular workaround to this is to break your code into a separate class that acts with a @future keyword (making the transaction asynchronous). Then in your trigger, you pass your list of IDs to the other class and they get updated separately.
Something like this:
trigger ReassignSalesLeadstoSupervisorforInactive on User (after update) {
//Get Id for Sales Supervisors Queue
Group sup = [SELECT Id FROM Group WHERE Name='Sales Supervisors' AND Type='Queue'];
Set<Id> inactive = new Set<Id>();
//Determine if user is a Sales User that has just been made Inactive
for(user u : trigger.new){
if(u.Department == 'Sales' && Trigger.oldMap.get(u.id).IsActive == True && u.IsActive == False){
inactive.add(u.id);
}
}
System.debug('****Users****'+inactive);
LeadReassign.reassignleads(inactive);
}
public class LeadReassign{
@future
public static void reassignleads(Set<ID> inactive){
List<Lead> reassign = new List<Lead>();
//Find the inactive user's leads and reassign them to the Sales Supervisors Queue
for(Lead l : [SELECT Id,OwnerId FROM Lead WHERE OwnerId IN :inactive]){
l.OwnerId = sup.id;
reassign.add(l);
}
System.debug('****Leads****'+reassign);
update reassign;
}
}
Jim,
Thanks so much.
That was just what I needed. It's amazing how much I'm learning from the boards in a past few weeks.
Can't wait for my formal training out in sunny CA.
When you mentioned
You are correct, you can't do DML on other objects when you have a trigger on a Setup object (like user).
is it also mean that we can't do dml in custom object ?
Thanks.