function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
gregusagregusa 

Need Help SOQL Query not returning a value in Trigger?

This is an excerpt of code running in an After Update query:

 

 

    
Set<Id> setOpptsToDelete = new Set<Id>();

// Loop through the Opportunities being updated or deleted and see if there are any for the record types we want.

if(trigger.isDelete){
for (Opportunity o : Trigger.old) {
If (s.contains(o.RecordTypeId)){
setOpptsToDelete.add(o.Id);
}
}
}

List<AdvisorGAS_Production__c> listAdvisorGasDelete = new List<AdvisorGAS_Production__c>();

for (AdvisorGAS_Production__c agpDelete : [Select Id from AdvisorGAS_Production__c where Opportunity__c in :setOpptsToDelete]) {
listAdvisorGasDelete.add(agpDelete);
}

 

 

The query in red returns nothing yet when I manually query against the specific Opportunity Id (ie: Select Id from AdvisorGAS_Production__c where Opportunity__c = '0068000000X2fFg'), I get a value.

 

I can't figure out why it's not returning anything.  Here's some debug data I've set up and am getting:

 

USER_DEBUG|[51,5]|DEBUG|#### Trigger is delete. setOpptsToDelete.size = 1


|SOQL_EXECUTE_BEGIN|[98,47]|Aggregations: 0 |Select Id from AdvisorGAS_Production__c where Opportunity__c in :setOpptsToDelete

 

USER_DEBUG|[158,2]|DEBUG|###      listAdvisorGasDelete.size = 0

Best Answer chosen by Admin (Salesforce Developers) 
gregusagregusa

For anyone who stumbles upon this...

 

Ultimatey I stopped trying to handle the "After Delete" event in this trigger and moved the code that I wanted run if an Opportunity was getting deleted, to a "Before Delete" trigger.   Once I did that, it worked fine.

All Answers

TrueCloudTrueCloud

Firstly, can you add the debug statement to the existing loop.

 

 If (s.contains(o.RecordTypeId)){     

            system.debug('Id that was added' + o.id);
             setOpptsToDelete.add(o.Id);
      }        

 

 

I think the issue here is that you are using Trigger.old for your for loop. I think the method should be Trigger.new. If you want to access the value that is for the record before it is updted then you need to use the following function

 

System.Trigger.oldMap.get(Field Name); and access the data accordingly.

 

Let me know if you see the id in the debug log and then we can perhaps proceed to work around this. Also, when is the trigger being fired. Can you provide the whole snippet.

 

gregusagregusa

First of all, thanks for helping!

 

Trying your suggestions, first I tried changing to trigger.new but got this null error when trying to delete an Opportunity...

 

System.NullPointerException: Attempt to de-reference a null object

Trigger.AdvisorGasSyncFromOpportunity: line 37, column 25

 

Here's the debug logged (cleaned up a bit to make it easier to read):

 

USER_DEBUG|[30,2]|DEBUG|###  Trigger is Update = false
USER_DEBUG|[31,2]|DEBUG|###  Trigger is Insert = false
USER_DEBUG|[32,2]|DEBUG|###  Trigger is Delete = true
USER_DEBUG|[33,2]|DEBUG|###  Trigger is Undelete = false
USER_DEBUG|[39,5]|DEBUG|#### Record Type set = 6
USER_DEBUG|[41,4]|DEBUG|Id that was added 0068000000X2ftlAAB
USER_DEBUG|[45,5]|DEBUG|#### Trigger is delete. Count of Oppt in Trigger.old before Record Type filter = 1
USER_DEBUG|[46,5]|DEBUG|#### Trigger is delete. opptsToDelete.size = 1
USER_DEBUG|[47,5]|DEBUG|#### Oppt Record Type ID = 01280000000Ell4AAC
SOQL_EXECUTE_BEGIN|[87,41]|Aggregations:0|Select Id from AdvisorGAS_Production__c where Opportunity__c in :opptsToUnDelete
SOQL_EXECUTE_BEGIN|[93,41]|Aggregations:0|Select Id from AdvisorGAS_Production__c where Opportunity__c in :opptsToDelete
SOQL_EXECUTE_BEGIN|[98,41]|Aggregations:0|Select Id from AdvisorGAS_Production__c where Opportunity__c in :opptsToUpdate
SOQL_EXECUTE_BEGIN|[106,22]|Aggregations:0|Select AccountId, Amount, CloseDate, Contact__c, Contact_Split__c, Contact_2nd__c, Contact_2nd_Split__c, Contact_3rd__c, Contact_3rd_Split__c, Contact_4th__c, Contact_4th_Split__c, StageName, Target_Premium__c, RecordTypeId, Product_Premium_Factor__c from Opportunity where Id in :opptsToUpdate
USER_DEBUG|[151,2]|DEBUG|### listAdvisorGasUnDelete.size = 0
USER_DEBUG|[152,2]|DEBUG|### listAdvisorGasDelete.size = 0
USER_DEBUG|[153,2]|DEBUG|### AdvisorGasInsert.size = 0

 

 

 

Here's the complete trigger code, sorry for the length!  There are a lot of debug statements I setup:

 

 

trigger AdvisorGasSyncFromOpportunity on Opportunity (after delete, after undelete, after update) {

// Create RecordType variable for us to use
List<RecordType> rtList = [SELECT Id, Name, SObjectType FROM RecordType
WHERE
((SobjectType = 'Opportunity') AND (Name = 'Annuity' OR Name = 'Life' OR Name='LTC' OR Name = 'TLC'))
OR
(SobjectType = 'AdvisorGAS_Production__c' AND (Name = 'Fixed Annuity Sales' OR Name = 'Life/LTC/TLC Sales'))
ORDER BY Name];

// Convert the List values to a set of type Id
Set<Id> s = new Set<Id>();

for (RecordType rt : rtList){
s.add(rt.Id);
}

// Create a list for Opportunities for processing
List<Opportunity> opptsToDelete = new List<Opportunity>();
List<Opportunity> opptsToUnDelete = new List<Opportunity>();
List<Opportunity> opptsToUpdate = new List<Opportunity>();

// For Debugging
Integer ocount = 0;
Integer OldCount = 0;
Boolean TriggerUpdate = Trigger.isUpdate;
Boolean TriggerInsert = Trigger.isInsert;
Boolean TriggerDelete = Trigger.isDelete;
Boolean TriggerUndelete = Trigger.isUndelete;
System.debug('### Trigger is Update = ' + TriggerUpdate);
System.debug('### Trigger is Insert = ' + TriggerInsert);
System.debug('### Trigger is Delete = ' + TriggerDelete);
System.debug('### Trigger is Undelete = ' + TriggerUndelete);

// Loop through the Opportunities being updated or deleted and see if there are any for the record types we want.
if(trigger.isDelete){
for (Opportunity o : Trigger.old) {

system.debug('#### Record Type set = ' + s.size());
OldCount += 1; //for debugging
system.debug('Id that was added' + o.id);
If (s.contains(o.RecordTypeId)){
opptsToDelete.add(o);
}
system.debug('#### Trigger is delete. Count of Oppt in Trigger.old before Record Type filter = ' + OldCount);
system.debug('#### Trigger is delete. opptsToDelete.size = ' + opptsToDelete.size());
system.debug('#### Oppt Record Type ID = ' + o.RecordTypeId);
}
}
else if(Trigger.isUnDelete){

for (Opportunity o : Trigger.new) {

ocount += 1; //for debugging
If (s.contains(o.RecordTypeId)){
opptsToUnDelete.add(o);
system.debug('#### Oppt Record Type ID from within for loop = ' + o.RecordTypeId);
}

system.debug('#### Trigger is UnDelete. Count of Oppt in Trigger.new = ' + ocount);
system.debug('#### Trigger is UnDelete. opptsToUnDelete.size = ' + opptsToUnDelete.size());
system.debug('#### Oppt Record Type ID = ' + o.RecordTypeId);
}
}
else if(Trigger.isUpdate) {

System.debug('### Overall Trigger.old count = ' + Trigger.old.size());
System.debug('### Overall Trigger.new count = ' + Trigger.new.size());

for (Opportunity o : Trigger.new) {

ocount += 1; //for debugging
If (s.contains(o.RecordTypeId)){
opptsToUpdate.add(o);
}

system.debug('#### Trigger is Update. Count of Oppt in Trigger.new = ' + ocount);
system.debug('#### Trigger is Update. opptsToUpdate.size = ' + opptsToUpdate.size());
system.debug('#### Oppt Record Type ID = ' + o.RecordTypeId);
}


}

//### PREP UNDELETE
List<AdvisorGAS_Production__c> listAdvisorGasUnDelete = new List<AdvisorGAS_Production__c>();
for (AdvisorGAS_Production__c agp : [Select Id from AdvisorGAS_Production__c where Opportunity__c in :opptsToUnDelete]) {
listAdvisorGasUnDelete.add(agp);
}

//### PREP DELETE for DELETE LIST of Oppts
List<AdvisorGAS_Production__c> listAdvisorGasDelete = new List<AdvisorGAS_Production__c>();
for (AdvisorGAS_Production__c agp : [Select Id from AdvisorGAS_Production__c where Opportunity__c in :opptsToDelete]) {
listAdvisorGasDelete.add(agp);
}

//### PREP DELETE for existing UPDATE LIST since new will be inserted
for (AdvisorGAS_Production__c agp : [Select Id from AdvisorGAS_Production__c where Opportunity__c in :opptsToUpdate]) {
listAdvisorGasDelete.add(agp);
}

//Variable to store our Inserts to be made into AdvisorGas
List<AdvisorGAS_Production__c> AdvisorGasInsert = new List<AdvisorGAS_Production__c>();

//Add to Insert AdvisorGas Records
for(Opportunity o : [Select AccountId, Amount, CloseDate, Contact__c, Contact_Split__c, Contact_2nd__c, Contact_2nd_Split__c, Contact_3rd__c, Contact_3rd_Split__c, Contact_4th__c, Contact_4th_Split__c, StageName, Target_Premium__c, RecordTypeId, Product_Premium_Factor__c from Opportunity where Id in :opptsToUpdate]) {

//Make sure Oppt is WON before creating any AGP!!!
If ((o.StageName == 'Won') || (o.StageName == 'Closed Won')){

system.debug('#### Oppt is Won, inside the loop!');
RecordType newGasType = new RecordType();
Decimal amount = null;

system.debug('### Recordtype rtList.get(0) = ' + rtList.get(0).Name + ' ' + rtList.get(0).SObjectType);
system.debug('### Recordtype rtList.get(1) = ' + rtList.get(1).Name + ' ' + rtList.get(1).SObjectType);
system.debug('### Recordtype rtList.get(2) = ' + rtList.get(2).Name + ' ' + rtList.get(2).SObjectType);
system.debug('### Recordtype rtList.get(3) = ' + rtList.get(3).Name + ' ' + rtList.get(3).SObjectType);
system.debug('### Recordtype rtList.get(4) = ' + rtList.get(4).Name + ' ' + rtList.get(4).SObjectType);
system.debug('### Recordtype rtList.get(5) = ' + rtList.get(5).Name + ' ' + rtList.get(5).SObjectType);

//Annuity
if(o.RecordTypeId == rtList.get(0).Id){
newGasType = rtList.get(1);
amount = o.Amount;
}
//Life/LTC/TLC
else if((o.RecordTypeId == rtList.get(2).Id) || (o.RecordTypeId == rtList.get(4).Id) || (o.RecordTypeId == rtList.get(5).Id)) {
newGasType = rtList.get(3);
amount = o.Target_Premium__c;
}

If(o.Contact_Split__c == 100){

system.debug('### newGasType RecordType = ' + newGasType.Name + ' ' + newGasType.SObjectType);
system.debug('### Amount being used for AGP = ' + amount);

//create only 1 agp
AdvisorGAS_Production__c agp = new AdvisorGAS_Production__c();
agp = AdvisorGasSyncClass.setUpAdvisorGasProduction(agp, o, o.Contact__c, newGasType, amount);
AdvisorGasInsert.add(agp);
}
else {
//create as many as there are reps on it
//Check for additional advisors to determine whether we should create more than one taking into account the Split %.
}

}
}

system.debug('### listAdvisorGasUnDelete.size = ' + listAdvisorGasUnDelete.size());
system.debug('### listAdvisorGasDelete.size = ' + listAdvisorGasDelete.size());
system.debug('### AdvisorGasInsert.size = ' + AdvisorGasInsert.size());

undelete listAdvisorGasUnDelete;
delete listAdvisorGasDelete;
insert AdvisorGasInsert;

}

 

 

 

 

TrueCloudTrueCloud

This doesn;t have a line number in the code snippet. Can you highlight the line that is getting dereferenced.

TrueCloudTrueCloud

Try adding at the end of your trigger the following statement. This will first check if the list is not null. Once we can alleviate that we can then try and see if right opportunities are being added to the list.

 

if (ListAdvisorGasDelete != null) {
delete listAdvisorGasDelete
}
gregusagregusa

This is the line that is null - trying to loop through trigger.new when it's a delete.

 

// Loop through the Opportunities being updated or deleted and see if there are any for the record types we want. 
if(trigger.isDelete){
for (Opportunity o : Trigger.old)

 

There's no need to check for null in the last one because it has no count and therefore it doesn't try the delete.

 

Greg

 

 

gregusagregusa

For anyone who stumbles upon this...

 

Ultimatey I stopped trying to handle the "After Delete" event in this trigger and moved the code that I wanted run if an Opportunity was getting deleted, to a "Before Delete" trigger.   Once I did that, it worked fine.

This was selected as the best answer