Cascade Delete Without CASCADE?

[Gooser]

So, what if I want to be able to do a cascading delete only in a certain scenario. That is, I don't want to set the tables up with cascade delete, but I do want to be able to cascade delete when I want to.

I found this article:

http://www.sqlteam.com/article/performing-a-cascade-delete-in-sql-server-7

Which sort-of gets me there, but not really.

I would be okay with using a stored procedure to do it like in the article, but I'm not really sure.

I have a table that relates to almost forty other tables, and then most of those, of course each relate to a couple of tables, so I'm looking at something around 150 tables that I'd have to link up in one crazy looking delete stored procedure.

On the flipside, I don't want to go willy-nilly deleting everything from the database every time I try to delete a record. That kind of defeats the purpose of the Foreign Keys doesn't it?

Any questions, comments and even rude remarks are welcome.

Thanks,
Gooser

 

[timfoster]

You have no choice other than to delete from the individual tables if you want to do it conditionally.

Depending on how your tables are structured, you could interogate the sys.columns and sys.objects tables to find all the tables that have the fk column, and then recursively loop through deleting the rows. This would mean you'd have to create a smaller sp, but then you get the world of pain that is recursive calls.

 

[Gooser]

What do you mean a smaller sp?


v/r

Gooser

Why do today
that which may not need to be done tomorrow --me

The Bailout We Need