Apex Code Development You need to sign in to do that
Don't have an account?
krishnag too many SOQL queries 21
hi ,
i wrote a trigger and it was working fine and i made small change in the logic and it throwing an exception saying too many SOQL queries 21.
BUt my trigger has only 6 SOQL queries.I am posting my code please anybody can find the error.
trigger UpdateRelationShipOnAccount on Account (before update) {
//start of condition 1
if(Trigger.new[0].Company_Relationship__c!='Broker' && Trigger.new[0].Company_Relationship__c!='Employee'){
System.debug('In if loop');
list<Policy__c> NonActivePolicy1 = [select Id,Policy_Status__c from Policy__c where
Company_Name__c =:Trigger.new[0].Id limit 1];
// start of condition 2
if(!NonActivePolicy1.isEmpty()){
Trigger.new[0].Company_Relationship__c = 'Customer';
list<Policy__c> ActivePolicy = [select Id,Policy_Status__c from Policy__c where Policy_Status__c ='Active'
and Company_Name__c =:Trigger.new[0].Id limit 1];
//Start of condition 3
if(!ActivePolicy.isEmpty()){
Trigger.new[0].Relationship_Type__c = 'Current';
}
//END of if 3
//Start of condition4 1
else{
Date temp = System.today()+180;
list<Policy__c> NonActivePolicy = [select Id,Policy_Status__c,Policy_Expiration_Date__c from Policy__c where
Company_Name__c =:Trigger.new[0].Id
and Policy_Expiration_Date__c >:temp limit 1];
if(!NonActivePolicy.isEmpty()){
Trigger.new[0].Relationship_Type__c = 'Current';
}
else{
list<Submission__c> subBondType = [SELECT Id,Submission_Status__c,Policy_Status__c FROM Submission__c where
Account_Name__c =:Trigger.new[0].Id and Submission_Status__c ='Bound' limit 1];
//start of condition7 5
if(!subBondType.isEmpty()){
Trigger.new[0].Company_Relationship__c = 'Customer';
if(subBondType[0].Policy_Status__c == 'Active')
{
Trigger.new[0].Relationship_Type__c = 'Current';
}
else
{
Trigger.new[0].Relationship_Type__c = 'Former';
}}
else{
Trigger.new[0].Relationship_Type__c ='Former';
}
//end of if 5
// Start of condition8 3
} }
//End of else 1
}
//end of if 2
//Start of condition5 2
else{
list<Submission__c> subType = [SELECT Id,Submission_Status__c,Policy_Status__c FROM Submission__c where
Account_Name__c =:Trigger.new[0].Id limit 1];
//start of condition6 4
if(!subType.isEmpty()){
list<Submission__c> subBondType = [SELECT Id,Submission_Status__c,Policy_Status__c FROM Submission__c where
Account_Name__c =:Trigger.new[0].Id and Submission_Status__c ='Bound' limit 1];
//start of condition7 5
if(!subBondType.isEmpty()){
Trigger.new[0].Company_Relationship__c = 'Customer';
if(subBondType[0].Policy_Status__c == 'Active')
Trigger.new[0].Relationship_Type__c = 'Current';
else
Trigger.new[0].Relationship_Type__c = 'Former';
}
//end of if 5
// Start of condition8 3
else {
Trigger.new[0].Company_Relationship__c = 'Prospect';
Trigger.new[0].Relationship_Type__c = 'Activity';
}
//END of else 3
}
//end of if 4
// START of condition9 4
else {
System.debug('In last if loop');
if(Trigger.new[0].Direct_Markets_Customer__c==True || Trigger.new[0].Z_Programs_Customer__c ==True)
{
Trigger.new[0].Company_Relationship__c = 'Programs';
}
else{
Trigger.new[0].Company_Relationship__c = 'Prospect';
Trigger.new[0].Relationship_Type__c = 'No Activity';
}
}
// End of else 4
}
//END of else 2
}
//END of if 1
}
its throwing the error at the highlighted part of the trigger.
Not that I can see, though as an FYI, the code won't work in bulk updates...
The 21 queries issues is usually a context issue - this trigger might only have 6 queries, but what other triggers etc does this in turn fire on other objects (e.g is there another before/after update trigger somewhere) - the 20 limit covers all the code executed by this update, not just the code in this trigger...
I would test it via the system console by executing a simple update in the console that will trigger this code, and you'll then have access to the debug log - it should give you a better insight into what else is being executed when you update the record
yaa i know i need to make to work on bulk what can i do to make it work on bulk data.
I think the main thing you'll need to is query the policy data upfront (loop through the trigger, place the Account Ids in a set, then query for the Policy Object for all records that match those Ids and the criteria in your selects. You then put those records into a map to link AccountId to Policy data so you can loop back through the trigger, and reference the lists from the map rather than query each time...
If your not sure what I mean, do a search for some other posts I have done on the topic...