Getting 101 SOQL error when updating bulk records through data loader

Question

I have a trigger which updates the total amount of child record values into parent record fields. It is working fine when I do the single records insertion. I have 50000 child records which are related to 7000 parent records. I have given batch size as 200 from dataloader to update some values of child object and I got issue with my below trigger. The error is showing as System.LimitException: Too many SOQL queries 101.

Apex Trigger:

trigger triggerUpdateAmount on Expense__c (after insert, after update, after delete) {

    List <Id> scratchSheetIds = new List<Id> ();
    List <Scratch_Sheet__c> scratchSheetList = new List<Scratch_Sheet__c>();
    List <AggregateResult> expenses = new List<AggregateResult>();
    List <AggregateResult> expensesFringe = new List<AggregateResult>();
    List <AggregateResult> expensesOther = new List<AggregateResult>();
    List <AggregateResult> expensesOtherTaxable = new List<AggregateResult>();



     if(Trigger.isInsert || Trigger.isUpdate) {
        for(Expense__c req:trigger.new){
            scratchSheetIds.add(req.Scratch_Sheet__c);
        }
     }

     if(Trigger.isDelete) {
        for (Expense__c req : Trigger.old) {
            scratchSheetIds.add(req.Scratch_Sheet__c);
        }
     }

     scratchSheetList = [Select Id, Bill_To_Facility_Total__c,fringe_expense1__c,
                            other_expense1__c,Other_Taxable__c From Scratch_Sheet__c 
                        Where Id In :scratchSheetIds];
      expenses = [Select Scratch_Sheet__c, SUM(Total_Amount__c) 
                From Expense__c Where Scratch_Sheet__c IN: scratchSheetIds
            AND Bill_Client__c = true AND Active__c = true
            AND Total_Amount__c != null
            Group By Scratch_Sheet__c];
    expensesFringe = [Select Scratch_Sheet__c, SUM(Total_Amount__c) 
                From Expense__c Where Scratch_Sheet__c IN: scratchSheetIds
                AND Grouping_Total__c = 'Fringe Expense' AND Active__c = true
                AND Total_Amount__c != null
                Group By Scratch_Sheet__c];

    expensesOther = [Select Scratch_Sheet__c, SUM(Total_Amount__c) 
                From Expense__c Where Scratch_Sheet__c IN: scratchSheetIds
                AND Grouping_Total__c = 'Other Expense' AND Active__c = true
                AND Total_Amount__c != null
                Group By Scratch_Sheet__c];  

    expensesOtherTaxable = [Select Scratch_Sheet__c, SUM(Total_Amount__c) 
                From Expense__c Where Scratch_Sheet__c IN: scratchSheetIds
                AND Grouping_Total__c = 'Other Taxable' AND Active__c = true
                AND Total_Amount__c != null
                Group By Scratch_Sheet__c]; 


    for(Scratch_Sheet__c scr:scratchSheetList){
        scr.Bill_To_Facility_Total__c = 0.00;
        for(AggregateResult ar: expenses){
            if(ar.get('Scratch_Sheet__c') == scr.Id){
                scr.Bill_To_Facility_Total__c = Decimal.ValueOf(String.ValueOf(ar.get('expr0')));
            }
        }
    }
    system.debug('scr....0'+scratchSheetList);


    for(Scratch_Sheet__c scr:scratchSheetList){
        scr.fringe_expense1__c = 0.00;
        for(AggregateResult ar: expensesFringe){
            if(ar.get('Scratch_Sheet__c') == scr.Id){
                //system.assert(false,ar.get('expr0'));
                scr.fringe_expense1__c = Decimal.ValueOf(String.ValueOf(ar.get('expr0')));
            }
        }
    }

     system.debug('scr....2'+scratchSheetList);

    for(Scratch_Sheet__c scr:scratchSheetList){
        scr.other_expense1__c = 0.00;
        for(AggregateResult ar: expensesOther){
            if(ar.get('Scratch_Sheet__c') == scr.Id){
                scr.other_expense1__c = Decimal.ValueOf(String.ValueOf(ar.get('expr0')));
            }
        }
    }

    for(Scratch_Sheet__c scr:scratchSheetList){
        scr.Other_Taxable__c = 0.00;
        for(AggregateResult ar: expensesOtherTaxable){
            if(ar.get('Scratch_Sheet__c') == scr.Id){
                scr.Other_Taxable__c = Decimal.ValueOf(String.ValueOf(ar.get('expr0')));
            }
        }
    } 
    update scratchSheetList;
}

I am sure that I am not doing I havn't writtten any SOQL queries inside the for loop also

Answer 1

Here as an example of the sort of change I think you should make for each case; won't fix the 101 SOQL error but leaves the code cleaner and will significantly reduce the risk of hitting the CPU limit:

Map<Id, Decimal> expenses = new Map<Id, Decimal>();
for (AggregateResult> ar : [
        Select Scratch_Sheet__c, SUM(Total_Amount__c) total
        From Expense__c
        Where Scratch_Sheet__c IN: scratchSheetIds
        AND Bill_Client__c = true
        AND Active__c = true
        AND Total_Amount__c != null
        Group By Scratch_Sheet__c
        ]) {
    expenses.put((Id) ar.get('Scratch_Sheet__c'), (Decimal) ar.get('total'));
}

...

for (Scratch_Sheet__c scr: scratchSheetList) {

    Decimal e = expenses.get(scr.Id);
    scr.Bill_To_Facility_Total__c = e != null ? e : 0.00;

    Decimal ef = expensesFringe.get(scr.Id);
    scr.fringe_expense1__c = ef != null ? ef : 0.00;

    ...
}

This removes the nested loops by using a map.