Apex Code Development You need to sign in to do that
Don't have an account?
Sammy Shk Error System.LimitException: Too many SOQL queries: 101
New in Apex, Trying to update No.of total reviews and no.of open reviews on Patient_event__c from child records(Root_Cause_Analysis__c). The code works fine however when i run the test code, it throws error "System.LimitException: Too many SOQL queries: 101".
Trigger CommitteeEventReviewTrigger on Root_Cause_Analysis__c(After insert, After Update, After Delete,After Undelete)
{
Set<Id> setSEIds = new Set<Id>();
if(Trigger.isInsert || Trigger.isUndelete || Trigger.isUpdate )
{
for(Root_Cause_Analysis__c CER : Trigger.new)
{
setSEIds.add(CER.Patient_Safety_Incident__c);
}
}
if(Trigger.isDelete)
{
//To fix System.NullPointerException:Attempt to de-reference a null object
for(Root_Cause_Analysis__c CER : Trigger.old)
{
setSEIds.add(CER.Patient_Safety_Incident__c);
}
}
List<Patient_Event__c> listSE = [Select id,name,No_of_Commitee_Event_Reviews__c ,(Select id from Root_Cause_Analysis__r) from Patient_Event__c where Id in : setSEIds];
for(Patient_Event__c SE :listSE)
{
SE.No_of_Commitee_Event_Reviews__c = SE.Root_Cause_Analysis__r.size();
}
update listSE;
List<Patient_Event__c> listSEStatusOpen = [Select id,name,No_of_Open_Committee_event_Review__c,(Select id from Root_Cause_Analysis__r where Status__c ='New' OR Status__c ='Reopened' OR Status__c ='Investigation') from Patient_Event__c where Id in : listSE];
for(Patient_Event__c SEOpen :listSEStatusOpen)
{
SEOpen.No_of_Open_Committee_event_Review__c = SEOpen.Root_Cause_Analysis__r.size();
}
update listSEStatusOpen;
}
Trigger CommitteeEventReviewTrigger on Root_Cause_Analysis__c(After insert, After Update, After Delete,After Undelete)
{
Set<Id> setSEIds = new Set<Id>();
if(Trigger.isInsert || Trigger.isUndelete || Trigger.isUpdate )
{
for(Root_Cause_Analysis__c CER : Trigger.new)
{
setSEIds.add(CER.Patient_Safety_Incident__c);
}
}
if(Trigger.isDelete)
{
//To fix System.NullPointerException:Attempt to de-reference a null object
for(Root_Cause_Analysis__c CER : Trigger.old)
{
setSEIds.add(CER.Patient_Safety_Incident__c);
}
}
List<Patient_Event__c> listSE = [Select id,name,No_of_Commitee_Event_Reviews__c ,(Select id from Root_Cause_Analysis__r) from Patient_Event__c where Id in : setSEIds];
for(Patient_Event__c SE :listSE)
{
SE.No_of_Commitee_Event_Reviews__c = SE.Root_Cause_Analysis__r.size();
}
update listSE;
List<Patient_Event__c> listSEStatusOpen = [Select id,name,No_of_Open_Committee_event_Review__c,(Select id from Root_Cause_Analysis__r where Status__c ='New' OR Status__c ='Reopened' OR Status__c ='Investigation') from Patient_Event__c where Id in : listSE];
for(Patient_Event__c SEOpen :listSEStatusOpen)
{
SEOpen.No_of_Open_Committee_event_Review__c = SEOpen.Root_Cause_Analysis__r.size();
}
update listSEStatusOpen;
}
>> https://salesforce.stackexchange.com/questions/33975/help-with-too-many-soql-queries-101-how-to-debug-where-its-failing
As mentioned in the above link, to determine where the cause may be, you can use the debug logs.
First, go to Setup > monitoring > debug logs, set up a log for the user you are testing as, then modify the filters to set all categories to None, except Profiling, which should be set to Finest.
Next, recreate the error with the user you set up as the test user (this could be your user, if you can recreate the error).
Finally, go to the debug logs and check out the profiling information. It will tell you where the queries are being executed and how many times they were executed.
Just because you avoided queries in loops (as far as you can tell) doesn't mean there aren't bulkification issues elsewhere. For example, an inefficient set of DML operations might be calling triggers recursively, or more often than optimal.
Only by using the logs can you tell for certain which queries are problematic. If there are a bunch in the same area, it means you need to work backward to figure out which loop is causing the problem.
Finally, it's possible that a large DML might call many trigger instances (e.g. trying to update 5000 records means the same trigger is called 25 times), in which case you may need batch processing, or query caching.
Without specific logs, just looking at your own code that you think may be a problem might not help. Note also that the other answer points out bulkification issues, which definitely will run into any random type of governor limits; it's always in your best interest to group queries and DML operations into as few units as possible.
Let me know if it helps you and close your query by marking it as solved so that it can help others in the future.
Thanks.
List<Patient_Event__c> listSE = [Select id, name, No_of_Commitee_Event_Reviews__c , (Select id, Status__c from Root_Cause_Analysis__r)from Patient_Event__c where Id in : setSEIds];
for(Patient_Event__c patientEvent: listSE){
List<Root_Cause_Analysis__c> rootCauses = patientEvent.Root_Cause_Analysis__r;
System.debug(rootCauses.size());
for(Root_Cause_Analysis__c rootCause: rootCauses){
// logic to perform where i can get count of no.of open reviews
}
patientEvent.No_of_Commitee_Event_Reviews__c=patientEvent.Root_Cause_Analysis__r.size();
}
update listSE;
}