function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
plai_highwireplai_highwire 

SOQL limits on trigger

I have a trigger that does multiple processes on the case object.  I seem to be hitting my soql limits on this trigger.  Could someone point me in the right direction of where I might be looping too many statements?  Much appreciated.

trigger BeforeCaseInsert on Case (before insert,before update)
{
    List<String> RecordTypeNameList = new List<String>();
    RecordTypeNameList.add('CR');
    RecordTypeNameList.add('Prod Specialist');
    RecordTypeNameList.add('BenchPress');
    RecordTypeNameList.add('Support');
    
    
    List<RecordType> RecordTypeResult = [SELECT id,Name FROM RecordType WHERE name in: RecordTypeNameList];
    
    Map<String,Id> RecordTypeNameIDMap = new Map<String,Id>();
    
    for(RecordType rt : RecordTypeResult)
    {
        RecordTypeNameIDMap.put(rt.Name,rt.id);
    }
    
 
    private List <Sites__c> sitesF;
    List<Spec_History__c> SpecList = new List<Spec_History__c>();
    
   
    sitesF = new List<Sites__c>([SELECT id, Name, Feedback__c, Account__c, Account__r.Publication_Manager2__c
                                 FROM Sites__c LIMIT 50000]);
    
    
    
    //Create lists for the case team member code
    List<CaseTeamMember> ctm = new List<CaseTeamMember>();
    List<CaseTeamMember> ctmdelete = new List<CaseTeamMember>();
    List<CaseTeamRole>ctrole = [SELECT Name, Id FROM CaseTeamRole];
    Map<String,String>caseteamr = new Map<String,String>{};
        for(CaseTeamRole ct:ctrole){
            caseteamr.put(ct.Name, ct.Id);}
    
    //Create Set of Case Ids where status is Closed
    Set<ID>cid= new Set<ID>();
    
    
    for (Case c : trigger.new) {
        
        if (c.Description != null
            && c.Description != ''
            && c.recordTypeID == RecordTypeNameIDMap.get('Support'))
        {
            for(Sites__c s : sitesF) {
                if(c.Description.contains(s.Feedback__c) && s.Feedback__c != null){
                    c.sites__c = s.id;
                    c.accountId = s.account__c;

                    break;}
            }
        }
        
        if (c.recordTypeId == RecordTypeNameIDMap.get('Prod Specialist')
            && c.JCode__c != null
            && c.JCode__c != ''
           ) {
               for(Sites__c s : sitesF) {
                   if (c.JCode__c == s.Name){
                       c.accountId = s.account__c;
                       c.Sites__c = s.id;
                       break;
                   }
               }
               
           }
        
        // Spec History Code
        if (Trigger.isUpdate) {
            if (c.recordTypeId == RecordTypeNameIDMap.get('CR'))      
            {
                Case oldCase = Trigger.oldMap.get(c.ID);
                if (c.Spec_Image__c != oldCase.Spec_Image__c || c.Spec_pages__c != oldCase.Spec_Pages__c || c.Jcode__c != oldCase.JCode__c || c.Spec_description__c != oldCase.Spec_description__c || c.Configuration_Details__c != oldCase.Configuration_Details__c ) {
                    Spec_History__c newspec = new Spec_History__c ();
                    if (c.Spec_Image__c != oldCase.Spec_Image__c) {
                        newspec.Spec_Image_History__c = oldCase.Spec_Image__c;
                    }
                    if (c.Spec_pages__c != oldCase.Spec_Pages__c){
                        newspec.Spec_pages__c = oldCase.Spec_Pages__c;
                    }
                    if (c.Jcode__c != oldCase.JCode__c){
                        newspec.Spec_Jcode__c = oldCase.JCode__c;
                    }
                    if (c.Spec_description__c != oldCase.Spec_description__c){
                        newspec.Spec_description__c = oldCase.Spec_description__c;
                    }
                    if (c.Configuration_Details__c != oldCase.Configuration_Details__c){
                        newspec.Spec_Config_Details__c = oldCase.Configuration_Details__c;
                    }
                    newspec.Case__c = c.ID;
                    SpecList.add(newspec);
                }
                
            }
        }
        
        if (c.recordTypeId == RecordTypeNameIDMap.get('BenchPress'))
        {
            for(Sites__c s : sitesF) {
                
                if (c.Journal_Code__c == s.id){
                    c.Sites__c = s.id;
                    c.accountId = s.account__c;
                    
                    //         accMap.put(s.account__c,s.Account__r);
                    break;
                }
            }
        }
        
        /**
Following code used to insert case team members using the case manager field
**/
        // Get the id of the old case
        if (Trigger.isUpdate) {
            Case oldCase = Trigger.oldMap.get(c.Id);
            //Delete old case manager from case team
            if(oldCase.Case_Manager__c != null || oldCase.Case_Manager__c == null){
                for(CaseTeamMember ctmrem : [SELECT Id FROM CaseTeamMember WHERE MemberID =: c.Case_Manager__c OR MemberID =: oldCase.Case_Manager__c]){
                    
                    ctmdelete.add(ctmrem);}
            }
            // If the old case manager doesn't equal the new case manager
            if(oldCase.Case_Manager__c != c.Case_Manager__c &&	 c.Case_Manager__c != null){
                
                // Create a list of the members in the case team
                
                // For each of the case managers in ctmlist
                
                
                CaseTeamMember ctmadd = new CaseTeamMember();
                ctmadd.ParentId = c.id;
                ctmadd.MemberId = c.Case_Manager__c;
                ctmadd.TeamRoleId = caseteamr.get('Case Manager');
                
                
                // Add the case manager to the list ctm
                ctm.add(ctmadd);
                
            }  
        }

//add to list of only cases that have been cancelled/declined
        if(c.Status=='Cancelled/declined'){
            cid.add(c.id);}
    }
    if(SpecList.isEmpty()==false){
        insert SpecList;    }
    if(ctmdelete.isEmpty()==false){
        delete ctmdelete;}        
    if(ctm.isEmpty() == false){
        insert ctm;}
    
    List < Opportunity > OppUpdateList = [SELECT Id, StageName from Opportunity WHERE Case__c in : cid AND StageName <> 'Closed - Lost'];
    
    //Set those Opportunities to Closed - Lost
    for (Opportunity oppsupd: oppUpdateList) {
        oppsupd.StageName = 'Closed - Lost';
    }
    update oppUpdateList;
    
}

 

Best Answer chosen by Admin (Salesforce Developers) 
crop1645crop1645

Yikes -- how did I miss that?!

 

Your code fragment here:

if(oldCase.Case_Manager__c != null || oldCase.Case_Manager__c == null){
                for(CaseTeamMember ctmrem : [SELECT Id FROM CaseTeamMember WHERE MemberID =: c.Case_Manager__c OR MemberID =: oldCase.Case_Manager__c]){
                    
                    ctmdelete.add(ctmrem);}

 should change to something like: (after declaring a set<ID> ctmMember IdToDelSet = new Set<ID> (); higher up in the trigger, outside of the Trigger loop)

if(oldCase.Case_Manager__c != null || oldCase.Case_Manager__c == null){ if (c.case_manager__c != null)
      ctmMemberIdToDelSet.add(c.case_manager__c);
  if (oldCase_manager__c != null)
      ctmMemberIdToDelSet.add(oldCase_manager__c);
                ;}

 and then at the end of the Trigger loop, do your SOQL to find all the ctm to delete and add to the ctmDelete list.

 

Oftentimes in triggers the logic ends up looking like:

 

Step 1 - First trigger loop to build up set  of IDs (or Map of ids to sobjects) of something to act on

Step 2 - Go through the set (map) of Ids built in pass 1, fetching records or doing other computations, possibly building up a new set  of ids or list of sobjects 

Step 3, act on the results of Step 2

...steps 4 and 4 doing similar things to step 2 and 3

 

It can be a bit mind bending to always think in batch operations and can also get even more mind bending if you allow for partial successes and want to associate errors back to the original triggered item within the triggered list

 

 

All Answers

crop1645crop1645

1. All of your update operations are bulkified - good

2. No SOQL within the Trigger.new loop - good

 

Your SOQL limit exceeded may be caused by triggers firing on objects that you are updating. Perhaps they weren't written to be properly bulkified. The SOQL llimit applies to the enture transaction - all downstream triggers plus workflows that update Sobjects and thus retrigger those SObjects. Check parent objects using RSF fields on triggered objects too.

 

The best way to see what is going on is to turn on the debug log and rerun your transaction.  Look at the Database portion of the log and see where the SOQL statements are being done and in which trigger

plai83plai83

Thanks Eric,

 

I ran the debug logs and it looks like I'm reaching my limits on this part of the trigger, which is within my trigger.new loop.  Is there a better way for me to write that SELECT statement outside the loop?

       if (Trigger.isUpdate) {
            Case oldCase = Trigger.oldMap.get(c.Id);
            //Delete old case manager from case team
            if(oldCase.Case_Manager__c != null || oldCase.Case_Manager__c == null){
                for(CaseTeamMember ctmrem : [SELECT Id FROM CaseTeamMember WHERE MemberID =: c.Case_Manager__c OR MemberID =: oldCase.Case_Manager__c]){
                    
                    ctmdelete.add(ctmrem);}
            }
            // If the old case manager doesn't equal the new case manager
            if(oldCase.Case_Manager__c != c.Case_Manager__c &&   c.Case_Manager__c != null){
                
                // Create a list of the members in the case team
                
                // For each of the case managers in ctmlist
                
                
                CaseTeamMember ctmadd = new CaseTeamMember();
                ctmadd.ParentId = c.id;
                ctmadd.MemberId = c.Case_Manager__c;
                ctmadd.TeamRoleId = caseteamr.get('Case Manager');
                
                
                // Add the case manager to the list ctm
                ctm.add(ctmadd);
                
            }  

 

NidhiKhoslaNidhiKhosla

Hi ,

I want to copy sum of all the order related to an account to account field but it is not doing that job

 

my trigger is

trigger orderrollup on Order__c (before update) {
Map<Id,Decimal> mapofaccount=new Map<Id,Decimal>();
Set<Id> setofaccount=new Set<Id>();
for(Order__c ord:Trigger.new){
//filter the order
if(ord.Status__c=='Draft'){
if(mapofaccount.containsKey(ord.Account__c)){
Decimal d=mapofaccount.put(ord.Account__c,ord.ordertotal__c)+ord.ordertotal__c;
}
else
Decimal d=mapofaccount.put(ord.Account__c,ord.ordertotal__c);
}
}
List<Account> lstacc=[select id,Totaldraft__c from Account where Id In: mapofaccount.keySet()];
for(Account acc:lstacc){
acc.Totaldraft__c=mapofaccount.get(acc.Id)+acc.Totaldraft__c;
}
update lstacc;
}

crop1645crop1645

Yikes -- how did I miss that?!

 

Your code fragment here:

if(oldCase.Case_Manager__c != null || oldCase.Case_Manager__c == null){
                for(CaseTeamMember ctmrem : [SELECT Id FROM CaseTeamMember WHERE MemberID =: c.Case_Manager__c OR MemberID =: oldCase.Case_Manager__c]){
                    
                    ctmdelete.add(ctmrem);}

 should change to something like: (after declaring a set<ID> ctmMember IdToDelSet = new Set<ID> (); higher up in the trigger, outside of the Trigger loop)

if(oldCase.Case_Manager__c != null || oldCase.Case_Manager__c == null){ if (c.case_manager__c != null)
      ctmMemberIdToDelSet.add(c.case_manager__c);
  if (oldCase_manager__c != null)
      ctmMemberIdToDelSet.add(oldCase_manager__c);
                ;}

 and then at the end of the Trigger loop, do your SOQL to find all the ctm to delete and add to the ctmDelete list.

 

Oftentimes in triggers the logic ends up looking like:

 

Step 1 - First trigger loop to build up set  of IDs (or Map of ids to sobjects) of something to act on

Step 2 - Go through the set (map) of Ids built in pass 1, fetching records or doing other computations, possibly building up a new set  of ids or list of sobjects 

Step 3, act on the results of Step 2

...steps 4 and 4 doing similar things to step 2 and 3

 

It can be a bit mind bending to always think in batch operations and can also get even more mind bending if you allow for partial successes and want to associate errors back to the original triggered item within the triggered list

 

 

This was selected as the best answer
case commentscase comments

Thanks for all your help Eric!.  I've added a new set outside my for loop.

  Set<id> ctMemberIdToDelSet = new Set<ID>();

 I then changed my code to the following:

if(oldCase.Case_Manager__c != null || oldCase.Case_Manager__c == null){
                if(c.Case_Manager__c != null){
                    ctMemberIDToDelSet.add(c.Case_Manager__c);}
                if(oldCase.Case_Manager__c != null){
                    ctMemberIDToDelSet.add(oldCase.Case_Manager__c);}
                    
                    
                    }

 Then outside the foor loop i added to following to delete:

    ctmdelete = [SELECT id FROM CaseTeamMember where MemberID IN: ctMemberIDToDelSet];

    if(ctmdelete.isEmpty()==false){
        delete ctmdelete;}        

 Now comes the fun part of writing a test class for all this.  Thanks again!