CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Lorenzo
Lorenzo

Reputation: 29427

SQL server Delete script order

My tables have the following relations

enter image description here

As you can see FirstEntity can have multiple Transactions related record. Transaction is divided into two tables because it represents an inheritance hierarchy (Table Per Type in Entity Framework).

I need to create a script which delete all record from FirstEntityTransaction and Transaction given a FirstEntityID. The delete should then follow the following order:

The problem is that when I execute first delete (FirstEntityTransaction) I do not have any way to find the related transactions by TransactionID. Is there any way that I can save those IDs and then execute the second delete?

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

Upvotes: 4

Views: 292

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46223

Below is an example that inserts deleted entity transactions into a table variable, which is subsequently used to delete the Transaction rows.

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;

Upvotes: 6

Related Questions