You need to sign in to do that
Don't have an account?
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;
}
}
}
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
what is the batch size you are trying for this ? can you try reducing the batch size for the time being ?
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.
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 ?
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;
}
}
Thanks so much Kiran :) it worked
You are welcome Venu:)