+ Start a Discussion

Trouble returning deleted records with ALL ROWS key word

I have a procedure that can be triggered by an update to or deletion of any one of several inter-related objects based around Opportunities. The trigger retrieves the related Opportunity and all the relevant sub-records in all the relevant objects by way of a single query in the form of



Opportunity[] opps = Database.query('SELECT [...fields and children...] FROM Opportunity WHERE Id IN (SELECT Opportunity__c FROM '+childObjectName+' WHERE Id IN :triggeredIds)');


The trouble, of course, is that when this is coming from a delete trigger, it finds no Opportunities related to the deleted record. I tried putting in the keyword 'ALL ROWS' but when this was placed outside the WHERE clause subselect it returned no records and when it was placed within the WHERE clause subselect (shown below), I received the error "Expecting ')'. Found ALL"


FROM Opportunity WHERE Id IN (SELECT Opportunity__c FROM '+childObjectName+' WHERE Id IN :triggeredIds ALL ROWS)');


 Does anyone know If there is a proper syntax for retrieving non-deleted parents of deleted child records using subselects?


How are you passing the values to triggeredIds, is it a trigger.old or trigger.new



It would be great if you could post your code



It passes Trigger.oldMap from the delete trigger and trigger.newMap for all other cases. I've already verified that the keys are passed into the SOQL statement correctly, so if possible, i just need to find out the SOQL syntax needed to retrieve parent records through their relationship to deleted child records.