2

Trigger to update Custom field(Count_Of_Title_in_Opp__c) on Account record whenever a new opportunity record (Whose Title contains 'CEO') is inserted or deleted

Trigger

trigger OppSizeOnAccount on Opportunity (after insert, after update, after delete) 
{
    if(Trigger.isInsert)
    {
        OppSizeOnAccountHandler.insertHandler(Trigger.New);
    }
    if(Trigger.isDelete)
    {
        OppSizeOnAccountHandler.deleteHandler(Trigger.Old);
    }
    if(Trigger.isUpdate)
    {
        OppSizeOnAccountHandler.updateHandler(Trigger.New, Trigger.oldMap);
    }
}

Apex Class

public class OppSizeOnAccountHandler 
{
    public static void insertHandler(List<Opportunity> newOppList)
    {
        Set<Id> accIds = new Set<Id>();
        for(Opportunity oppSobj : newOppList)
        {
            accIds.add(oppSobj.AccountId);
        }
        List<Account> accList = [SELECT Id,(SELECT Id,Name FROM Opportunities WHERE Title__c LIKE '%CEO%') from Account WHERE Id IN:accIds];
        List<Opportunity> oppList = [SELECT Id, Name FROM Opportunity WHERE Title__c LIKE '%CEO%' AND AccountId IN:accIds];
        for(Account acctSobj : accList)
        {
            acctSobj.Count_Of_Title_in_Opp__c = oppList.size();
        }
        Update accList;
    }
    public static void deleteHandler(List<Opportunity> oldOppList)
    {
        System.debug('Hello Delete');
        Set<Id> accIds = new Set<Id>();
        for(Opportunity oppSobj : oldOppList)
        {
            accIds.add(oppSobj.AccountId);
        }
        List<Account> accList = [SELECT Id,(SELECT Id,Name FROM Opportunities WHERE Title__c LIKE '%CEO%') from Account WHERE Id IN:accIds];
        List<Opportunity> oppList = [SELECT Id, Name FROM Opportunity WHERE Title__c LIKE '%CEO%' AND AccountId IN:accIds];
        for(Account acctSobj : accList)
        {
            acctSobj.Count_Of_Title_in_Opp__c = oppList.size();
        }
        Update accList;
    }
    public static void updateHandler(List<Opportunity> newOppList, Map<Id, Opportunity> oldOppList)
    {
        Set<Id> accIds = new Set<Id>();
        Set<Id> oldAccountIds = new Set<Id>();

        for(Opportunity oppSobj : newOppList)
        {  
            if(oppSobj.AccountId != oldOppList.get(oppSobj.id).AccountId)
            {
                accIds.add(oppSobj.AccountId);
            }
            oldAccountIds.add(oldOppList.get(oppSobj.id).AccountId);    
        }  
        List<Account> accList = [SELECT Id,(SELECT Id,Name FROM Opportunities WHERE Title__c LIKE '%CEO%') from Account WHERE Id IN:accIds];
        List<Opportunity> oppList = [SELECT Id, Name FROM Opportunity WHERE Title__c LIKE '%CEO%' AND AccountId IN:accIds];
        if(!accIds.isEmpty())
        {  
            for(Account acctSobj : accList)
            {
                acctSobj.Count_Of_Title_in_Opp__c=oppList.size();
            }
            Update accList;
            //for Old Accounts and Opportunities
            List<Account> oldAccountList = [SELECT Id,(SELECT Id,Name FROM Opportunities WHERE Title__c LIKE '%CEO%') from Account WHERE Id IN:oldAccountIds];
            List<Opportunity> oldOpportunityList = [SELECT Id, Name FROM Opportunity WHERE Title__c LIKE '%CEO%' AND AccountId IN:oldAccountIds];

            for(Account acctSobj : oldAccountList)
            {
                acctSobj.Count_Of_Title_in_Opp__c=oldOpportunityList.size();
            }
            Update oldAccountList;
        }
    }
}

Everything is Working fine but if I click on Update Opportunity and remove the Title__c to Blank then Count is not decreasing. Is it because of SOQL in Update??Please Help.

3

Your code won't work in bulk (try using the Apex Data Loader to insert two or more opportunities on two or more accounts at once). In addition, you have a lot of redundant code by implementing a naive trigger handler class, and you have a lot of waste in your queries by not using an aggregate query call.

All that aside, the answer to your original question is that, unless AccountId changes, it won't end up in accIds, so the list will be empty, and the query returns no accounts (the other account Id values are in oldAccountIds, which is never used as part of a query).

Your code should look more like this:

public class OppSizeOnAccountHandler {
  // Private method; get account Ids from opportunity records
  static Set<Id> getAccountIds(Opportunity[] records) {
    Set<Id> accountIds = new Set<Id>();
    for(Opportunity record: records) {
      accountIds.add(record.AccountId);
    }
    // Ignore opportunities with no account
    accountIds.remove(null);
    return accountIds;
  }
  // private method to get counters for each account by using a query
  static Map<Id, AggregateResult> getCounts(Set<Id> accountIds) {
    return new Map<Id, AggregateResult>([
      SELECT AccountId Id, COUNT(Id) sum
      FROM Opportunity
      WHERE AccountId = :accountIds AND Title__c LIKE '%CEO%'
      GROUP BY AccountId
    ]);
  }
  // private method to update the account records
  static void updateAccountCounters(Set<Id> accountIds, Map<Id, AggregateResult> counters) {
    Account[] updates = new Account[0];
    for(Id accountId: accountIds) {
      AggregateResult result = counters.get(accountId);
      // If result is null, there are 0 matching records
      if(result == null) {
        updates.add(new Account(Id=accountId, Count_Of_Title_in_Opp__c=0));
      } else {
        updates.add(new Account(Id=accountId, Count_Of_Title_in_Opp__c=(Decimal)result.get('sum')));
      }
    }
    update updates;
  }
  static void countRecords(Opportunity[] recordList) {
    Set<Id> accountIds = getAccountIds(recordList);
    Map<Id, AggregateResult> results = getCounts(accountIds);
    updateAccountCounters(accountIds, results);
  }
  public static void insertHandler(Opportunity[] newRecords) {
    countRecords(newRecords);
  }
  public static void updatehandler(Opportunity[] newRecords, Opportunity[] oldRecords) {
    // Just combine old and new lists, then call main logic.
    Opportunity[] temp = new Opportunity[0];
    temp.addAll(newRecords);
    temp.addAll(oldRecords);
    countRecords(temp);
  }
  // Like insertHandler, but with Trigger.old
  public static void deleteHandler(Opportunity[] oldRecords) {
    countRecords(oldRecords);
  }
  // With undelete, we treat it as a new insert
  public static void undeleteHandler(Opportunity[] newRecords) {
    countRecords(newRecords);
  }
}

You'll need to adjust your trigger to account for the undelete handler, as well as not using Trigger.oldMap (Trigger.old will perform just fine here). We need to handle undelete triggers to make sure we handle the situation where an opportunity is deleted and then later restored from the recycle bin.

  • What is recordid in Set<Id> accountIds = getAccountIds(recordId); – Dave Jul 22 at 13:34
  • 3
    @Dave A typo. I went ahead and fixed it. – sfdcfox Jul 22 at 13:35
  • Thanks @sfdcfox, Trigger is working now with all Conditions. If I am not doing work in bulk, In that case what is wrong in my Code.. Is it because of if(!accIds.isEmpty()) Condition?? – Dave Jul 22 at 13:49
  • 1
    @Dave Yes, that was your original problem (mentioned i n the second paragraph); everything else is just stuff that would only have vexed you later when you realized what was happening. – sfdcfox Jul 22 at 13:55
  • Thank you for taking the time to explain this. I appreciate what you did. – Dave Jul 22 at 13:57
0

In your updateHandler method:

  • When you change the title but not account on opportunity, then if(oppSobj.AccountId != oldOppList.get(oppSobj.id).AccountId) will be false and so only oldAccountIds will be populated inside loop.
  • Because of above point, if(!accIds.isEmpty()) will be false and will not enter the condition and so nothing is updated.

Other Points:

  • acctSobj.Count_Of_Title_in_Opp__c=oppList.size(); is wrong because you are assingning the count of all opps list of all accounts to each account count field. This will fail in bulkification test. This should be acctSobj.Count_Of_Title_in_Opp__c=acctSobj.Opportunities.size();. So you should also remove oppList query.
    if(oppSobj.AccountId != oldOppList.get(oppSobj.id).AccountId)
            {
                accIds.add(oppSobj.AccountId);
            }
            oldAccountIds.add(oldOppList.get(oppSobj.id).AccountId);

Here, oldAccountIds will have both new and old acc Ids. So put else like below:

if(oppSobj.AccountId != oldOppList.get(oppSobj.id).AccountId)
        {
            accIds.add(oppSobj.AccountId);
        }
        else oldAccountIds.add(oldOppList.get(oppSobj.id).AccountId);
  • You dont need Update accList; and Update oldAccountList; both. Add both new and old to single list and have single DML.
  • I get it that problem is with if(!accIds.isEmpty()) Condition. and about other points You dont need Update accList; and Update oldAccountList; both. Add both new and old to single list and have single DML... While combining both with same DML trigger is not updating the Count if i remove the Account from Opportunity – Dave Jul 22 at 13:37

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.