+ Start a Discussion
ColaCola 

Trigger on Opportunity to check all related Opportunities in Account

I'm currently trying to create a trigger that runs whenever an Opportunity is created or updated. The trigger needs to check all the other Opportunities related to the Account of the Opportunity being updated. It should check to see if any of the Opportunities have a StageName equal to 'Closed Won - One Time' or 'Closed Won - Recurring' and if so, it should update the account Type to 'Customer'. If none of the Opportunities are closed won, the Account Type should be 'Prospect'.

This is the code I have but I'm not sure if I'm querying the right things: 

trigger updateAccountIfOppCustomer on Opportunity (after insert, after update) {
    List<Account> accts = new List<Account>();
    List<Opportunity> opps = new List<Opportunity>(); 
    
    for (Opportunity opp : Trigger.new) {
        accts = [SELECT Id, Name, Type FROM Account WHERE Id =: opp.AccountId LIMIT 1]; 
        opps = [SELECT Id, AccountId, StageName, Account.Type FROM Opportunity WHERE AccountId =: opp.AccountId];
    }

    for (Account a : accts) {
        for (Opportunity o : opps) {
            if (o.StageName == 'Closed Won - One Time' || o.StageName == 'Closed Won - Recurring' || o.StageName == 'Customer Reseller') {
                if (a == null) {
                    a = new Account(Id = o.AccountId, name='TestingName');
                }
                a.Type = 'Customer'; 
            } else {
                a.Type = 'Prospect';
            }
        }
    }
    update accts;
}
Any help is much appreciated.

Cheers
Best Answer chosen by Cola
Deepak Kumar ShyoranDeepak Kumar Shyoran
The above method is wrong as you are using SOQL in for loop which is a bad practice instead you can use below code  to update all accont associated with Opportunites in Trigger

trigger updateAccountIfOppCustomer on Opportunity (after insert, after update) {
    
	List<Account> acctToUpdate = new List<Account>();
    List<Opportunity> opps = new List<Opportunity>(); 
    
	Set<Id> associatedAccId = new Set<Id>() ;
	for (Opportunity opp : Trigger.new) {
		associatedAccId.add(opp.AccountId) ;
	}
	
	opps = [SELECT Id, AccountId, StageName, Account.Type FROM Opportunity WHERE AccountId in =: associatedAccId];
    
	for (Opportunity opp : opps) {
            if (opp.StageName == 'Closed Won - One Time' || o.StageName == 'Closed Won - Recurring' || o.StageName == 'Customer Reseller') {   
				Opp.Account.Type  = 'Customer';
				acctToUpdate.add(Opp.Account) ; 
			}
           else {
                opp.Account.Type = 'Prospect';
				acctToUpdate.add(Opp.Account) ;
			}
    }
    update acctToUpdate;
}

Hope it'll help you.


All Answers

MikeGillMikeGill
You can do this one without code.

Here is how

1. Create a number field on the Opportunity (default = 0)
2. Create a roll up summary on the Account which sums this field
3. Create a WFR on the Opportunity which checks the Roll up summary field is greater than 0
4. Create a Cross-object field update which sets Customer type from Prospect to Customer

Something like that
Deepak Kumar ShyoranDeepak Kumar Shyoran
The above method is wrong as you are using SOQL in for loop which is a bad practice instead you can use below code  to update all accont associated with Opportunites in Trigger

trigger updateAccountIfOppCustomer on Opportunity (after insert, after update) {
    
	List<Account> acctToUpdate = new List<Account>();
    List<Opportunity> opps = new List<Opportunity>(); 
    
	Set<Id> associatedAccId = new Set<Id>() ;
	for (Opportunity opp : Trigger.new) {
		associatedAccId.add(opp.AccountId) ;
	}
	
	opps = [SELECT Id, AccountId, StageName, Account.Type FROM Opportunity WHERE AccountId in =: associatedAccId];
    
	for (Opportunity opp : opps) {
            if (opp.StageName == 'Closed Won - One Time' || o.StageName == 'Closed Won - Recurring' || o.StageName == 'Customer Reseller') {   
				Opp.Account.Type  = 'Customer';
				acctToUpdate.add(Opp.Account) ; 
			}
           else {
                opp.Account.Type = 'Prospect';
				acctToUpdate.add(Opp.Account) ;
			}
    }
    update acctToUpdate;
}

Hope it'll help you.


This was selected as the best answer
ColaCola
Thanks that worked! 
Nathan BartonNathan Barton
why not just do this through the process builder?

If Opportunity won, then Immediate Action:

Update Records
Record*
[Opportunity].Account ID

Criteria for Updating Records* 
Updated records meet all conditions

Filter the records you update based on these conditions
Account type = Prospect

Set new field values for the records you update
Account type = Customer
Mohammed Irfan MayanMohammed Irfan Mayan
Got to see this thread recently, 4 years late though. Just sharing my thought here on the best answer by Deepak. Correct me if wrong as I'm a newbie.

First of all, it would throw a duplicate Id exception if there are multiple opportunities under a single account.
Second, even if duplicate Id exception is taken care, for accounts with multiple opportunities, only the stageName of the last opportunity in iteration is taken into consideration as the for loop in on Opportunity and the check is not from an 'Opportunities under an account' perspective.

Not sure how this resolved Cola's problem. If still going with a trigger, the below would help:
 
trigger updateAccountType on Opportunity(after insert, after update){

    Set<Id> parentAccountIds= new Set<Id>();
    List<Account> lstAccountsToUpdate= new List<Account>();
    
    for(Opportunity opp: trigger.new)
        parentAccountIds.add(opp.AccountId);

    List<Account> lstAccountsToUpdateRaw=[select id, (select id from opportunities where stageName='Your value') from Account where id in :parentAccountIds];
    
    for(Account acc: lstAccountsToUpdateRaw){
        
        Account objAccount= new Account(Id=acc.Id);
        
        if(acc.opportunities.size()>0)
            objAccount.type='Desired Value';
            
        else
            objAccount.type='Other Desired Value';
        
        lstAccountsToUpdate.add(objAccount);        
    }
	update lstAccountsToUpdate;
}