I need to create a trigger that will collect all new Opportunities (the ones with brand new owners that aren't owners of existing Opportunities) and create a Lookup Table of them. Right now, my main worry is creating the trigger, the lookup table can wait.

I'm creating a trigger that will basically do the following:

  1. If a new Opportunity gets created, check to see if it's a brand new owner that hasn't previously existed in past Opportunities.
  2. If it's a brand new owner, then create a new Opportunity and update all the fields within the opportunity.
  3. This trigger will use before insert and before update.

So far, this is what the skeleton of my code looks like.

trigger trig_Opportunity_CreateOppOwner on Opportunity (before insert, before update) {

//Opportunity OppOwner = null; 

//List that will hold all id of all new Opportunities owners
List<id>OppsID = new List<id>(); 
List<Opportunity>oOpportunity = new List<id>(); 

//Loop that only retrives updated Opportunties with new Owners. 
for(Opportunity Opp:Trigger.new){ 
    if Trigger.oldMap.get(opp.id).Owner__c != Trigger.oldMap.get(OppToUpdate.id).Owner__c
        newMap.get[ID].Op_Owner__c = newMap.get[ID].owner
    //adds all updated and new Opportunities Id's only and puts them in a separate list called OppsID.

// Only interested in OpportunityID
List<Opportunity>OppToUpdate = [SELECT Id, Name, Owner__c, Op_owner__c  
                                FROM Opportunity
                                WHERE Id IN : OppsId]; 

Could anyone let me know if I'm on the right track or if I'm making any kind of major mistakes? Coding isn't my strong suit so any help would be deeply appreciated.

  • can you please explain in more detail what you want to archive, it is very confusing. especially since you are not posting the entire trigger code. where do you query 'oppsFromDb'? btw. you can get a set of opportunity ids using trigger.newMap.keySet() instead of looping through the records – Seb Wagner Jun 3 '13 at 19:30
  • I've updated my code to account for the newmap idea as suggested above. Sorry for the confusion. Hopefully it's clear now? – SSadat Jun 3 '13 at 19:43
  • where do you instantiate OppToUpdate which you use to lookup the opportunity? OppToUpdate will not work in BEFORE_INSERT triggers, since records havent been created yet. in addition, what happens if there are two opportunities for the same in the trigger context? can you line out step by step what the code is supposed to do? – Seb Wagner Jun 3 '13 at 20:41
  • By "brand new owner" can you clarify? Do you mean: if an opportunity is created and the owner of that opportunity is not the owner of any other opportunity, or if an existing opportunity is assigned to a user who has no opportunities, you want to set some values in the opportunity? Yes? – pchittum Jun 3 '13 at 21:01
  • You only want this trigger to kick off it is a new user creating a Opp? First you need to get all the users that have created a Opp.. I believe there is a field called CreatedById in opps.. – EricSSH Jun 9 '14 at 20:18

As far as I understood your query. I believe you want that the current user who is creating the opp, should not have created any opportunity before. If this the case then you want some specific operation to happen. I have tried on my side, hope it solves some of the problem for you. If not then please explain your question in more detail. Thanks

Here is the code

trigger CheckOwner on Opportunity (before Insert) {
    Integer oppsOwned = [SELECT count() FROM Opportunity WHERE OwnerId = :UserInfo.getUserId()];
    for (Opportunity opp : Trigger.new) {
        if (oppsOwned > 1) {
            opp.addError('error messages');
  • ... fixed your syntax and also a line of your code... "Trigger.new[0]" isn't applicable when you are going through a non-integer-incrementing list... – Nathan Williams Aug 8 '14 at 23:11
  • Shouldn't the oppsOwned be > 0 and not > 1? Also, you can limit the rows returned by using LIMIT 1 in your query as you are not concerned with anything more than 1. – Eric Nov 11 '14 at 4:50

From your post, your goals are very difficult to discern. As I understand them, your goals are:

  1. Find out which Opportunity records have an OwnerId not yet in the system.
  2. Duplicate (validate?) all such Opportunity records.
  3. Do this even when a cursory update happens to an Opportunity.

New Owner

Question 1 is easy to understand. You can get a Set<Id> containing the Id of every User that owns an Opportunity like so:

Set<Id> existingOwnerIds = new Set<Id>();
for (AggregateResult aggregate : [
    SELECT OwnerId FROM Opportunity
    WHERE Owner.Type = 'User'
    GROUP BY OwnerId

I would be wary of performing this in an org that has a large volume of Opportunity records (over 100k), but I believe it should actually work fine (depending on your distribution of ownership) up to a million records, or possibly even several million. For instance I can query against an object with ~560k records like the above and get back ~1800 aggregates. That 1800 is the number I get back when I debug Limit.getQueryRows(), so query usage seems moderate. However, retrieving them, looping through them, and adding them to a Set<Id> ended up taking a total of almost 2 full seconds, so CPU timeout is likely to be your limiting factor. I tried with Set<Object> and no casting to see if it was faster, but it actually was over 2.5 seconds.

But I digress. Once you have the above Set<Id>, it is fairly simple to check if a given Opportunity has an OwnerId that is not already in your org.

for (Opportunity newRecord : trigger.new)
    if (existingOwnerIds.contains(newRecord.OwnerId))
        //do stuff

Duplicate Opportunity

Question 2 does not make much sense as you have it worded. If your desire is to validate that each owner can only own one Opportunity, then you would simply call newRecord.addError(someMessage) where I have //do stuff above. If that is your desire, it changes some of my first response, mostly in that I would probably remove the User.Type filter from my aggregate query.

If what you want is to duplicate these records, I am very curious as to your specific goals. Without more backstory, it sounds like a very bad idea and I would advise against it.

Before Insert/Update

So much of this hinges on what you really mean by question 2. If validation is your goal, these events are correct. If you want to do cloning, the operations should really be performed after, and doesn't make a whole lot of sense on update.

Not the answer you're looking for? Browse other questions tagged or ask your own question.