[SalesForce] System.LimitException: Too many SOQL queries: 101 with trigger

trigger updateCaseStatusonOwnerChange on Case (before update) {  
        Set<Id> QIds = new Set<Id>();
         //Get the Ids of the different Queues
        Id MarkeRecoTyId = Schema.SObjectType.case.getRecordTypeInfosByName().get('Marketing').getRecordTypeId();
        Id GTGrLRecoTyId = Schema.SObjectType.case.getRecordTypeInfosByName().get('G&T Group Leads').getRecordTypeId();
        Id CCSCRecoTyId = Schema.SObjectType.case.getRecordTypeInfosByName().get('CCSC').getRecordTypeId();
        Id EcomRecoTyId = Schema.SObjectType.case.getRecordTypeInfosByName().get('E-Commerce').getRecordTypeId();
        for (Group q:[select Id, Name from Group where Type = 'Queue'])
        {
         if (q.Name == 'DS Service Queue' || q.Name == 'Data Services RFP' || q.Name == 'Data Services') 
             QIds.add(q.Id); 
        }
        //Loop through all Cases and check owner change
        Case oldC = new Case();
        system.debug('oldrecord type==>+' + oldc.RecordTypeId ); 
        for (Case c:trigger.new)
        { 
            oldC = trigger.oldMap.get(c.Id);
            System.debug('Oldc'+ oldc);
               System.debug('RecordTypename' + oldc.RecordType.name); 
            if (oldC.OwnerId != c.OwnerId && QIds.contains(oldC.OwnerId))
            {
                c.Status = 'Open';
                if (c.Category__c == null || c.Sub_Department__c == null)
                    c.addError('You must provide a value for Category and Sub Department before changing the Case Owner');
            } 
           // Guest Assistance Project Requirments - Auto stamp Department and Sub-Department when a GA case is re-assigned to other Queues      
            for(RecordType rt : [Select ID, Name From RecordType Where sObjectType = 'case'  and id=:oldc.RecordTypeId limit 1]) {
                system.debug('Record Type name ==>' + rt.Name);
                if(rt.Name == 'Guest Assistance') {
                 System.debug('RecordTypeIds ==>'+ 'Marketing==>' + MarkeRecoTyId + '  ' +  'G&T Group Leads==>'+  GTGrLRecoTyId + '  ' +  'CCSC==>' + CCSCRecoTyId + '  ' + 'E-Commerce ==>' + EcomRecoTyId  );
                 for( Group qu :[select id, Name from Group where id =: c.OwnerId and Type = 'Queue' limit 1]){
                     if (qu.name == 'Marketing Queue') { 
                         c.RecordTypeId = MarkeRecoTyId;
                         c.Department__c = 'Marketing';
                         c.Sub_Department__c = 'Marketing';
                         c.Category__c = 'Website Feature'; 
                     }  
                     if (qu.name == 'G&T Service Queue' || Test.isRunningTest() ) {
                         c.RecordTypeId = GTGrLRecoTyId; 
                         c.Department__c = 'Group & Tour';
                         c.Sub_Department__c = 'Group & Tour';
                         c.Category__c = 'Group Event';                                 
                     } 
                     if (qu.name == 'CCSC Queue'||Test.isRunningTest()) {
                         c.RecordTypeId = CCSCRecoTyId;
                         c.Department__c = 'Guest Assistance';
                         c.Sub_Department__c = 'Guest Assistance';
                         c.Category__c = null ;                                 
                     } 
                     if (qu.name == 'E-Commerce'||Test.isRunningTest()) {
                         c.RecordTypeId = EcomRecoTyId; 
                         c.Department__c = 'E-Commerce';
                         c.Sub_Department__c = 'E-Commerce';
                         c.Category__c = 'Other';    
                    }
                }
            }
                else if ((rt.Name == 'Marketing') || (rt.Name == 'G&T Group Leads') || (rt.Name == 'CCSC') || (rt.Name == 'E-Commerce'|| Test.isRunningTest())) {
                   Id GuesRecoTyId = Schema.SObjectType.case.getRecordTypeInfosByName().get('Guest Assistance').getRecordTypeId();
                    for( Group qu :[select id, Name from Group where id =: c.OwnerId and Type = 'Queue' limit 1]){
                        if (qu.name == 'Guest Assistance Queue') { 
                            c.RecordTypeId = GuesRecoTyId; 
                            c.Department__c = 'Guest Assistance';
                            c.Sub_Department__c = 'Guest Assistance';
                            c.Category__c = null;     
                        }
                    } 
                }                  
            }
        }
    }

I am unable to figure it where to make change to avoid below error during bulk operation

updateCaseStatusonOwnerChange: System.LimitException: Too many SOQL queries: 101

Best Answer

Using a LIMIT clause in a SOQL query does not protect you against the limit on total number of SOQL queries. You need to factor your queries on Group and RecordType outside of the loop that processes Cases. (Your RecordType queries can be completely removed by using Describe methods, as you already do at the start of your method).

The usual pattern for this factoring goes something like this (this is an example that you will need to adapt for your own code):

Set<Id> ownerIds = new Set<Id>();

for (Case c : Trigger.new) {
    ownerIds.add(set.OwnerId);
}

Map<Id, Group> groupMap = new Map<Id, Group>(
    [SELECT THE_FIELDS_YOU_NEED
     FROM Group
     WHERE Id IN :ownerIds]
);

for (Case c : Trigger.new) {
    // Iterate over our Cases again, and make updates as needed.
    // Getting information about the Case Owner via groupMap.get(c.OwnerId)
}

This pattern can be extended to query multiple objects in the "center" portion, but the overall structure stays the same: you iterate over your trigger objects once to gather the details of the other records you need in order to work with them, query those objects in a single query per object, usually into a Map, and finally iterate over the trigger objects a second time, using the Maps to source details about their related records.

Related Topic