+ Start a Discussion
Rick_NookIndRick_NookInd 

Formula Fields to Stretch SOQL query governor limit in Apex Triggers

I found a way that apparently works to "stretch" the limit of 20 SOQL queries in a transaction/triggers.  Could I get some expert commentary on whether this technique is reliable?  In other words, is every formula field always calculated, queried, update to date, and available when the before insert/update trigger is run?

 

Basically, instead of doing a SOQL query in a before insert/update trigger, I created a formula field that is supposed to have that exact same data.

 

Here's an example:

 

NEW CODE:

 

Opportunity.Account_Number__c is a formula field with the formula "Account.AccountNumber"

 

trigger Opportunity_Prevent_Corporate_HQ_Customer on Opportunity (before insert, before update) {
 for (Opportunity opp : trigger.new) {
  if (opp.Account_Number__c != null) {
   if (opp.Account_Number__c.startsWith('CORP')) {
    opp.addError('Invalid account on opportunity.  Corporate HQ accounts may not have opportunities, quotes, orders, or invoices against them.');
   }
  }
 }

}

 

OLD CODE:

trigger Opportunity_Prevent_Corporate_HQ_Customer on Opportunity (before insert, before update) {
 List<String> theIds = new List<String>();
 for (Opportunity opp : trigger.new) {
  theIds.add(opp.AccountId);
 }
 Map<Id, String> acc_map = new Map<Id,String>();
 List<Account> acc_list = [SELECT Id,AccountNumber FROM Account WHERE Id IN :theIds]; 
 for (Account acc : acc_list) {
  acc_map.put(acc.Id,acc.AccountNumber); 
 }
 for (Opportunity opp : trigger.new) {
  if (acc_map.containsKey(opp.AccountId)) {
   if (acc_map.get(opp.AccountId) != null) {
    if (acc_map.get(opp.AccountId).startsWith('CORP')) {
     opp.addError('Invalid account on opportunity.  Corporate HQ accounts may not have opportunities, quotes, orders, or invoices against them.');
    }
   }
  }
 }
}

 

 

 

And, before someone says "you shouldn't need to do 20 queries if you coded in properly", allow me to point out that I have 13 separate triggers on Opportunity and it's child objects and that they need access to a dozen different objects in various contexts.  When a batch gets posted that triggers enough of the code of the various triggers, there is just a lot of different data to pull, e.g. Account info, User info, Task info, related custom object info, queries for update, etc.

 

I'm also aware that the example given might be better done with a validation rule, but it was a simplest example I could find that clearly demonstrates the technique I am inquiring about.

ahab1372ahab1372

It is actually recommended to use formula fields, workflow rules with field updates, and validation rules instead of triggers whenever possible. Save the gov limits for the serious stuff, I suppose you have enough of that if you have 13 triggers.

 

To answer your question, yes the formula fields will be up-to-date. Your new trigger will work. See this about order of execution:

http://www.salesforce.com/us/developer/docs/apexcode/index_Left.htm#StartTopic=Content/apex_triggers_order_of_execution.htm

 

But you don't need this trigger, you can and should use a validation rule instead. As I said, save the APEX code for the heavy stuff.

 

One of my Validation rules looks like this:

Account.HQ_Flag__c

 

That's all :-)  HQ-Flag__c is a checkbox which is checked (true) or unchecked (false) by 2 workflow rules. When the Validation rule evaluates to true (checkbox is checked), it will throw an error.

 

 

WesNolte__cWesNolte__c

Hey

 

You can also make any queries static, so that if lists of object records are needed by different triggers, but within the same transaction the information is only queried once e.g.

 

 

public class MyUtil{

private static List<Account> accs;
public static List<Account> myAccs{get {
  if (myAccs == null){
      accs = [SELECT id ... FROM Account];
 }
 return accs;
}

set;

}

 

Wes

 

crop1645crop1645

One last tip -- If you have a trigger on a Detail record that updates a parent record -- and that parent record also has triggers, and ...

 

the parent record has RSF (roll up summary) fields, those values will not be current when the trigger fires.