Hi all,

I am getting the error: 

Error:Apex trigger handleReservationChange caused an unexpected exception, contact your administrator: handleReservationChange: System.LimitException: Too many SOQL queries: 101

I know that it's wrong to have the SOQL queries inside the for loops, but can't understand how to perform what I need otherwise. Could someone please post a code sample of how I could take the SOQL queries outside of the loops?

private static void assignPricesToReservation (ReservationRoomAssociation__c assoc)
    {
    	Double todayRate = 0;
		Date currentDay;
		
		System.debug('adding assoc ' + assoc);
		
		// Initialize price
		assoc.Price__c = 0;
		
          
        // Get room type                            
		Room_Type__c rt = [SELECT Name,Number_of_Rooms__c, Default_Rate__c, Id
		 				   FROM Room_Type__c 
		 				   WHERE Id = :assoc.Room_Type__c LIMIT 1];
		System.debug('found roomtype ' + rt);		 
		String roomType = rt.Name;
		System.debug('Room type is ' + roomType);
		
		// Get reservation
		Reservation__c resa = [SELECT Total_Nights__c, Check_In__c, Check_Out__c, Total_Price__c
		 					   FROM Reservation__c 
		 					   WHERE Id = :assoc.Rooms__c LIMIT 1];
	        
        // Add price per night    
		for (Integer i = 0; i < resa.Total_Nights__c; i++)
        {
            currentDay = resa.Check_In__c.addDays(i);          
            System.debug('Date: ' + currentDay);
           
            // Check for availability for the night
            try 
            {      
                Availability__c avail = [SELECT Name, Rooms_Available__c,Room_Type__c 
                                         FROM Availability__c 
                                         WHERE Date__c = :currentDay AND Room_Type__r.Name = :roomType];

                // Yes, there is availability
                if (avail.Rooms_Available__c <= 0)
                {
                	throw new reservationException('No availablity for the selected dates');
                }		
                else
                {
                	// Remove one room from the total available rooms of this date / room-type match
                	avail.Rooms_Available__c -= 1;
                	update avail;
                }	    
		    }
		    catch (Exception e) 
			{
				// No availability record, create one
			    System.debug('no availability record, creating a new one with max rooms ' + rt.Number_of_Rooms__c);
			    Availability__c avail = new Availability__c ();
			    avail.Date__c = currentDay;
			    avail.Rooms_Available__c = rt.Number_of_Rooms__c - 1;
			    avail.Total_Rooms__c = rt.Number_of_Rooms__c;
			    avail.Room_Type__c = rt.Id;
			    insert avail;
			    	    			    
			}
			
			
			try 
            {
			    // Is there a specific rate for this day?
			    Date_Rate__c dateRate = [SELECT Price__c
			    						 FROM Date_Rate__c
			    						 WHERE Date__c = :currentDay
			    						 AND Room_Type__r.Name = :roomType
			    						 ];
				 if (dateRate != null)
				 {
				 	todayRate = dateRate.Price__c;
					System.debug('got date-specific rate for ' + currentDay + ' rt: ' + roomType + ' rate is: ' + todayRate);			 
				 }
			}
			catch (Exception e)
			{
			 	todayRate = rt.Default_Rate__c;    
				System.debug('couldn\'t find specific rate for ' + currentDay + ' using default rate: ' + todayRate);	 	
			}
			
	        // Add the rate for this date and room type   
	        System.debug('adding rate to the total price: ' + todayRate);   
	        assoc.Price__c += todayRate;
	          		
	        if (resa.Total_Price__c != null)
	        {
	        	resa.Total_Price__c += todayRate; 
	        }
	        else
	        {
	        	resa.Total_Price__c = todayRate;
	        } 
		            
        }
	    System.debug('New reservation total price is ' + resa.Total_Price__c);   
	    update resa;   	
    }
    
	private static void calculateReservationPrice (Reservation__c r)
	{
		try 
		{
			Reservation__c resa = [SELECT Total_Price__c FROM Reservation__c WHERE Id = :r.Id];
			resa.Total_Price__c = 0;
			update resa;
		}
		catch (Exception e)
		{
			System.debug('couldn\'t set reservation price to 0');
		}
		
		System.debug('calculating new reservation price');
				
		// Get all room associations for this resa
		List <ReservationRoomAssociation__c> assoc = [SELECT a.Id, a.Room_Type__c, a.Rooms__c
												      FROM ReservationRoomAssociation__c a
												      WHERE a.Rooms__c = :r.Id];
		
		System.debug('updating pricing for ' + r.Name);
		
		for (ReservationRoomAssociation__c a : assoc)
		{
			System.debug('Assigning price for association ' + a);
			assignPricesToReservation(a);
		}
	}
	public static void handleReservationChange(List <Reservation__c> oldResas, 
											   List <Reservation__c> newResas)
    {
    	System.debug('handleReservationChange');
    	
    	
    	Reservation__c oldResa;
    	// Check if reservation changed in length
    	for (Reservation__c r : newResas)
    	{
    		oldResa = null;
    		
    		// get the corresponding old resa
    		System.debug('in reservation iteration loop');
 
    		for (Reservation__c oldR : oldResas)
    		{
    			if (oldR.Id == r.Id)
    			{
    				oldResa = oldR;
    				break;
    			}
    		}
    		if (oldResa == null)
    		{
    			throw new reservationException('can\'t find old reservations');
    		}
    		
    		System.debug('old nights: ' + oldResa.Total_Nights__c + ' new nights: ' + r.Total_Nights__c);
    		
    		if (oldResa.Total_Nights__c != r.Total_Nights__c)
    		{
    			System.debug('calling caluclateReservationPrice for' + r);
    			calculateReservationPrice(r);
    		}
    	}
    	
    	
    }
    

Thanks!