You need to sign in to do that
Don't have an account?
Email Message Trigger interacting with installed App and giving too many SOQL Queries Error
I have a trigger that I created on Email Message that populates a date in the parent case of the most recent email message created date. This allows me to run a workflow that reopens a case if an email comes in after the case has closed. Here is the code for the trigger.
trigger LastEmailDateTime on EmailMessage (after insert) { EmailMessage email = Trigger.new[0]; //Populate the case with the Last EmailMessage CreatedDate value for(EmailMessage em:[Select CreatedDate, ParentID from EmailMessage where Id = :email.Id limit 1]){ for(Case c:[Select Id, Last_Email_Date_Time__c from Case where Id = :em.ParentId limit 1]){ c.Last_Email_Date_Time__c = em.CreatedDate; update c; } } }
I recently installed the Case Age in Business Hours app. Since I did that I am getting script exception errors for too many SOQL queries. Here is an example of the error I receive.
Developer script exception : LastEmailDateTime : LastEmailDateTime: execution of AfterInsert caused by: System.Exception: Too many SOQL queries: 22 Trigger.LastEmailDateTime: line 7, column 23
Any suggestions on how I can reduce the number of SOQL queries?
Thanks!
You don't need the 2nd query, you can just call update directly
}
But you do need to do something about the fact that you're only processing one record in your trigger, the Trigger.new array can be larger than one.
Thanks Simon! Is that what people are referring to when they talk about bulkifying? Can you direct me to where I can find directions on how to do this?
Thanks,
Christian
If I'm reading this correctly, you don't need either query but you are going to have to figure out what you want to do in the case where there are two email messages for the same case in one invocation of the trigger - unless the only path to create emailMessage objects prevents this somehow and that behavior is never going to change.
A better question might be what is "Case Age in Business Hours app." doing here. Is it really using 20 queries before this ever gets invoked or is it calling insert on EmailMessage 20 times somewhere?
Andrew,
I don't quite know what you mean by me not needing either query, I'm still new to apex so I'm open to any suggestion on another way to do this. If multiple emails cause one invocation of my trigger I just want the most recent email created date to populate into a field on the associated case. I think the only ways that a new Email Message is created is by Email2Case, through the UI, or through an import into the application.
Here is the trigger code on Case Age in Business Hours. There are 3 select queries, two are in for loops.
trigger CalculateBusinessHoursAges on Case (before insert, before update) {
if (Trigger.isInsert) {
for (Case updatedCase:System.Trigger.new) {
updatedCase.Last_Status_Change__c = System.now();
updatedCase.Time_With_Customer__c = 0;
updatedCase.Time_With_Support__c = 0;
}
} else {
//Get the stop statuses
Set<String> stopStatusSet = new Set<String>();
for (Stop_Status__c stopStatus:[Select Name From Stop_Status__c]) {
stopStatusSet.add(stopStatus.Name);
}
//Get the default business hours (we might need it)
BusinessHours defaultHours = [select Id from BusinessHours where IsDefault=true];
//Get the closed statuses (because at the point of this trigger Case.IsClosed won't be set yet)
Set<String> closedStatusSet = new Set<String>();
for (CaseStatus status:[Select MasterLabel From CaseStatus where IsClosed=true]) {
closedStatusSet.add(status.MasterLabel);
}
//For any case where the status is changed, recalc the business hours in the buckets
for (Case updatedCase:System.Trigger.new) {
Case oldCase = System.Trigger.oldMap.get(updatedCase.Id);
if (oldCase.Status!=updatedCase.Status && updatedCase.Last_Status_Change__c!=null) {
//OK, the status has changed
if (!oldCase.IsClosed) {
//We only update the buckets for open cases
//On the off-chance that the business hours on the case are null, use the default ones instead
Id hoursToUse = updatedCase.BusinessHoursId!=null?updatedCase.BusinessHoursId:defaultHours.Id;
//The diff method comes back in milliseconds, so we divide by 3600000 to get hours.
Double timeSinceLastStatus = BusinessHours.diff(hoursToUse, updatedCase.Last_Status_Change__c, System.now())/3600000.0;
System.debug(timeSinceLastStatus);
//We decide which bucket to add it to based on whether it was in a stop status before
if (stopStatusSet.contains(oldCase.Status)) {
updatedCase.Time_With_Customer__c += timeSinceLastStatus;
} else {
updatedCase.Time_With_Support__c += timeSinceLastStatus;
}
if (closedStatusSet.contains(updatedCase.Status)) {
updatedCase.Case_Age_In_Business_Hours__c = updatedCase.Time_With_Customer__c + updatedCase.Time_With_Support__c;
}
}
updatedCase.Last_Status_Change__c = System.now();
}
}
}
}
Thanks,
Christian
I'm saying, based on what I see in your trigger, you don't need to issue any queries because all you want to do is update the related case which you can do like this:
trigger LastEmailDateTime on EmailMessage (after insert) { Map<Id, Case> caseMap = new Map<Id,Case>(); for(EmailMessage e:Trigger.new) { caseMap.put(e.parentId, new Case(id = e.parentid, Last_Email_Date_Time__c = e.createdDate)); } Database.update(caseMap.values()); }
After more closely reviewing the use case, I don't think there is concern with having multiple in one trigger invocation since the createddate will be the same for all the emailmessage objects.
I don't have email2case functionality written and am not familiar with it so I wrote this test class to make sure the above does what is expected:
@IsTest private class emailCaseTests { static testmethod void basicTriggerTest() { /* Insert a few cases for the test */ List<Case> cases = new List<Case>(); for(Integer i=0;i<10;i++) { cases.add(new Case(Status = 'New', Origin = 'Email')); } Database.insert(cases); /* Now create emailmessage objects for them. */ List<EmailMessage> emails = new List<EmailMessage>(); for(Case c:cases) { emails.add(new EmailMessage(parentId = c.id)); } Test.startTest(); Database.insert(emails); Test.stopTest(); for(Case c:[select last_email_date_time__c, (select createddate from emailmessages) from case where id in :cases]) { System.assertEquals(c.emailMessages.get(0).createddate, c.last_email_date_time__c,'Trigger did not set the date on the case according to the email message.'); } } }
Thank you Andrew! I appreciate you showing me a better way to write that trigger. After changing the trigger and reinstalling the Case Age in Business Hours app I find I am still getting the "Too many SOQL queries:21" error.
Apex script unhandled trigger exception
LastEmailDateTime: execution of AfterInsert
caused by: System.DmlException: Update failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, CalculateBusinessHoursAges: execution of BeforeUpdate
caused by: System.Exception: Too many SOQL queries: 21
Trigger.CalculateBusinessHoursAges: line 20, column 32
Trigger.LastEmailDateTime: line 7, column 5
Is there some place I can look to find out what is triggering all of these SOQL queries?
Trigger.CalculateBusinessHoursAges: line 20, column 32 is referring to this line...
for (CaseStatus status:[Select MasterLabel From CaseStatus where IsClosed=true]) {
closedStatusSet.add(status.MasterLabel);
}
and Trigger.LastEmailDateTime: line 7, column 5 is referring to this line...
Database.update(caseMap.values());
I presume you are testing this by inserting an email messages somehow as the entry point. If so then are there any other triggers on email message? any others on Case?
From the your error it seems you are entering this query with 18 queries used up. Something is consuming them.
I don't have any other triggers on EmailMessage or Case so I'm not sure what might be doing the queries. I do have other triggers in my org but they are on account and opportunity. I haven't been able to figure out what action is creating the script exceptions.
I tried using the system log and so far I haven't been able to get any value from it. Do I just past a trigger into it and execute? When I do this I just get an error...
I have added a few users to the debug logs so hopefully the next script exception will show up in my debug logs to help troubleshoot.
I'll let you know.
Thanks,
Christian
Andrew, a script exception has occurred and showed up in my debug logs. It appears to be started by an Email2Case. In this exception it looks like 5 cases were created, although I can't find them in the DB under the IDs in the debug log.
Here is the order that appears to be happening.
Case Trigger Begins - 5 Ids, this accounts for 3 SOQL queries
Case Trigger Ends
Task Trigger Begins
Task Tigger Ends
Email Message Trigger Begins
Case Trigger Begins - Case ID #1, this accounts for 3 SOQL queries
Case Trigger Ends
Email Message Trigger Ends
Task Trigger Begins
Task Trigger Ends
Email Message Trigger Begins
Case Trigger Begins - Case ID #2, this accounts for 3 SOQL queries
Case Trigger Ends
Email Message Trigger Ends
And this repeats for the 5 iterations which would be 18 SOQL queries plus the original 3 putting me over the limit of 20.
How can I prevent this from happening?
Thanks,
Christian
Does that process create tasks one at a time or does it perform one bulk task operation for all the cases?
It is possible to have both, but only the client Email To Case would create multiple cases at once (whereas On Demand Email To Case creates the cases as it gets the emails).
In any case I think the issue is that you have not bulkified your update statement -- you should be keeping a list of cases to update and then do that update last and on that list. Then Case Age In Business Hours will do its 3 queries but will calculate the age for all the cases at once.
Are you sure the code mtbclimber gave you is in fact the trigger that's being called here?
Yes, the Email Message trigger that is showing up in my debug log is the code that I got from mtbclimber, or at least it has the exact same name.
So are you saying that my update dml in the Email Message trigger needs to be bulkified?
So we're investigating the possibility that the on-premise email2case functionality could be processing multiple records row-by-row rather than in bulk on our side which means you have to either alter your client processing to never send more than one at a time or wait for us to fix that on our side. As I said this is under investigation so no conclusion yet but you are free to try changing your client processing if it's not inconvenient to do so.
When an exception occurs in an apex request that is unhandled there is no commit so cases are not being created when this happens.
In fact it does appear that the old Email To Case client, which you are evidently using here, processes emails row-by-row. Any chance you could migrate that email address to On Demand Email To Case? On Demand Email To Case would not have this issue.
The alternative is to rewrite Case Age In Business Hours to work given the fact that multiple cases could be expected in a single transaction. Such a thing is possible actually, and it's something I've been meaning to do for a while, although I can't guarantee I'll have time for it immediately (I am the guy who wrote it in the first place). I'm surprised no one else has reported this issue before, but it could be because not that many people still use the old Email To Case client.
Thanks for getting back to me on this. We changed to On-Demand Email-to-Case but quickly reverted because this meant that we were no longer able to filter out SPAM and Out of Office email which is something we do on our exchange server, unless you know of a way to do this even though this email traffic is no longer getting as deep as our exchange server.
We are attempting to drop the amount of time that the interval for standard Email-to-Case pushes email into Saleforce. Do you think this could help limit the problem since less cases would be created at one time?
You should be redirecting emails post-spam and post-filters (like OOO) to On Demand Email To Case, and as such it should work just like your Email To Case client. Basically let the traffic go to the Exchange server but redirect it from there. That would probably be the best choice.
You can up the refresh rate on client Email To Case but the minimum is 1 minute I think, and it's not guaranteed to solve the problem. Incidentally I'd expect that your cases are still created just fine even when the issue does occur, just that for those cases the Case Age In Business Hours fields will be empty.