+ Start a Discussion
Reppin__505Reppin__505 

Bulk Before Delete Trigger (Too Many Soql Statements)

So i have no idea why this is, and i don't understand it.

 

I have a bulkified trigger that uses maps in order to avoid running into SOQL governor limits. This trigger also uses static class variables for recursion and for limiting queries.

 

What i'm doing is when a bulk operation such as an insert or update is started on the object, Contact object in this case, then the trigger will build the maps of related Accounts on the first trigger, and will use those maps for the rest of the trigger firings.

 

Here is an example of the operation that is working great, but only for After Update & After Insert trigger operations:

 

1.) Check that a static class variable is not true.

2.) If variable is not true build maps.

3.) Set the static class variable to true.

4.) Perform the trigger operations.

 

For the insert/update triggers, the session state is maintained and the static class variable is not reset until after the end of the bulk operation.

 

However for the before delete trigger, it seems there is no session state, and that the session is reset each time a record is deleted. The session is reset, but the governor limits are cumulitive for a bulk delete of records. So with a before delete triggger, even if using maps, the soql query count keeps counting running into the infamous 'Too Many Soql Queries' limit for the deletion of more than 100 records.

 

Any thoughts on how to prevent running into the SOQL limit would be much much appreciated. I wasn't able to find anything on this anywhere.

 

Thank you.

Best Answer chosen by Admin (Salesforce Developers) 
Rahul SharmaRahul Sharma

After looking your code i noticed that query is been made inside a for loop.

that too.

For every contact record you are querying all the Account in database.

So even you if you try to delete 100 records, query would be executed 100 times (since its in a for loop) hitting the governor .


try to move query outside the for loop.

for(Contact oldContact : Trigger.old)
{ 
for(Account a : [Select Id, Primary_Contact__c, Primary_Contact_Email__c from Account Where Id IN (Select AccountId From Contact)])

All Answers

Rahul SharmaRahul Sharma

Hi Reppin__505,

You might be querying inside a for loop in the delete operation.

Could you post your code.

Reppin__505Reppin__505

Here it is. As you can see i'm using a map outside of the loop. But for every delete, the query count accumulates for the before delete triggers. The salesforce documentation doesn't mention anything about this. What is the common practice for handling bulk deletes?

 

trigger triggerIsPrimaryContactDelete on Contact (before delete) 
{
public Map<Id, List<Account>> mapAccount = new Map<Id, List<Account>>();

for(Contact oldContact : Trigger.old)
{
for(Account a : [Select Id, Primary_Contact__c, Primary_Contact_Email__c from Account Where Id IN (Select AccountId From Contact)])
		{
			if(mapAccount.containsKey(a.Id))
			{
				mapAccount.get(a.Id).add(a);   
			}
			else
			{
				mapAccount.put(a.Id, new List<Account>{a});  
			}		
		}
}
  			
	for(Contact oldContact : Trigger.Old)
	{
		try
		{
			
			Account qryAccount;
			try
			{
				for(Account a : mapAccount.get(oldContact.AccountId))
				{
					qryAccount = a;
				}			
			}
			catch(Nullpointerexception n){}	
																
			qryAccount.Primary_Contact__c = null;
			qryAccount.Primary_Contact_Email__c = null;						
			update qryAccount;
		}
		catch(Nullpointerexception n){}
	}
}

 

Rahul SharmaRahul Sharma

After looking your code i noticed that query is been made inside a for loop.

that too.

For every contact record you are querying all the Account in database.

So even you if you try to delete 100 records, query would be executed 100 times (since its in a for loop) hitting the governor .


try to move query outside the for loop.

for(Contact oldContact : Trigger.old)
{ 
for(Account a : [Select Id, Primary_Contact__c, Primary_Contact_Email__c from Account Where Id IN (Select AccountId From Contact)])
This was selected as the best answer