SQL Output on Cascaded Delete

Question

Is it possible to output values from a cascaded delete in SQL?

Something like:

DELETE [Families]
    OUTPUT [deleted].[FamilyID] [deleted].[FamilyName], [Members].[MemberName]
    FROM [Families]
    LEFT JOIN [Members]
        ON [Members].[FamilyID] = [Families].[FamilyID]

If there were two families with three total members, it should yield something like:

[FamilyID]   [FamilyName]   [MemberName]
 12           'Family A'     'Member 1'
 12           'Family A'     'Member 2'
 13           'Family B'     'Member 3'

It is also acceptable for a solution to yield 1 row per family with the cascade-deleted members in a delimited list. Something like:

[FamilyID]   [FamilyName]   [MemberNames]
 12           'Family A'     'Member 1|Member 2'
 13           'Family B'     'Member 3'

Answer 1

This looks more like a Select rather than a Delete

You could use a before delete trigger to add the deleted records info to a log table and then select that table

Answer 2

The best solution I could find yields two result sets:

1. Select the IDs of the families to delete into a table variable.
2. Delete/output family members inner-joined against the table variable.
3. Delete/output families inner-joined against the table variable.