+ Start a Discussion
Patricia Mozzoni 9Patricia Mozzoni 9 

OpportunityLineItem trigger works in sandbox but not production DML?

Thanks to some help from this forum, I was able to get my first trigger written, tested and in production however, now it does not work!  Looking at comments in the forum, perhaps it is something to do with recursion which I am unsure how to fix.  I have tried to fix all day and now my trigger is  not working and the error I get is:

System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, oliUpdate: execution of AfterInsert

caused by: System.DmlException: Update failed. First exception on row 0 with id 00k2i000003mRZaAAM; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, oliUpdate: maximum trigger depth exceeded
OpportunityLineItem trigger event AfterInsert
OpportunityLineItem trigger event AfterUpdate
OpportunityLineItem trigger event AfterUpdate
OpportunityLineItem trigger event AfterUpdate
OpportunityLineItem trigger event AfterUpdate
OpportunityLineItem trigger event AfterUpdate
OpportunityLineItem trigger event AfterUpdate
OpportunityLineItem trigger event AfterUpdate
OpportunityLineItem trigger event AfterUpdate
OpportunityLineItem trigger event AfterUpdate
OpportunityLineItem trigger event AfterUpdate
OpportunityLineItem trigger event AfterUpdate
OpportunityLineItem trigger event AfterUpdate
OpportunityLineItem trigger event AfterUpdate
OpportunityLineItem trigger event AfterUpdate
OpportunityLineItem trigger event AfterUpdate: []

Trigger.oliUpdate: line 21, column 1: []

I would really appreciate any help as I am supposed to have this live this week, ugh!

 I have a formula field (Net_Amount__c) on OpportunityLineItem that calculates my UnitPrice.  The formula for Net_Amount__c is  ((Quantity * Annual_Amount__c) * Revenue_Years__c).  Because the field I want to update is the UnitPrice, I have to do an after trigger that calculates the formula field value of Net_Amount__c and updates UnitPrice. 



Trigger:
trigger oliUpdate on OpportunityLineItem (after insert, after update) {

    Set <String> oliID = New Set <String> (); 
    For (OpportunityLineItem oli: Trigger.new) { 
        if (oli.OpportunityId != Null ) { 
        oliID.add (oli.Id); 
        } 
    } 
    If (oliID.size ()> 0) { 
        List<OpportunityLineItem> upOpiList = new List <OpportunityLineItem>();
        For (OpportunityLineItem ol: [SELECT Id, OpportunityID, Product2Id, PricebookEntryId, Quantity, Net_Amount__c, UnitPrice FROM OpportunityLineItem WHERE id in: oliID AND Net_Amount__c >0.00]){
            
      
             Formula.recalculateFormulas(new List<OpportunityLineItem>{ol});
                ol.UnitPrice = ol.Net_Amount__c; 
                UpOpiList.add (ol); 
            }
           
        
        If (upOpiList.size ()> 0) 
            update upOpiList; 
}
}

Test:
@isTest 
public class oliUpdateTest 
{
     static testMethod void TestoliUpdate()
    {
        
        List<Account> testAccounts = new List<Account>();
        Account testAccount0 = new Account(
            Name = 'Arbor'
        );
        testAccounts.add(testAccount0);
        Account testAccount1 = new Account(
            Name = 'Arbor Villa'
        );
        testAccounts.add(testAccount1);
        insert testAccounts;

   
        Opportunity testOpportunity = new Opportunity(
            Name                 = 'Arbor - Cypress Tree Opportunity',
            StageName            = 'Strategy',
            CloseDate            = Date.today(),
            AccountId            = testAccount0.Id
            
        );
          insert testOpportunity;

        Product2 testProduct2 = new Product2(
            Name                  = 'Cypress Tree',
            IsActive              = true,
            CanUseRevenueSchedule = true
        );
        insert testProduct2;

 

       

        PricebookEntry testPricebookEntry = new PricebookEntry(
            IsActive     = true,
            Pricebook2Id = Test.getStandardPricebookId(),
            Product2Id   = testProduct2.Id,
            UnitPrice    = 0
        );
        insert testPricebookEntry;

        OpportunityLineItem testOpportunityLineItem = new OpportunityLineItem(
            OpportunityId          = testOpportunity.Id,
            PricebookEntryId       = testPricebookEntry.Id,
            Quantity               = 1,
            Revenue_Years__c       = 3,
            Annual_Amount__c       = 5000,
            ServiceDate            = Date.newInstance(2020, 1, 1)
        );
        insert testOpportunityLineItem;   
        System.assertEquals(NULL, testOpportunityLineItem.Net_Amount__c, 'should be null, formula is not evaluated yet');
        
        Test.startTest();
        
        OpportunityLineItem item = [SELECT Id, Net_Amount__c, UnitPrice
                                  FROM OpportunityLineItem WHERE Id = :testOpportunityLineItem.Id];
        Formula.recalculateFormulas(new List<OpportunityLineItem>{item});
        Test.stopTest();
      
       
       System.assertNotEquals(0, testOpportunityLineItem.UnitPrice, 'UnitPrice should equal Net_Amount__c');
 

}
}

​​​​​​​
 
Andrew GAndrew G
Hi

You are using an after trigger to update the records which caused the trigger to fire.  Since they are updated, they fire again.  Get updated again and fire the trigger again.. and so on.... i hope you get the picture.

Change your context to (before insert, before update)

After triggers should be used to update related objects, not the objects that fire the trigger in the first place.  Before triggers to update or validate the records that fire the trigger.

regards
Andrew
Patricia Mozzoni 9Patricia Mozzoni 9
Thank you, Andrew, I will give that a try. Sure, it does make sense to me, I had read somewhere that it had to be an after Trigger, with my zero knowledge I thought that was true. Appreciate your time. Patti Mozzoni M 484.432.2637
Andrew GAndrew G
And I forgot to mention, in a before trigger, there is no need to invoke the update method as the DML event will take care of that.

Regards
Andrew
mukesh guptamukesh gupta
Hi Patricia,

Please use below code in your trigger:-
trigger oliUpdate on OpportunityLineItem (after insert, after update) {

    Set <String> oliID = New Set <String> (); 
    For (OpportunityLineItem oli: Trigger.new) { 
        if (oli.OpportunityId != Null ) { 
        oliID.add (oli.Id); 
        } 
    } 
    If (oliID.size ()> 0) { 
        List<OpportunityLineItem> upOpiList = new List <OpportunityLineItem>();
        For (OpportunityLineItem ol: [SELECT Id, OpportunityID, Product2Id, PricebookEntryId, Quantity, Net_Amount__c, UnitPrice FROM OpportunityLineItem WHERE id in: oliID AND Net_Amount__c >0.00]){
            
      
             Formula.recalculateFormulas(new List<OpportunityLineItem>{ol});
           if(ol.UnitPrice != ol.Net_Amount__c){

             ol.UnitPrice = ol.Net_Amount__c; 
                UpOpiList.add (ol); 
            }
               
            }
           
        
        If (upOpiList.size ()> 0) 
            update upOpiList; 
}
}

If this solution is usefull for you, Please mark as a Best Answer to help others.


Regards
Mukesh