Simple Apex trigger throwing 'too many SOQL queries' error

Question

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
}
}

Answer 1

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.

Answer 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