+ Start a Discussion
CKN_1CKN_1 

System.Exception: Too many SOQL queries

I am running into the governor limit issue since I have the SQls inside the for loop. I am trying to optimize the code to get the case and owner information outside the for loop.

 

New to Apex development. Would appreciate some pointers on writing efficient SOQls outside the For loop in a trigger.

 

/* Trigger logic.. If transportation case type, and status = "awaiting internal response, case creator alias = "CS agents" and case owner = regional call center, MVC agent field = TASR agent, then assign then case back to the MVC agent *./

'

 

trigger TR_2 on Case (before update) {
   Case[] cases = Trigger.new;
   for (Case a:cases){ 
    string creatorid;
    string casecreatoriwdalias;
      String caseowner;
    string iWdAliasCaseOwner;
    String caseno;
    String err_msg = '';
    String MVCowner;
      String iWDMVCOwner;
                     
             creatorid = a.CreatedById;
          caseno = a.CaseNumber;
          
          case c = [select ownerid from case where casenumber = :caseno limit 1];
           caseowner = a.ownerid;
          MVCowner  = a.MVC_Owner__c;
    
         // get user alias information for the case creator   
    User userinfor = [select alias, iWD_Alias_Case_Owner__c, Iwd_Case_Creator__c from User where id = :creatorid];
    casecreatoriwdalias = userinfor.Iwd_Case_Creator__c;
    
      if (caseowner.substring(0,3) == '005'){
    // get case owner alias information
    User caseowneruserinfor = [select alias, iWD_Alias_Case_Owner__c, Iwd_Case_Creator__c from User where id = :caseowner limit 1];
    iWdAliasCaseOwner = caseowneruserinfor.iWD_Alias_Case_Owner__c;
     
     // check for status and SRCL Transportation record type and case owner and case creator alias
        if ((a.STATUS == 'Awaiting Internal Response'&& a.RecordTypeID == '012300000004rDZ')&& 
            (casecreatoriwdalias =='CAS' ||casecreatoriwdalias =='TASR/P1')&& 
                 (iWdAliasCaseOwner == 'DISP'|| iWdAliasCaseOwner == 'RCC'))  {
       a.IWD_Testing__c = 'trigger 2 enter the loop'; 
          }// status 'internal response'
                   
   if ((a.STATUS == 'Closed'&& a.RecordTypeID == '012300000004rDZ')&& 
            (MVCowner =='CAS' ||MVCowner =='TASR/P1')&& 
                 (iWdAliasCaseOwner == 'DISP'|| iWdAliasCaseOwner == 'RCC'))  {
       a.IWD_Testing__c = 'trigger2 working!!'; 
       a.ownerid = creatorid;  
       a.status = 'Awaiting internal response';   
        }// status 'closed'
                    } //if case owner 
      }// FOR LOOP
} // EOF

nnewbold-sfdcnnewbold-sfdc

Have you read the bulk pattern article in the Apex doc?  It should get you started:

http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_patterns_bulk.htm

 

In your case, you would rewrite your queries by creating collections of user Ids and then querying in bulk:

List<Id> createdByIds = new List<Id>();

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

 

for (Case c : Trigger.new) {

    createdByIds.add(c.createdById);

    ownerIds.add(c.ownerId);

}

 

List<User> userInfo = [select alias, iWD_Alias_Case_Owner__c, Iwd_Case_Creator__c from User where id IN :createdByIds];

 

// use a map here, that way, when you do your if statement, you can randomly access the User record by their Id using Map.get()

Map<Id, User> caseOwnerUserInfo = new Map<Id, User>([select alias, iWD_Alias_Case_Owner__c, Iwd_Case_Creator__c from User where id = :ownerIds]);

nnewbold-sfdcnnewbold-sfdc

Posted too soon...that last query should use an IN operator:

Map<Id, User> caseOwnerUserInfo = new Map<Id, User>([select alias, iWD_Alias_Case_Owner__c, Iwd_Case_Creator__c from User where id IN :ownerIds]);

CKN_1CKN_1

Thanks so much. i will try this out now.

 

Chitra

CKN_1CKN_1

I rewrote the code to inlcude Maps outside the for loop but am still running into the issue. We have several for update triggers on the case object.  Is the governor limit per trigger or per transaction ( i.e when I hit the save button on the case, the system log shows all the for update triggers that run) Do they all count towards the governor limit.

 

How do I overcome it?

 

Chitra

 

rahulsharmarahulsharma

hi,

Why Dont u try to call batch apex from trigger..

i also was having similar problem,

because more query inside  the triggers is not alowed ( limit is less)

CKNCKN

Rahul,

 

Thanks for the suggestion. I am fairly new to apex development. Can you give me some pointers on how to call batch Apex?

 

 

rahulsharmarahulsharma

i'm also new but will just try to explain:


/* calling batch class from any trigger or class*/
String strQuery='query';      // instead put ur query in place of query- whatever u want to send
BatchClassName objBatchClassName=new BatchClassName(strQuery);
Database.executeBatch(objBatchClassName);

/* batch class*/
global class BatchClassName implements Database.Batchable<sObject>
{
    global final string query;
    global BatchClassName(String query)//batch class constructor
    {
          Query=q;
    }
    global Database.QueryLocator start(Database.BatchableContext BC)
    {
        return Database.getQueryLocator(query);
    }
global void execute(Database.BatchableContext BC,List<sObject> scope)
    {
        for(Sobject s : scope)  // in scope all records will be coming which are send as parameter to batch
        {
            
        }
    }
    global void finish(Database.BatchableContext BC)
    {
        
    }
}

try link below for more details:

http://www.salesforce.com/us/developer/docs/apexcode/salesforce_apex_language_reference.pdf:

 

CKNCKN

Rahul,

 

Thanks so much.. It seems complex but I am going to give it a try..:)

 

Chitra