How to determine records to be deleted in related/other tables before running DELETE statement in SQ-CodePudding

I'm relatively new to SQL Server. I wanted to delete some records from a table. I wanted to run the statement:

DELETE Table1 
WHERE COLUMN1 = 7

So, before running my query, I ran the following Query:

SELECT * 
FROM Table1 
WHERE COLUMN1 = 7

The results of the query were 3 rows.

I ran the original DELETE query in SSMS and it showed:

324 ROWS affected
0 ROWS affected
3 ROWS affected

I'm guessing there are some sort of either triggers or foreign keys somewhere that caused this statement to delete additional 324 rows in other table. I restored the database to resolve the issue.

The question is: is there a way to show/list any/all records to be deleted based on my DELETE statement before I execute it in SSMS?

CodePudding user response：

To run a data-modification query and be confident it has the desired and expected outcome, proceed as follows.

Always preceed your statement(s) with begin tran.

Make sure you retrieve the actual execution plan - (CTRL M) or Query menu > Include Actual Execution Plan.

Before running your query, run set statistics io on

Run your query, making sure you have included begin tran

Examine the execution plan tab which will show you every table involved in the query.

Look at the messages tab for the IO statistics, it will list you every table and the amount of IO performed against each.

If everything looks correct, run commit tran, or if you want to cancel the query run rollback

Be aware that if you are working on a production system your transaction could block other queries until you either commit or rollback.

CodePudding user response：

Utilized ON DELETE CASCADE this will specifies that the child data is deleted when the parent data is deleted.