How write tigger Insert & update other records same object with update query

Question

I have this trigger where I insert a record. On the insert I want to check on birthdate and set a checkbox on true for the record with the oldest birthdate related to same Household/Huishouden__c.

On the before insert I do not have the Id of the current record so my record on insert does not get queried.

If I use after insert I get an error for the update part of the trigger. How do I solve this?

UPDATE MORE DETAILED: Relatie__c has a lookup to account(sports club/society) with a relation number field/Relatienummer__c. Relatie__c has also a lookup to contact. If Relatie__c is created for the fist time a memberschip is created :an adres/record on an another object Huishouden__c(Household: same adres as contact. If the Household/Huishouden__c exists I need to place a check on the record of the oldest relation/Relatie__c so this becomes the Main responsible for this Household/Huishouden__c.

Trigger

    trigger Huishouden on Relatie__c(before update, before insert, before delete ) {

                                if ( trigger.isInsert ) {
                                                HuishoudenTriggerHandler.onBeforeInsert(trigger.new);
                                } else if ( trigger.isUpdate ) {
                                                HuishoudenTriggerHandler.onBeforeUpdateOrDelete(trigger.new);
                                } else if ( trigger.isDelete ) {
                                                HuishoudenTriggerHandler.onBeforeUpdateOrDelete(trigger.new);
                                }

}

Handler

public class HuishoudenTriggerHandler {
public static void onBeforeInsert(Relatie__c[] rcs) {
    // EXECUTE BEFORE INSERT LOGIC  
    Map<Id,Account> accountIdMap; 
    List<Id> setOfId = new List<Id>(); 
    for(Relatie__c rc : rcs) { 
        setOfId.add(Rc.Account__c); 
    } 
    accountIdMap =new Map<Id,Account>([SELECT Relatienummer__c 
                                       FROM Account 
                                       WHERE Id in :setOfId]); 
    for(Relatie__c rc : rcs) { 
        Account acc = accountIdMap.get(Rc.Account__c); 
        List<Contact> cont =[SELECT MailingPostalCode, mailingstreet, FirstName, LastName  FROM Contact WHERE Id = :Rc.Contactpersoon__c];

        if(rc.Relatietype__c == 'Lid') {
            List<Huishouden__c> huishList = [SELECT id, name
                                             FROM Huishouden__c 
                                             WHERE Name= :cont[0].MailingPostalCode + cont[0].mailingstreet + ' '+ acc.Relatienummer__c]; 
            system.debug('huishList '+huishList);
            if(huishList.size()>0) { 
                system.debug('>0');
                rc.Huishouden__c = huishList[0].id ; 
                system.debug('huishList[0].id '+huishList[0].id);
                List<Relatie__c> rcList = [SELECT id, Contact_Birthdate__c
                                           FROM Relatie__c 
                                           WHERE Huishouden__c = :huishList[0].id
                                           ORDER BY Contact_Birthdate__c ASC]; 
                 system.debug('rcList '+rcList);

                for(Relatie__c rch : rcList ) { 
                            rch.Hoofdlid_huishouden__c= false; 
                        } 
                rcList[0].Hoofdlid_huishouden__c = true;
                system.debug('rcList[0].Hoofdlid_huishouden__c = '+rcList[0].Hoofdlid_huishouden__c);
                Upsert rcList;
                } 

Answer 1

Here's what I would suggest as a general solution to your problem.

Create set of Ids for Rc.Contactpersoon__c.

set<Id>rdContacts = new set<Id>; 
for(Relatie__c rc : rcs) { 
    rcContacts.add(rc.Contactpersoon__c);
}

Then, your contact query can be done outside of the for(Relatie__c rc : rcs) { loop as something like:

List<Contact> cont =[SELECT MailingPostalCode, mailingstreet, FirstName,
     LastName, AccountId, Type__c  FROM Contact WHERE Id = IN: rcContacts];

After querying, put the results into a map of either AccountId to Contact or ContactId to AccountId.

Then, you can iterate in a loop on Realatie__c from your trigger as you originally have, but use your maps to pull out the relationships as needed.

I'm not entirely certain what this query is:

if(rc.Relatietype__c == 'Lid') {
        List<Huishouden__c> huishList = [SELECT id, name
                                         FROM Huishouden__c 
                                         WHERE Name= :cont[0].MailingPostalCode + cont[0].mailingstreet + ' '+ acc.Relatienummer__c];

But it looks like a query to get the address for your contacts and their current relationship position within the account, perhaps by Household. This can also be done outside the loop if you include the accountId in the Select clause. You can then create another map of what appears to be householdId to Account or possibly even two maps if needed if you include one for Contact.

My point being here, that you should can bulkify this into single queries on existing Contacts, Accounts, and Household before you start any looping.

It would only be if you don't find a match that you'd need to create a new Contact, Account or Household if doing a 'before' trigger. If you do an 'after' trigger, you'd always have everything you need and wouldn't run into any issues with SOQL limits.