Hi all,
I have two tables, Results and ComparedResults.
ComparedResults has two columns which reference the primary key of the Results table.
My problem is that if a record in Results is deleted, I wish to delete all records in ComparedResults which reference the deleted record, regardless of whether it's one column or the other (and the columns may reference the same Results row).
A row in Results may deleted directly or through cascade delete caused by deleting in a third table.
Googling this could indicate that I need to disable cascade delete and rewrite all cascade deletes to use triggers instead. Is that REALLY nessesary? I'd be prepared to do much restructuring of the database to avoid this, as my main area is OO programming, and databases should 'just work'. It is hard to see, however, how a restructuring could help as I would just move the problem around... Or am I missing something?
I am also a bit at a loss as to why my initial construct should even be a problem for the Sql Server?!
Any comments welcome and much appreciated!
Anders, Denmark