Apex Validation Rules bypass for Child records during Bulk Insert/Update

Question

There is custom object Member__c which has lookup relationship with Opportunity. Member object has a Custom Picklist field 'Role'. This Role field has certain value. User can create multiple Team Member records.

Our Business has introduced new requirements where User should not create More than 2 records of Team Member with Role as 'Wealth' and more than 1 records of Family wealth.Wealth and Family Wealth are type of roles on Member object.

Below is the code I have written on Before Insert and Before Update of Member__c object:

public void validationRule(List<sObject> newItems,Map<Id,SObject> mapOldItems){
    set<string> OpportunityId_set = new set<string>();
    Map<String,Integer> mapOfRoleCount = new Map<String,Integer>();
    for(SObject newObj :newItems){
        Member__c newMemberDetailsObj = (Member__c)newObj;
        Member__c oldMemberDetailsObj = null;            
        if(newMemberDetailsObj.Id != null && mapOldItems != null){
            oldMemberDetailsObj = (Member__c)mapOldItems.get(newMemberDetailsObj.Id);
        }
        if(newMemberDetailsObj.Opportunity__c <> null){
            OpportunityId_set.add(newMemberDetailsObj.Opportunity__c);  
        }

    }
    if(!OpportunityId_set.isEmpty()){
        for(AggregateResult ag:[Select Opportunity__c optId,day_only(createdDate),Count(Id) roleCount from Member__c where Opportunity__c in:Opportunity__c AND
                                Role__c='Wealth' group by day_only(createdDate),Opportunty__c Having Count(Id)>2 order by day_only(createdDate) DESC]){
                                    mapOfRoleCount.put(String.valueOf((Id)ag.get('optId')+'WS'),(Integer)ag.get('roleCount'));  
                                }
        for(AggregateResult ag:[Select Opportunity__c optId,day_only(createdDate),Count(Id) roleCount from Member__c where Opportunity__c in:Opportunity__c AND
                                Role__c='Family Wealth' group by day_only(createdDate),Opportunty__c Having Count(Id)>1 order by day_only(createdDate) DESC]){
                                    mapOfRoleCount.put(String.valueOf((Id)ag.get('optId')+'FWS'),(Integer)ag.get('roleCount')); 
                                }               
    }
    if(!mapOfRoleCount.isEmpty()){
        for(SObject newObj :newItems){
            Member__c newMemberRec= (Member__c)newObj;
            if(mapOfRoleCount.containKey(String.valueOf(newMemberRec.Opportunity__c)+'WS') && newMemberRec.newMemberRec__c='Wealth'){
                newObj.addError('Not more than 2 Wealth Records');
                break;
            }
            else if(mapOfRoleCount.containKey(String.valueOf(newMemberRec.Opportunity__c)+'FWS') && newMemberRec.newMemberRec__c='Family Wealth'){
                newObj.addError('Not more than 2 Wealth Records');
                break;
            }
        }   
    }
}

above code working fine when I manually create Member record if Opportunity already has 2 Wealth Records and same for Family Wealth then it throws validation which is as expected.

But it fails in below scenario:

On an Opportunity where is no Team Member Record of Wealth and Family Wealth. If I do bulk insert(3 Wealth and 3 Family Wealth) then Validation rules gets bypassed and all 6 records gets created while it should only create 2 records of wealth and 1 records of family wealth.

after doing some debug analysis , I have observed that I do not get any count in Aggregate Results. As in beforeinsert , Opportunity does not have Wealth and Family Wealth Count.

I tried to move the method on After Insert and After Update where AggregateResult gives me count but problem is in AggreagteResult i can not get RecordId as a key.As I am already using Count(Id) which is the reason in After insert and after update it throws unexpected error & unexpected result.

One of the error :

"Too many retries of batch save in the presence of Apex triggers with failures: when triggers are present partial save while loading Data requires that some subset of rows save without any errors in order to avoid inconsistent side effects from those triggers. Number of retries: 2"

As we make certain callouts to external system which might be the reason I see above error.

Can someone please suggest what changes needs to be done so that while doing Bulk Insert or Update if I Insert more than 2 Wealth and more than 1 Family Wealth then it should throws validation message and only First 3 records(1 Family Wealth and 2 Wealth Records gets inserted)

P.S. We have a batch that creates Member records. In Real life we do not create Member records manually.

Answer 1

Do not break the loop. What's happening is that you're processing until the first error, and it gets aborted, so the list is retried with the remaining records, which also gets an error, and then aborted, and then the third attempt happens, and an error here results in this exception. You need to try to add all the errors in a single pass, so that means processing every record even if an error is detected.

    for(SObject newObj :newItems){
        Member__c newMemberRec= (Member__c)newObj;
        if(mapOfRoleCount.containKey(String.valueOf(newMemberRec.Opportunity__c)+'WS') && newMemberRec.newMemberRec__c=='Wealth'){
            newObj.addError('Not more than 2 Wealth Records');
            continue; // Go to the next record to check
        }
        if(mapOfRoleCount.containKey(String.valueOf(newMemberRec.Opportunity__c)+'FWS') && newMemberRec.newMemberRec__c=='Family Wealth'){
            newObj.addError('Not more than 2 Wealth Records');
            continue; // Go to the next record to check
        }
    }   

The last continue is not necessary, of course, but I would consider a good pattern in case someone needs to add more rules later.