How can I move my select statement from my for loop (trigger)

Question

Hi i have a trigger in my custom object and it hit governor limit due to select statment in my for loop. now i would like to move my statement out. can anyone help? here is my trigger:

trigger CalculateMorgage on Property__c (before insert) 
{
    for(Property__c pp: Trigger.new)
    {       
        List <House_Type__c> ht = [SELECT ID, Property_Type__c  FROM House_Type__c WHERE Range__c =: pp.range__c and Quantity__c =: pp.p_quantity__c];

        if(!ht.isempty())
        {
            for(House_Type__c htt : ht)
            {
                pp.type__c = htt.Property_Type__c ;
            }        
        }
    }
}

in my House_Type__c object it is something like this:

Range__c    Quantity__c     Property_Type__c
R_10            10              AC01
R_10            50              AC02
R_10            500             AC03

so if my quantity is 25 it will return AC02 & AC03 but i need only AC02

Answer 1

See below the bulkified version of your trigger. Not sure if it's going to work as I typed it on the fly and never compiled but you can fix it up and get the idea based on my comments:

trigger CalculateMorgage on Property__c (before insert) 
{
    Set <String> ranges = new Set <String> (); // Set of all ranges from every property in this trigger
    Set <Integer> quantities = new Set <Integer> (); // Set of all quantities from every property in this trigger
    Map <String, Property__c> rangeQuantityProperty = new Map <String, Property__c> (); // Map of concatenated string of range + quantity for the property as a key, and the actual property record as a value

    for(Property__c pp : Trigger.new)
    {
        // For each property in this trigger populate the sets of ranges and quantities and also map the property record to it's Range + Quantity string
        ranges.add(pp.Range__c);
        quantities.add(pp.P_Quantity__c);
        rangeQuantityProperty.put(pp.Range__c + pp.P_Quantity__c, pp);
    }

    // Get all house types in the system that have range that is in the set of ranges above and quantity that is in the set of quantities above
    for (House_Type__c houseType : [SELECT ID, Property_Type__c, Range__c, Quantity__c FROM House_Type__c WHERE Range__c IN :ranges and Quantity__c IN :quantities])
    {
        // Generate concatenated string for the house type range + quantity
        String rangeQuantity = houseType.Range__c + houseType.Quantity__c;

        // Check if we have this range + quantity in the map as a key (if there is a matching property record with the same values)
        if (rangeQuantityProperty.get(rangeQuantity) != null)
        {
            // if there is, then get that record from the map and set the Type__c to this current house type property type
            rangeQuantityProperty.get(rangeQuantity).Type__c = houseType.Property_Type__c;
        }
    }
}