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...