You need to sign in to do that
Don't have an account?
Trigger for Contact count on Account giving Exception: Too many code statements: 200001
Hi,
I have trigger that rolls up total number of contacts on Account, This trigger was working fine in sandbox, But when moved to production it is throwing errors at the backend saying 'System.LimitException: Too many code statements: 200001'. My company production enviroment is integrated with 3rd party system. So everytime there is an update from external system this trigger is firing errors.
Below is my code:
trigger ContactRollUpOnAccounts on Contact (after delete, after insert, after update) { set<ID> AccountIds = new set<ID>(); set<ID> conIDs = new Set<ID>(); if(trigger.isInsert || trigger.isUpdate){ for(Contact c : trigger.new){ AccountIds.add(c.AccountId); } } if(trigger.isDelete){ for(Contact c : trigger.old){ AccountIds.add(c.AccountId); } } Map<ID, Contact> AccountMap = new Map<ID, Contact>([select id,AccountId from Contact where AccountId IN :AccountIds]); List<Account> AccountsToUpdate = new List<Account>(); for(Account a : [Select Id, Contacts__c from Account where Id IN :AccountIds ]){ for (Contact con : AccountMap.values()) { if (con.AccountId == a.Id) conIDs.add(con.Id); } if (a.Contacts__c!= conIDs.size()) a.Contacts__c= conIDs.size(); AccountsToUpdate.add(a); } update AccountsToUpdate; }
I understand the problem is because I am writing for loop within a for loop, but I am not aware of the workaround to avoid this.
Please help me with your valuiable thoughts
I wrote that free hand from memory.
I forgot, the get method returns Object as data type.
You'll need to do something to cast the result of get('AccountId') to Id or String. You will need to do the same for get('ContactCount') to cast it to a number data type (Integer or Decimal typically). So try something like this:
All Answers
You can prevent this by using Aggregate SOQL.
Instead of querying every contact for every Account in the trigger context and then counting in your code, you should be let SOQL count the number of contacts per account. You query will instead look something like this:
Aggregate result is just a special sObject type that you can then use the alias "ContactCount" as a field name using a standard getter method like this.
Once you have your aggregate count in a map, then you loop over the relevant accounts, thus avoiding the loop-within-a-loop antipattern that you identified. But here's the cool thing. Because you're not *reading* anything from those accounts in the database, you can do a little trick where you create a new account in memory only, and then update it.
Hope this helps.
i Thanks for your reply, i tried to inserting your code I am getting the following error at line
conCountMap.put(agg.get('AccountId'),agg.get('ContactCount'));
and the error is 'Incompatible key type Object for MAP<Id,Integer> at line 25 column 13'
I wrote that free hand from memory.
I forgot, the get method returns Object as data type.
You'll need to do something to cast the result of get('AccountId') to Id or String. You will need to do the same for get('ContactCount') to cast it to a number data type (Integer or Decimal typically). So try something like this:
I'm building a test method for this trigger, but the trigger fails when I update a contact by moving it to another account. The other account contact rollup increases the number, but the old account does not decrease! How would I fix that?
Here is my line in the test class:
And here is the trigger as I have it thus far:
It also doesn't change the number when I delete. So basically, it is not counting down, only up.
Hi, try the below code, it works for the old contacts also...
I tested it also works when contact is deleted.
Queried Contacts bases on old Account Id. Hope this helps you
Let me know if you find any other solution
Thanks
Thank you, that did the trick!
I modified some of the fields, so here is the unified trigger/test that worked for me:
Here is the trigger:
And the test class: