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.
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.)
Stay away from unnecessary triggers.
Go with ON DELETE CASCADE if that's all you need to do.
I would use cascade on delete, but that is only if you definitely want to delete the child if the parent is deleted.
If you have any conditional logic (I only delete the child if deleted on a Sunday) then use a trigger.
I would just change it to cascade on delete, on a development system, then run my unit tests and make certain that nothing breaks.
I almost agree with Dems here except I use ON DELETE CASCADE (and ON UPDATE CASCADE for that matter) referential actions wherever possible and only resort to using triggers where necessary. I'd also consider revoking the permissions from the tables and forcing the use of 'helper' stored procs for deletes and updates.
Call me an optimist but I believe a) my code will survive porting to a future release of MS SQL Server and b) the SQL Server team will one day soon get around to fixing the 'one cascade path' limitation, at which point I'll replace the triggers with cascade referential actions :)
