I use cookies on this website. By using this site, you agree that I may store and access cookies on your device. Find out more and set your preferences here.

Efficient APEX Triggers when using SOQL

The Problem

Sometimes when working with Salesforce you have no choice but to use an APEX trigger. In many cases a workflow rule will do what you need however there are cases where you need extended functionality. A good example is when the action of inserting, updating or deleting a record requires a field update on a different record.

Writing triggers can appear straight foward at first, you may think you only need to act on a single record at a time. But what about batch actions, such as those from the Data Loader or other APEX functions?

It turns out that if your trigger only works on a single record at a time, a batch update will call that trigger for every record in the batch. This can be catasrophic if you have any SOQL in your trigger since it will be added up and count towards your limit of 200 SOQL calls. So what can you do instead?

As you may know, a trigger provides you with an array of the records in the batch which means you can loop over the records and perform your logic for the record inside the loop. Unfortuneatly you will find that if you perform any SOQL calls inside this loop you will hit the same 200 limit as before. This is where you have to be clever.

The Solution

Your aim when writing any APEX code (or indeed any interaction with Salesforce) should be to run as few SOQL queries as possible. The fewer you run on each indervidual basis, the less likely you will hit the dreaded 200 per transaction cap.

In our trigger problem the aim should be to perform all your SOQL queries OUTSIDE of the batch loop.

In this example I want to incriment the field "Number_of_Interviews__c" on the "Vacancy__c" object every time a child "Interview__c" object is created. "Interview__c" has a lookup to "Vacancy__c".

To be continued...

Featured Blog Entries

Website Admin Framework

I have been working on a new website administration framework which should mean making custom website content editable by users much simpler.

Integers and Commas

I recently discovered a bug in my Salesforce "On Click" Javascript where Integer values were coming back with commas to seperate the thousands.

Efficient APEX Triggers when using SOQL

In this entry I discuss the problems when working with Salesforce APEX triggers with complex SOQL. I then present a possible solution to this by building efficient SOQL queries which work on batch triggers.