[SalesForce] Trigger to insert Child Object Records based on various relationships

I am create a custom object record on Opportunities called Recipe Sheets (based on the Opportunity Line Items) and this trigger is working properly. Then I have a second trigger firing on the insert of these Recipe Sheets to create Recipe Sheet Items based on the Product Items associated to the Products in the Opportunity Line Items.

If I deactivate Trigger 2, Trigger 1 works, but when I activate Trigger 2 I get this error:

"Error:Apex trigger NewRecipeSheets caused an unexpected exception, contact your administrator: NewRecipeSheets: execution of AfterUpdate caused by: System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, CreateRecipeItems: execution of AfterInsert caused by: System.DmlException: Insert failed. First exception on row 0 with id a07Z000000HdDsxIAF; first error: INVALID_FIELD_FOR_INSERT_UPDATE, cannot specify Id in an insert call: [Id] Trigger.CreateRecipeItems: line 22, column 1: []: Trigger.NewRecipeSheets: line 24, column 1"

Does anyone have any suggestions for what I am doing wrong in Trigger 2?

Trigger 1

trigger NewRecipeSheets on Opportunity (after update){

    List<Recipe_Sheet__c> RecipeList =new List<Recipe_Sheet__c>();
    List<Opportunitylineitem> opplinitemlist =new List<Opportunitylineitem>();
    List<Opportunity> Opportunitylist =new List<Opportunity>();

    for(Opportunity opp :trigger.new){
        if(opp.Stagename=='Closed Won'&&opp.Create_Recipe_Sheet_s__c==True){       
        Opplinitemlist =[select Id,PricebookEntry.Product2.Name,PricebookEntry.Product2.Do_Not_Discount__c,PricebookEntry.Product2.Record_Type_ID__c,Total_Drop__c,Finish_s__c,Notes__c, For_Vaulted_Ceiling__c,PricebookEntry.Product2.id from Opportunitylineitem where Opportunityid =: opp.id AND Product2.Do_Not_Discount__c=False];
        Opportunitylist  =[Select Accountid from Opportunity where Id =: opp.id];

        for(Opportunitylineitem Opplist :opplinitemlist ){
        Recipe_Sheet__c rec =new Recipe_Sheet__c();
        rec.Opportunity__c =Opportunitylist[0].Id;
        rec.product__c=opplist.PricebookEntry.Product2.Id;
        rec.Name=opplist.PricebookEntry.Product2.Name;
        rec.For_Vaulted_Ceiling__c=opplist.For_Vaulted_Ceiling__c;
        rec.Finish_s__c=opplist.Finish_s__c;
        rec.Notes__c=opplist.Notes__c;
        rec.Total_Drop__c=opplist.Total_Drop__c;
        rec.RecordTypeID=opplist.PricebookEntry.Product2.Record_Type_ID__c;
        RecipeList.add(rec);
        }
        insert RecipeList;
        } 
        }       
}

Trigger 2

trigger CreateRecipeItems on Recipe_Sheet__c (after insert) {

    List<Recipe_Sheet_Item__c> RecipeItemList =new List<Recipe_Sheet_Item__c>();
    List<Recipe_Sheet__c> RecipeList =new List<Recipe_Sheet__c>();
    List<Product_Item__c> ProductItemlist =new List<Product_Item__c>();
    List<Product2> ProductList =new List<Product2>();

    for(Recipe_Sheet__c rec :trigger.new){
        if(rec.Name !=''){       
        ProductList = [select Id,Name,Do_Not_Discount__c,Record_Type_Id__c from Product2 where Id =: rec.Product__c];
        ProductItemList =[select Id,Product__c,Quantity__c,Group__c,Item__c,Length_in_inches__c,External_Wall_Mount_Vaulted_Ceiling__c from Product_Item__c where Product__c =: rec.Product__c];
        Recipelist  =[Select Id,Opportunity__c,Product__c from Recipe_Sheet__c where Id =: rec.id];

        for(Product_Item__c pilist :ProductItemlist ){
        Recipe_Sheet_Item__c ritem = new Recipe_Sheet_Item__c();
        ritem.Recipe_Sheet__c = Recipelist[0].Id;
        ritem.Group__c = pilist.Group__c;
        ritem.Item__c = pilist.Item__c;
        ritem.Quantity__c = pilist.Quantity__c;
        RecipeItemList.add(ritem);
        }
        if (!RecipeItemList.isEmpty()) insert RecipeItemList ;
        } 
        }       
}

Best Answer

The problem is with the scope of your variables. RecipeItemList is declared outside the for(Recipe_Sheet__c rec :trigger.new) loop so the items added to the list the first time round the loop and then inserted are still in the list the second time round the loop and so are inserted again which is an error because they already have an ID assigned.

Narrowing the scope of RecipeItemList so that it is declared and initialised within the loop will solve the immediate problem because each time round the loop a new empty list will be created as the starting point.

However, your code in both triggers does contain the anti-patterns of querying and doing DML inside a loop and so really also needs bulkifying.

Related Topic