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.

Monday 8 February 2010

Website performance optimization

These days, there are lots of tools out these to help optimise the loading times and performance of your websites. YAHOO even have a set of rules dedicated to helping you get the most out of your websites' performance:

YAHOO website performance optimization rules

If you don't have time to read through these rules, some clever person had the good sense the implement them in a Firebug plugin that you can run on your website:

YSLOW performance measurement firebug plugin for firefox

Auto-remove SVN files with a shell command

I just found a .reg script to remove SVN files fith a shell command:

Click here to see it

I recommend you check out the other posts on the Lunar Media blog - some useful tips. I'm not affiliated with them in any way (just so you know ;))