0

I need to "classify" an incoming Record A based on several related fields on records B,C,D,.. etc. Therefore I need a trigger that is triggering (:D) on this new record and check more then 5 lookup relationships for the content of specific fields in the related records B,C,D ...

Following https://developer.salesforce.com/page/Apex_Code_Best_Practices #2 I should not put the query for the related records in the foreach loop on record "A" Also I need to bulkify this, so it could handle the max load of 200 records entering the trigger at once.

What would be the most efficient way to query those related records for each incoming record and classify "A" according to the values in the related records?

  • Hi mlew, can you provide more details on your data model? If Record A looks up to B, C, and D, you should need only one SOQL query to obtain the related values. – David Reed Feb 24 at 13:50
3

In more cases than not, worrying about efficiency is not the way to approach a problem (i.e. premature optimization is the root of all evil).

The thing to worry about here is how to bulkify your operation. For that, it'll be essentially the same approach in this situation as it is in any situation. Iterate over your trigger records (trigger.new), and gather Ids of things you want to query into collections, and then perform the query once using
FROM <SObject name> WHERE Id IN :<collection of Ids>

Rinse and repeat for your other related objects.

Store the results in a Map<Id, [SObject name here]>, and you can iterate over trigger.new again and grab related records from your various maps as you need them.

There is a second way to do this.

Instead of performing one query per related object, if you make a small sacrifice, you can use a single query.

The sacrifice is to use an After Insert trigger instead of a Before Insert trigger. You lose the ability to update your trigger records without DML, but in return your trigger records will have Ids and can be queried.

So, in an After Insert trigger, you can query your base object A using WHERE Id IN :trigger.new, and include, in the SELECT clause, B.Important_Field__c, C.Cant_Forget_This_One__c, D.That_Field__c, etc...

Of course you will need to perform a DML update on these records, but that's a small price to pay I think.

+edit:

Elaborating on one of my comments.

Trigger.new is read-only in after triggers. To be able to update the records taking part in an after trigger, you'd need to clone the record in trigger.new.

e.g.

List<SObject> recordsToUpdate = new List<SObject>();

for(SObject myObj :trigger.new){
    SObject cloneObj = myObj.clone();
    cloneObj.my_field__c = 'new value';
    recordsToUpdate.add(cloneObj);
}

// Instead of update trigger.new ...
update recordsToUpdate;

As for avoiding an infinite loop, you'll need to make sure that you don't update the records in a trigger context variable inside of an update trigger (updating record 'a' causes record 'a' to be updated, which causes record 'a' to be updated, etc...)

You'll also need to make sure you avoid an update trigger from inserting new records that get sent to the update trigger, to create yet more records, etc...

The naive approach to preventing recursion is to set a static boolean variable (declared in an Apex class, not declared in the trigger itself). The step up from that approach is to store a static set of Ids that you've already processed (and check to see if you've already encountered some records, and then remove them, before proceeding to the bulk of your logic).

  • Hi thanks for the answer! I will try to get my head around it tomorrow morning! For the second way of doing this: I won't be able to update Record A doing that approach, can I? – mlew Feb 24 at 18:20
  • @mlew Yes, you can. You may need to clone the record in trigger.new to be able to modify any values (or myList.add(new MyObject__c(Id = idFromTriggerDotNew, Category__c = calculatedCategory));), but performing DML inside of a trigger (even on records involved in trigger.new) is possible. You, of course, need to take care not to get yourself into an infinite loop, but that's usually pretty simple. The recommendation to use a before trigger to update records in the trigger context variable is just that, a recommendation (not an absolute rule). – Derek F Feb 24 at 20:29
  • Okay thanks for the great answer! I will try implementing this as I think that I got my head around it. Also, thanks for the tip with going efficiency second, helped me to focus on providing a solution I can iterate on over time. – mlew Feb 25 at 19:47
  • @mlew You're welcome. One more note regarding efficiency, usually the most constrained resource in the Salesforce platform is SOQL queries. So, if your logic is solid and you make as few queries as possible, that's about 95% of the battle. – Derek F Feb 25 at 20:38
  • in this specific use case could you elaborate on how not to get in an infinite loop and what you mean by cloning the record to be able to modify values seems like I'm stuck there again. – mlew Mar 5 at 18:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.