+ Start a Discussion
GhilliGhilli 

No. of cases attached to an account

Hi Guys,

 

I have a requirement to count the no. of cases attached to an account.

We dont have have a roll-up summary field for this. So that i have created a trigger to do this (on Case object while insert, update, delete). It was working fine but later i fouint that while merging two accounts this case count is not getting updated. So that i have return on more trigger while updating an account so that i can get the correct case count.

 

But an account is having 1500 cases. While updating one of its cases its thrwing an error saying "too many query rows". While deactivate the trigger which return on account the updation happens without any error.

 

In the same way while updating 1000 account records at a time its throwing the same error. While deactivating the case trigger its working fine.

 

I am not able to understand this.

 

Please suggest me a way.

 

 

Trigger under account:

 

trigger updateNoOfCases on Account (after update) { try { for(Account accTemp : Trigger.new) { List<Case> caseList = new List<Case> (); caseList = [select id from Case where AccountId = :accTemp.Id]; if (accTemp.of_cases_attached__c != caseList.size()) { accTemp.of_cases_attached__c = caseList.size(); } } } }

 

 

Trigger on Case:

 

if(Trigger.isupdate) { List<Account> accountsWithCases= [select id, of_cases_attached__c, (select id from Cases) from Account where Id IN :Trigger.newMap.keySet() or Id In : Trigger.oldMap.keySet()]; for(Account a :accountsWithCases) { Integer count=0; for(Case caseObj : a.cases) { count++; } a.of_cases_attached__c=count; accObj.add(a); } update accObj; }

 

werewolfwerewolf

Your subquery is selecting all the cases under an account.  When you hit 1000 cases under the account, you hit the query limit.  In any case you don't need to do that -- just select the number of cases currently on the account and add 1 to it.

 

If you need to initialize all accounts with the case numbers you can do so using batch apex, but there's no need to be recounting all cases under an account every time you simply modify a case.  Also, in your before update trigger, you should be checking to see if the case's account has changed, because you only need to change the case counter on Account for those cases, which are likely a very small minority.

WmWm
It is throwing an error because it salesforce has a governance limit due to its multi tenant architecture. Please read this for understanding gvernance limits. You would need to use maps and sets your Apex in to overcome the governance limits. For a solution to your query you might read this blog.