Above is a diagram of my data structure. It represents a hierarchy which can contain three different types of "elements": "A"s, "B"s and "C"s. The relationships show the delete cascade behavior I would like to use if it were possible.
All types have properties in common, including columns that show position in the hierarchy (Parent and Index) and type of element. Those common columns are stored in the
Each type of element also has unique properties which are stored on the the corresponding tables based on element type.
Each of the rows in
CData reference a unique main row in
ElementBase. "A" and "C" elements also each reference a "B" element. "B" elements can have 0 or more "S"s.
My problem is: how can I maintain reference integrity and support something like cascade deletion?
I want to be able to delete a row from
ElementBase and have the corresponding row in either
CData also be deleted. If, for example, a "B"-type element gets deleted from
ElementBase, firstly the corresponding row from
BData should be delete, then every "C"-type element that references it needs to be deleted in both the
CData tables, and all "A"-type elements need to have their references set to
And the cherry on top: if the element I delete has children of any type, I want this same logic to run recursively down the hierarchy.
ElementBase is self-referencing, I can't use the simple
ON DELETE CASCADE feature in that table. I also can't use that on
CData because they both reference
BData which could then result in "multiple cascade paths" which are apparently evil in SQL Server.
One alternative I've found is
INSTEAD OF triggers. The problem with that is this behavior has to be recursive and I can't quite figure out how to enable them to be recursive and also eventually do the original delete at the end.