Everyone,

I have a custom object named budget__c that has a M:D relationship with the account object.  I have multiple accounts that are in a hierarchical relation ship such as:   Corporate->Division->Region->State->Office

The levels in the hierarchy are limited to just the 5 listed.

I want to write a trigger on the budget object to update the total budget on the effected accounts.  So basically, whenever the office budget gets changed, this change is seen at all levels above it.

Here is my poorly written first attempt.  Please note this code has a query in a loop - any advice on how to keep it out of the loop would be great.  Also, this code calculates the amount on the child and parent (i.e. grand parent and great grand parent records don't calculate changes to the grand child record).

trigger UpsertDelBudget on Budget__c (after delete, after insert, after update) {
	/* 
		This trigger will be used to:
		
		1. Update a 'roll up summary' field based on the account
		   hiearchy.  This will allow the division for example to show
		   the budgets of the offices in that division based on hiearchy.
		   
	*/
	
	system.debug('>>>> UpsertDelBudget Trigger Start');
	
	set<Id> AccountIDsInUpdate = new Set<Id>();
	set<Id> ParentAccountIDsInUpdate = new Set<Id>();
	set<Id> GrandParentAccountIDsInUpdate = new Set<Id>();
	
	// Build set of Account ID's in this update
	if (trigger.isInsert || trigger.isUpdate) {
		
		system.debug('>>>> Insert or Update operation');
		
		for(Budget__c B:Trigger.new)
			{
				AccountIDsInUpdate.add(B.Budget_Account__c);
				system.debug('>>>> Adding Account ID: ' + B.Budget_Account__c); 
				
				if (B.Budget_Parent_ID__c != null) {
					AccountIDsInUpdate.add(B.Budget_Parent_ID__c);
					
					ParentAccountIDsInUpdate.add(B.Budget_Parent_ID__c);
				}	// check for null parent id
					
			} // end for loop
			
	} // end if (trigger.isInsert || trigger.isUpdate) {
	
	if (trigger.isDelete) {
		
		system.debug('>>>> Delete operation');
		
		for(Budget__c B:Trigger.old)
			{
				AccountIDsInUpdate.add(B.Budget_Account__c);
				system.debug('>>>> Adding Account ID: ' + B.Budget_Account__c);

				if (B.Budget_Parent_ID__c != null) {
					AccountIDsInUpdate.add(B.Budget_Parent_ID__c);
							
					ParentAccountIDsInUpdate.add(B.Budget_Parent_ID__c);
				}	// check for null parent id	
				
			} // end for loop
	} // end if (trigger.isDelete) {

	// This loop will add grand parent ID's first run
	// then keep going up until no more parents (great grand, grand, etc) are found
	do {	
		system.debug('>>>> In Do Loop');
		// Adding grand parent IDs 
		for(Account A:
					[SELECT ID, Name, ParentID 
					FROM Account 
					WHERE ID IN :ParentAccountIDsInUpdate 
					]) 
		{
			AccountIDsInUpdate.add(A.ParentID);
			system.debug('>>>> Adding ID ' + A.ParentID);
			system.debug('>>>> Adding Name = ' + A.Name);
			GrandParentAccountIDsInUpdate.add(A.ParentID);
		} // end for loop
		if (GrandParentAccountIDsInUpdate.isEmpty() == false) {
			system.debug('>>>> Grandparents have parents - going again!');
			ParentAccountIDsInUpdate.clear();
			ParentAccountIDsInUpdate.addAll(GrandParentAccountIDsInUpdate);
			GrandParentAccountIDsInUpdate.clear();
		} else {
			system.debug('>>>> No more parents - exiting loop!');
			break; // exit the loop
		}
	} while (true); // seemingly infinite loop!
		
    // ---------------------------------------------------------------
	// build a map of Account id's and calculated totals for budgets 
		Map<Id, Double> CurrentAccountTotalBudgetMap = new Map<Id, Double>();

	for(Id A:AccountIDsInUpdate) {	
		
		system.debug('>>> Current ID ' + a);
		
		// Use Group by in SOQL statement to have a single ID with a single sum
		for(AggregateResult d: 
								[
				SELECT SUM(Current_Year_Budget__c) Total
				FROM Account WHERE ParentID = :A OR ID =:A 
								]		
		){
			CurrentAccountTotalBudgetMap.put(a,(Double)d.get('Total'));
			
			//system.debug('>>>> Current Name = ' + d.get('Name'));
			system.debug('>>>> Current Total ' + d.get('Total'));
					
		} // end for loop to find totals
	} // end of loop thru account IDs
			
	// ---------------------------------------------------------------

	system.debug('>>>> Final AccountIDsInUpdate.size ' + AccountIDsInUpdate.size() ) ;

	// Get sum value from map and create list to update
	
		List<Account> AccountsToUpdate = new List<Account>();
	
		// now loop thru the payments
		
		for(Account a: [SELECT Id, Name, Total_Current_Year_Budget__c, Current_Year_Budget__c
						   FROM Account
						   WHERE Id IN :AccountIDsInUpdate]) 
	    {	    	
			System.debug('>>>> ------------ <<<<<');
			System.debug('>>>> Name ' + a.name);
			
			
			// 1D. set the totals on the payments
			Double BudgetSum = CurrentAccountTotalBudgetMap.get(a.Id);
			if (BudgetSum != null) {
				a.Total_Current_Year_Budget__c = BudgetSum;
			} else {
				a.Total_Current_Year_Budget__c = 0;
			} // check for null values
			
			
    		AccountsToUpdate.add(a);
	    	 
	    } // end loop thru payments
		
	// Update the totals in one DML call
	update AccountsToUpdate;	
	
	system.debug('>>>> UpsertDelBudget Trigger END');
	
} // end class

 Any advice in fixing the two above mentioned flaws would be much appricated.