-1

This is a follow-on question to Help with Trigger to that will index Account Number field. That question provides more context.

So I have the following trigger that is designed to assign an Account Number when Opportunity probability moves to 95%. It's throwing a SOQL error, my guess is because I'm running the query inside the for loop.

  1. How can I move this query outside the loop?
  2. Should I map the Account object?

The current trigger:

trigger AssignAccountNumber on Opportunity(before insert, before update) {
    for (Opportunity o: Trigger.new) {
        if ((o.Probability >= 95) && (o.Account.AccountNumber == null)) {
            accIndexx__c n;
            Boolean ok;
            do {
                try {
                    ok = true;
                    n = [SELECT Ones__c from accIndexx__c limit 1
                        for update
                    ];
                    if (o.LC_Region__c == 'Americas') {
                        n.Ones__c = n.Ones__c + 1;
                    }
                    update n;
                } catch (QueryException e) {
                    ok = false;
                }
            } while (!ok);
        }
    }
}
3
  • You're getting down voted because this is a common topic and a Google search would give you a ton of articles about this..
    – EricSSH
    May 28, 2014 at 21:11
  • 1
    The same could be said of 80% of the questions asked in Salesforce.StackExchange. At least user8556 provided his code as a starting point. That's 90% more than the the vast majority of new posters who provide us with none. Lets give him a break and welcome him to the community.
    – crmprogdev
    May 29, 2014 at 4:05
  • possible duplicate of General trigger bulkification - best practices Aug 21, 2014 at 18:21

2 Answers 2

5

This is a relatively unusual case in that the purpose of the query inside the loop is to obtain a sequence number.

I suggest this pattern:

  • Loop through Trigger.new once adding to 4 separate lists to collect the various category of Opportunities.
  • Outside of that loop, do the "for update" query on the number allocating object.
  • Instead of incrementing the counter field by 1, for each of the 4 separate lists increment the counter field by the length of the list.
  • Update the number allocating object.
  • Loop through each of the 4 separate lists, and assign a number that starts at the number obtained in the "for update" query and is incremented by one for each list item.

So you end up only doing one query and one update of the number allocating object no matter how many objects are being processed by the trigger.

2
  • After reading Keith comments, it may be easier to nuke it, and Rethink the logic and use Keith's bullet points to move forward
    – EricSSH
    May 28, 2014 at 20:57
  • Thanks guys, let me try redesigning using your pointers.
    – user8556
    May 28, 2014 at 20:59
0

This would be a good start to bulkifying your trigger.

https://developer.salesforce.com/page/Best_Practice%3A_Bulkify_Your_Code

On another note your code is extremly hard to read so after this post I will format it and take a look at it, but in the mean time take a look at that page and try some techniques on there. At first glance, you need to pull out that query and do it outside of the loop

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .