Apex Code Development You need to sign in to do that
Don't have an account?
pjaenicke Bulkify - when to pull default value in Trigger
Hi all,
Getting my feet wet with triggers - trying to "bulkify" an otherwise working trigger using best practices.
I'm using a zip code field on the Account table to query a custom table, and populate a lookup field. In the event the zip code is blank, invalid, or not found in the query, I'd like to populate a "default" record in the lookup.
My choices are
1. hard-code the record id of the "default" record in a variable (and hope that it never changes).
2. query the "default" record exactly one time - up-front in the trigger, before the "for (Account Acct:Trigger.new) {"
3. query the "default" record zero to 'n' times - only when needed.
I'm leaning towards #2. Though I'm still getting "too many SOQL queries" in my testing, with batch sizes > 10 :
trigger setWidgetOwner on Account (before insert, before update) {
// Get the Default Widget
Widget__c DefaultWidget = [
Select Id from Widget__c
where Name = 'Default' Limit 1 // 2nd q: does "Limit 1" help processing at all, if I know there's only one?
];
// Begin the bulk processing...
for (Account Acct:Trigger.new) {
...
Thanks,
Pete
Option 2 is the best, as the ids may change between sandboxes etc. Definitely not option 3, as that behaviour leads to blown trigger limits.
If you are still getting too many soql queries, that implies to me you have some soql inside your for loop - if you post the rest of the code we may be able to offer further advice.
WRT Limit 1, there's nothing in the docs to suggest this is good or bad.
All Answers
Option 2 is the best, as the ids may change between sandboxes etc. Definitely not option 3, as that behaviour leads to blown trigger limits.
If you are still getting too many soql queries, that implies to me you have some soql inside your for loop - if you post the rest of the code we may be able to offer further advice.
WRT Limit 1, there's nothing in the docs to suggest this is good or bad.
Thanks Bob,
Here's the entire trigger definition.. I'm trying to retrieve the associated Widget (lookup) and Owner (as text) based on the Billing Zip. (Account => Zip__c ==> Widget__c ==> User).
Any/all recommendations to help be 'bulkify' this code would be most appreciated. Thanks, Pete.
trigger setWidgetOwner on Account (before insert, before update) {
Widget__c DefaultWidget = [
Select Id from Widget__c
where Name = 'Default' Limit 1
];
for (Account Acct:Trigger.new) {
string BillZip = '0';
boolean Default = True;
//skip if Null
if (Acct.BillingPostalCode != Null) {
//find the position of the dash, (or if it doesn't exist, return the entire string length)
integer Dashpos = (Acct.BillingPostalCode + '-').indexof('-');
//remove zip+four, remove leading zeros:
BillZip = (Acct.BillingPostalCode.substring(0,Dashpos)).replaceAll('^0+(?!$)','');
//find Zip Code in Zip code table:
List<Zip__c> ZipArray = [
select Id, Widget__c from Zip__c
where Name = :BillZip limit 1
];
if (ZipArray.size() > 0) {
//if Zip is found, get Widget owner from Widget table
for (Zip__c ZipCode: ZipArray) {
for (Widget__c MyWidget: [
Select OwnerId from Widget__c
where Id = :ZipCode.Widget__c limit 1
]) {
// If Widget Owner is not a Queue Id, get the user name
If (String.valueof(MyWidget.OwnerId).substring(0,3) != '00G') {
Acct.Widget__c = ZipCode.Widget__c;
// Grab Widget owner name from User table
for (User WidgetOwner: [
Select Name from User
where Id = :MyWidget.OwnerId limit 1
]) {
Acct.Widget_Owner__c = WidgetOwner.Name;
Default = False;
}
}
}
}
}
}
If (Default) {
Acct.Widget_Owner__c = 'Default';
Acct.Widget__c = DefaultWidget.Id;
}
}
}
This is because you have some select statements embedded inside loops in your trigger - these select statements will be exercised each time through the loop, which will blow soql limits quickly.
What you'd normally do in these circumstances is to carry out bulk queries outside the loop, to pull all records that you are interested in into a collection - either a map or list. In order to do this, you may need to build lists of ids to query against etc.