Implement a trigger to ensure that no more than one Opportunity can be created per Account within a single day.

/ Trigger Scenarios / By Ntsh

Implement a trigger to ensure that no more than one Opportunity can be created per Account within a single day.

This scenario was asked in Deloitte interview -If a user attempts to create a second Opportunity for the same Account on the same day, display an appropriate error message.

Trigger

trigger OpportunityRestrictionTrigger on Opportunity (before insert) {
    OpportunityRestrictionHandler.restrictDailyOpportunityCreation(trigger.new);
}

Apex Class

public class OpportunityRestrictionHandler{
    public static void restrictDailyOpportunityCreation(List<Opportunity> newOpps){
        Set<Id> accIds = new Set<Id>();

        for(Opportunity opp : newOpps){
            if(opp.AccountId != null) {
                accIds.add(opp.AccountId);
            }
        }

        if(accIds.isEmpty()) return;

        Map<Id, Account> accOppMap = new Map<Id, Account>(
            [SELECT Id, (SELECT Id FROM Opportunities WHERE CreatedDate = TODAY) 
             FROM Account 
             WHERE Id IN :accIds]
        );

        for(Opportunity opp : newOpps) {
            if(opp.AccountId != null) {
                Account acc = accOppMap.get(opp.AccountId);
                if(acc != null && acc.Opportunities != null && !acc.Opportunities.isEmpty()) {
                    opp.addError('An Opportunity has already been created for this Account today. Please try again tomorrow.');
                }
            }
        }
    }
}

Alternative Approach – We can collect all Account Ids from the incoming Opportunities into a Set, and then perform a single query to fetch all existing Opportunities created today for those Account IDs. This way, we avoid navigating through nested subqueries and simplify our validation logic by working directly with flat Opportunity records

public class OpportunityRestrictionHandler{
    public static void restrictDailyOpportunityCreation(List<Opportunity> newOpps){
        Set<Id> accIds = new Set<Id>();

        for(Opportunity opp : newOpps){
            if(opp.AccountId != null) {
                accIds.add(opp.AccountId);
            }
        }

        if(accIds.isEmpty()) return;
        
        Map<Id, List<Opportunity>> todaysOppsMap = new Map<Id, List<Opportunity>>();
        for (Opportunity opp : [
            SELECT Id, AccountId 
            FROM Opportunity 
            WHERE AccountId IN :accIds 
            AND CreatedDate = TODAY
        ]) {
            if (!todaysOppsMap.containsKey(opp.AccountId)) {
                todaysOppsMap.put(opp.AccountId, new List<Opportunity>());
            }
            todaysOppsMap.get(opp.AccountId).add(opp);
        }

        for (Opportunity opp : newOpps) {
            if (opp.AccountId != null && todaysOppsMap.containsKey(opp.AccountId)) {
                opp.addError('An Opportunity has already been created for this Account today. Please try again tomorrow.');
            }
        }
    }
}