+ Start a Discussion
syricsyric 

Trigger Bulkify Help - Hitting limits with SOQL query and For Loop.

I need help with a trigger I wrote.  It works fine with one record updates but it hits the govenor limits when a batch is procesesd.  I understand that it is because I have the SOQL query in a for loop which is causing the query to fire multiple times when a batch is processed.  The issue is I am not sure how to write it where the Query is outside the loop and the trigger will still work and how to "Bulkify" the portion where I am updating BillingCity and BillingZip to handle the mutiple records in the query.  I'm a beginner/learning and any help would be appreciated.

 

trigger triggerAccountCityState on Account (before insert, before update) {

// IF THE ACCOUNT RECORD BEING INSERTED/UPDATED HAS A BILLINGPOSTALCODE AND DOES NOT HAVE A BILLINGCOUNTRY OF "FOR", EXECUTE TRIGGER
    for(Account acct:Trigger.new){
        if(acct.BillingPostalCode != null && acct.BillingCountry != 'FOR'){     
            string zipcode = acct.BillingPostalCode;
            string zipcity;
            string zipstate;
            
// QUERY THE CUSTOM sOBJECT "ZIP CODE" TO GET THE CITY AND STATE FOR THE ZIP CODE BEING INSERTED/UPDATED
            List <Zip_Code__c> zipList = [select Name, City__c, State__c from Zip_Code__c where Name = :zipcode];

// REPLACE THE EXISTING CITY AND STATE WITH THE DATA RETURNED FROM THE QUERY
                for(Zip_Code__c z : zipList){
                zipcity = z.City__c;
                zipstate = z.State__c;
                }
            acct.BillingCity = zipcity;
            acct.BillingState = zipstate;

// IF NOTHING WAS RETURNED/UDPATED GIVE THE INVALID ZIP CODE ERROR
            for (Integer i = 0; i < Trigger.new.size(); i++) {
                if (Trigger.new[i].BillingCity == null){
                    Trigger.new[i].addError('The Zip Code you have entered is not valid.');
                }
            }
           }

    }

}
Best Answer chosen by Admin (Salesforce Developers) 
PrakashbPrakashb

Set<String> zip = new Set<String>();

 

for(Account acc : Trigger.New){

if(acct.BillingPostalCode != null && acct.BillingCountry != 'FOR'){

zip.add(acct.BillingPostalCode);

}

}

 

List <Zip_Code__c> zipList = new List<>([select Name, City__c, State__c from Zip_Code__c where Name IN :zipcode]);

 

Map<String,Zip_code__c> zipmap = new Map<String,Zip_Code__c>();

for(Zip_Code__c z : zipList){

zipmap.put(z.Name,z);

}

 

for(Account acct : Trigger.New){

if(acct.BillingPostalCode != null && acct.BillingCountry != 'FOR'){

 acct.BillingCity = zipmap.get(acct.BillingPostalCode).City__c;
            acct.BillingState = zipmap.get(acct.BillingPostalCode).State__c;

}

}

 

 

 

 

 

All Answers

PrakashbPrakashb

Set<String> zip = new Set<String>();

 

for(Account acc : Trigger.New){

if(acct.BillingPostalCode != null && acct.BillingCountry != 'FOR'){

zip.add(acct.BillingPostalCode);

}

}

 

List <Zip_Code__c> zipList = new List<>([select Name, City__c, State__c from Zip_Code__c where Name IN :zipcode]);

 

Map<String,Zip_code__c> zipmap = new Map<String,Zip_Code__c>();

for(Zip_Code__c z : zipList){

zipmap.put(z.Name,z);

}

 

for(Account acct : Trigger.New){

if(acct.BillingPostalCode != null && acct.BillingCountry != 'FOR'){

 acct.BillingCity = zipmap.get(acct.BillingPostalCode).City__c;
            acct.BillingState = zipmap.get(acct.BillingPostalCode).State__c;

}

}

 

 

 

 

 

This was selected as the best answer
syricsyric

Thank you.  Your solution helped a lot.  To help me learn about what you did, could you explain what is happening here?

Map<String,Zip_code__c> zipmap = new Map<String,Zip_Code__c>();
for(Zip_Code__c z : zipList){
zipmap.put(z.Name,z);
}

 In particular, how does the trigger update the correct city/state when Zipmap contains multiple records from the query when a batch is processed.

 

 

Also I would like it to post an understable error to the user if an incorrect zip code was entered, similar to my original code.  If the query returns nothing from the zip code entered or if BillingCity or BillingState have null values, then an incorrect zipcode was entered.  I tried this:

for(Account acct : Trigger.New){
if( zipmap.get(acct.BillingPostalCode).City__c == null){
	acct.addError('The Zip Code you have entered is not valid.');
} else if(acct.BillingPostalCode != null && acct.BillingCountry != 'FOR'){

 acct.BillingCity = zipmap.get(acct.BillingPostalCode).City__c;
            acct.BillingState = zipmap.get(acct.BillingPostalCode).State__c;

}

 but I still get the same Null exception error in the UI like I did without it: (Apex trigger triggerAccountCityState caused an unexpected exception, contact your administrator: triggerAccountCityState: execution of BeforeUpdate caused by: System.NullPointerException: Attempt to de-reference a null object: Trigger.triggerAccountCityState: line 61, column).

 

Is there a way to prompt the custom "Zip Code is invalid" error without causing the null pointer exception?

 

syricsyric

After rereading the developers guide's portion on Maps and drawing some stuff out on paper, I'm pretty sure I understand the logic now.

 

I also believe I figured out the solution to my nullpointerexception error / custom error issue.  I used the containskey method to see if the record was in the map before referencing it in the statement:

for(Account acct : Trigger.New){
Boolean contains = zipmap.containsKey(acct.BillingPostalCode);
if( contains == False && acct.BillingPostalCode != null && acct.BillingCountry != 'FOR'){
	acct.addError('The Zip Code you have entered is not valid.');
} else if(acct.BillingPostalCode != null && acct.BillingCountry != 'FOR'){
 			acct.BillingCity = zipmap.get(acct.BillingPostalCode).City__c;
            acct.BillingState = zipmap.get(acct.BillingPostalCode).State__c;

 If anyone has any thoughts or other suggestions I would be curious to hear them.  Otherwise, Thank you Prakashb for the help.