INSTEAD OF Triggers and CASCADE paths

Question

Let's say I have 3 tables in a hierarchy:

TableA -> TableB -> TableC

TableC has a foreign key relationship with TableB, and TableB has a foreign key relationship with TableA.

If i delete a record in TableA, it should cascade delete down through the hierarchy. Using ON DELETE CASCADE would work fine.

However let's say I need to put an INSTEAD OF trigger on TableC. My understanding is that an INSTEAD OF trigger can not be put on a table that has a delete cascade going to it. Taken from MSDN:

For INSTEAD OF triggers, the DELETE option is not allowed on tables that have a referential relationship specifying a cascade action ON DELETE.

If I have to take the cascade delete off TableB->TableC, I would need to use an INSTEAD OF trigger to enforce Referential Integrity, and then I have the same problem with TableB->TableA. This is a simple example, but imagine the cascade path being much larger. It seems like it could easily snowball throughout a long cascade path.

So what are the best practices for dealing with this scenario?

Gussie

What do you want the trigger to do (instead of deleting) that would not break referential integrity? – Danas

I want to avoid adding further complication to the example as I'm not looking for table redesign solutions, rather an answer to the specific scenario. However for reference, TableC uses the Adjacency List Model to store a hierarchy. I'm using the INSTEAD OF trigger to recursively delete through the hierarchy. HierarchyID is not possible due to using SS2005. – Gussie

Without redesigning the table, perhaps this can help: SQL Server: deleting with self-referential FOREIGN KEY – Danas

That link is really helpful, I will try it out. Although i'm still interested to hear how people would deal with the original problem if the INSTEAD OF trigger was necessary. – Gussie

Personally, I'd use a stored procedure to control the DELETE. No cascade, no trigger. – Terhune

Triggers are like a ping ball thrown into a room full of mousetraps, m'kay. – Heath

If I had a lot of child tables the very last thing I would ever consider allowing anyone on my database to do is define a cascade delete on the parent table! You could lock up the whole database deleting millions of records!!!! – Reporter

@Poz You've gotten a whole bunch of responses to your question. You should accept an answer or upvote a comment. – Heath

@jonnyGold When I originally asked the question a month ago, none of the answers provided were acceptable imo. In fact the question was only re-raised as a bounty was put on it by another user. I have already upvoted 2 comments, one of which by gbn would be my accepted answer if it were one. – Gussie

Answer 1

Assuming you must use INSTEAD OF triggers, and AFTER triggers are not an option, the best approach is to a) tightly control the schema so that you can b) script the INSTEAD OF triggers out in a regular fashion to implement the CASCADE DELETE and whatever other operations you need.

Create the FK constraints as before, but w/out any cascade behavior. In the FK name, use some convention to indicate what kind of cascade behavior and custom behavior should occur, eg:

• FK_UC_DC_Table1_Table2 -- update cascade, delete cascade
• FK_UC_DN_Table1_Table3 -- update cascade, delete set null

Use whatever makes sense, but do create the FKs, they are useful metadata for code generation, and you can use the FK names to record directives for the code-generator.

I'd then take it a step further and isolate these tables in their own schema. They won't behave the same way as other tables, and they will be more buggy at first as you test and fine-tune the code generation. Best to keep all this quarantined, and easily identifiable by a common container.

A dedicated schema will also inform anyone modifying the data that different rules and behavior apply.

Vasyuta

Answer 2

The standard best-practice is to define INSTEAD OF triggers on views, not on tables.

If you have to use a trigger on a FK update/delete you are best to use AFTER, since it will always execute.

If you want to cancel the cascading actions but retain the FKs, just set the FK action to NO ACTION.

Azurite

Unfortunately AFTER triggers are not a solution in this scenario as the Referential Integrity will be violated before the trigger gets fired. – Gussie

Can you be more specific about what you want the trigger to do? – Azurite

Please see the second comment under the question. However I'm not so concerned with the implementation, rather what people would do given the generic scenario. I appreciate this may depend on the implementation but still! – Gussie

I saw that, however, I think that's not reasonable. You should tell people what you want to do. My answer is the best practice but you're saying it's not possible to do that, and you should say why that's the case. You may in fact have a bad design and you're going to have to deal with that. at some point, but that's not why Im asking. I just want to know what the trigger is supposed to do. – Azurite

My comment states that the table stores a hierarchy using the adjacency list model, and as such it uses an INSTEAD OF trigger to maintain RI. i.e. if i delete a parent i want to cascade delete the children of that parent. An AFTER trigger is not possible as the RI will be violated on delete before the trigger can be executed. – Gussie

But you are not being clear on what else you actually want to DO before/after/when you delete the children. – Azurite

I'm sorry if I'm not being clear but I'm really not sure what additional information you are looking for. The INSTEAD OF trigger is responsible for recursively cascading the delete down through the adjacency list parent/child hierarchy in TableC. That is it's sole responsibility. See this question for what I'm referring to: deleting-hierarchical-data-in-sql-table – Gussie

Oh. I see. So TableC is supposed to delete a bunch of children within TableC when one of its rows is deleted. That is definitely a bad design. You should not mix approaches like that. – Azurite

That logic was eventually moved out into a deletion procedure, however the original trigger approach is what raised my original question and I was interested to see how people would deal with that generic scenario. Thanks for your input anyway. – Gussie