Safe Rollup Aggregation: Why You Must Initialize to Zero

Custom rollup logic in Apex triggers is a common pattern for aggregating child record values onto a parent. The implementation seems straightforward -- query aggregates and update parents -- but there is a subtle and dangerous gap: what happens when all child records are deleted?

If you only update parents that appear in your aggregate query results, parents whose children have all been deleted will retain their old, stale rollup values. The fix is a simple but critical step: initialize all affected parent rollup fields to zero before applying the aggregated values.

The Problem: Stale Values After Full Deletion

Consider an Order that rolls up Total_Shipped_Quantity__c from its Order Items:

// UNSAFE: Does not handle the "all children deleted" case
public static void rollupShippedQuantity(Set<Id> orderIds) {
    List<AggregateResult> results = [
        SELECT OrderId__c orderId, SUM(Shipped_Quantity__c) totalShipped
        FROM OrderItem__c
        WHERE OrderId__c IN :orderIds
        GROUP BY OrderId__c
    ];

    List<Order__c> ordersToUpdate = new List<Order__c>();
    for (AggregateResult ar : results) {
        ordersToUpdate.add(new Order__c(
            Id = (Id) ar.get('orderId'),
            Total_Shipped_Quantity__c = (Decimal) ar.get('totalShipped')
        ));
    }

    update ordersToUpdate;
}

This works when at least one child record exists. But if the last Order Item is deleted, the aggregate query returns no rows for that Order. The parent never gets updated, and Total_Shipped_Quantity__c keeps its previous value -- a stale number that no longer reflects reality.

The Fix: Pre-Initialize All Parents to Zero

Before applying aggregate results, set every affected parent's rollup field to zero. Then overwrite with the actual aggregate values. Parents with no remaining children keep the zero.

public class OrderRollupService {

    public static void rollupShippedQuantity(Set<Id> orderIds) {
        if (orderIds == null || orderIds.isEmpty()) return;

        // Step 1: Initialize ALL affected parents to zero
        Map<Id, Order__c> orderMap = new Map<Id, Order__c>();
        for (Id orderId : orderIds) {
            orderMap.put(orderId, new Order__c(
                Id = orderId,
                Total_Shipped_Quantity__c = 0,
                Total_Line_Count__c = 0
            ));
        }

        // Step 2: Run a single aggregate query
        List<AggregateResult> results = [
            SELECT OrderId__c orderId,
                   SUM(Shipped_Quantity__c) totalShipped,
                   COUNT(Id) lineCount
            FROM OrderItem__c
            WHERE OrderId__c IN :orderIds
            GROUP BY OrderId__c
        ];

        // Step 3: Overwrite with actual values (only parents that have children)
        for (AggregateResult ar : results) {
            Id orderId = (Id) ar.get('orderId');
            Order__c ord = orderMap.get(orderId);
            ord.Total_Shipped_Quantity__c = (Decimal) ar.get('totalShipped');
            ord.Total_Line_Count__c = (Integer) ar.get('lineCount');
        }

        // Step 4: Update all parents -- those with children get real values,
        // those without children get zero
        update orderMap.values();
    }
}

Why a Map-Based Pattern

Using a Map<Id, SObject> serves two purposes:

1. Deduplication -- If the same parent ID appears multiple times in the trigger context (e.g., bulk operations), the map naturally deduplicates.
2. Single update statement -- Both zeroed-out parents and parents with aggregated values are in the same collection, requiring only one DML operation.

Handling All Trigger Events

Rollups must recalculate on insert, update, and delete of child records. Each event produces a different set of parent IDs to process:

trigger OrderItemTrigger on OrderItem__c (after insert, after update, after delete, after undelete) {
    Set<Id> affectedOrderIds = new Set<Id>();

    if (Trigger.isInsert || Trigger.isUndelete) {
        for (OrderItem__c item : Trigger.new) {
            affectedOrderIds.add(item.OrderId__c);
        }
    }

    if (Trigger.isDelete) {
        for (OrderItem__c item : Trigger.old) {
            affectedOrderIds.add(item.OrderId__c);
        }
    }

    if (Trigger.isUpdate) {
        for (OrderItem__c item : Trigger.new) {
            OrderItem__c oldItem = Trigger.oldMap.get(item.Id);
            // Recalculate if quantity changed or parent reparented
            if (item.Shipped_Quantity__c != oldItem.Shipped_Quantity__c
                || item.OrderId__c != oldItem.OrderId__c) {
                affectedOrderIds.add(item.OrderId__c);
                affectedOrderIds.add(oldItem.OrderId__c); // old parent too
            }
        }
    }

    affectedOrderIds.remove(null);

    if (!affectedOrderIds.isEmpty()) {
        OrderRollupService.rollupShippedQuantity(affectedOrderIds);
    }
}

Note the affectedOrderIds.remove(null) call -- this guards against child records that have a null parent lookup, which would otherwise cause a query error.

Efficiency: Single Aggregate Query

A common anti-pattern is querying each parent's children individually:

// INEFFICIENT: One query per parent
for (Id orderId : orderIds) {
    List<OrderItem__c> items = [SELECT Shipped_Quantity__c FROM OrderItem__c WHERE OrderId__c = :orderId];
    // ...
}

This hits governor limits quickly. The aggregate query pattern above uses a single SOQL query regardless of how many parents are affected, making it safe for bulk operations.

Key Takeaways

• Always initialize parent rollup fields to zero before applying aggregates.
• Without initialization, deleting all children leaves stale values on the parent.
• Use a Map<Id, SObject> to deduplicate and consolidate updates into a single DML call.
• Handle insert, update, delete, and undelete events -- each produces different affected parent IDs.
• On update, track both the old and new parent IDs when the lookup field changes.
• Use aggregate queries (SUM, COUNT) instead of iterating child records individually.