Aggregate result delete issue in trigger

Question

Requirement : There is rollup (Sum) between opportunity and opportunitylineitem. Insert and update event is working fine but for delete event is not working correct.

I have one opportunity and one opportunitylineitem amount of 25k$ and its sum also showing on opportunity custom field which is Total OLI see below image.

After delete the opportunitylineitem the value of Total OLI on Opportunity should be 0 but its not refelecting its showing last oppotunitylineitem value. see below image

------------------Trigger code--------------------------------

trigger OppProductPrice on OpportunityLineItem (After delete) {

  if(Trigger.isDelete ){
    OpportunityPriceHandler.performDelete(Trigger.old);
  }
}

----------------Handler Code------------------------

public class OpportunityPriceHandler{

public set<Id> OppId = new set<Id>();

public static void performDelete( LIst<OpportunityLineItem>oldValue ){
 if( oldValue.size() > 0 ){

    set<Id> OppId = new set<Id>();
     for( OpportunityLineItem item : oldValue ){
        OppId.add( item.opportunityId );
    }

    List<Opportunity> opList = new List<Opportunity>();
    List<AggregateResult> result = [Select OpportunityId, Sum(TotalPrice) TP From OpportunityLineItem where OpportunityId IN : OppId and OpportunityId != null Group By OpportunityId];

    for(AggregateResult ar : result){
        id str = (ID)ar.get('OpportunityId');

        if(str != null){
            Opportunity op = new Opportunity(id = str);
            op.lightning_vip__Total_OLI__c = (Decimal)ar.get('TP');
            opList.add(op);
         }

    }
    if(opList != null){
            update opList;
    }
  }
 }
}

Answer 1

This is a common problem for people who roll their own roll-up code.

The crux of the issue is the following line of code:

List<AggregateResult> result = [Select OpportunityId, Sum(TotalPrice) TP From OpportunityLineItem where OpportunityId IN : OppId and OpportunityId != null Group By OpportunityId];

You're querying OpportunityLineItem (OLI for short) based on the Opportunity Id(s), but if you delete the last OLI for a given Opportunity, then this query will return no result for that Opportunity.

No result = no loop = no update

Fixing this, in this case, is relatively simple. Instead of relying on the loop to generate the Opportunities to update, you need to generate those Opportunities prior to that loop.

The idea is:

• Generate Opportunities before your query on OLI using the SObject constructor (i.e. Opportunity myOpp = new Opportunity(Id = someId);)
• Set your custom "rollup" field value to 0
• Add the Opportunity to a map (keyed on Opportunity Id)
• Perform your query
• Inside your loop, get() the relevant Opportunity from the map you populated earlier
• Update your "rollup" field value
• DML update all Opportunities in the map

If an Opportunity has at least one OLI related to it, then you'll update the value of your field like normal.

If your Opportunity has no OLIs, then the initial value of 0 remains unchanged, and you'll correctly update the Opportunity to have a 0 value.

Answer 2

Because your aggr result is empty as you have deleted the OLI for that Opp and no OLI so it will not go in the for loop and hence it won't have any value and it will persist the old value. If you try to add 2 OLI and delete 1 it will work. So you need to check if the result is empty make the Total_OLI to zero

Instead of Updating the Opportunity in the aggre for loop create a Map<id,Integer> oppIdCount and iterate on the opList and from the Map check if the value is there then get and set the value. If not then set it as zero.

for(AggregateResult ar : result){
    id str = (ID)ar.get('OpportunityId');

    if(str != null){
        Opportunity op = new Opportunity(id = str);
        op.lightning_vip__Total_OLI__c = (Decimal)ar.get('TP');
        opList.add(op);
     }

}