How to calculate no of opportunitycontactroles on opportunity

Question

I am trying to calculate no of opportunitycontactroles on opportunity object but it is not working. Below is the trigger -

trigger fulfillmentcontactcreatedopp on Opportunity (after update) {
    Map<Id, Opportunity> oppsToCheckMap = new Map<Id, Opportunity>();

    for(Opportunity opp : trigger.new) {

        oppsToCheckMap.put(opp.Id, opp);

    List<AggregateResult> result = [select OpportunityId,count(id) from OpportunityContactRole where OpportunityId in:oppsToCheckMap.keySet() group by OpportunityId];
    Integer count = result.size();

    opp.No_Of_Contact__c = count;    

    }      
}

Answer 1

You need to use before update since you are trying to update same object record.

You can directly query and update the opportunity No_Of_Contact__c field

 trigger fulfillmentcontactcreatedopp on Opportunity (before update) {    
        map<Id, Opportunity> mapOpportunity = trigger.Newmap;                
        // Use aggregate query to get the total count
        for(AggregateResult objAgg: [select OpportunityId, count(id) totalCount 
                                       from OpportunityContactRole 
                                      where OpportunityId in:mapOpportunity.keySet() 
                                   group by OpportunityId] )
        {
          mapOpportunity.get((Id)objAgg.get('OpportunityId')).No_Of_Contact__c = (Integer)objAgg.get('totalCount') ;
        }   
    }

Updates

Since we can't create trigger on opportunitycontactrole SO you can create a batch and that batch will count all child opportunitycontactrole

You can run this batch hourly basis or 1/2 hourly basis your wish

global class UpdateOpportunityFields implements Database.Batchable<sObject>{ 

global Database.QueryLocator start(Database.BatchableContext BC){ 
       return Database.getQueryLocator('SELECT Id, No_Of_Contact__c FROM Opportunity'); 
} 

global void execute(Database.BatchableContext BC, List<Opportunity> scope){ 

      map<Id, Opportunity> mapOpportunity = new map<Id, Opportunity>(scope); 

      for(AggregateResult objAgg: [select OpportunityId, count(id) totalCount 
                                     from OpportunityContactRole 
                                    where OpportunityId in:scope 
                                    group by OpportunityId] ) 
      { 
            mapOpportunity.get((Id)objAgg.get('OpportunityId')).No_Of_Contact__c = (Integer)objAgg.get('totalCount') ; 
      } 
      update mapOpportunity.values(); 
} 

 global void finish(Database.BatchableContext BC){ } 
}