Hello,

I'm having difficultly with a trigger that we are running in our instance of salesforce. It runs perfectly in test, but gives me an error in production. I didn't write this, so I was hoping that someone with more experiance than I have could help me figure out where the error is, and what I need to do to fix it. Any help would be appreciated!

Thanks,

Ryan

The error that I recieve is:
NVMTaskTrigger: execution of AfterUpdate
 
caused by: System.QueryException: Aggregate query has too many rows for direct assignment, use FOR loop
 
Class.ActivityUtils.updateActivityHistory: line 75, column 1
Class.ActivityUtils.updateAccountActivityCount: line 28, column 1
Trigger.NVMTaskTrigger: line 9, column 1

Trigger:
trigger NVMTaskTrigger on Task (after insert, after update, after delete, after undelete) {
     
    sObject[] triggerRecords;
    if(!trigger.isDelete) triggerRecords = trigger.new;
    else triggerRecords = trigger.old;
     
    //Update Open Activity Count
    ActivityUtils au = new ActivityUtils(triggerRecords);
    au.updateAccountActivityCount();
    au.updateContactActivityCount();
    au.updateLeadActivityCount();
    au.updateOpportunityActivityCount();
    au.updateCaseActivityCount();
}

Class:
public class ActivityUtils {
     
    //config
     
    String fieldToUpdate = 'NVM_Activities__c'; //this field must be added to each object we're updating
    String fieldOpenToUpdate = 'Open_Activity_Count__c'; //this field must be added to each object we're updating
     
    //state
    set<id> accountIds;
    set<id> contactIds;
    set<id> opportunityIds;
    set<id> leadIds;
    set<id> caseIds;
     
    public ActivityUtils(sObject[] records) {
        accountIds = new set<id>();
        contactIds = new set<id>();
        opportunityIds = new set<id>();
        leadIds = new set<id>();
        caseIds = new set<id>();
        captureWhatAndWhoIds(records);
        addAccountIdsFromRlatedObjects();
    }
     
    public void updateAccountActivityCount() {
        if(accountIds.size() == 0) return;
        updateActivityCount('Account','WhatId', getStringFromIdSet(accountIds));
         updateActivityHistory('Account','WhatId', getStringFromIdSet(accountIds));
         
         
    }
    public void updateContactActivityCount() {
        if(contactIds.size() == 0) return;
        updateActivityCount('Contact','WhoId', getStringFromIdSet(contactIds));
                updateActivityHistory('Contact','WhoId', getStringFromIdSet(contactIds));
 
    }
    public void updateOpportunityActivityCount() {
        if(opportunityIds.size() == 0) return;
        updateActivityCount('Opportunity','WhatId', getStringFromIdSet(opportunityIds));
                updateActivityHistory('Opportunity','WhatId', getStringFromIdSet(opportunityIds));
 
    }
    public void updateLeadActivityCount() {
        if(leadIds.size() == 0) return;
        updateActivityCount('Lead','WhoId', getStringFromIdSet(leadIds));
                updateActivityHistory('Lead','WhoId', getStringFromIdSet(leadIds));
 
    }
    public void updateCaseActivityCount() {
        if(caseIds.size() == 0) return;
        //updateActivityCount('Case','WhoId', getStringFromIdSet(caseIds));
        //updateActivityHistory('Case','WhoId', getStringFromIdSet(caseIds));
 
    }
    private void updateActivityCount(String objToUpdate, String queryFld, String updateIds) {
        string strQuery = 'SELECT Id, (SELECT Id FROM OpenActivities) FROM ' + objToUpdate + ' WHERE Id IN (' + updateIds + ')';
        sObject[] sobjects = new list<sobject>();
        for(sObject so : database.query(strQuery)) {
            OpenActivity[] oActivities = so.getSObjects('OpenActivities');
            Integer openActivityCount = oActivities == null ? 0 : oActivities.size();
            sObject obj = createObject(objToUpdate, so.Id);
            obj.put(fieldOpenToUpdate, openActivityCount);
            sobjects.add(obj);
            system.debug('openActivityCount: ' + openActivityCount);
        }
        update sobjects;
    }
      
    private void updateActivityHistory(String objToUpdate, String queryFld, String updateIds) {
        string strQuery = 'SELECT Id, (SELECT Id FROM ActivityHistories) FROM ' + objToUpdate + ' WHERE Id IN (' + updateIds + ')';        
System.debug(strQuery);
        sObject[] sobjects = new list<sobject>();
        for(sObject so : database.query(strQuery)) {
            ActivityHistory[] oActivities = so.getSObjects('ActivityHistories');
            Integer closedActivityCount = oActivities == null ? 0 : oActivities.size();
            sObject obj = createObject(objToUpdate, so.Id);
            obj.put(fieldToUpdate, closedActivityCount);
            sobjects.add(obj);
            system.debug('ActivityHistoryCount: ' + closedActivityCount);
        }
        update sobjects;
    }
     
    private void captureWhatAndWhoIds(sObject[] objects) {
        for(sObject o : objects) {
            Id whatId = (Id)o.get('WhatId');
            Id whoId = (Id)o.get('WhoId');
            if(whatId != null) {
                String objectName = getObjectNameFromId(whatId);
                if(objectName == 'account') accountIds.add(whatId);
                if(objectName == 'opportunity') opportunityIds.add(whatId);
            }
            if(whoId != null) {
                String objectName = getObjectNameFromId(whoId);
                if(objectName == 'contact') contactIds.add(whoId);
                if(objectName == 'lead') leadIds.add(whoId);
            }
        }
    }
     
    private void addAccountIdsFromRlatedObjects() {
        for(Opportunity o : [SELECT AccountId FROM Opportunity WHERE Id IN :opportunityIds]) accountIds.add(o.AccountId);
        for(Contact c : [SELECT AccountId FROM Contact WHERE Id IN :contactIds]) accountIds.add(c.AccountId);
    }
     
    private String getObjectNameFromId(Id objId) {
        String preFix = String.valueOf(objId).left(3).toLowercase();
        if(prefix == '001') return 'account';
        if(prefix == '003') return 'contact';
        if(prefix == '006') return 'opportunity';
        if(prefix == '00q') return 'lead';
        //if(prefix == '500') return 'case';
        return '';
    }
     
    private String getStringFromIdSet(set<id> idSet) {
        string idString = '';
        for(Id i : idSet) idString+= '\'' + i + '\',';
        return idString == '' ? idString : idString.left(idString.length()-1); //If idString contains some ids we want to ensure we strip out the last comma
    }
     
    //The main part of the method below was taken from //Taken from http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_dynamic_dml.htm
    //However we've modified this to accept an object id
    private sObject createObject(String typeName, Id objId) {
        Schema.SObjectType targetType = Schema.getGlobalDescribe().get(typeName);
        if (targetType == null) {
            // throw an exception
        }
         
        // Instantiate an sObject with the type passed in as an argument
        //  at run time.
        return targetType.newSObject(objId);
    }
     
}

Hello,

I'm having difficultly with a trigger that we are running in our instance of salesforce. It runs perfectly in test, but gives me an error in production. I didn't write this, so I was hoping that someone with more experiance than I have could help me figure out where the error is, and what I need to do to fix it. Any help would be appreciated!

Thanks,

Ryan

The error that I recieve is:
NVMTaskTrigger: execution of AfterUpdate
 
caused by: System.QueryException: Aggregate query has too many rows for direct assignment, use FOR loop
 
Class.ActivityUtils.updateActivityHistory: line 75, column 1
Class.ActivityUtils.updateAccountActivityCount: line 28, column 1
Trigger.NVMTaskTrigger: line 9, column 1

Trigger:
trigger NVMTaskTrigger on Task (after insert, after update, after delete, after undelete) {
     
    sObject[] triggerRecords;
    if(!trigger.isDelete) triggerRecords = trigger.new;
    else triggerRecords = trigger.old;
     
    //Update Open Activity Count
    ActivityUtils au = new ActivityUtils(triggerRecords);
    au.updateAccountActivityCount();
    au.updateContactActivityCount();
    au.updateLeadActivityCount();
    au.updateOpportunityActivityCount();
    au.updateCaseActivityCount();
}

Class:
public class ActivityUtils {
     
    //config
     
    String fieldToUpdate = 'NVM_Activities__c'; //this field must be added to each object we're updating
    String fieldOpenToUpdate = 'Open_Activity_Count__c'; //this field must be added to each object we're updating
     
    //state
    set<id> accountIds;
    set<id> contactIds;
    set<id> opportunityIds;
    set<id> leadIds;
    set<id> caseIds;
     
    public ActivityUtils(sObject[] records) {
        accountIds = new set<id>();
        contactIds = new set<id>();
        opportunityIds = new set<id>();
        leadIds = new set<id>();
        caseIds = new set<id>();
        captureWhatAndWhoIds(records);
        addAccountIdsFromRlatedObjects();
    }
     
    public void updateAccountActivityCount() {
        if(accountIds.size() == 0) return;
        updateActivityCount('Account','WhatId', getStringFromIdSet(accountIds));
         updateActivityHistory('Account','WhatId', getStringFromIdSet(accountIds));
         
         
    }
    public void updateContactActivityCount() {
        if(contactIds.size() == 0) return;
        updateActivityCount('Contact','WhoId', getStringFromIdSet(contactIds));
                updateActivityHistory('Contact','WhoId', getStringFromIdSet(contactIds));
 
    }
    public void updateOpportunityActivityCount() {
        if(opportunityIds.size() == 0) return;
        updateActivityCount('Opportunity','WhatId', getStringFromIdSet(opportunityIds));
                updateActivityHistory('Opportunity','WhatId', getStringFromIdSet(opportunityIds));
 
    }
    public void updateLeadActivityCount() {
        if(leadIds.size() == 0) return;
        updateActivityCount('Lead','WhoId', getStringFromIdSet(leadIds));
                updateActivityHistory('Lead','WhoId', getStringFromIdSet(leadIds));
 
    }
    public void updateCaseActivityCount() {
        if(caseIds.size() == 0) return;
        //updateActivityCount('Case','WhoId', getStringFromIdSet(caseIds));
        //updateActivityHistory('Case','WhoId', getStringFromIdSet(caseIds));
 
    }
    private void updateActivityCount(String objToUpdate, String queryFld, String updateIds) {
        string strQuery = 'SELECT Id, (SELECT Id FROM OpenActivities) FROM ' + objToUpdate + ' WHERE Id IN (' + updateIds + ')';
        sObject[] sobjects = new list<sobject>();
        for(sObject so : database.query(strQuery)) {
            OpenActivity[] oActivities = so.getSObjects('OpenActivities');
            Integer openActivityCount = oActivities == null ? 0 : oActivities.size();
            sObject obj = createObject(objToUpdate, so.Id);
            obj.put(fieldOpenToUpdate, openActivityCount);
            sobjects.add(obj);
            system.debug('openActivityCount: ' + openActivityCount);
        }
        update sobjects;
    }
      
    private void updateActivityHistory(String objToUpdate, String queryFld, String updateIds) {
        string strQuery = 'SELECT Id, (SELECT Id FROM ActivityHistories) FROM ' + objToUpdate + ' WHERE Id IN (' + updateIds + ')';        
System.debug(strQuery);
        sObject[] sobjects = new list<sobject>();
        for(sObject so : database.query(strQuery)) {
            ActivityHistory[] oActivities = so.getSObjects('ActivityHistories');
            Integer closedActivityCount = oActivities == null ? 0 : oActivities.size();
            sObject obj = createObject(objToUpdate, so.Id);
            obj.put(fieldToUpdate, closedActivityCount);
            sobjects.add(obj);
            system.debug('ActivityHistoryCount: ' + closedActivityCount);
        }
        update sobjects;
    }
     
    private void captureWhatAndWhoIds(sObject[] objects) {
        for(sObject o : objects) {
            Id whatId = (Id)o.get('WhatId');
            Id whoId = (Id)o.get('WhoId');
            if(whatId != null) {
                String objectName = getObjectNameFromId(whatId);
                if(objectName == 'account') accountIds.add(whatId);
                if(objectName == 'opportunity') opportunityIds.add(whatId);
            }
            if(whoId != null) {
                String objectName = getObjectNameFromId(whoId);
                if(objectName == 'contact') contactIds.add(whoId);
                if(objectName == 'lead') leadIds.add(whoId);
            }
        }
    }
     
    private void addAccountIdsFromRlatedObjects() {
        for(Opportunity o : [SELECT AccountId FROM Opportunity WHERE Id IN :opportunityIds]) accountIds.add(o.AccountId);
        for(Contact c : [SELECT AccountId FROM Contact WHERE Id IN :contactIds]) accountIds.add(c.AccountId);
    }
     
    private String getObjectNameFromId(Id objId) {
        String preFix = String.valueOf(objId).left(3).toLowercase();
        if(prefix == '001') return 'account';
        if(prefix == '003') return 'contact';
        if(prefix == '006') return 'opportunity';
        if(prefix == '00q') return 'lead';
        //if(prefix == '500') return 'case';
        return '';
    }
     
    private String getStringFromIdSet(set<id> idSet) {
        string idString = '';
        for(Id i : idSet) idString+= '\'' + i + '\',';
        return idString == '' ? idString : idString.left(idString.length()-1); //If idString contains some ids we want to ensure we strip out the last comma
    }
     
    //The main part of the method below was taken from //Taken from http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_dynamic_dml.htm
    //However we've modified this to accept an object id
    private sObject createObject(String typeName, Id objId) {
        Schema.SObjectType targetType = Schema.getGlobalDescribe().get(typeName);
        if (targetType == null) {
            // throw an exception
        }
         
        // Instantiate an sObject with the type passed in as an argument
        //  at run time.
        return targetType.newSObject(objId);
    }
     
}