You need to sign in to do that
Don't have an account?
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
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
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
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
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.
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?
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 ; } } } }
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