sql - Cascade on Delete or use Triggers?

Question

Im going through a project I have taken over, and on the database side I have noticed that the previous programmers have written a bunch of triggers to delete child records. The thing is, these records already have a a foreign key relationship with the parent record I am deleting. The delete triggers are nothing but simple delete statements for the child records.

Is there a benefit to writing a trigger to delete child records, or can I just change it to cascade on delete and be fine?

Im using MSSQL 2008.

Answer 1

CASCADE DELETE in MSSQL Server can only cascade to a single table. If you have two tables with foreign key relationships to a dimension table, you can only cascade delete to one of them. (This is to prevent deletes cascading through multiple paths and creating conflicts, much as C++ allows multiple inheritance but C# only allows single inheritance)

When this is the case, you are forced to use triggers or specifically handle the case in your code.

For this reason I have seen many people opt for using triggers in all cases. Even when there is only one foreign table. This ensures consistency and so people know what to look for when maintaining the database.

If one could cascade a delete to more than one table I would say it would be the most preferable option. This limitation, however, muddies the waters and I'm currently more in favour of triggers owning all such behaviours. The overhead in using triggers for cascaded deletes and updates is only minor in terms of coding, but does allow for standard practices that are truely generic.

EDIT:

You might want to move the 'accepted answer' to someone else, I've worked out I was wrong abot the above.

You CAN have multiple fact tables have ON DELETE CASCADE Foreign Key Contraints to a signle Dimension table.

What you Can't do is have one Fact Table have have ON DELETE CASCADE Foreign Key Constraints to multiple Dimension Tables.

So for example...
- Dimension Table [Person] (id INT IDENTITY, )
- Dimension Table [Exam] (id INT IDENTITY, )
- Face Table [Exam_Score] (person_id INT, exam_id INT, score INT)

If either the Person or the Exam are deleted, you'd want the associated Exam_Score record(s) to also be deleted.

This is not possible using ON DELETE CASCADE in MS SQL Server, thus the need for triggers.

(Apologies to Mehrdad who tried to explain this to me but I completely missed his point.)