Peter Larsson Blog

Patron Saint of Lost Yaks

Finding table reference levels and simulating cascading deletes

I worked with this topic recent weekend and posted the final functions here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97454
 

The general idea is to have a generic purge functionality.

Legacy Comments


Jason
2008-02-16
re: Finding table reference levels and simulating cascading deletes
I understand why some people might want this, but I think it is truly a bad idea to even do something like this. A good database developer/dba should know their relationships in the database and should always explicitly do the delete statements. I believe it will perform better, protects against potential bugs in code, and is just the correct way. One should never depend on Cascade deleting it is just a bad practice. I always have cascades disabled.

Peso
2008-02-17
re: Finding table reference levels and simulating cascading deletes
It is not necessarily a bad idea.
Sometime you want to purge old data from a system.

In this case, there was a need to purge customers who had a certain statusid and had hold that statusid for a period of time.

This particular system is sprung from a "off-the-shelf" product but has many modules installed specialized for this customer.

Instead of having to maintain a "purge" sproc and make sure ALL involved developers over time (8 years) added their tables to this sproc, I thought I could make a generic function to create all necessary delete statements for me.