Apex Code Development You need to sign in to do that
Don't have an account?
Too many SOQL queries but trigger is written in bulk
Below is the code for a trigger on leads. As far as I can tell it is written in bulk and should not have any issues but for some reason we are getting unhandled trigger exceptions for too may SOQL queries, sometimes as high as 80 and I can't figure it out. There is only one SOQL query and it is not in any type of loop.
I can insert 200 record through the data loader with no issues but when we have an external integration insert these leads an the error occurs.
Code:
trigger leadPromotion on Lead (before insert, before update) {
//For all the leads inserted or updated capture the Source Code
Set<String> sourceCodes = new Set<String>();
for(Lead l : Trigger.new){
sourceCodes.add(l.Source_Code__c);
}
//Query all Campaign/Promotions that have the Source Codes on the leads
Map<Id,Campaign> campaignMap = new Map<Id,Campaign>([select Id, Source_Code__c From Campaign where Source_Code__c IN :sourceCodes]);
//Create and populated a Source Code -> CampaignID Map
Map<String,ID> sourceCodeToCampaignMap = new Map<String,ID>();
for(Campaign c : campaignMap.values()){
sourceCodeToCampaignMap.put(c.Source_Code__c,c.Id);
}
//For all of the leads being inserted or updated retrieve the Id of the corresponding Promotion and set it to the Primary Promotion field (lookup)
for(Lead l : Trigger.new){
if(l.Source_Code__c == null){
l.Primary_Promotion__c = null;
}else{
l.Primary_Promotion__c = sourceCodetoCampaignMap.get(l.Source_Code__c);
}
}
}
I can insert 200 record through the data loader with no issues but when we have an external integration insert these leads an the error occurs.
So the trigger looks good and from what I can see the trigger is not the issue.
I guess is the outside integration is causing another trigger to fire that is not built for bulk use.
If you want to change this you can:
//Query all Campaign/Promotions that have the Source Codes on the leads Map<Id,Campaign> campaignMap = new Map<Id,Campaign>([select Id, Source_Code__c From Campaign where Source_Code__c IN :sourceCodes]); //Create and populated a Source Code -> CampaignID Map Map<String,ID> sourceCodeToCampaignMap = new Map<String,ID>(); for(Campaign c : campaignMap.values()){ sourceCodeToCampaignMap.put(c.Source_Code__c,c.Id); } //For all of the leads being inserted or updated retrieve the Id of the corresponding Promotion and set it to the Primary Promotion field (lookup) for(Lead l : Trigger.new){ if(l.Source_Code__c == null){ l.Primary_Promotion__c = null; }else{ l.Primary_Promotion__c = sourceCodetoCampaignMap.get(l.Source_Code__c); } }to this and it should work.
//Query all Campaign/Promotions that have the Source Codes on the leads Map<String,Id> campaignMap = new Map<String,Id>(); //Create and populated a Source Code -> CampaignID Map for(Campaign c :[select Id, Source_Code__c From Campaign where Source_Code__c IN :sourceCodes]){ campaignMap.put(c.Source_Code__c,c.Id); } //For all of the leads being inserted or updated retrieve the Id of the corresponding Promotion and set it to the Primary Promotion field (lookup) for(Lead l : Trigger.new){ if(l.Source_Code__c == null){ l.Primary_Promotion__c = null; }else{ l.Primary_Promotion__c = campaignMap.get(l.Source_Code__c); } }Please post what the outside integration does.
I will try the code change but from what I've read that is the fix for a query that returns too many records, not necessarily to many SOQL calls.
I can't seem to figure out why that SOQL query is executing so many times. Even if 4000 records are inserted it should only execute once.
This trigger also doesn't update any other records that would cause additional triggers to fire.
Thanks for the help.
Message Edited by TehNrd on 05-15-2008 08:44 AM
This is only part of it. It goes all the way up to 50 SOQL queries and it is still climbing as I type this.
Message Edited by TehNrd on 05-15-2008 09:55 AM
Message Edited by TehNrd on 05-15-2008 10:24 AM
And it looks like new Leads are being created and then the trigger is firing on them as well.
Can you post the full trigger?
One strange thing I have noticed is that for the first Id it tries to update, 00Q5000000LHoa3, when I try to look this this up in the UI is says the Data Not Available, yet it seems to be processing this record ok in the trigger.
It looks like it is inserting the leads and trying to update them at the same time. Looking at the first lines of the log you can see it inserts 7 records:
Log *** Beginning leadPromotion on Lead trigger event BeforeInsert for null, null, null, null, null, null, null
I have made the debug log above longer and you can see it repeatedly tries to process 7 Ids over and over again. This eventually completely errors out and the insert is reverted so it is not there when I look it up.
Interesting facts but it still doesn't answer why its looping over and over.
Message Edited by TehNrd on 05-15-2008 10:37 AM