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.

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 ;))

My day illustrated

Hiding configuration sections from nested ASP.NET apps

If you have an ASP.NET website nested under another in IIS, you will likely get a server error unless both sites have exactly the same dependencies. This is because IIS, by default, applies web.config settings hierarchically to sub-sites. You can use this fact to your advantage, to prevent you having to repeat the same settings in the child site's web.config.

However, often, you will need certain configuration settings to apply only to the parent site. To restrict specific web.config sections to apply to the parent application only, wrap the relevant sections in the parent site's web config file within:

<location path="." inheritinchildapplications="false">
// your configuration sections
</location>

You can have as many of these location tags in your web.config as you like.

Note: Visual Studio 2005 will throw an error on the inheritInChildApplications attribute. To get round this you will need to add your location elements to web.config after you publish the site (or use VS 2008)

MS SQL Server How To - Creating relationships in a database diagram

This one had me scratching my head for 5 minutes, so I thought I would share it.

If you want to create relationships in your SQL Server 2005 database diagram:

1. Right-click the table that contains the foreign key (FK) in the forthcoming relationship.
   
2. To create the relationship, click the ellipsis (...) box to the right of "Tables and Columns Specification". (This is quite hard to see.)
   
3. In the Tables and Columns dialog, you can now select the tables, FK, and PK (Primary Key) that will participate in the relationship.
   
   

Have fun!