Wednesday 10 February 2010

Alternative to on delete cascade in sql server

I'm working with a modified asp.net database, a partial diagram is shown here.

I discoved that Sql server management studio wouldn't let me put cascade delete rules on all the relationships. Unfortunately this is a limitation of SQL server, but there is a way around it.

Simply set the delete rule of either the FK_B_aspnet_Users or FK_A_aspnet_Users to No Action, or delete one of the relationships altogether. Then create a delete trigger:

CREATE TRIGGER [trig_delUser]
ON [dbo].[aspnet_Users]
FOR DELETE
AS
delete B
from B, deleted
where B.UserId = deleted.UserId

Where B is the table joined by the No Action relationship, or where the relationship was deleted. (Note: you could make B->Linker the no action relationship, then create the delete trigger on B).

In this way, deletes over Users->A and A->Linker are automatically handled, with your trigger handling deletion of B entities when a user is deleted, with the B->Linker cascade taking care of the rest.

You can also do this using stored procedures, but this is my preferred method, as I think it is more flexible and easier to debug.

For very large deletion operations, things are a bit different. What you should do is write the deletes to the child tables first then the delete to the parent table, wrapping everything in a transaction - I'll explain that in another post. However, the above method will work perfectly well when you are deleting a few records at a time.

No comments: