Apex Trigger for Updating Sibling Records

Question

I have an object (call it "Details") with data that would look like this:

+------------+------------+-----------------------+
| Sequence # | % of total | Cumulative % of Total |
+------------+------------+-----------------------+
|          1 |         20 |                    20 |
|          2 |         25 |                    45 |
|          3 |         30 |                    75 |
|          4 |         15 |                    90 |
|          5 |         10 |                   100 |
+------------+------------+-----------------------+

The users can only edit the "% of Total" column. So for example if I they edit sequence #3 to have a % of total like 25 (denoted by the **below), the results should look like this:

+------------+------------+-----------------------+
| Sequence # | % of total | Cumulative % of Total |
+------------+------------+-----------------------+
|          1 |         20 |                    20 |
|          2 |         25 |                    45 |
|          3 |     **25** |                   *70 |
|          4 |        *15 |                   *85 |
|          5 |        *15 |                   *100|
+------------+------------+-----------------------+

I'm trying to perform the recalculation in an Apex trigger. In this example, the user updated the % of total for row 3 to 25%. The cumulative % of total for Seq. #3 needs to be updated, as do the % of total in rows 4 and 5, with the % of total for rows 4 and 5 being split evenly.

I can get the logic to work in a trigger. The way I have it now, I'm using two triggers: one before update, and one after.

• The before trigger updates row 3's cumulative % of total.
• When the after trigger runs, it updates the % of total on the next row, so in this case, row 4.
• That fires the before update trigger for row 4,which sets its cumulative % of total.
• The after trigger runs which updates row 5, which fires the before trigger.

The problem is that if there are too many rows, then I exceed the trigger depth. My first question: what's the best way to avoid this?

Or, alternatively, have I approached this completely wrong? I could probably do it all in an after trigger, but I'd probably end up with the same issue.

Answer 1

This approach should work better.

1. Create a custom field in the parent sobject [let's call it Recalculate, type: Boolean, default value: false].
2. In the child object's apex trigger, whenever the % of Total value changes (or a new child record is inserted), simply update the boolean field of the parent sobject. [No need to update the sibling records here. Lets handover that to parent sobject trigger]
3. Create apex trigger on the parent sobject that checks if the boolean field has changed and is now equal to true. If the condition checks out to be true, then query the child records and update the % fields (Sequence # field can aid in identifying which child records are to be updated). Finally, reset the boolean field to false.
4. Make use of static variable to avoid any recursion in the child and parent sobject triggers, as needed. [Thanks to @PhilW for pointing out the clarification needed here. DO NOT use boolean static variable because that can interfere when trigger executes multiple times for different chunks of data within a single transaction. Check out the link suggested in his comment for best practice.]

By taking this approach,

• You will not exceed the trigger depth.
• There will be a clear separation of responsibility between the child and parent sobject apex triggers.
• In your current set-up, you are probably not considering the scenario of simultaneous insert as well as update. For example, lets say you already have 3 child records and 1 new child record is getting inserted. All of this occurs in single transaction. While update on one record tries to update the others, the new insert is not yet committed to the DB and hence, you cannot locate it efficiently (even if you end up with some workaround). You will run into incorrect distribution of % values or exceed trigger depth.

---

Update:

Sequence # field can aid in identifying which child records are to be updated.

This should serve two purposes, namely, avoiding any recalculation on records with sequence # that are lesser than the one updated by user and also, track which sequence # records are to be recalculated. This can be accomplished using any of the following (I would prefer the second option because it can be done in before trigger without a need of an additional DML):

• Store the sequence #s in another additional field in the parent record (to be updated in insert/ update trigger of child sobject)
• Have an boolean field in the child record (to be updated in insert/ update of child sobject trigger and toggled by the parent sobject trigger).

You should consider additional scenarios here. For example, you have 5 existing child records and the user updates the % of Total value on the records with sequence # 3 & 5. Now, the recalculation should happen on records with sequence # 3, 4 & 5. While the Cumulative % of Total should be updated on all 3 records (seq # 3, 4 & 5), the % of Total should be updated only on 1 record (seq # 4). Slightly different additional scenarios can also be worked out based on this example.