+ Start a Discussion
sam_Adminsam_Admin 

Error in Production

Hi All,

    I deployed this trigger to production but i get exception sometimes , recently am trying to assign campaigns from one user to other user by dataloader but it failed i got this error

 

Apex script unhandled trigger exception by user/organization: 00540000001RV3E/00D300000001a40

 

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

 

Here is my trigger , please help me to avoid this exceptions

 

trigger UpdateCount on Campaign (after update,after insert) {

  Set<Id> recordIds = new Set<Id>();
  Set<Id> parentIds = new Set<Id>();
 
  for(Campaign c:Trigger.new)
   {
     //system.debug('--c.ParentId--'+c.ParentId);
     if(c.ParentId != null )
       {
         recordIds.add(c.id);
         parentIds.add(c.parentid);
         //system.debug('-------recordIds----'+recordIds);
         //system.debug('-------parentIds----'+parentIds);
   
       List <Campaign> cmpParent = [select id,ELOQUA__Emails_Sent__c,ELOQUA__Emails_Opened__c,ELOQUA__Possible_Email_Forwards__c,ELOQUA__Email_Click_Throughs__c,ELOQUA__Email_Bouncebacks__c,ELOQUA__Email_Unsubscribes__c,
       ELOQUA__Forms_Submitted__c,ELOQUA__Hypersite_Visits__c,ELOQUA__Event_Registrations__c,ELOQUA__Surveys_Completed__c,ELOQUA__Online_Referral_Visits__c from Campaign where id in :ParentIds limit 1];
         //system.debug('-------cmpParent----'+cmpParent);
         LIST<AggregateResult> ChildCmp = [select sum(ELOQUA__Emails_Sent__c)childsum,sum(ELOQUA__Emails_Opened__c)childsum1,sum(ELOQUA__Possible_Email_Forwards__c)childsum2,sum(ELOQUA__Email_Click_Throughs__c)childsum3,
         sum(ELOQUA__Email_Bouncebacks__c)childsum4,sum(ELOQUA__Email_Unsubscribes__c)childsum5,sum(ELOQUA__Forms_Submitted__c)childsum6,sum(ELOQUA__Hypersite_Visits__c)childsum7,
         sum(ELOQUA__Event_Registrations__c)childsum8,sum(ELOQUA__Surveys_Completed__c)childsum9,sum(ELOQUA__Online_Referral_Visits__c)childsum10 from Campaign where parentid in :ParentIds];
         //System.debug('ChildCmp: ' + ChildCmp);
         Decimal D = (Decimal)ChildCmp[0].get('childsum');
         Decimal D1 = (Decimal)ChildCmp[0].get('childsum1');
         Decimal D2 = (Decimal)ChildCmp[0].get('childsum2');
         Decimal D3 = (Decimal)ChildCmp[0].get('childsum3');
         Decimal D4 = (Decimal)ChildCmp[0].get('childsum4');
         Decimal D5 = (Decimal)ChildCmp[0].get('childsum5');
         Decimal D6 = (Decimal)ChildCmp[0].get('childsum6');
         Decimal D7 = (Decimal)ChildCmp[0].get('childsum7');
         Decimal D8 = (Decimal)ChildCmp[0].get('childsum8');
         Decimal D9 = (Decimal)ChildCmp[0].get('childsum9');
         Decimal D10 = (Decimal)ChildCmp[0].get('childsum10');
         //system.debug('--------D-------'+D+'-------D1----'+D1);
         //system.debug('------cmpParent[0].ELOQUA__Emails_Sent__c-----'+cmpParent[0].ELOQUA__Emails_Sent__c+'----'+ cmpParent[0].ELOQUA__Emails_Opened__c);
         if(cmpParent[0].ELOQUA__Emails_Sent__c!=null)
         cmpParent[0].Total_Emails_Sent__c = D + cmpParent[0].ELOQUA__Emails_Sent__c;
         else
         cmpParent[0].Total_Emails_Sent__c = D;
         
         if(cmpParent[0].ELOQUA__Emails_Opened__c!=null)
         cmpParent[0].Total_Emails_Opened__c = D1 + cmpParent[0].ELOQUA__Emails_Opened__c;
         else
         cmpParent[0].Total_Emails_Opened__c = D1;
         
         if(cmpParent[0].ELOQUA__Possible_Email_Forwards__c!=null)
         cmpParent[0].Total_Possible_Email_Forwards__c = D2 + cmpParent[0].ELOQUA__Possible_Email_Forwards__c;
         else
         cmpParent[0].Total_Possible_Email_Forwards__c = D2;
         
         if(cmpParent[0].ELOQUA__Email_Click_Throughs__c!=null)
         cmpParent[0].Total_Email_Click_Throughs__c = D3 + cmpParent[0].ELOQUA__Email_Click_Throughs__c;
         else
         cmpParent[0].Total_Email_Click_Throughs__c = D3;
         
         if(cmpParent[0].ELOQUA__Email_Bouncebacks__c!=null)
         cmpParent[0].Total_Email_Bouncebacks__c = D4 + cmpParent[0].ELOQUA__Email_Bouncebacks__c;
         else
         cmpParent[0].Total_Email_Bouncebacks__c = D4;
         
         if(cmpParent[0].ELOQUA__Email_Unsubscribes__c!=null)
         cmpParent[0].Total_Email_Unsubscribes__c = D5 + cmpParent[0].ELOQUA__Email_Unsubscribes__c;
         else
         cmpParent[0].Total_Email_Unsubscribes__c = D5;
         
         if(cmpParent[0].ELOQUA__Forms_Submitted__c!=null)
         cmpParent[0].Total_Forms_Submitted__c = D6 + cmpParent[0].ELOQUA__Forms_Submitted__c;
         else
         cmpParent[0].Total_Forms_Submitted__c = D6;
         
         if(cmpParent[0].ELOQUA__Hypersite_Visits__c!=null)
         cmpParent[0].Total_Hypersite_Visits__c = D7 + cmpParent[0].ELOQUA__Hypersite_Visits__c;
         else
         cmpParent[0].Total_Hypersite_Visits__c = D7;
         
         if(cmpParent[0].ELOQUA__Event_Registrations__c!=null)
         cmpParent[0].Total_Event_Registrations__c = D8 + cmpParent[0].ELOQUA__Event_Registrations__c;
         else
         cmpParent[0].Total_Event_Registrations__c = D8;
         
         if(cmpParent[0].ELOQUA__Surveys_Completed__c!=null)
         cmpParent[0].Total_Surveys_Completed__c = D9 + cmpParent[0].ELOQUA__Surveys_Completed__c;
         else
         cmpParent[0].Total_Surveys_Completed__c = D9;
         
         if(cmpParent[0].ELOQUA__Online_Referral_Visits__c!=null)
         cmpParent[0].Total_Online_Referral_Visits__c = D10 + cmpParent[0].ELOQUA__Online_Referral_Visits__c;
         else
         cmpParent[0].Total_Online_Referral_Visits__c = D10;
         
         update cmpParent;
     
    }
  }
}

Best Answer chosen by Admin (Salesforce Developers) 
sai.sfsai.sf

hey sam_admin alias venu .. try below code...

 

 

trigger UpdateCount on Campaign (after update,after insert) {

  Set<Id> recordIds = new Set<Id>();
  Set<Id> parentIds = new Set<Id>();
 
  for(Campaign c:Trigger.new)
   {
     //system.debug('--c.ParentId--'+c.ParentId);
     if(c.ParentId != null )
       {
         recordIds.add(c.id);
         parentIds.add(c.parentid);
         //system.debug('-------recordIds----'+recordIds);
         //system.debug('-------parentIds----'+parentIds);
       }
       
   }
    if(parentIds.size()>0){
       List <Campaign> cmpParent = [select id,ELOQUA__Emails_Sent__c,ELOQUA__Emails_Opened__c,ELOQUA__Possible_Email_Forwards__c,ELOQUA__Email_Click_Throughs__c,ELOQUA__Email_Bouncebacks__c,ELOQUA__Email_Unsubscribes__c,
       ELOQUA__Forms_Submitted__c,ELOQUA__Hypersite_Visits__c,ELOQUA__Event_Registrations__c,ELOQUA__Surveys_Completed__c,ELOQUA__Online_Referral_Visits__c from Campaign where id in :ParentIds limit 1];
         //system.debug('-------cmpParent----'+cmpParent);
         LIST<AggregateResult> ChildCmp = [select sum(ELOQUA__Emails_Sent__c)childsum,sum(ELOQUA__Emails_Opened__c)childsum1,sum(ELOQUA__Possible_Email_Forwards__c)childsum2,sum(ELOQUA__Email_Click_Throughs__c)childsum3,
         sum(ELOQUA__Email_Bouncebacks__c)childsum4,sum(ELOQUA__Email_Unsubscribes__c)childsum5,sum(ELOQUA__Forms_Submitted__c)childsum6,sum(ELOQUA__Hypersite_Visits__c)childsum7,
         sum(ELOQUA__Event_Registrations__c)childsum8,sum(ELOQUA__Surveys_Completed__c)childsum9,sum(ELOQUA__Online_Referral_Visits__c)childsum10 from Campaign where parentid in :ParentIds];
         //System.debug('ChildCmp: ' + ChildCmp);
         Decimal D = (Decimal)ChildCmp[0].get('childsum');
         Decimal D1 = (Decimal)ChildCmp[0].get('childsum1');
         Decimal D2 = (Decimal)ChildCmp[0].get('childsum2');
         Decimal D3 = (Decimal)ChildCmp[0].get('childsum3');
         Decimal D4 = (Decimal)ChildCmp[0].get('childsum4');
         Decimal D5 = (Decimal)ChildCmp[0].get('childsum5');
         Decimal D6 = (Decimal)ChildCmp[0].get('childsum6');
         Decimal D7 = (Decimal)ChildCmp[0].get('childsum7');
         Decimal D8 = (Decimal)ChildCmp[0].get('childsum8');
         Decimal D9 = (Decimal)ChildCmp[0].get('childsum9');
         Decimal D10 = (Decimal)ChildCmp[0].get('childsum10');
         //system.debug('--------D-------'+D+'-------D1----'+D1);
         //system.debug('------cmpParent[0].ELOQUA__Emails_Sent__c-----'+cmpParent[0].ELOQUA__Emails_Sent__c+'----'+ cmpParent[0].ELOQUA__Emails_Opened__c);
         if(cmpParent[0].ELOQUA__Emails_Sent__c!=null)
         cmpParent[0].Total_Emails_Sent__c = D + cmpParent[0].ELOQUA__Emails_Sent__c;
         else
         cmpParent[0].Total_Emails_Sent__c = D;
         
         if(cmpParent[0].ELOQUA__Emails_Opened__c!=null)
         cmpParent[0].Total_Emails_Opened__c = D1 + cmpParent[0].ELOQUA__Emails_Opened__c;
         else
         cmpParent[0].Total_Emails_Opened__c = D1;
         
         if(cmpParent[0].ELOQUA__Possible_Email_Forwards__c!=null)
         cmpParent[0].Total_Possible_Email_Forwards__c = D2 + cmpParent[0].ELOQUA__Possible_Email_Forwards__c;
         else
         cmpParent[0].Total_Possible_Email_Forwards__c = D2;
         
         if(cmpParent[0].ELOQUA__Email_Click_Throughs__c!=null)
         cmpParent[0].Total_Email_Click_Throughs__c = D3 + cmpParent[0].ELOQUA__Email_Click_Throughs__c;
         else
         cmpParent[0].Total_Email_Click_Throughs__c = D3;
         
         if(cmpParent[0].ELOQUA__Email_Bouncebacks__c!=null)
         cmpParent[0].Total_Email_Bouncebacks__c = D4 + cmpParent[0].ELOQUA__Email_Bouncebacks__c;
         else
         cmpParent[0].Total_Email_Bouncebacks__c = D4;
         
         if(cmpParent[0].ELOQUA__Email_Unsubscribes__c!=null)
         cmpParent[0].Total_Email_Unsubscribes__c = D5 + cmpParent[0].ELOQUA__Email_Unsubscribes__c;
         else
         cmpParent[0].Total_Email_Unsubscribes__c = D5;
         
         if(cmpParent[0].ELOQUA__Forms_Submitted__c!=null)
         cmpParent[0].Total_Forms_Submitted__c = D6 + cmpParent[0].ELOQUA__Forms_Submitted__c;
         else
         cmpParent[0].Total_Forms_Submitted__c = D6;
         
         if(cmpParent[0].ELOQUA__Hypersite_Visits__c!=null)
         cmpParent[0].Total_Hypersite_Visits__c = D7 + cmpParent[0].ELOQUA__Hypersite_Visits__c;
         else
         cmpParent[0].Total_Hypersite_Visits__c = D7;
         
         if(cmpParent[0].ELOQUA__Event_Registrations__c!=null)
         cmpParent[0].Total_Event_Registrations__c = D8 + cmpParent[0].ELOQUA__Event_Registrations__c;
         else
         cmpParent[0].Total_Event_Registrations__c = D8;
         
         if(cmpParent[0].ELOQUA__Surveys_Completed__c!=null)
         cmpParent[0].Total_Surveys_Completed__c = D9 + cmpParent[0].ELOQUA__Surveys_Completed__c;
         else
         cmpParent[0].Total_Surveys_Completed__c = D9;
         
         if(cmpParent[0].ELOQUA__Online_Referral_Visits__c!=null)
         cmpParent[0].Total_Online_Referral_Visits__c = D10 + cmpParent[0].ELOQUA__Online_Referral_Visits__c;
         else
         cmpParent[0].Total_Online_Referral_Visits__c = D10;
         
         update cmpParent;
      }
     
  }

All Answers

AmitSahuAmitSahu

what is the batch size you are trying for this ? can you try reducing the batch size for the time being ?

AmitSahuAmitSahu

Also try keeping the query out of the for loop...it seems you have the queries  inside the for loop. if there are 2 queries in the loop ,by the time when your 51st record is inserted via dataloader this will fail.

sam_Adminsam_Admin

I reduced my batch size to 100 and tried to do it again but it failed , ihave 103 records and only 3 records got successfully updated , how do you write that query out of loop ?

sai.sfsai.sf

hey sam_admin alias venu .. try below code...

 

 

trigger UpdateCount on Campaign (after update,after insert) {

  Set<Id> recordIds = new Set<Id>();
  Set<Id> parentIds = new Set<Id>();
 
  for(Campaign c:Trigger.new)
   {
     //system.debug('--c.ParentId--'+c.ParentId);
     if(c.ParentId != null )
       {
         recordIds.add(c.id);
         parentIds.add(c.parentid);
         //system.debug('-------recordIds----'+recordIds);
         //system.debug('-------parentIds----'+parentIds);
       }
       
   }
    if(parentIds.size()>0){
       List <Campaign> cmpParent = [select id,ELOQUA__Emails_Sent__c,ELOQUA__Emails_Opened__c,ELOQUA__Possible_Email_Forwards__c,ELOQUA__Email_Click_Throughs__c,ELOQUA__Email_Bouncebacks__c,ELOQUA__Email_Unsubscribes__c,
       ELOQUA__Forms_Submitted__c,ELOQUA__Hypersite_Visits__c,ELOQUA__Event_Registrations__c,ELOQUA__Surveys_Completed__c,ELOQUA__Online_Referral_Visits__c from Campaign where id in :ParentIds limit 1];
         //system.debug('-------cmpParent----'+cmpParent);
         LIST<AggregateResult> ChildCmp = [select sum(ELOQUA__Emails_Sent__c)childsum,sum(ELOQUA__Emails_Opened__c)childsum1,sum(ELOQUA__Possible_Email_Forwards__c)childsum2,sum(ELOQUA__Email_Click_Throughs__c)childsum3,
         sum(ELOQUA__Email_Bouncebacks__c)childsum4,sum(ELOQUA__Email_Unsubscribes__c)childsum5,sum(ELOQUA__Forms_Submitted__c)childsum6,sum(ELOQUA__Hypersite_Visits__c)childsum7,
         sum(ELOQUA__Event_Registrations__c)childsum8,sum(ELOQUA__Surveys_Completed__c)childsum9,sum(ELOQUA__Online_Referral_Visits__c)childsum10 from Campaign where parentid in :ParentIds];
         //System.debug('ChildCmp: ' + ChildCmp);
         Decimal D = (Decimal)ChildCmp[0].get('childsum');
         Decimal D1 = (Decimal)ChildCmp[0].get('childsum1');
         Decimal D2 = (Decimal)ChildCmp[0].get('childsum2');
         Decimal D3 = (Decimal)ChildCmp[0].get('childsum3');
         Decimal D4 = (Decimal)ChildCmp[0].get('childsum4');
         Decimal D5 = (Decimal)ChildCmp[0].get('childsum5');
         Decimal D6 = (Decimal)ChildCmp[0].get('childsum6');
         Decimal D7 = (Decimal)ChildCmp[0].get('childsum7');
         Decimal D8 = (Decimal)ChildCmp[0].get('childsum8');
         Decimal D9 = (Decimal)ChildCmp[0].get('childsum9');
         Decimal D10 = (Decimal)ChildCmp[0].get('childsum10');
         //system.debug('--------D-------'+D+'-------D1----'+D1);
         //system.debug('------cmpParent[0].ELOQUA__Emails_Sent__c-----'+cmpParent[0].ELOQUA__Emails_Sent__c+'----'+ cmpParent[0].ELOQUA__Emails_Opened__c);
         if(cmpParent[0].ELOQUA__Emails_Sent__c!=null)
         cmpParent[0].Total_Emails_Sent__c = D + cmpParent[0].ELOQUA__Emails_Sent__c;
         else
         cmpParent[0].Total_Emails_Sent__c = D;
         
         if(cmpParent[0].ELOQUA__Emails_Opened__c!=null)
         cmpParent[0].Total_Emails_Opened__c = D1 + cmpParent[0].ELOQUA__Emails_Opened__c;
         else
         cmpParent[0].Total_Emails_Opened__c = D1;
         
         if(cmpParent[0].ELOQUA__Possible_Email_Forwards__c!=null)
         cmpParent[0].Total_Possible_Email_Forwards__c = D2 + cmpParent[0].ELOQUA__Possible_Email_Forwards__c;
         else
         cmpParent[0].Total_Possible_Email_Forwards__c = D2;
         
         if(cmpParent[0].ELOQUA__Email_Click_Throughs__c!=null)
         cmpParent[0].Total_Email_Click_Throughs__c = D3 + cmpParent[0].ELOQUA__Email_Click_Throughs__c;
         else
         cmpParent[0].Total_Email_Click_Throughs__c = D3;
         
         if(cmpParent[0].ELOQUA__Email_Bouncebacks__c!=null)
         cmpParent[0].Total_Email_Bouncebacks__c = D4 + cmpParent[0].ELOQUA__Email_Bouncebacks__c;
         else
         cmpParent[0].Total_Email_Bouncebacks__c = D4;
         
         if(cmpParent[0].ELOQUA__Email_Unsubscribes__c!=null)
         cmpParent[0].Total_Email_Unsubscribes__c = D5 + cmpParent[0].ELOQUA__Email_Unsubscribes__c;
         else
         cmpParent[0].Total_Email_Unsubscribes__c = D5;
         
         if(cmpParent[0].ELOQUA__Forms_Submitted__c!=null)
         cmpParent[0].Total_Forms_Submitted__c = D6 + cmpParent[0].ELOQUA__Forms_Submitted__c;
         else
         cmpParent[0].Total_Forms_Submitted__c = D6;
         
         if(cmpParent[0].ELOQUA__Hypersite_Visits__c!=null)
         cmpParent[0].Total_Hypersite_Visits__c = D7 + cmpParent[0].ELOQUA__Hypersite_Visits__c;
         else
         cmpParent[0].Total_Hypersite_Visits__c = D7;
         
         if(cmpParent[0].ELOQUA__Event_Registrations__c!=null)
         cmpParent[0].Total_Event_Registrations__c = D8 + cmpParent[0].ELOQUA__Event_Registrations__c;
         else
         cmpParent[0].Total_Event_Registrations__c = D8;
         
         if(cmpParent[0].ELOQUA__Surveys_Completed__c!=null)
         cmpParent[0].Total_Surveys_Completed__c = D9 + cmpParent[0].ELOQUA__Surveys_Completed__c;
         else
         cmpParent[0].Total_Surveys_Completed__c = D9;
         
         if(cmpParent[0].ELOQUA__Online_Referral_Visits__c!=null)
         cmpParent[0].Total_Online_Referral_Visits__c = D10 + cmpParent[0].ELOQUA__Online_Referral_Visits__c;
         else
         cmpParent[0].Total_Online_Referral_Visits__c = D10;
         
         update cmpParent;
      }
     
  }

This was selected as the best answer
sam_Adminsam_Admin

Thanks so much Kiran :) it worked

sai.sfsai.sf

You are welcome Venu:)