+ Start a Discussion
sususudiosususudio 

Using Change Owner button for Leads under List Views causing "too many SOQL queries: 21" error

We just launched a new Apex trigger to prevent duplicate emails from being created. But now when we try to use the "Change Owner" button under Lead List views to move Leads from either an owner to a queue or another user, we get the following error: "Error: Apex trigger LeadDuplicate Trigger caused an unexpected exception. Contact your Administrator. LeadDuplicateTrigger: System LimitException: Too many SOQL queries: 21."

 

 Does anyone know what might be happening here? Thanks.

 

here's the code:

 

trigger LeadDuplicateTrigger on lead (before insert, before update) 

    {   
    for (Lead c : Trigger.new) {
    
    if (c.Email!='' && c.Email!=null && c.Email!='none@none.com') {
            Contact[]  contacts = [select id from Contact where (Email = :c.Email) and IsDeleted=false];
            Lead[]     Leads     = [select id from Lead where Id<> :c.Id and (Email = :c.Email) and IsDeleted=false and IsConverted=false];
        if (contacts.size() > 0 || Leads.size() > 0) 
            {c.Email.addError('NOTICE-RECORD EXISTS: A lead/contact record with email already exists in SalesForce. Please search your database and update the existing record');      }
            }
          }
     }

uptime_andrewuptime_andrew

You should never have a SOQL query inside a for loop, because if you insert/update 20+ leads at once, it will cause an error.

 

The trick is to use the for loop to build a list, then query with the full list.

 

Here is the idea (some of the syntax may be off, I haven't tested it):

 

 

trigger LeadDuplicateTrigger on lead (before insert, before update) {   
	List<String> myEmails = new List<String>();

	Map<String, Integer> emailCheck = new Map<String, Integer>();

    for (Lead c : Trigger.new) {
		if (c.Email!='' && c.Email!=null && c.Email!='none@none.com') {
			myEmails.add(c.Email);
		}
	}


	Contact[]  contacts = [select id, Email from Contact where (Email IN :myEmails) and IsDeleted=false];

	Lead[]     Leads     = [select id, Email from Lead where Id<> :c.Id and (Email IN :myEmails) and IsDeleted=false and IsConverted=false];

	for(Contact c : contacts) { 
		emailCheck.put(c.Email, 1);
	}
	for(Lead c : Leads) { 
		emailCheck.put(c.Email, 1);
	}



    for (Lead c : Trigger.new) {
		if(emailCheck(c.Email) == 1) { 
			c.Email.addError('NOTICE-RECORD EXISTS: A lead/contact record with email already exists in SalesForce. Please search your database and update the existing record'); 
		}
    }
}