trigger to populate/update these field on Account record whenever a new Contact, opportunity record is inserted or deleted

Question

Number fields on Account : "No of Contacts" Write trigger to populate/update field on Account record whenever a new Contact record is inserted or deleted.

All is working fine but whenever i am deleting last contact record it is not updating Account field from 1 to 0 Can anyone help me on this

trigger UpdateContactCountOnAccount on Contact (after insert, after update, after delete) 
{
    Set<Id> AccountIds = new Set<Id>();

    if(!Trigger.isDelete)
    {
        for (Contact ct : Trigger.new) 
        {        
            if(Trigger.isInsert && ct.AccountId != null)
            {
                AccountIds.add(ct.AccountId);
            }
            if(Trigger.isUpdate)
            {
                if(ct.AccountId==null && Trigger.oldMap.get(ct.Id).AccountId != null)
                {
                    AccountIds.add(Trigger.oldMap.get(ct.Id).AccountId);
                }
                if(ct.AccountId!=null && Trigger.oldMap.get(ct.Id).AccountId != null 
                                                                    && ct.AccountId != Trigger.oldMap.get(ct.Id).AccountId)
                {
                    AccountIds.add(ct.AccountId);
                    AccountIds.add(Trigger.oldMap.get(ct.Id).AccountId);
                }
                if(ct.AccountId!=null && Trigger.oldMap.get(ct.Id).AccountId == null)
                {
                    AccountIds.add(ct.AccountId);
                }
            }
        }
    }
    else
    {
        for (Contact ct : Trigger.old)
        {
            if(Trigger.isDelete && ct.AccountId != null)
            {
                AccountIds.add(ct.AccountId);
            }
        }   
    }

    List<Account> AcctToUpdate = new List<Account>();
    for (AggregateResult ar: [Select Count(Id) ContactCount, AccountId 
                              from Contact where AccountId IN: AccountIds GROUP BY AccountId])
    {
        Account tmp = new Account(Id=(Id)ar.get('AccountId'), No_of_Contacts__c=(Decimal)ar.get('ContactCount'));
        AcctToUpdate.add(tmp);
    }
    if(AcctToUpdate.size()>0) 
        update AcctToUpdate;
 }

All is working fine but whenever i am deleting last contact record it is not updating Account field from 1 to 0

eyescream eyescream · Accepted Answer · 2019-07-13T21:08:02

You're in "after delete". The row was deleted in database (transaction didn't commit yet but still, if you query you'll see the "new" state of things).

So take a step back and think, what do you expect to see in that SELECT statement when you're deleting last one? There are no Contacts related to this Account anymore -> no rows will be returned -> nothing to update.

I'd simplify your code a bit. Couple ideas, give them a go and see if they match your personal style.

On insert and undelete(! you forgot about restoring from recycle bin) add ids from trigger.new to the Set<Id>. On delete - add from trigger.old. On update - add from both old and new.
Forget null checking. You can simply do accountIds.remove(null); once everything is collected. before you're ready to query.
Try this SELECT Id, (SELECT Id FROM Contacts) FROM Account WHERE Id IN :accountIds. It should always return accounts, even if they have no associated contacts. And then you can
```
List<Account> accs = [SELECT ...];
for(Account a : accs){
    a.No_of_Contacts__c = a.Contacts.size();
}
update accs;
```

That related list of contacts will always be there. it might be empty (size = 0) but it'll never be null. Simpler, eh?

There could be more tweaks to it, to count based on current scope of triggers (you don't really have to query all contacts related to these accounts, right? Especially if in future there could be thousands of them. You could just look at trigger's current scope, because that's what's changing and decide "ok, add 7 to current number, job done". That's a battle for another day ;)

trigger to populate/update these field on Account record whenever a new Contact, opportunity record is inserted or deleted

1 Answers