Hello.

I've attemped the following logic numerous times (as a trigger, trigger calling apex, batchable, @future) and every attempt I've run into governor limits. I'm not sure how to achieve what I'm looking to do.

Basically, I have a custom object called Gift, that contains a lookup to Account. The Account object has a string field called TAGS_Applic_Codes that contains a 5-character unique identifier to a legacy system. In our legacy system, though, it's possible for a single account to have multiple codes (different addresses, for example, will each have their own code, and thus be a separate Account record in Salesforce). As such, the Account object also contains a string field called Related_TAGS_App_Codes that contains a comma delimited listing of all of the TAGS applic codes that should be treated together.

The Gift object contains a currency field called Total_Cost, and the Account object contains a field called X2010_Gift_Expenditure_Total. What I'm looking to accomplish is that whenever a gift record is created, I want to calculate the total of all of the gifts for that particular Account AND any related accounts through that additional field, and store them in the X2010_Gift_Expenditure_Total field on the Account object (and related records).

Below is a semi-functional Trigger version of my code, so you can see my thought process. In this particular version, I'm getting a "too many query rows" error, but my governor errors have ranged from that, to "too many SOQL queries: 21", to limits on batchables and @future calls.

trigger trigUpdateCompanyGiftTotal on Gift__c (after insert, after update, after delete) {


    List<Id> changedAccountIds = new List<Id>();
    List<Account> changedAccounts = new List<Account>();
    List<Account> allAccounts = new List<Account>();
    List<Gift__c> allGifts = new List<Gift__c>();
    Map<Id,Account> linkedAccounts = new Map<Id,Account>();

    // Store all accounts and gifts to avoid governor limits
    for (Account acc: [SELECT id, name FROM account]) {
        allAccounts.add(acc);
    }
    allGifts = [SELECT Id FROM Gift__c];


    //Loop on each changed gift entry. For deletes, need trigger.old
    if (Trigger.isDelete) {
        for(Gift__c g : Trigger.old){
            if (g.Company_Firm__c <> Null) {
                changedAccountIds.add(g.Company_Firm__c);    
            }
        }
    } else {
        for(Gift__c g : Trigger.new){
            changedAccountIds.add(g.Company_Firm__c);
        }
    }


    // In triggers, lookup fields do not contain references to the entire object... they only contain the Id.
    // So, need to get the actual changed Account objects from the stored IDs.
    for (Id changedId: changedAccountIds) {
        changedAccounts = [SELECT Id,Related_TAGS_App_Codes__c FROM Account WHERE Id=:changedId];
    }

    for (Account a: changedAccounts) {
        //Determine if any related accounts by a nested loop over the full account list
        if (a.Related_TAGS_App_Codes__c <> null && a.Related_TAGS_App_Codes__c.length() > 0) {
            for (Account accLinkCheck: allAccounts) {
                if (a.Related_TAGS_App_Codes__c.contains(accLinkCheck.Tags_Applic__c)) {
                    linkedAccounts.put(a.Id,accLinkCheck);
                }
            }
        } else {
            linkedAccounts.put(a.Id,a);
        }
    }


    // Loop on all gifts, adding the totals and storing it back in the account and any related accounts
    Decimal totalGift2009 = 0;
    Decimal totalGift2010 = 0;

    for (Gift__c g: allGifts) {
        if (linkedAccounts.containsKey(g.Company_Firm__c)) {
            if (g.Date__c.year() == 2009) {
                totalGift2009 = totalGift2009 + g.Total_Cost__c;
            }
            if (g.Date__c.year() == 2010) {
                totalGift2010 = totalGift2010 + g.Total_Cost__c;
            }
        }
    }


    // Update the total for each account
    for (Account acc: linkedAccounts.values()) {
        acc.X2009_Gift_Expenditure_Total__c = totalGift2009;
        acc.X2010_Gift_Expenditure_Total__c = totalGift2010;
        update acc;
    }

}

My last resort would be to eliminate the trigger portion of this and just have a batch process that runs the update at set intervals, but I'd really prefer to avoid that and have it update when a Gift record is added/changed. I just don't see a way to do it without hitting some sort of governor limit.

Any advice is greatly appreciated. Thank you.

-Greg