Limiting number of records created through Salesforce trigger

Question

I am practicing building triggers in Salesforce. I have a trigger that creates an opportunity record automatically when an account record's Type field is "Prospect". How can I take this trigger further by limiting the number of opportunities created to just one? For instance, every time a person updates the account record to "Prospect", it will create a new opportunity which I do not want since one was created already.

trigger createOppty on Account (after update) {
    list <Opportunity> Opptylist = new list <Opportunity>();

    for (Account o : trigger.new){
        if (o.Type == 'Prospect') {
            Opportunity Oppty = new Opportunity(
               AccountId = o.Id,
               Name = 'Edit Opportunity',
               CloseDate = system.today(),
               StageName = 'Prospecting');
            Opptylist.add(Oppty);

        }
        insert Opptylist;
     }
}

Answer 1

One approach is, you can check if the account already has an opportunity before creating a new one for it.

trigger createOppty on Account (after update) {

    Map<Id, Opportunity> opportunitiesByAccountId = new Map<Id, Opportunity>();
    for(Opportunity o : [SELECT Id, AccountId FROM Opportunity WHERE AccountId IN :Trigger.new]) {
        opportunitiesByAccountId.put(o.AccountId, o);
    }

    list <Opportunity> Opptylist = new list <Opportunity>();

    for (Account o : trigger.new){
        if (o.Type == 'Prospect' && !opportunitiesByAccountId.containsKey(o.Id)) {
            Opportunity Oppty = new Opportunity(
               AccountId = o.Id,
               Name = 'Edit Opportunity',
               CloseDate = system.today(),
               StageName = 'Prospecting');
            Opptylist.add(Oppty);

        }
     }
     insert Opptylist;
}

Another is to check if the account's update include changing its type from something else to 'Prospect'. This way, an opportunity will be created for the account when it's type changes to 'Prospect' but not with any other updates.

trigger createOppty on Account (after update) {
    list <Opportunity> Opptylist = new list <Opportunity>();

    for (Account o : trigger.new){
        if (o.Type == 'Prospect' && Trigger.oldMap.get(o.Id).Type != 'Prospect') {
            Opportunity Oppty = new Opportunity(
               AccountId = o.Id,
               Name = 'Edit Opportunity',
               CloseDate = system.today(),
               StageName = 'Prospecting');
            Opptylist.add(Oppty);

        }
     }
     insert Opptylist;
}

If you are never going to change the type of the account once you make it 'Prospect', then the second approach is better. Otherwise it will create a new opportunity everytime an account's type changes to 'Prospect'.

If the type of the account can change from something else -> Prospect -> something else -> Prospect multiple times, then go with the first approach.

Note: insert Opptylist should be outside the for loop.

Answer 2

You can query for the Opportunity first to see if it exists. How you filter this will depend on business rules. Here's an example of just checking to see if they Account is already tied to an open opportunity with a stage of 'Prospecting' (also, you should move your insert outside of the for loop):

trigger createOppty on Account (after update) {

    list <Opportunity> Opptylist = new list <Opportunity>();

    // get the accounts that were triggered and include child opportunities
    // that are open and in prospecting stage
    Map<Id, Account> accountsWithOpps = new Map<Id, Account>([
        select Id, (select Id from from Opportunities where StageName = 'Prospecting' and IsClosed = false) 
        from Account
        where Id in :Trigger.newMap.keySet()
    ]);

    for (Account o : trigger.new){

        // if the account is prospect and there were not existing opps, create new opp
        if (o.Type == 'Prospect' && accountsWithOpps.get(o.Id).Opportunities.size() < 1) {

            Opportunity Oppty = new Opportunity(
                AccountId = o.Id,
                Name = 'Edit Opportunity',
                CloseDate = system.today(),
                StageName = 'Prospecting');
            Opptylist.add(Oppty);

        }    
    }

    insert Opptylist;
}