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 onGroup
andRecordType
outside of the loop that processes Cases. (YourRecordType
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):
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 theMap
s to source details about their related records.