How to query related Contact from Account by Id in "after insert, before update" trigger?

Question

I need to query the related Contact after a new Account is inserted and before it is updated, but I am encountering an error: System.QueryException: List has no rows for assignment to SObject. I've tried a few solutions but nothing so far works to query the Contact.

The SELECT statement works in my Apex Test file AND in the Execute Anonymous window, but it does not work here in the trigger!

Note: The account is automatically created when a field on the Contact is updated to match a specific value in a picklist i.e. when someone in SF changes Contact's the Lead_Status__c field to 'Converted'.

Here's a sample of the code:

trigger Postback_Trigger on Account (after insert, before update) {
    for (Account newAccount : Trigger.new) {

        // This throws the System.QueryException error
        Contact c = [
            SELECT Id, AccountId, Lead_Number__c, Bank_Name__c
            FROM Contact
            WHERE Account.Id = :newAccount.Id
        ];
        // Very similar to ^^ but throws the same error
        // WHERE AccountId = :newAccount.Id

        // a.Contacts is an empty object
        Account a = [
            SELECT Account.Name,
                (SELECT Contact.Lead_Status__c, Contact.Bank_Name__c FROM Account.Contacts)
            FROM Account
            WHERE Id = :newAccount.Id
        ];
        System.debug(a.Contacts);

        if (Bank_Name__c.containsIgnoreCase('test') {
            // Send data to future handler...
        }
    }
}

I've looked here: https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL_foreign_key.htm

And here: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_understanding.htm

Help!?

Answer 1

There are several issues with this code (you should never¹ have queries inside of a loop), but the fundamental problem you're running into is one of how relationships work.

The immediate error that you're facing, List has no rows for assignment to SObject is because you're taking the result of a query (which always returns a list, even if it's an empty list) and putting it into a single SObject variable. Salesforce allows the SObject record = [some query]; syntax a "syntactic sugar". It's a nice quality-of-life thing when it works, but it requires that your query return exactly one row. 0 rows and >= 2 rows will cause issues.

The reason that your query is returning 0 rows is because your contact and account are not yet related to one another. If you're creating Accounts from a trigger on Contact, then it's impossible for you query based on Contact.AccountId in your Account trigger.

You can't relate two records until they both have Ids. Based on your description, I imagine you're doing something like

List<Account> accountsToInsert = new List<Account>();
for(Contact c :trigger.new){
    if(c.Lead_Status__c ==  'Converted'){
        accountsToInsert.add(new Account());
    }
}
insert accountsToInsert;

Your Contacts have Ids, and you're correct in thinking that the Accounts will be assigned Ids by the time you get into your trigger after insert, but there's not yet any correlation between the two. Unless you have another way to tie a newly inserted account to an existing lead, a trigger on Account is not the right approach to take.

In general, the responsibility for populating relationship fields falls to the code that causes the DML for the related record. Here, that would be my example code (which could be part of an update trigger on Contact).

Expanding on that example

// Best practice is to only have one trigger per SObject
// Also best practice, using a trigger framework and keeping the trigger itself free of logic.
// That's off-topic for this question, and is covered in plenty of other questions
trigger Contact on Contact(before update){
    // Using a map here will make it easy to relate the Contact to the Account
    //   after we're done with the DML
    Map<Id, Account> contactIdToAccount = new Map<Id, Account>();

    // Step 1 is creating the accounts
    for(Contact c :trigger.new){
        // skip processing records that aren't converted, or haven't changed to converted
        if(c.Lead_Status__c != 'Converted' || trigger.oldMap.get(c.Id).Lead_Status__c == c.Lead_Status__c){
            continue;
        }

        contactIdToAccount.put(c.Id, new Account(
            // set account fieldName = value pairs here
        );
    }

    // DML is another thing that should never appear in a loop
    insert contactIdToAccount.values();

    // Step 2 is to relate the newly created Accounts to their Contacts
    // Looping over trigger.new again so we can take advantage of the "before" trigger
    //   to avoid DML
    for(Contact c :trigger.new){
        c.AccountId = contactIdToAccount.get(c.Id).Id;
    }
}

The catch there being that you'd need to wait until after update on Contact before you could query for Contacts based on the Account Id (or have a static variable defined in an Apex class (not a trigger) to hold that correlation for you).

In the end, this strikes me as work better handled completely by a trigger on Contact rather than a trigger on Account.

^{1: Ok, there are a few, very specific situations where it is ok, but you need an astoundingly firm grasp on what you're doing, why you're doing it, and why the query belongs in the loop. This is definitely not one of those situations}

Answer 2

This block of code successfully returns the correct Contact because both the Account and Contact share a custom field, Lead_Number__c.

The AccountId on the Contact object is not yet assigned when the trigger executes because workflow processes (that aren't using straight Apex code) have not ran or completed yet.

Contact c = [
    SELECT Id, AccountId, Lead_Number__c, Bank_Name__c
    FROM Contact
    WHERE Lead_Number__c = :newAccount.Lead_Number__c
    LIMIT 1
];