typed dataset, cascade delete of child table records

Question

I have a typed dataset with two tables like this:

Table A (Parent Table)

ID  MessageID   
1     1
2     1
3     2
4      3

Table B (Child Table)

ID  MessageID   
1     1
2     2
3     3

I have a relationship between Table A and Table B on the MessageID column. The delete rule is set to Cascade. I cannot change the design of these tables, it is what it is.

Table A contains two records with MessageID 1. If I delete only one of those, the child record is deleted from Table B, creating an orphan in the second parent table. Is there a way to only delete if there are no other parent records sharing the MessageID?

Answer 1

You could create a trigger instead of the cascade-delete, for example(MS SQL-Server, untested):

CREATE TRIGGER [dbo].[trDeleteTableB] ON [dbo].[TableA]
    FOR DELETE
    AS
    DELETE FROM TableB
    WHERE (MessageID IN
         (SELECT MessageID
           FROM  DELETED
           WHERE (NOT EXISTS
                 (SELECT * FROM  TableA
                  WHERE (TableA.MessageID = DELETED.MessageID)))))

Another idea is to extend your typed Dataset. If you want to extend the functionality you can't change the generated classes in the DatesetName.designer.cs/vb (it will be recreated on any change) but the file without designer in its name(create it if it not exists). Then you have to extend the partial DataTable-class(look in the designer-file if you don't know the exact name, it's normally f.e. TableBDataTable).

Have a look at following code to see what i mean, i haven't tested it but i hope you take my point:

Partial Class Datset1
    Partial Class TableBDataTable
        Private Sub TableB_RowDeleting(ByVal sender As Object, ByVal e As TableBRowChangeEvent) Handles Me.TableBRowDeleting
            If e.Action = DataRowAction.Delete Then
                '*** check here if there is another TableARow with this MessageID ***'
                e.Row.RejectChanges()
            End If
        End Sub
    End Class
End Class