You need to sign in to do that
Don't have an account?
Indira Reddy 1
Help with Trigger error - system.limitexception: too many soql queries: 101
Hello Everyone,
I modified a rollup trigger I found online for our use case. Below trigger rolls up the count of subscriptions on Account. We have around 1.5 million subscriptions and an account can have up to 30,000 subscription records. It works fine when I manually update a child record. However, it's throwing an error,1. When I update "Update_for_Sub_Trigger__c" field using data loader in 10k records batch, it's throwing "system.limitexception: Apex CPU time limit exceeded" 2. When I update fields that are not referenced in the trigger update section of the code (using a dataloader), it's throwing error- "system.limitexception: too many soql queries: 101". I am a novice Apex developer and not able to understand the reason for these errors. Can someone let me know if I am coding it the wrong way? Thank you!
trigger SubscriptionCount on Subscription__c (after insert, after update, after undelete, after delete) {
List <Subscription__C> newSubscriptionList = new List<Subscription__C>();
Set<Id> accountIdsSet = new Set<Id>();
if ( Trigger.isDelete ) {
newSubscriptionList = Trigger.Old;
} else {
newSubscriptionList = Trigger.New;
}
for ( Subscription__C Sub : newSubscriptionList ) {
if ( Trigger.isUpdate ) {
Subscription__C oldSub = (Subscription__C)Trigger.oldMap.get(Sub.Id); // Map<Id, sObject>
if (( oldSub.Account__c != Sub.Account__c )|| ( oldSub.Update_for_Sub_Trigger__c != Sub.Update_for_Sub_Trigger__c)){
accountIdsSet.add(oldSub.Account__c);
accountIdsSet.add(Sub.Account__c);
}
}
else {
if (Sub.Account__c != null ){
accountIdsSet.add(Sub.Account__c);
}
}
List<Account> accountList = [Select Id, Total_Subscriptions__c, (Select Id From Subscriptions__r)
From Account Where Id IN : accountIdsSet];
for (Account acc : accountList) {
List<Subscription__C> relatedSub = acc.Subscriptions__r;
if ( relatedSub != null ){
acc.Total_Subscriptions__c = relatedSub.size();
} else {
acc.Total_Subscriptions__c = 0;
}
}
if(accountList.size() > 0){
update accountList;
}
}
}
I modified a rollup trigger I found online for our use case. Below trigger rolls up the count of subscriptions on Account. We have around 1.5 million subscriptions and an account can have up to 30,000 subscription records. It works fine when I manually update a child record. However, it's throwing an error,1. When I update "Update_for_Sub_Trigger__c" field using data loader in 10k records batch, it's throwing "system.limitexception: Apex CPU time limit exceeded" 2. When I update fields that are not referenced in the trigger update section of the code (using a dataloader), it's throwing error- "system.limitexception: too many soql queries: 101". I am a novice Apex developer and not able to understand the reason for these errors. Can someone let me know if I am coding it the wrong way? Thank you!
trigger SubscriptionCount on Subscription__c (after insert, after update, after undelete, after delete) {
List <Subscription__C> newSubscriptionList = new List<Subscription__C>();
Set<Id> accountIdsSet = new Set<Id>();
if ( Trigger.isDelete ) {
newSubscriptionList = Trigger.Old;
} else {
newSubscriptionList = Trigger.New;
}
for ( Subscription__C Sub : newSubscriptionList ) {
if ( Trigger.isUpdate ) {
Subscription__C oldSub = (Subscription__C)Trigger.oldMap.get(Sub.Id); // Map<Id, sObject>
if (( oldSub.Account__c != Sub.Account__c )|| ( oldSub.Update_for_Sub_Trigger__c != Sub.Update_for_Sub_Trigger__c)){
accountIdsSet.add(oldSub.Account__c);
accountIdsSet.add(Sub.Account__c);
}
}
else {
if (Sub.Account__c != null ){
accountIdsSet.add(Sub.Account__c);
}
}
List<Account> accountList = [Select Id, Total_Subscriptions__c, (Select Id From Subscriptions__r)
From Account Where Id IN : accountIdsSet];
for (Account acc : accountList) {
List<Subscription__C> relatedSub = acc.Subscriptions__r;
if ( relatedSub != null ){
acc.Total_Subscriptions__c = relatedSub.size();
} else {
acc.Total_Subscriptions__c = 0;
}
}
if(accountList.size() > 0){
update accountList;
}
}
}
1. Inactive the Trigger
2. Update using Data Loader
3. Active the trigger