+ Start a Discussion
Jerry ClifftJerry Clifft 

Runaway trigger

I have this trigger, when an opportunity product line item "status" is "Add Requested" or "Remove Requested" it generates a text string that copied to the Opportunity. Also, if the Opportunity is updated, it checks to see if the any OLI's have this status, if they do, it will also copy the string to the Opportunity. Both of these work fine.

HOWEVER, if the data loader or other such tool is used to we encounter problems. For example, a batch of 200 in the data loader, a single Opportunity has OLI's with the status to activate the trigger, all 200 opportunties in the batch will have the string written them...all.

I guess, what I need, is why to limit this update to a single Opprtunity. Is there such as thing on the trigger as LIMIT 1 or simiar, if so, I have been unable to locate, please advise.


Here is my trigger.
trigger OpportunityProuctCopytoHiddenFieldUpdate on Opportunity (before insert, before update) 
    {
        Opportunity op1 = trigger.new[0];
            Set<Id> opIds = new Set<Id>();

            for (Opportunity o: Trigger.New){

                opIds.add(op1.Id);
    }
    list<opportunityLineItem> OpLine = [SELECT Status__c, Submitted__c, CreatedBy.Name, Quantity, TotalPrice, LastModifiedDate, LastModifiedBy.Name, PriceBookEntry.Name, UnitPrice, ID FROM OpportunityLineItem WHERE OpportunityId =:op1.Id AND (Status__c ='Add Requested' OR Status__c ='Remove Requested') AND Submitted__c != TRUE ] ;

     if(OpLine.size() > 0 )    
        {
            for(Opportunity t:trigger.new)
                  {
                    List<String> NewList= new List<String>();
            
                    if (t.HasOpportunityLineItem == true) 
                    {
                         for(OpportunityLineItem XOpLine: OpLine ) 
                        {
                            NewList.add(XOpLine.Status__c);
                            NewList.add(', ');
                            NewList.add(XOpLine.PriceBookEntry.Name);
                            NewList.add(', @ ');
                            String str = '' + XOpLine.Quantity;
                            NewList.add(str);
                            NewList.add(', Units, ');
                            NewList.add(' @ ');
                            String str2 = '$' + XOpLine.UnitPrice;
                            NewList.add(str2);
                            NewList.add(', Per Unit/Month  ');
                            NewList.add(XOpLine.LastModifiedBy.Name);
                            NewList.add(', on ');
                            String str1 = '' + XOpLine.lastModifiedDate;
                            NewList.add(str1);
                            NewList.add(' GMT 0');
                            NewList.add(' <br />');
                           }
                                
            
                                String s = '';
                        for(String c : NewList){
                            s = s + c;
                            system.debug('********' +  t.Hidden_Products__c);
                        }
                    {    t.Hidden_Products__c = s; t.Hidden_Case_History__c = True;}
                    }
                }
             }}
Best Answer chosen by Jerry Clifft
Himanshu ParasharHimanshu Parashar
Hi Jerry,

You are facing this issue because line 3 of your code takes first opportunity and get data from Opportunitylineitem though you are already getting all ids but you are not using them to query Opportunitylineitem.

I have also added a check of current opportunity while getting data from Lineitem. Please check the comment for specific line.
trigger OpportunityProuctCopytoHiddenFieldUpdate on Opportunity (before insert, before update) 
    {
        //Opportunity op1 = trigger.new[0];  //Issue line.
            Set<Id> opIds = new Set<Id>();

            for (Opportunity o: Trigger.New){

                opIds.add(op1.Id);  //This is correct
    }
    list<opportunityLineItem> OpLine = [SELECT Status__c, Submitted__c, CreatedBy.Name, Quantity, TotalPrice, LastModifiedDate, LastModifiedBy.Name, PriceBookEntry.Name, UnitPrice, ID FROM OpportunityLineItem WHERE OpportunityId in : opIds AND (Status__c ='Add Requested' OR Status__c ='Remove Requested') AND Submitted__c != TRUE ] ;

     if(OpLine.size() > 0 )    
        {
            for(Opportunity t:trigger.new)
                  {
                    List<String> NewList= new List<String>();
            
                    if (t.HasOpportunityLineItem == true) 
                    {
                         for(OpportunityLineItem XOpLine: OpLine ) 
                        {
                            if(XOpLine.opportunityid==t.id) // Check for current opportunity. new code
                            {
                            NewList.add(XOpLine.Status__c);
                            NewList.add(', ');
                            NewList.add(XOpLine.PriceBookEntry.Name);
                            NewList.add(', @ ');
                            String str = '' + XOpLine.Quantity;
                            NewList.add(str);
                            NewList.add(', Units, ');
                            NewList.add(' @ ');
                            String str2 = '$' + XOpLine.UnitPrice;
                            NewList.add(str2);
                            NewList.add(', Per Unit/Month  ');
                            NewList.add(XOpLine.LastModifiedBy.Name);
                            NewList.add(', on ');
                            String str1 = '' + XOpLine.lastModifiedDate;
                            NewList.add(str1);
                            NewList.add(' GMT 0');
                            NewList.add(' <br />');
                            }
                           }
                                
            
                                String s = '';
                        for(String c : NewList){
                            s = s + c;
                            system.debug('********' +  t.Hidden_Products__c);
                        }
                    {    t.Hidden_Products__c = s; t.Hidden_Case_History__c = True;}
                    }
                }
             }}

This code will work with dataloader as well. It can be rectified in more better manner to run more efficiently but I am not doing that and modifying your code to make it work the way you want. Please let us know if that work.


Thanks,
Himanshu
Salesforce Certified Developer | Administrator | Service Cloud Consultant

P.S. If my answer helps you to solve your problem please mark it as best answer. It will help other to find best answer.

All Answers

Himanshu ParasharHimanshu Parashar
Hi Jerry,

You are facing this issue because line 3 of your code takes first opportunity and get data from Opportunitylineitem though you are already getting all ids but you are not using them to query Opportunitylineitem.

I have also added a check of current opportunity while getting data from Lineitem. Please check the comment for specific line.
trigger OpportunityProuctCopytoHiddenFieldUpdate on Opportunity (before insert, before update) 
    {
        //Opportunity op1 = trigger.new[0];  //Issue line.
            Set<Id> opIds = new Set<Id>();

            for (Opportunity o: Trigger.New){

                opIds.add(op1.Id);  //This is correct
    }
    list<opportunityLineItem> OpLine = [SELECT Status__c, Submitted__c, CreatedBy.Name, Quantity, TotalPrice, LastModifiedDate, LastModifiedBy.Name, PriceBookEntry.Name, UnitPrice, ID FROM OpportunityLineItem WHERE OpportunityId in : opIds AND (Status__c ='Add Requested' OR Status__c ='Remove Requested') AND Submitted__c != TRUE ] ;

     if(OpLine.size() > 0 )    
        {
            for(Opportunity t:trigger.new)
                  {
                    List<String> NewList= new List<String>();
            
                    if (t.HasOpportunityLineItem == true) 
                    {
                         for(OpportunityLineItem XOpLine: OpLine ) 
                        {
                            if(XOpLine.opportunityid==t.id) // Check for current opportunity. new code
                            {
                            NewList.add(XOpLine.Status__c);
                            NewList.add(', ');
                            NewList.add(XOpLine.PriceBookEntry.Name);
                            NewList.add(', @ ');
                            String str = '' + XOpLine.Quantity;
                            NewList.add(str);
                            NewList.add(', Units, ');
                            NewList.add(' @ ');
                            String str2 = '$' + XOpLine.UnitPrice;
                            NewList.add(str2);
                            NewList.add(', Per Unit/Month  ');
                            NewList.add(XOpLine.LastModifiedBy.Name);
                            NewList.add(', on ');
                            String str1 = '' + XOpLine.lastModifiedDate;
                            NewList.add(str1);
                            NewList.add(' GMT 0');
                            NewList.add(' <br />');
                            }
                           }
                                
            
                                String s = '';
                        for(String c : NewList){
                            s = s + c;
                            system.debug('********' +  t.Hidden_Products__c);
                        }
                    {    t.Hidden_Products__c = s; t.Hidden_Case_History__c = True;}
                    }
                }
             }}

This code will work with dataloader as well. It can be rectified in more better manner to run more efficiently but I am not doing that and modifying your code to make it work the way you want. Please let us know if that work.


Thanks,
Himanshu
Salesforce Certified Developer | Administrator | Service Cloud Consultant

P.S. If my answer helps you to solve your problem please mark it as best answer. It will help other to find best answer.
This was selected as the best answer
Jerry ClifftJerry Clifft
Thanks for the reply, when I use this code and I add an Opp Line Item, I receive the eror msg below:

OpportunityProuctCopytoHiddenFieldUpdate: execution of BeforeUpdate caused by: System.SObjectException: SObject row was retrieved via SOQL without querying the requested field: OpportunityLineItem.OpportunityId Trigger.OpportunityProuctCopytoHiddenFieldUpdate: line 22, column 1
Himanshu ParasharHimanshu Parashar
Hi Jerry,

Add Opportunityid in line 10 SOQL.

Thanks,
Himanshu
Jerry ClifftJerry Clifft
Thanks Himanshu for the help, your solution works perfectly!

For anyone else who wants a copy of the revised and workig properly trigger... here it is.

trigger OpportunityProuctCopytoHiddenFieldUpdate on Opportunity (before insert, before update)
    {
            Set<Id> opIds = new Set<Id>();
            for (Opportunity o: Trigger.New){
                opIds.add(o.Id);  //This is correct
    }
    list<opportunityLineItem> OpLine = [SELECT Status__c, Submitted__c, CreatedBy.Name, Quantity, TotalPrice, LastModifiedDate, LastModifiedBy.Name, PriceBookEntry.Name, UnitPrice, ID, OpportunityId FROM OpportunityLineItem WHERE OpportunityId in : opIds AND (Status__c ='Add Requested' OR Status__c ='Remove Requested') AND Submitted__c != TRUE ] ;

     if(OpLine.size() > 0 )    
        {
            for(Opportunity t:trigger.new)
                  {
                    List<String> NewList= new List<String>();
            
                    if (t.HasOpportunityLineItem == true)
                    {
                         for(OpportunityLineItem XOpLine: OpLine )
                        {
                         if(XOpLine.opportunityid==t.id)
                            {
                            NewList.add(XOpLine.Status__c);
                            NewList.add(', ');
                            NewList.add(XOpLine.PriceBookEntry.Name);
                            NewList.add(', @ ');
                            String str = '' + XOpLine.Quantity;
                            NewList.add(str);
                            NewList.add(', Units Passed, ');
                            NewList.add(' @ ');
                            String str2 = '$' + XOpLine.UnitPrice;
                            NewList.add(str2);
                            NewList.add(', Per Unit/Month  ');
                            NewList.add(XOpLine.LastModifiedBy.Name);
                            NewList.add(', on ');
                            String str1 = '' + XOpLine.lastModifiedDate;
                            NewList.add(str1);
                            NewList.add(' GMT 0');
                            NewList.add(' <br />');
                           }
                           }

                                String s = '';
                        for(String c : NewList){
                            s = s + c;
                            system.debug('********' +  t.Hidden_Products__c);
                        }
                    {    t.Hidden_Products__c = s; t.Hidden_Case_History__c = True;}
                    }
                }
             }}