+ Start a Discussion
JitendraJitendra 

Data Uploader gives error too many SOQL in trigger before insert

Hi Friends,

I have one trigger to check the duplicasy of lead.

following  is the code snap which gives the error of TOO MANY SOQL.

 

 

 

if(lead.Email != null){

            leadList = [Select l.LastName,l.id, l.FirstName, l.Email, l.Company From Lead l where l.Email=:lead.Email limit :Limits.getLimitQueryRows()];

            if(!leadList.isEmpty()){

                for(Lead singleLead:leadList){

                if(lead.id != singleLead.id){//DO not compare with itself

                lead.Email.adderror('duplicate lead exist with same email id : '+singleLead.Email+' and Lead name : '+singleLead.FirstName+' '+singleLead.LastName);

                return ;

                }

                }

            } 

        } 

 

 

Can any body help me, that why this error is happening.

I am uploading record from dataloader nearlly 400 records.

 

Thanks,

Jitendra 

 

 

Best Answer chosen by Admin (Salesforce Developers) 
greenstorkgreenstork

It looks like your SOQL is indeed inside a for loop.  For every lead in the trigger set, you're creating a new list (leadList) and running a SOQL.  Instead of running your SOQL for every lead, create a map with email address as the keyset, and check against this map for matches.

 

There is an exact example of what you're trying toi accomplish here:

 

http://www.forcebrain.com/node/19 

All Answers

hisrinuhisrinu

There are two things you can do.

 

1. Reduce the batch size to 20 

 

2. Keep the query outside of for loop by using Sets and Lists

JitendraJitendra

My Query is already outside the loop, as you can see - first i fetch the data into list and then iterate on this.

 

I dont think that first option is feasible, because dataloader is made only for bulk upload.

 

Thanks for your quick reply. can you suggest something else ?

 

Regards,

Jitendra 

greenstorkgreenstork

What is Lead I? Is this some custom object or are you using the standard lead object. 

 

If this is the standard lead object your SOQL should read "FROM Lead WHERE" - just a shot in the dark, see if that helps.  Also, you don't need the l. in front of each lead field in the SOQL.

 

Edit: Sorry, I'm an idiot, I see it looks like you constructed your SOQL using the schema explorer.  Try the SOQL without the l's but otherwise you'll have to post the rest of your code.

Message Edited by greenstork on 06-14-2009 06:37 PM
JitendraJitendra

Hi greenstork ,

 

How removing l will be beneficial ?

i didnt see any documentation of sales force , which describe this method.

Can you please explain how will it work?

 

SuperfellSuperfell
Your code only seems to look at one lead's email, leading me to believe you call this for every lead in the batch, which would cause the error. perhaps you can post the full code of your trigger.
JitendraJitendra

Hi SimonF,

 

Please find the complete code below:

 

trigger NoDuplicateLead on Lead (before insert, before update) { for (Lead lead : Trigger.new) { List<Lead> leadList; if(lead.Email != null){ leadList = [Select l.LastName,l.id, l.FirstName, l.Email, l.Company From Lead l where l.Email=:lead.Email limit :Limits.getLimitQueryRows()]; if(!leadList.isEmpty()){ for(Lead singleLead:leadList){ if(lead.id != singleLead.id){//DO not compare with itself lead.Email.adderror('duplicate lead exist with same email id : '+singleLead.Email+' and Lead name : '+singleLead.FirstName+' '+singleLead.LastName); return ; } } } }

 

 

 

greenstorkgreenstork

It looks like your SOQL is indeed inside a for loop.  For every lead in the trigger set, you're creating a new list (leadList) and running a SOQL.  Instead of running your SOQL for every lead, create a map with email address as the keyset, and check against this map for matches.

 

There is an exact example of what you're trying toi accomplish here:

 

http://www.forcebrain.com/node/19 

This was selected as the best answer