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.

Lora

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.)

Diplo

Thanks Dems, that looks like exactly why they put the triggers in place – Lora

Answer 2

Stay away from unnecessary triggers.

Go with ON DELETE CASCADE if that's all you need to do.

Mancilla

They're not always unecessary. You can't for example, cascade dlete to more than one foreign table. – Diplo

I didn't say they are unnecessary. I said you should use ON DELETE CASCADE if that suits your problem (I think the OP's situation is satisfied by CASCADE). BTW, what do you mean "to more than one foreign table" I'm sure ON DELETE CASCADE works in a hierarchical fashion too. However, it doesn't support both direct and indirect references from a single table. – Mancilla

No, MS SQL Server doesn't allow the deletion of a Primary Key or Unique Constraint to be cascaded to more than one table. Try setting it up and you get an error message. Cascade delete only works from 1 dimension table to 1 fact table. It doesn't work from 1 dimension table to many fact tables. – Diplo

Dems: It does support multiple tables referencing one table. It also supports those tables to be referenced by other tables. However, it doesn't support cycles or multiple cascade paths. For instance, it supports B -> A, C -> A, D -> C but it doesn't support B -> A, C -> A, D -> C, D -> A. – Mancilla

This expressly didn't work in the instances of 2000 and 2005 which I use day to day. I'll have to give it another go tomorrow when I'm back in work. Do you know if this is new to 2008 or am I just donkeying something up when I try to do it in 2000/2005? – Diplo

CREATE TABLE A(id int primary key not null identity); CREATE TABLE B(id int primary key not null identity, Aid int not null references A(id) ON DELETE CASCADE); CREATE TABLE C(id int primary key not null identity, Bid int not null references B(id) ON DELETE CASCADE) works on SQL Server 2008 and I'm pretty sure the behavior has been similar, at least since 2000. – Mancilla

Yes, that works. But if both TableB and TableC had a foreign key to TableA.id, then you could not make Both foreign keys constraints ON DELETE CASCADE. – Diplo

Yes, that's what I said the previous comment. Multiple paths or cycles are unsupported. – Mancilla

But you did say "B->A, C->A, D->C" is supported. The first two of those are what I just said can't be supported, which is when triggers are needed. In effect "a linear cascade path" works but a "hierarchical cascade path" does not. – Diplo

Yes, it's supported. By A,B,C,D, I mean tables, not columns. B is a completely unrelated path while A, C, D lie on the same path. – Mancilla

I stand corrected and have updated my answer accordingly, apologies for being a combination of blind and ignorant... – Diplo

Answer 3

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.

Emcee

Answer 4

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 :)

Thuythuya

Answer 5

For anyone still reading, check out this and this blogs. Seems that on delete cascade may cause locking hell in some cases.

Amphimacer