January 21, 2008 at 11:41 am
Hi,
I'm trying to use an Instead-of trigger to define a cascading delete that I wasn't able to define using cascadng referential integrity constraints, due to the 'may cause cycles or multiple cascade paths' error. If you are unfamiliar with this (uniquely) SQL Server limitation, check out this link.
The problem I'm getting is that the table I'm trying to define the trigger on itself has a foreign key with cascading deletes defined on it from another table. Here's an example.
Three tables. Customers. Invoices. Payments. There's a one-to-many relationship from Customers to Invoices, and a one-to-many relationship from Invoices to Payments. Let's say that you successfully defined cascading deletes from Customers to Invoices, but because of other cascading delete paths in the database, you can't define the relationship from Invoices to Payments with cascading deletes (you would get the 'may cause cycles' error). You could try to define the cascading delete with a trigger, as follows:
CREATE TRIGGER tr_InvoiceDelete ON Invoices
INSTEAD OF DELETE
AS
DELETE FROM Payments
WHERE InvoiceID IN (SELECT InvoiceID FROM deleted)
DELETE FROM Invoices WHERE InvoiceID IN (SELECT InvoiceID FROM deleted)
GO
But guess what? You'd get an error:
Cannot create INSTEAD OF DELETE or INSTEAD OF UPDATE TRIGGER 'tr_InvoiceDelete' on table 'Invoices'. This is because the table has a FOREIGN KEY with cascading DELETE or UPDATE.
meaning that because of the cascading deletes defined from Customers to Invoices, you can't define this trigger. Really frustrating right? You could, of course, remove the cascading updates from Customers to Invoices, and do that with a trigger as well, but what a pain.
Anyone have any suggestions? Thanks.
Aaron
January 21, 2008 at 1:33 pm
I have never been a big fan of cascading deletes with triggers or through the use of the default cascading delete behavior. Preventing referential integrity violation by automatically deleting orphaned records has always felt a bit like preventing world poverty by killing anyone without money - effective, but not really getting the point.
My first suggestion would be to go back and see if you can alter your application logic to delete the child records before the parent.
If you cannot do this, you can either do as you have suggested - create a delete trigger on the other table as well (and any other tables above it) or remove your referential integrity (last resort) and manage the referential integrity through the triggers yourself (regular triggers rather than instead-of triggers).
January 21, 2008 at 1:51 pm
I totally agree with you. In an ideal world I'd re-write the application logic. Unfortunately, I'm just cleaning up after the application developer, and there isn't the budget to re-write application logic.
No, I think I'll be best off just replacing the necessary DRI with triggers. I didn't know that you had to go all the way up the chain, however. Pain in the butt.
Aaron
February 1, 2011 at 4:41 pm
I am facing the same issue in my app.
I think we can resolve this by
1. Create Instead of Trigger - Disable the Foreign key constraints, delete records from parent table.
2. Create After trigger - Delete records from all child tables, enable the foreign keys again.
But there can be performance issues and data leaks in multi threaded env.
April 1, 2011 at 7:42 am
I disagree with SSCrazy. Cascade delete in no way violates referential integrity. On the contrary, it is an integral, and indispensable, part of the RI concept, the point of which is to assure that data conforms to the relational constructs while it is in the database. It is not the function of RI to keep unwanted data in the database. Since it makes perfect sense to use cascade delete for many business entities, why would you want to force app developers - and/or database developers - to write redundant code to perform a routine function that is already implemented flawlessly in the database engine?
I don't want to trust my data integrity to application developers. And since I am also, myself, an application developer, I want encapsulate all the business logic I can into my database to make it simpler and faster to deploy apps that use it. Not to mention that database changes are much faster to deploy when you can adapt the business logic in one place without having to worry about it breaking applications.
With that said, there are, of course, often times when cascade delete is definitely not what you want. For example, I have a table of project tasks in my database which has a foreign key to the employees table for the employee who owns the task. If the employee is deleted, I don't want all the associated tasks to disappear - I want to reassign them to the employee's team leader or group leader. I don't want to disable RI, either, because it's essential to task assignment.
There are two alternatives that I provide for this, and the developer can choose whichever suits the app. One is a stored procedure that can be called by the app. The sp executes the business logic to reassign tasks for the employee to be deleted, then deletes the employee record. This has the advantage that the app can optionally supply the employee id of the person who the tasks should be reassigned to in an input parameter.
The second alternative is a view with schema binding to the employees table. You can have an Instead Of Delete trigger over a view, and my trigger essentially executes the same logic as the sp, but without the option for the app to stipulate the reassignment.
Of course, there's nothing to stop an app developer from implementing a different way to reassign tasks and then deleting from the employees table directly - in this case, enforced RI prevents the app from deleting a record until all associated tasks are reassigned.
April 1, 2011 at 7:58 am
I have to disagree with phbarn, and agree with the sound advice given my Michael Earl.
Without cascade deletes, accidental deletion of a parent is stopped in its tracks. I've heard too many people say "the application will handle that", only to write a maintenance or fix script and realise the mistake too late. Another reason being this gotcha if the tables involved are replicated (which I posted on another thread):-
Any update statement that changes the primary key or any other unique constraint is actually done as a DELETE/INSERT pair, and gets written to the transaction log as such.
Transactions to be replicated are read from the transaction log, and as long as you only update 1 row in your statement, SQL Server matches up the DELETE/INSERT pair it finds in the transaction log and replicates it as an UPDATE.
However... if you update more than 1 row in the UPDATE statement, they get written to the transaction log as DELETE,DELETE,DELETE... INSERT,INSERT,INSERT and SQL Server is unable to match them as pairs, so ends up replicating them as separate DELETEs and INSERTs. That leaves you with the possibility of the DELETE failing due to FK constraints, but the INSERT works.
This quirk is one reason why I would never recommend CASCADE DELETES... the child rows can suddenly disappear for no apparent reason.
April 1, 2011 at 7:58 am
phbarn (4/1/2011)
You made a good point with your reply and provided very good alternatives and how-to's,
however ....
please keep in mind this is an old thread ( Posted 1/21/2008 )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 1, 2011 at 8:01 am
ALZDBA (4/1/2011)
please keep in mind this is an old thread ( Posted 1/21/2008 )
Doh!!:blush:
April 1, 2011 at 8:35 am
Ian Scarlett, I think you misunderstand my post. If you don't want a parent to be accidentally deleted, then by all means don't enable cascade delete. I certainly don't advocate using it blindly. In fact, if you look at my solutions, you will realize that I do not have cascade delete enabled on the employees table, specifically to ensure that it is not deleted (accidentally or deliberately) while tasks remain assigned to it. If I try to delete an employee who has tasks assigned, it will fail with an RI error.
However, this is very much a backstop - my sp and view trigger are intended to ensure that this never happens - without any reliance on the application developer. Relying on RI to prevent the accidental deletion of a parent record is misguided and an abuse of RI. You can accidentally delete a record from a childless table just as easily as deleting one from a parent table. It is not the proper function of RI to protect against such an accident, since it does not protect all tables equally. You have to find other ways to do this. I have written application code that cycles through child tables and deletes them before deleting the parent record. Such code is invoked once the application logic determines that the parent record will be deleted. If the logic is faulty, all are deleted regardless. It makes no difference in such a case if cascade delete were enabled on all the child table relationships and the application code merely deleted the parent, using precisely the same logic. The accident would have precisely the same outcome.
Also, I'm not sure how you got the idea that I was suggesting updating the primary key of a table. I would never do such a thing and I hope that nobody would. The employee id in my tasks table is a foreign key and can be updated just like any other field, provided RI is maintained. This is not going to cause any havoc with replication.
April 1, 2011 at 8:46 am
ALZDBA - Thanks; I realize it's an old thread, but it's still topical (it was high on Google's matches for my keyword search). I get a lot of good information from old threads.
April 1, 2011 at 9:05 am
phbarn (4/1/2011)
Also, I'm not sure how you got the idea that I was suggesting updating the primary key of a table. I would never do such a thing and I hope that nobody would.
I wasn't suggesting that you would 🙂 ... most of the post was cut and paste from another thread I posted this on. Unfortunately, not everybody has the same beliefs, which is where I discovered this. If I remember rightly, the primary key wasn't actually being changed, but was included as part of an update that said SET pk = pk...
I agree with much you have said in your post. Implementing logic in a stored procedure to delete parent and child records is a lot different to writing a trigger to do that or enabling cascade deletes.
I wouldn't be rich if I had £1 for every time I'd heard expletives when a developer or DBA realises they've run a DELETE or UPDATE statement from SSMS and managed to un-highlight the WHERE clause, but I wouldn't have wasted hours with restores and copying data.
At the end of the day it's all personal preference, but I would shy away from anything that automatically deleted child records, as it makes it far too easy to make mistakes with far reaching consequences.
April 1, 2011 at 10:13 am
Ian Scarlett (4/1/2011)
I forgot to mention that one of the benefits of having all the code in the database is transaction control - if anything fails, you can rollback, even if cascade delete is enabled. It's too easy for an application to delete a bunch of child records, but miss one child table and then fail on the parent; leaving a big mess.
It's not just RI - any function that inserts or deletes a record, or updates a foreign key, has static business rules associated with it. I believe they should be codified in the database, since not all app developers will code them consistently or correctly - and even if they do, you have potentially multiple redundant iterations of the same logic in different places, which all have to be updated if the database changes. In addition to indexes and constraints, I use a combination of schema-bound views, triggers and stored procedures to avoid exposing any 'raw' element of the database to applications. It generally makes life easy for app developers, because it gives them a full set of "black-box" functions for data operations and they can concentrate on the user interface.
None of it, unfortunately, gives much protection against a rogue DBA running amok with SSMS 😉
There are many ways to skin a cat, of course. As you say, it's down to personal preferences and I try not to be dogmatic about mine. I confess I was spurred by the "Thou Shalt" posture of the Michael Earl post. Cascade delete is a powerful tool capable of wreaking destruction. So is a chainsaw, but there is nothing better for felling a tree quickly. Just don't blame it if you cut the wrong tree 😉
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply