Trigger to update grand parent in account .Getting error

Question

I am writing a trigger to update grand parent in account.e.g A>B>C>D...>T>U.So grand parent of Account A is U.For the same i wrote a trigger but getting below error:

Review all error messages below to correct your data. Apex trigger updateUltimateParentOnAccount caused an unexpected exception, contact your administrator: updateUltimateParentOnAccount: execution of AfterUpdate caused by: System.DmlException: Update failed. First exception on row 0 with id 00128000006bm2dAAA; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, updateUltimateParentOnAccount: maximum trigger depth exceeded Account trigger event AfterUpdate for [00128000006bm2d] Account trigger event AfterUpdate for [00128000006bm2d] Account trigger event AfterUpdate for [00128000006bm2d] Account trigger event AfterUpdate for [00128000006bm2d] Account trigger event AfterUpdate for [00128000006bm2d] Account trigger event AfterUpdate for [00128000006bm2d] Account trigger event AfterUpdate for [00128000006bm2d] Account trigger event AfterUpdate for [00128000006bm2d] Account trigger event AfterUpdate for [00128000006bm2d] Account trigger event AfterUpdate for [00128000006bm2d] Account trigger event AfterUpdate for [00128000006bm2d] Account trigger event AfterUpdate for [00128000006bm2d] Account trigger event AfterUpdate for [00128000006bm2d] Account trigger event AfterUpdate for [00128000006bm2d] Account trigger event AfterUpdate for [00128000006bm2d] Account trigger event AfterUpdate for [00128000006bm2d]: []: Trigger.updateUltimateParentOnAccount: line 24, column 1

Trigger:

trigger updateUltimateParentOnAccount on Account (after insert,after update) {

List<Account> account = [SELECT id,Name,Grand_parent__c,ParentId FROM Account where id IN :trigger.new];
//List<Account> account = new List<Account>();
for(Account acc : account){
    Boolean isAccNotNull = true;

String strParentRecordId = acc.ParentId;

while(isAccNotNull) {

    Account objParentAccount = [SELECT ParentId, Name FROM Account WHERE Id = :strParentRecordId];

    if(objParentAccount.ParentId == null) {

        acc.Grand_parent__c = objParentAccount.Name;
        isAccNotNull = false;
        continue;
    }

    strParentRecordId = objParentAccount.ParentId;
}

update acc;
}

}

Answer 1

The same trigger is being recursively fired, hitting the maximum stack depth of 16. You also have a SOQL query inside two nested loops, and a DML statement inside one loop...anyway, the issue is pretty clear from the stack trace:

maximum trigger depth exceeded Account trigger event AfterUpdate for

[00128000006bm2d] Account trigger event AfterUpdate for

[00128000006bm2d] Account trigger event AfterUpdate for

[00128000006bm2d] Account trigger event AfterUpdate for

[00128000006bm2d] Account trigger event AfterUpdate for

[00128000006bm2d] Account trigger event AfterUpdate for

[00128000006bm2d] Account trigger event AfterUpdate for

[00128000006bm2d] Account trigger event AfterUpdate for

[00128000006bm2d] Account trigger event AfterUpdate for

[00128000006bm2d] Account trigger event AfterUpdate for

[00128000006bm2d] Account trigger event AfterUpdate for

[00128000006bm2d] Account trigger event AfterUpdate for

[00128000006bm2d] Account trigger event AfterUpdate for

[00128000006bm2d] Account trigger event AfterUpdate for

[00128000006bm2d] Account trigger event AfterUpdate for

[00128000006bm2d] Account trigger event AfterUpdate for

[00128000006bm2d]

A common solution is to use a member static variable to ensure that a block is only executed once in a transaction. You can also add conditional logic that only applies the update based on some criteria, such as a value of a field on the SObject. Andrew Fawcett has an excellent description here:

What are recursive triggers? how to control them? Please provide an example also