Salesforce: Apex Update Object And All Child Object Records Associated to Parent Object

Question

if I have an custom object X which can have multiple layers of inheritance of

Grandparent, Parent, Child......... via a Lookup field to it's direct parent.

E.G Record A is Grandparent - Record B is a child and has a lookup to A, - Record C is a child and has a lookup to B - Record D is a child and has a lookup to C - Record E is a child and has a lookup to D ..................and on and on and on. I do not know how many levels deep this will require.

If I have a scenario when I update a field on Parent A and need to update all associated child records what is the recommended or best way to do this via Apex without hitting governor limits of SOQL queries, DML's etc?

On updating A a trigger will fire, and can start off my process........

Do you know the best approach I should take to then update all associated records (future methods, batch methods, best ways to query & update.............. all suggestions are welcomed).

Thanking you in advance for any suggestions.

Answer 1

Triggers perform limited recursion - see e.g. Triggers and Order of Execution - so changes will not automatically cascade down many levels. But they can execute more than once, so it is probably best to start any trigger with a class static variable guard to ensure that the modification code only runs once:

if (!GuardClass.isObjectXUpdating) {
    try {
        GuardClass.isObjectXUpdating = true;
        // Recursive algorithm goes here
    } finally {
        GuardClass.isObjectXUpdating = false;
    }
}

Here is a "recursive algorithm" that follows the parent to child relationship down an open ended number of levels and collects all changes into a single update list:

List<ObjectX__c> updates = new List<ObjectX__c>();
Map<Id, ObjectX__c> parents = Trigger.newMap;
do {
    Map<Id, ObjectX__c> children = new Map<Id, ObjectX__c>([
            select Id, ParentObjectX__c, ...
            from ObjectX__c
            where ParentObjectX__c in :parents.keySet()
            ]);
    // Add code to modify children map data using data from parents map as required
    updates.addAll(children.values());
    parents = children;
} while (parents.size() > 0);
updates updates;

The current Execution Governors and Limits allow 100 queries to be made and 10,000 records to be updated. So assuming little else is going on in the transaction, close to 100 parent-child levels can be spanned (i.e. a lot), and close to 10,000 child objects can be updated (more likely to be a problem).

The above is a simple synchronous solution. If you think your data will push you over the 100 or 10,000 governor limit, then an asynchronous approach will be needed such as kicking off a Batchable. (You can only have up to 10 @future requests outstanding at once so my thinking is that the sequential nature of Batchable is generally more predictable.) Probably best to ask a separate question if that is the case, explaining that the volumes of data will be large.

(FYI the situation is a little different when going from child to parent because SOQL allows multiple levels to be queried at once so allowing fewer queries to be used. The tree also naturally narrows going in that direction.)

Answer 2

Because you don't know how deep the hierarchy will go, you should start at the top. If your trigger fires on a child object, try to use a relationship query to get to the top, or have access to the top of the hierarchy as soon as possible. For instance, if your trigger fires on child object B, but you know you'll need the A level, snag it with: [select id, foo, bar, relationship_to_A__r.id from B where ...]

Once there, work down the hierarchy using the pervious generations id list. Ie: [select id, foo, bar from C where relationship_to_b__r.id in :list_of_b_ids

Answer 3

Here's how I'd recommend querying the objects where possible, assuming you want all of them and have the relationships between the various levels of children and the parent.

map<Id,ObectA__c>ParntMapA = new map<Id,ObjectA__c>([SELECT Id, Field1, Field2...(SELECT ID, Field1__c, Field2__c FROM ObjectB__r), (SELECT ID, Field1__c, Field2__c FROM ObjectC__r), (SELECT ID, Field1__c, Field2__c FROM ObjectD__r), (SELECT ID, Field1__c, Field2__c FROM ObjectE__r) FROM ObjectA__c WHERE ID =: Some relationship with Other Objects]); 

Once you have that, you can easily retrieve and manipulate the data using for loops like the following:

set<Id>ObAIds = new set<Id>;
ObAIds = ParntMapA.keyset();
list<ObjectA__c>LstA = new list<ObjectA__c>();
list<ObjectB__c>LstB = new list<ObjectB__c>();
list<ObjectC__c>LstC = new list<ObjectC__c>();
list<ObjectD__c>LstD = new list<ObjectD__c>();
list<ObjectE__c>LstE = new list<ObjectE__c>();
list<ObjectF__c>LstF = new list<ObjectF__c>();

for(Id aID:ObAIds){
   ObjectA__c obA = ParntMapA.get(aID);
   LstB = obA.ObjectB__r;
   LstC = obA.ObjectC__r;
   LstD = obA.ObjectD__r;
   LstE = obA.ObjectE__r;

   for(ObjectB__C obB:LstB){

      obB.Id = Id of each unique record returned for the child;
      obB.RefobAID__c = aID; // (or obA.Id)
   }

   for(ObjectC__C obC:LstC){

      obC.Id = Id of each unique record returned for the child;
      obC.RefobAID__c = aID; // (or obA.Id)
   }

   for(ObjectD__C obD:LstD){

      obD.Id = Id of each unique record returned for the child;
      obD.RefobAID__c = aID; // (or obA.Id)

      for(ObjectF__c obF:LstF){

         obF.Id = Id of each unique record returned for the child;
         obF.RefobAID__c = aID;
         obF.RefobDID__c = obD.Id; // (or obD.Id)
      }

    }

   for(ObjectE__C obE:LstE){

      obE.Id = Id of each unique record returned for the child;
      obE.RefobAID__c = aID; // (or obA.Id)
   }

} // end of for loop over parent ObjectA__c

In this manner, you can retrieve everything you need for each object, make revisions as your business model requires, then perform updates on each object type afterward or do additional calculations as necessary from the lists you've generated.

Edit: You'll notice the addition of ObjectF to my original code above where I've nested it inside ObjectD. I've also added references to from each of the child Objects to ObjectA as well as a reference from ObjectF to ObjectD. Its the use of these relationships that will help you gather the Id's you'll need to construct your main query for your triggers.

Once you have those, you can then utilize the kind of code that @Keith C has kindly posted. I didn't want to take you there until you understood the relationships between the children, how to construct the query, and how to pull the from the map that gets returned so you can use the results. Until you get this part, there's not much you'll be able to do with Keith's excellent code and advice. These sub-loops are important because they can return many results for a single ID of ObjectA, or for that matter, of say ObjectF relative to ObjectD.

I hope we'll see you posting your code once you begin working on this in earnest.