+ Start a Discussion
Indira Reddy 1Indira 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;
        }
       
       
    }
}
 
Dharmesh Jagetia 7Dharmesh Jagetia 7
Just simply follow the steps:
1. Inactive the Trigger
2. Update using Data Loader
3. Active the trigger
Indira Reddy 1Indira Reddy 1
Thank you, Dharmesh! I was wondering if something is wrong with the code. 
 
David Zhu 🔥David Zhu 🔥
There is one major issue in your code. You put soql and dml command in the loop by mistake. You may try the code below.
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;
    }
          
    
}