SQL Server Remove Script Order

Question

My tables have the following relationships

As you can see, there FirstEntitymay be several related entries Transactions. TransactionIt is divided into two tables, because it represents a hierarchy of inheritance (a table by type in the Entity Framework).

I need to create a script that deletes the entire record from FirstEntityTransactionand Transactiontaking into account FirstEntityID. Then the removal should be performed in the following order:

• Delete all entries from FirstEntityTransaction
• Delete all entries from Transaction
• Delete entry from FirstEntity

The problem is that when I do the first delete ( FirstEntityTransaction), I have no way to find related transactions using TransactionID. Is there a way to save these identifiers and then perform a second deletion?

EDIT: I modified the message to have a more meaningful diagram

Answer 1

, , .

DECLARE @DeletedMyEntityTransaction table ( TransactionID int );

DELETE  dbo.MyEntityTransaction
OUTPUT  deleted.TransactionID
        INTO @DeletedMyEntityTransaction
WHERE   MyEntityID = @MyEntityID;

DELETE  dbo.[Transaction]
WHERE   TransactionID IN ( SELECT   TransactionID
                           FROM     @DeletedMyEntityTransaction );

DELETE  dbo.MyEntity
WHERE   MyEntityID = @MyEntityID;