Cascading SQL Server

Question

I create a site where users can post messages, and then users can "Comment" on these posts. I have a database with 3 tables. One contains information about the user, one contains information about the message, and the last contains information about the comments.

I want to configure the rules so that if the User has been deleted, all their messages and comments are deleted, and if the user deletes one of his messages, all related comments are deleted. However, this sets up "Multiple Cascade Paths".

I was looking for a solution and found some information about triggers. Will they be best used? If I used them, would I have to change all the CASCADES that will be executed using triggers?

thanks

Clivest

Answer 1

Another option is to create two dedicated stored procedures that will perform these removal steps as needed.

CREATE PROCEDURE dbo.DeleteUser @UserID VARCHAR(50) AS BEGIN DELETE FROM dbo.Comments WHERE Author = @UserID DELETE FROM dbo.Posts WHERE Author = @UserID DELETE FROM dbo.User WHERE UserID = @UserID END 

and for the second rule:

 CREATE PROCEDURE dbo.DeletePost @PostID INT AS BEGIN DELETE FROM dbo.Comments WHERE PostID = @PostID DELETE FROM dbo.Posts WHERE ID = @PostID END 

In this case, you have full control over what is actually happening, there are no unexpected surprises from cascading deletions, and there is no need to use triggers.

Answer 2

use declarative referential integrity.

 create table foo ( id int not null primary key , foo varchar(32) not null , ) create table bar ( id int not null primary key , foo_id int null foreign key references foo ( id ) on delete cascade , ) 

Removing a line from foo will delete all related lines in the line.

Be careful with cascading deletions, though - fat-fingering of the deletion instruction can cause very large damage very quickly, unlike rm (1) in * nix. Cascading deletions can also chew on a transaction log very quickly if you delete a lot of data in one fell swoop.

Answer 3

I think this can be set up quite easily, without the error you encountered, and without using triggers, as follows:

 1) The foreign key between Users and Posts should be set up to be cascade delete 2) The foreign key between Posts and Comments should be set up to be cascade delete