1

I have a trigger that is running two queries on an Account update: on an User object to get the User's name and one to the Account object to check if the user is different that before the update. One of our managers is doing bulk updates to the associated accounts and is triggering a 'too many SOQL queries' error:

trigger AccountOwnerUpdate on Account (after update) {
for (Account a : Trigger.new){
    if (a.OwnerId != Trigger.oldMap.get(a.Id).OwnerId) {
        List<Account> aId = [Select OwnerId From Account where Id = :a.Id];
        List<User> oName = [Select Name from User where Id = :aId[0].OwnerId];

       //Do addtl actions
    }
}
}

Is there a way to improve this to avoid the error? it really seems like 2 queries shouldn't trigger a 'too many queries' error. Our manager is currently limited to updating 10 Accounts at a time.

possible improvement:

trigger AccountOwnerUpdate on Account (after update) {
Map<Id,Id> accountIdToOwnerId = new map<Id,Id>();
for (Account a : Trigger.new){
    if (a.OwnerId != Trigger.oldMap.get(a.Id).OwnerId) {
        accountIdToOwnerId.put(a.Id, a.OwnerId);
    }
}
List<User> oName = [Select Id, Name from User where Id in : accountIdToOwnerId.values()];

for (User owner : oName) {
    String accountId = '';
    String newOwnerName = EncodingUtil.urlEncode(owner.Name, 'UTF-8');
    for(Id acctId: accountIdToOwnerId.keyset()){
        if (owner.Id == accountIdToOwnerId.get(acctId)) {
            accountId = acctId;
        }
    }

    //Do addtl actions with accountId and newOwnerName Strings
}
}
  • Why do you need to assign values to the variable inside for loop and if condition? &You can simply take that out of loop and condition. – Ricky Dec 19 '16 at 16:01
6

You don't need to run the initial query since that information is contained in trigger.new. All you need to do is iterate on trigger.new to collect the Ids of the new owner.

To do that, you just need to add the ownerId that's in trigger.new to a list. From that list you can then run a single query to get all of the User names and run the rest of your logic on the results of that query. Put it into a map if you need to.

trigger AccountOwnerUpdate on Account (after update) {
Map<Id,Id>accountIdToOwnerId = new map<Id,Id>();
Set<Id>OwnerIds = new Set<Id>();
for (Account a : Trigger.new){
    if (a.OwnerId != Trigger.oldMap.get(a.Id).OwnerId) {

        Set<Id>OwnerIds.add(a.OwnerId);
        accountIdToOwnerId.put(a.Id, a.OwnerId);        
}
List<User> oNames = [Select Id, Name from User where Id IN OwnerIds];        

for (Account a : Trigger.new){
    if (a.OwnerId != Trigger.oldMap.get(a.Id).OwnerId) {

       //Do addtl actions
    }
}
}

EDIT

In your revised code, you don't want to iterate for accountIds because you'll continue to find the same AccountId and may not find all of the related Accounts for the same OwnerId should there be more than one per userId. Instead, you want to have your outer for loop on Account, then use the get method on the map to find the correct OwnerId/UserId. That last loop should look more like what follows:

map<Id,User>idToUsername = [Select Id, Name from User where Id IN OwnerIds];        

for (Id acctId: accountIdToOwnerId.keyset()){
    String accountId = string.valueOf(acctId);
    String newOwnerName = EncodingUtil.urlEncode(idToUsername.get(accountIdToOwnerId.get(acctId)).Name, 'UTF-8');

       //Do addtl actions

}

Now you only have a single for loop for what you wanted to obtain. The map methods allow you to directly get what you were looking for. The inner map gets the OwnerId using the acctId. The outer method uses the OwnerId to retrieve the correct Owner record from the Map Query (note the change in the query to return it as a map) then adds the Name field to it.

|improve this answer|||||
  • Thx - wouldn't I just need to iterate over the accountIdToOwnerId map and not another run over for (Account a : Trigger.new)? would List<User> oNames contain both the user id and name? – dbcn Dec 19 '16 at 16:28
  • Yes, you could do that as well. Just use the keyset() from that map. It all depends on what you're trying to reference and what's the easiest way to get to the data. If it's contained in trigger.new or trigger.old, it might be easier to iterate on trigger.new. – crmprogdev Dec 19 '16 at 16:33
  • Thx - I've updated the question with a new round of code - I haven't tested it yet, but any thoughts appreciated. – dbcn Dec 19 '16 at 17:05
  • You don't want to use the ownerId as the key in your map. I used the accountId for a reason. A user can own more than one account, but an account can only have one owner. You would use getValues() in the map I created. – crmprogdev Dec 19 '16 at 17:09
  • You'd also still need to iterate on the accountId in your second loop, then pull the OwnerId of the related account because of the one to many relationship between between Owners and Accounts. – crmprogdev Dec 19 '16 at 17:16
2

You can use IN clause to get your SOQL out of your for loops.

trigger AccountOwnerUpdate on Account (after update) {
List<ID> OwnerIDSet=new List<ID>();

for (Account a : Trigger.new){
    if (a.OwnerId != Trigger.oldMap.get(a.Id).OwnerId) {
        OwnerIDSet.add(a.OwnerId);

    }
}

List<User> oName = [Select Name from User where Id in :OwnerIDSet];


}

I would suggest you to go through Trigger optimizations. https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_triggers_bestpract.htm

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.