CODEWITHSUNDEEP
sqlsql-servertriggers
Kairan
Kairan

Reputation: 5542

Delete Value with Relational Table Trigger vs

How do you delete values with a relational database for connected tables.

Example of Movie Database: Movie Table -> Movie_has_Genre Table -> Genre Table

If I delete a Movie I would want to delete all the rows of Movie_has_Genre table where the foreign key is the same as the id from the movie table.

Should I be using a Trigger on the Movie table (on delete... do a delete on the relational table) or is there some other built in function to handle this?

I just vaguely recall there was another way to do this but cannot remember what it was called.

Upvotes: 2

Views: 344

Answers (2)

Mark Davidson
Mark Davidson

Reputation: 414

There is an option in SQL Server to do this automatically via CASCADE settings as already shown.

This is a really handy option but make sure you don’t apply it to all of your tables that have foreign key references as it might cause unexpected loss of data. Make sure to thoroughly analyze weather this won’t cause any damage.

Another option is to use multiple delete statements starting from the tables that are referenced first.

Upvotes: 0

dcaswell
dcaswell

Reputation: 3167

You use the cascade delete statement. It's syntax looks like this:

ALTER TABLE dbo.T2
   ADD CONSTRAINT FK_T1_T2_Cascade
   FOREIGN KEY (keyId) REFERENCES dbo.T1(keyId) ON DELETE CASCADE

The complete syntax is: here

Upvotes: 2

Related Questions