General information
Forum:
Microsoft SQL Server
Environment versions
SQL Server:
SQL Server 2008
Ruling out some of the options (some of which should be self-obvious):
(a) Using cascading delete from TableA -> TableB fails because parent Ids in TableB cannot be deleted whilst child rows exist.
IAC this would not deal with the deletion of child nodes in TableB so this was a non-starter anyway.
(b) Using 'INSTEAD OF DELETE' trigger in TableB
(1) With normal TableA-TableB FK Constraint I can't delete from TableA because it would orphan TableB items
(2) Can't apply a cascade delete because of the presence of the 'INSTEAD OF DELETE' trigger.
(3) Will work if there is no specified relationship between TableA-TableB and I delete from TableB using a 'AFTER DELETE' trigger in TableA
(c) Using 'AFTER DELETE' trigger on TableB - basically the same problem as (b)(1)
FWIW, a cascading delete from TableB -> TableC works OK.
Not having a specified FK Constraint between TableA-TableB (as in (b)(3)) would be a bit of a nightmare when it comes to creating the EF model. The only solution I can see is to define this FK relationship but use an 'INSTEAD OF DELETE' trigger in TableA which deletes associated TableB rows *before* it deletes its own rows, TableB still using the same 'INSTEAD OF DELETE' trigger.
A quick test seems to show that this will work - anyone see problems ?
It still means I'm going to have to tweak the EF model if I want it to perform cascading deletes on the entities - which is something I'm not looking forward to :-{
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only