Hi,
Here i am going to tell you about some good practice to avoid "Too many SOQL queries: 21" error on Triggers. I got this approach from one of my good colleague "Prakash Gyamlani" (Thanks for him).

Here is some background what problem i faced:-

In one of my organization i had 7 triggers on Case. Some triggers are "before update", some triggers are on "after update". In some triggers i was also updating Case object itself based on some criteria. So it generally again calls some Triggers ( I have taken care of Recursion, that's not the problem).
After some days when i tried to save one case record i started getting "Too many SOQL queries: 21" error. I search debug logs and found that my queries are going beyond the 20. After some research i found the area which was causing the root problem.

Root Problem:-

There was one "Before Update" trigger on Case which was querying Record Types on top of the trigger like below:-

trigger SendEmailsOnCaseComment on Case (before update) {

Map<id,RecordType>mapRecordTypes = new Map<id,RecordType>([SELECT Id, Name from RecordType WHERE SObjectType = 'Case']);

for(Case cs: Trigger.NEW){
//logic
}
//extra code
}

The problem in the above code is that the query on top will always be executed whenever our control comes in this trigger. Suppose because of 7 triggers, on Case update (and inner updates), this trigger is called 6 times then we have 5 time SOQL queries extra. We only interested to execute this query one time only within the execution of a Trigger Thread.

How to solve this:-

So we should code our trigger in this way that if there are any queries which are general, not dependent on any conditions, then put those queries in a place where they can be executed only once. And Whenever we need the results we get our results always in single execution of Trigger Thread.


Code:-
The solution for above problem is to code like this:-

Make one utility class for getting record types and initialize that (Fire SOQL) in a "Static" block. In this way we will always have that soql fired only once.

public class CaseManagementUtility{
public static Map<id,RecordType> mapRecordTypes = null;
static {
mapRecordTypes = new Map<id,RecordType>([SELECT Id, Name from RecordType WHERE SObjectType = 'Case']);
}

public static Map getRecordTypeMap(){
return mapRecordTypes;
}
}


Now, in our trigger use this code like this:-
trigger SendEmailsOnCaseComment on Case (before update) {

Map<id,RecordType> mapRecordTypes = CaseManagementUtility.getRecordTypeMap();

for(Case cs: Trigger.NEW){
//logic
}

//extra code
}

Now, suppose above trigger is called 10 times (in a single execution), but SOQL query will be fired only once.


Quite simple. Isn't it.

Thanks
Aslam Bari