Apex Code Development You need to sign in to do that
Don't have an account?
jaden Getting around SOQL limits
Hi,
Get SOQL limits error, I have tried several things and cannot seem to get around, any help greatly appreciated.
Here is the trigger:
trigger ZipAcctChangeShare on Account (after update) {
//Will create shares when zip changes - JJB Blackiron Group 8-2011
AccountShare[] newShares = new AccountShare[0];
set<id> idsn = new set<id>();
for (Account AcctRecs : Trigger.new)
{
idsn.add(AcctRecs.Id);
system.debug('just added if ' +AcctRecs.Id);
}
List<Territory_Zip_Code__c> tc = new List<Territory_Zip_Code__c>();
set<string> zipso = new set<string>();
for (Account AcctRecs : Trigger.old)
{
zipso.add(AcctRecs.BillingPostalCode);
}
For(List< Territory_Zip_Code__c> t: [SELECT id, GroupId__c, name FROM Territory_Zip_Code__c WHERE Name in :zipso])
Tc.addall(t);
list<Account> AcctRecs = [SELECT Id, BillingPostalCode FROM Account WHERE ID in :idsn];
//list< Territory_Zip_Code__c > ZipRecs = [SELECT id, GroupId__c, name FROM Territory_Zip_Code__c WHERE Name in :zipso];
// for (Account AcctIn : Trigger.new) {
for (Account AcctIn :AcctRecs) {
system.debug('the Current ' + AcctIn.BillingPostalCode);
system.debug('the Old zip is ' + Trigger.oldMap.get(Acctin.Id).BillingPostalCode);
if (Trigger.oldMap.get(Acctin.Id).BillingPostalCode != AcctIn.BillingPostalCode) {
//Build list of Territory zip records to get the groups
// List<Territory_Zip_Code__c> ZipRecs =
// [SELECT terr.id, terr.GroupId__c
// FROM Territory_ZIP_Code__c terr
// WHERE name = :Trigger.oldMap.get(Acctin.Id).BillingPostalCode ];
for (Territory_Zip_Code__c tzc: tc) {
if (tzc.name == Trigger.oldMap.get(Acctin.Id).BillingPostalCode)
{
system.debug('the group id is ' + tzc.GroupID__c);
// Find and delete shares
try {
system.debug('The acct id before share read is ' + AcctIn.id );
AccountShare accsh = [Select id, AccountID from AccountShare where AccountID = :AcctIn.id and UserOrGroupId = :tzc.GroupID__c ];
try
{
delete accsh;
}
catch (Exception ex) {
}
}
catch (Exception ex) {
}
}
}
}
}
}Here is the error:
15:16:12.830 (9830246000)|CODE_UNIT_FINISHED|ZipAcctChangeShare on Account trigger event AfterUpdate for [001T000000rsYqi]
15:16:12.832 (9832910000)|FATAL_ERROR|System.LimitException: Too many SOQL queries: 101
Trigger.ZipAcctChangeShare: line 21, column 38
Thanks to all for input.
By getting the SOQL out of the loop this was solved.
All Answers
You need to get teh SQOL out of the for loops.
This may need a bit of checking to ensure all the brackets are right and what you want it checking for is correct, but this should help. Also, do some reading up on 'Bulkify Trigger"
trigger ZipAcctChangeShare on Account (after update) { AccountShare[] newShares = new AccountShare[0]; set<id> idsn = new set<id>(); for (Account AcctRecs : Trigger.new) { idsn.add(AcctRecs.Id); system.debug('just added if ' +AcctRecs.Id); }List<Territory_Zip_Code__c> tc = new List<Territory_Zip_Code__c>(); set<string> zipso = new set<string>(); for (Account AcctRecs : Trigger.old) { zipso.add(AcctRecs.BillingPostalCode); } For(List< Territory_Zip_Code__c> t: [SELECT id, GroupId__c, name FROM Territory_Zip_Code__c WHERE Name in :zipso]) Tc.addall(t); list<Account> AcctRecs = [SELECT Id, BillingPostalCode FROM Account WHERE ID in :idsn]; //list< Territory_Zip_Code__c > ZipRecs = [SELECT id, GroupId__c, name FROM Territory_Zip_Code__c WHERE Name in :zipso]; //List of account share to be deleted List<AccountShare> tbdACCShList = New List<AccountShare>(); //Map of ID to account shares in AccRecs Map<id,AccountShare> mapOfAccountshare = New Map<id,AccountShare>(); //Populate Map for (Account AcctIn :AcctRecs) { mapOfAccountShare.put(AcctIn.id,AcctIn);
} For(Account acc :AccRecs){ If trigger.oldMap.get(Acc.Id).BillingPostalCode != Acc.BillingPostalCode) { for (Territory_Zip_Code__c tzc: tc) { if (tzc.name == Trigger.oldMap.get(Acc.Id).BillingPostalCode) { system.debug('the group id is ' + tzc.GroupID__c); //Check to see if UserOrGroupID Matches, if So, get AccountShare Record from map and add to list to be deleted if(UserOrGroupId == tzc.GroupID__c){ tbdACCShList.add(mapOfAccountShare.get(acc.id)); } /* // Find and delete shares try { AccountShare accsh = [Select id, AccountID from AccountShare where AccountID = :AcctIn.id and UserOrGroupId = :tzc.GroupID__c ]; try { delete accsh; } catch (Exception ex) { } } */ catch (Exception ex) { } //If we have records to be deleted, delete the records If(tbdACCShList.size() > 0) Delete tbdACCShList; } } } } }
Thanks for your help; I appreciate
This is the statement that I am getting the limits error:
For(List< Territory_Zip_Code__c> t: [SELECT id, GroupId__c, name FROM Territory_Zip_Code__c WHERE Name in :zipso])
Tc.addall(t);
That is only one SOQL statement so you should not be getting an error there unless you are creating an recursive loop in trigger updates......
You did have an SOQL statement in a for loop: for (Territory_Zip_Code__c tzc: tc)
When you get the error are you just adding / updating one record or bulk?
Yes, I did have the SOQL in the loop but even mioving that out I am getting an error. I suspect you are correct about this being a recursive issue.
Forgive me I am pretty new to this, how can find out and prevent?
I have a debug log, but it is too big to post, can I email it to you?
I REALLY appreciate your time, Thank you very much
There is existing cookbook recipe for controlling recursive triggers. You can find it here: http://developer.force.com/cookbook/recipe/controlling-recursive-triggers
Basically you'll use a static variable to control the recursion from occuring more than once.
Thanks to all for input.
By getting the SOQL out of the loop this was solved.