0

my customer service people are using LiveHelpNow and after chat window closes I've the agent name from Live help now mapped to case.Case_Owner__c custom text field. I'm writing a trigger that will update the Default case Owner(user lookup standard field) based on searching the User object and matching with the text value in the Case.Case_Owener__c custom field. and here is the code, but I believe the last part is giving SOQL query error of Limit issue 50001.

trigger livehelp on Case (before insert, before update) {
set<Id> cas = new set<Id>();
set<String> ownername = new set<String>();
for(case c:Trigger.new)
{
    cas.add(c.Id);
    ownername.add(c.Case_owner__c);
}

List<user> allUsers = [SELECT ID, name From User Where name =:ownername];
map<string,Id> userIds = new map<string, Id>();
for(user userIdStore: allUsers)
{
    userIds.put(userIdStore.Name, userIdStore.Id);
}
list<case> cases = [select Id, Case_owner__c from case];
for(case ca : cases)
{
    if(userIds.containsKey(ca.Case_owner__c))
    {
        ca.OwnerId = userIds.get(ca.Case_Owner__c);
    }
    else
    {
        ca.OwnerId = null;
    }
}
update cases;

}

  • Why are you getting every single case at list<case> cases = [select Id, Case_owner__c from case]; – EricSSH Jun 20 '16 at 15:21
0

Your code has a number of problems.

Null Owner

You can't set OwnerId to null. Don't even try.

Unlimited Queries

You're querying every case in the system. This won't scale well, as you've observed.

Alternate Assignment

If you set up an assignment rule, it will take priority over your trigger.

Null Case Id

Before insert, there won't be a case Id. Similarly, you can't query for the records in Trigger.new in a "before insert" trigger because they do not yet exist in the database.

Infinite Recursion

Using an update as you've done would result in an infinite loop. However, since you're using "before insert/before update", you don't need to do another DML, because records in Trigger.new that are modified will result in the changes being saved to the database.


That said, I see what you're trying to do, so assuming you don't have an assignment rule, your code might look like:

Map<String, Id> ownerUser = new Map<String, Id>();
for(Case record: Trigger.new) {
    ownerUser.put(record.Case_Owner__c, null);
}
for(User record: [SELECT Name FROM User WHERE Name IN :ownerUser.keySet()]) {
    ownerUser.put(record.Name, record.Id);
}
for(Case record: Trigger.new) {
    if(ownerUser.get(record.Case_Owner__c) != null) {
        record.OwnerId = ownerUser.get(record.Case_Owner__c);
    }
}
  • 1st thanks for all your help. 2nd the case is not being created and no error. 3rd if I try to change the before insert to after insert or after update I get "Error:Apex trigger LiveHelpCaseOwner caused an unexpected exception, contact your administrator: LiveHelpCaseOwner: System.LimitException: Too many query rows: 50001" – Templar Jun 20 '16 at 16:02
  • @sfdfox it is working now, it was due to other triggers interfering with this one. Thanks for your contribution. – Templar Jun 20 '16 at 16:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.