Apex Code Development You need to sign in to do that
Don't have an account?
Collaborative e Opportunity Trigger Creating Unhandled Trigger Exception
Newer developer here looking for some guidance. I developed the following trigger that queries information on opportunities tied to a campaign and populates opportunity amount data from the opportunities up to the campaign:
trigger rollupBranchAVtoCampaign on Opportunity (after delete, after insert, after undelete,
after update) {
double sumTotalRollUpBranchAVOpportunities = 0.0;
double sumTotalBookedRollupBranchAVOpportunities = 0.0;
Campaign [] revenueToUpdate = new Campaign[]{};
//***********************************************
//Code for updating existing records and new records
//***********************************************
if(Trigger.isInsert)
{
Opportunity [] opNew = trigger.new;
for(Opportunity op : opNew)
{
for (Campaign totalRevenue : [select Id, Name, Roll_up_Booked_AV_Branch__c,Roll_Up_Open_AV_Branch__c from Campaign where Id = :op.CampaignId])
{
//Sum all the Branch AV opportunities
for (Opportunity opptyRevenue: [select Id, Roll_up_Booked_AV_Branch__c,Roll_Up_Open_AV_Branch__c from Opportunity where CampaignId = :totalRevenue.id])
{
//gather all formula currency fields for record types from opportunities tied to event campaign
sumTotalRollUpBranchAVOpportunities += opptyRevenue.Roll_Up_Open_AV_Branch__c;
sumTotalBookedRollupBranchAVOpportunities += opptyRevenue.Roll_up_Booked_AV_Branch__c;
}
//set campaign fields to variables from above
totalRevenue.Roll_up_Booked_AV_Branch__c = sumTotalRollUpBranchAVOpportunities;
totalRevenue.Roll_Up_Open_AV_Branch__c = sumTotalBookedRollupBranchAVOpportunities;
//add opportunity to list to be updated outside of the loop
revenueToUpdate.add(totalRevenue);
}
}
//commit the changes to Salesforce
update revenueToUpdate;
}
//***********************************************
//Code for updating when a record is updated
//***********************************************
else if(Trigger.isUpdate)
{
//sum total both old and new
Opportunity [] oldOppty = Trigger.old;
Opportunity [] newOppty = Trigger.new;
Double newSumBranchAV = 0.0;
Double oldSumBranchAV = 0.0;
Double OldSumBranchAVBooked = 0.0;
for(Opportunity oldOp : oldOppty)
{
Campaign oldTotalBranchAVValue = [Select Id, Name, Roll_up_Booked_AV_Branch__c,Roll_Up_Open_AV_Branch__c from Campaign where Id = :oldOp.CampaignId];
Opportunity [] oldSumBranchAVRevenues = [Select Id, Name, Roll_up_Booked_AV_Branch__c,Roll_Up_Open_AV_Branch__c from Opportunity where CampaignId = :oldTotalBranchAVValue.Id];
//sum premiums from child objects
for(Opportunity oldSumBranchAVRevenue : oldSumBranchAVRevenues)
{
oldSumBranchAV += oldSumBranchAVRevenue.Roll_Up_Open_AV_Branch__c;
oldSumBranchAVBooked += oldSumBranchAVRevenue.Roll_up_Booked_AV_Branch__c;
}
oldTotalBranchAVValue.Roll_Up_Open_AV_Branch__c = oldSumBranchAV;
oldTotalBranchAVValue.Roll_up_Booked_AV_Branch__c = oldSumBranchAVBooked;
revenueToUpdate.add(oldTotalBranchAVValue);
}
update revenueToUpdate;
}
//***********************************************
//Code for updating when a record is deleted
//***********************************************
else if(Trigger.isDelete)
{
Opportunity [] opptyOld = trigger.old;
for(Opportunity op: opptyOld)
{
for (Campaign totalRevenue: [select Id, Name, Roll_Up_Open_AV_Branch__c,Roll_up_Booked_AV_Branch__c from Campaign where Id = :op.CampaignId])
{
for (Opportunity opptyRevenue: [select Id, Roll_Up_Open_AV_Branch__c,Roll_up_Booked_AV_Branch__c from Opportunity where CampaignId = :totalRevenue.id])
{
sumTotalRollUpBranchAVOpportunities += opptyRevenue.Roll_Up_Open_AV_Branch__c;
sumTotalBookedRollupBranchAVOpportunities += opptyRevenue.Roll_up_Booked_AV_Branch__c;
}
totalRevenue.Roll_Up_Open_AV_Branch__c = sumTotalRollUpBranchAVOpportunities;
totalRevenue.Roll_up_Booked_AV_Branch__c = sumTotalBookedRollupBranchAVOpportunities;
revenueToUpdate.add(totalRevenue);
}
}
update revenueToUpdate;
}
}
When I run the code in sandbox it works fine, performing just want I want it to peform.
When I then tried to do a data loader to enact the trigger on multiple opportunities I got the following error:
Apex script unhandled trigger exception by user/organization: 00540000000sPw1/00DS0000000FLsO
Source organization: 00D300000000O4F (null)
rollupBranchAVtoCampaign: execution of AfterUpdate
caused by: System.QueryException: List has no rows for assignment to SObject
Trigger.rollupBranchAVtoCampaign: line 59, column 26
Any thoughts on what I might be doing that's causing the trigger exception?
The source of the error would be either of these lines:
Campaign oldTotalBranchAVValue = [Select Id, Name, Roll_up_Booked_AV_Branch__c,Roll_Up_Open_AV_Branch__c from Campaign where Id = :oldOp.CampaignId]; Opportunity [] oldSumBranchAVRevenues = [Select Id, Name, Roll_up_Booked_AV_Branch__c,Roll_Up_Open_AV_Branch__c from Opportunity where CampaignId = :oldTotalBranchAVValue.Id];
This will occur if your query doesn't have any results (i.e. you have incomplete data in your testMethod). As a side-note, you really should "bulkify" your code-- it will fail if you attempt to load more than a small handful of records at once (here's a tip: if you have [select ... ] inside the body of a for() loop, you're doing it wrong). If you need further assistance after reading the article, feel free to ask the community.
Thanks for the insight Fox. So would you suggest running my SOQL Query in a list and then putting the list (if it contains results) into a for loop?
Just wanting to make sure I'm running down the cleanest path.
Usually, how I handle this requirement is to create a map (especially useful if the records are not linked by Record ID, but there are a ton of uses for maps). For example, let us say I had a custom field on an object called "Product Code." This is used in lieu of a Product2 ID because the data comes from a hypothetical other system that does not understand Salesforce ID values, and can not be easily coerced to do so (or, more likely, not at all). This might be one way to handle this scenario:
// Input: Array of Opportunities with a Product Code. // Output: Opportunities updated with a Product2 ID. public void findProducts(List<Opportunity> records) { // Map a relationship between product codes and the product. // Note that this hypothecial code presumes that there are no // duplicate product codes, and all products have codes. Map<String,Id> ProductCodes = new Map<String,Id>(); // Add the product codes to the map. for(Opportunity opp:records) ProductCodes.put(opp.Product_Code__c,null); // Find all products that match the inputted codes. for(Product2 product:[select id,productcode from product2 where productcode in :ProductCodes.keyset()]) ProductCodes.put(product.productcode,product.id); // Put the mapped values back into the opportunity. for(Opportunity opp:records) opp.Related_Product__c = ProductCodes.get(opp.Product_Code__c);
Inside a trigger, this would locate the product for each opportunity and place the link to the product back into the opportunity. If there is no matching product, the field will be blank (null). In the advent of duplicates, one would be selected (based on the database's internal query sorting algorithm) to be the "winner" in the case that there are duplicate products.
This is a contrived example that outlines the basic concepts of how to use maps to look up related data:
1) Create a Set (I simply use the map's key set to accomplish this) of the lookup values you'd like to use.
2) Loop through a query (a for/select loop counts as only one query, while placing a query inside a loop causes one query per loop execution), and place the results into the map. In the advent that you are querying based on record IDs, and the values for the keys are the record themselves, you can create a new map instead. That syntax looks like this:
// Create a map. Map<Id,Product2> ProductIds = new Map<Id,Product2>(); // Loop through the records, placing IDs into the map. for(Opportunity opp:records) ProductIds.put(opp.Related_Product__c,null); // Create a new in-memory map, using the same variable. ProductIds = new Map<Id,Product2>([select id,productcode from product2 where id in :productids.keyset()]); // Update the opportunity product codes. for(Opportunity opp:records) if(ProductIds.get(opp.Product_Id__c)<>null) // Avoid null records opp.Product_Code__c = ProductIds.get(opp.product2).ProductCode;
3) Go back into the original list and update the values from what you've queried into the map.
This simple, yet effective,technique will assist you in not overrunning your governor limits and be able to update bulk amounts of records with ease.