How to build a trigger for 2 scenarios in 1 object which will create/update multiple records in another unrelated object?

Question

Scenario 1:

a) Trigger on Custom Object 'Prod' only when record type = 'Forecast'.

b) It should take data from 1 custom date field and 3 custom number fields in Object 'Prod'

c) Then creates 3 records in Custom Object 'Inv' - Each record will have that 1 date field and 1 of the custom number field from 'Prod'

Scenario 2:

a) Trigger on Custom Object 'Prod' only when record type = 'Actual'

b) It should take data from the same custom date field and 3 different custom number fields in Object 'Prod'

c) Result of this will update the 3 records in 'Inv' with the respective correct date and volume

Here is my Apex Trigger:

trigger ProductionTrigger on Prod__c (before insert, before update) {

List<Prod__c> prod_Forecast = new List<Prod__c>();
List<Prod__c> prod_Actual = new List<Prod__c>();
List<Inv__c> inv_Forecast = new List<Inv__c>();
List<Inv__c> inv_afterInsertForecast = new List<Inv__c>();
List<Inv__c> inv_toUpdateWithActuals = new List<Inv__c>();

for (Prod__c p : Trigger.new) {
    //Loop through all records in the Trigger.new collection only if Production = 'Forecast'
    if (p.RecordTypeId == Schema.SObjectType.Prod__c.RecordTypeInfosByName.get('Forecast').RecordTypeId) {
        prod_Forecast.add(p);

    } else if (p.RecordTypeId == Schema.SObjectType.Prod__c.RecordTypeInfosByName.get('Actual').RecordTypeId) {
            prod_Actual.add(p);

    }
}

//Only trigger for when record type = 'Forecast'
if (prod_Forecast.isEmpty() == false) {
    for (Prod__c p : prod_Forecast){
        if (p.A_Volume__c != 0){
            Inv__c inv = new Inv__c();
            inv.Inventory_Date__c = p.Production_Date__c;
            inv.Product_Family__c = 'Liquid A';
            inv.Product_Grade__c = 'A';
            inv.Incoming_Stock__c = p.A_Volume__c;
            inv_Forecast.add(inv);
        }
        if (p.B_Volume__c != 0) {
            Inv__c inv2 = new Inv__c();
            inv2.Inventory_Date__c = p.Production_Date__c;
            inv2.Product_Family__c = 'Liquid B';
            inv2.Product_Grade__c = 'B';
            inv2.Incoming_Stock__c = p.B_Volume__c;
            inv_Forecast.add(inv2);
        }
        if (p.C_Volume__c != 0) {
            Inv__c inv3 = new Inv__c();
            inv3.Inventory_Date__c = p.Production_Date__c;
            inv3.Product_Family__c = 'Liquid C';
            inv3.Product_Grade__c = 'C';
            inv3.Incoming_Stock__c = p.C_Volume__c;
            inv_Forecast.add(inv3);
        } 
    }
    if (inv_Forecast.size() > 0) {
        insert inv_Forecast;

        inv_afterInsertForecast = ([SELECT Id, Inventory_Date__c , Product_Family__c, Product_Grade__c, Opening_Stock__c
                            FROM Inv__c
                            WHERE id IN :inv_Forecast]);
    }
}   

//Only trigger for when record type = 'Actual'
if (prod_Actual.isEmpty() == false) {
    for (Prod__c p : prod_Actual){
        for (Inv__c i : inv_afterInsertForecast){
            if (i.Inventory_Date__c == p.Production_Date__c &&
                i.Product_Family__c == 'Liquid A' &&
                i.Product_Grade__c == 'A' ){
                    Inv__c invA = new Inv__c();
                    invA.id = i.id;
                    invA.Inventory_Volume__c = p.A_Volume__c;
                    inv_toUpdateWithActuals.add(invA);
                }
            if (i.Inventory_Date__c == p.Production_Date__c &&
                i.Product_Family__c == 'Liquid B' &&
                i.Product_Grade__c == 'B'){
                    Inv__c invB = new Inv__c();
                    invB.id = i.id;
                    invB.Inventory_Volume__c = p.B_Volume__c;
                    inv_toUpdateWithActuals.add(invB);
                }
            if (i.Inventory_Date__c == p.Production_Date__c &&
                i.Product_Family__c == 'Liquid C' &&
                i.Product_Grade__c == 'C'){
                    Inv__c invC = new Inv__c();
                    invC.id = i.id;
                    invC.Inventory_Volume__c = p.B_Volume__c;
                    inv_toUpdateWithActuals.add(invC);
                }
        }
    }
    if (inv_toUpdateWithActuals.size() > 0) {
        update inv_toUpdateWithActuals;
    }
}

}

Unexpected outcomes from my code

• It can accomplish Scenario 1 by creating 3 records in Inv__c but somehow also creates records in Prod__c for ALL Prod__c record types.

• It doesn't update the 3 records in inventory at all even when I mapped the Inventory_Date__c, Product_family__c and Product_group__c.

Help please?

****UPDATE**** Also a few side questions (besides the fact I need to make handlers to minimize the codebase in triggers) :

1) Are my parameters correct that I did before insert, before update on the 1st line of my code? (was thinking scenario 2 would be after insert, after update)

2) How could I have done this differently using Map, Set?

3) I tried to separate both scenarios using Trigger.IsBefore/ Trigger.isUpdate/ Trigger.isInsert but had an inkling that separating by record types should be good enough

Answer 1

There's a lot to tackle here. I'm going to try to give a pretty broad treatment of the separate issues here and I hope that the refactoring that addressing these issues will involve will both add clarity and serendipitously resolve other problems you're seeing.

Scenario 1 / Issue 1

Your code appears to be to be correct for Scenario 1, save that it's implemented in the trigger body rather than inspecting the trigger operation (either using Trigger.isInsert and friends or looking at Trigger.operationType). More on the issues this causes below.

The issue you're seeing with multiple Prod__c objects of different record types being inserted appears to have nothing to do with this trigger per se.

Scenario 2 / Issue 2

This issue has a lot to do with the structure of the trigger. Let me pull out a skeleton here and try to show why that's the case.

trigger ProductionTrigger on Prod__c (before insert, before update) {
    for (Prod__c p : Trigger.new) {
        // Accumulate records in collections
    }

    if (prod_Forecast.isEmpty() == false) {
        // Create Inv__c records.
    }
    if (inv_Forecast.size() > 0) {
        // Insert and requery Inv__c records.
        // NOTE that this code will *only* execute when there exist `Forecast`
        // records in the trigger set. Otherwise `inv_afterInsertForecast` will be empty.
        // If there is a mixture of `Forecast` and `Actual` records,
        // we only requery the new `Forecast`-related Inv__c records.
        // Also, what happens if you update `Forecast` records without
        // changing their record types? There's an issue.
        insert inv_Forecast;

        inv_afterInsertForecast = ([SELECT Id, Inventory_Date__c , Product_Family__c, Product_Grade__c, Opening_Stock__c
                            FROM Inv__c
                            WHERE id IN :inv_Forecast]);
    }

    if (prod_Actual.isEmpty() == false) {
        for (Prod__c p : prod_Actual){
            // Iterate over `inv_afterInsertForecast`
            // Match records and update them
        }
        if (inv_toUpdateWithActuals.size() > 0) {
            update inv_toUpdateWithActuals;
        }
    }
}

So trace the execution path here if you're changing Prod__c records from Forecast to Actual. The trigger's going to execute in before update context, but not before insert. You'll collect Actual records, but not Forecasts (we're supposing a homogenous group).

So no Inv__c records get created, and inv_afterInsertForecast is empty.

We get down to your if (prod_Actual.isEmpty() == false) loop and enter the if. Then, the for looks at that empty collection inv_afterInsertForecast and... does nothing.

You'll never get those Inv__c records updated because you never query them in before update context. The code that runs on insert hasn't happened.

Issue 3

Are my parameters correct that I did before insert, before update on the 1st line of my code? (was thinking scenario 2 would be after insert, after update)

It doesn't appear to me to make a difference in these scenarios, since you're not using the Id of the Prod__c object and you're not performing any updates against Prod__c itself. If you were using the Id, you'd need after insert.

Issue 4

How could I have done this differently using Map, Set?

I would refactor this trigger first before worrying about this question. You'll find that you likely need a Map in your before update scenario - you'll query your Inv__c records, and then you'll want to postprocess them into a Map<String, Inv__c, where your keys will be a concatenation of the three parameters you use to uniquely identify the Inv__c records you want for any given Prod__c. That will let you pull them with a Map.get() rather than a combinatorial search (visit every Inv__c for every Prod__c), which is slow.

I tried to separate both scenarios using Trigger.IsBefore/ Trigger.isUpdate/ Trigger.isInsert but had an inkling that separating by record types should be good enough

You need to implement a trigger handler class with separate methods for each trigger event due to issues like the above. You'll find it'll also make your code a lot easier to understand - getting confused about which code executes in which context is one of the major flaws of putting everything into the trigger body.