System.QueryException: List has no rows - How can I tie Events created for qualified Contacts to Opps created from the Contact?

Question

Our org was configured such that Opps can only be created during Lead Conversion or from a Contact related list. This ensures each Opp has a Contact Role associated.

SDRs create an Event when they have qualified a prospect (sometimes on a Lead, sometimes on a Contact) and set a meeting. If it's on a Lead, once the Lead is converted with an Opp created, the Event automatically associates with the Opp.

If the Event was created on a Contact which was qualified and an Opp is later created off the Contact, the Event doesn't associate with the Opp.

Here's my trigger for associating Events to an Opp from Contacts associated as Contact Roles on the Opp.

trigger AddMQLEventstToOpp on Opportunity (after update) {

    for (Opportunity myOpp : Trigger.new) {
        OpportunityContactRole contactRole = [select ContactId from OpportunityContactRole where OpportunityId = :myOpp.Id];
        String cRoleId = contactRole.ContactId;

        List<Event> mqlEventsToAdd = new List <Event>();
        mqlEventsToAdd.add([select Id from Event where WhoId = :cRoleId and WhatId = null and Type = 'MQL']);
        for (Event mqlEvent : mqlEventsToAdd){
            mqlEvent.WhatId = myOpp.Id;
            update mqlEvent;
        }   
    }
}

I'm getting this error and not sure how to troubleshoot.

System.QueryException: List has no rows for assignment to SObject: External entry point

Answer 1

Woah! Never query or perform DML in a loop! You do two queries and an update within a loop in this trigger! And your update is within a further nested loop!

In addition to having no guarantee that these records will even exist, you're chewing through governor limits at an incredible rate. With this code in place, you will never be able to update more than 50 Opportunity records at a time.

The basic pattern is known as Query-Transform-Load. You do your transformations within your loop. Move everything else outside of it.

Query

// this first loop combines two common patterns that allow you to
// use a collection to move your query outside the loop

Set<Id> contactIds = new Set<Id>();
Map<Id, List<OpportunityContactRole>> ocrs = new Map<Id, List<OpportunityContactRole>>();
for (OpportunityContactRole ocr : [
    SELECT OpportunityId, ContactId FROM OpportunityContactRole
    WHERE OpportunityId IN :trigger.new
]){
    contactIds.add(ocr.ContactId);
    if (!ocrs.containsKey(ocr.OpportunityId))
        ocrs.put(ocr.OpportunityId, new List<OpportunityContactRole>());
    ocrs.get(ocr.OpportunityId).add(ocr);
}

// you may recognize this pattern
Map<Id, List<Event>> events = new Map<Id, List<Event>>();
for (Event record : [
    SELECT WhoId FROM Event
    WHERE WhoId IN :contactIds
    AND WhatId = null
    AND Type = 'MQL'
]){
    if (!events.containsKey(record.whoId))
        events.put(record.whoId, new List<Event>());
    events.get(record.whoId).add(event);
}

Transform

Now the key (pun intended) is simply to extract your data from these collections.

for (Opportunity record : trigger.new)
{
    if (!ocrs.containsKey(record.Id)) continue;
    for (OpportunityContactRole ocr : ocrs.get(record.Id))
    {
        if (!events.containsKey(ocr.ContactId)) continue;
        for (Event event : events.get(ocr.ContactId))
            event.WhatId = record.Id;
    }
}

Load

Now you can just flatten your events collection and make one update call. Now this entire trigger consumes just two queries and one DML Statement.

List<Event> eventsToUpdate = new List<Event>();
for (List<Event> contactSpecificEvents : events.values())
{
    eventsToUpdate.addAll(contactSpecificEvents);
}
update eventsToUpdate;

That's the basic idea to bulkification. You should also have a read of General trigger bulkification - best practices.