+ Start a Discussion
whateverwhatever 

Bulk Trigger with multiple where statements

I'm working on going through our triggers and ensuring they will preform with a bulk load. There is a trigger that I'm lost on how to remove the select statement from the loop. The purpose of the trigger is to ensure 3 fields are unique in the database.

 

trigger PROP_LotBlockPlanIsUnique on Property__c (before insert, before update)

{

 

    for (Property__c currentProperty : Trigger.new)
    {

 

    for (Property__c currentProperty : Trigger.new)    

    {

 

list <Property__c> properties =

                [select id, name from Property__c where

                 Block_Number__c =: currentProperty.Block_Number__c and

                 Plan_Number__c =: currentProperty.Plan_Number__c and

                 Lot_Number__c =: currentProperty.Lot_Number__c and

                 id <>: currentProperty.id limit 1];

//If we have any properties with this LOT BLOCK PLAN - notify user.             

if (properties.size() <> 0 )

{

//Error

string errorMessage = 'Property Lot Block Plan is in use for property: ';

 

currentProperty.addError(errorMessage + properties[0].name);

currentProperty.Lot_Number__c.addError(errorMessage + properties[0].name);

}

     }

}

 

 

How could I move that query outside of the loop? I think my only option is to create a new field which concatenates the three fields together and use that in a query.

ahab1372ahab1372

First of all, if your fields are text or number fields, you can make them unique in the Field set up. That would be the easiest and does the same what your trigger does.

 

If not, think about this:

1. create three lists, one for each field. If the fields are of the same type, one list should suffice.

2. create three Maps, one for each of the fields, like Map<string (or whatever the field type is),Property__c>

3. Loop through your trigger records and add these field values of each record to the list(s).

3. Query all Property__c were the field values are in the list(s), and add them to the Map

4. Loop through the trigger again, and compare the field value of each record with the Map. If it exists in the Map, you can add the error.

 

This migh give you some ideas:

apex dedup cookbook

 

If I find time, I will add some code/pseudo code later