Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»

CASCADE - 1 Expand / Collapse
Author
Message
Posted Thursday, March 21, 2013 7:00 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:15 PM
Points: 1,129, Visits: 1,163
db4breakfast (3/21/2013)

To be clear, we don't allow users to delete any record in our application. However there's a function for users to flag a record as error. Instead of cascade delete, the application just flag the record as "error" in the parent table. All data are still there.


Well, we do allow it .. depends on business scenarios ..

In our case, it just marks a record for delete followed up by a workflow , background, process that deletes the records and it's associations ..


~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one
Post #1433764
Posted Thursday, March 21, 2013 8:10 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 8:11 AM
Points: 8,682, Visits: 9,209
marlon.seton (3/21/2013)
Lokesh Vij (3/20/2013)
db4breakfast (3/20/2013)
I never use cascade delete on a production db. It seem to be safer by allowing users mark order as "error" instead.


+1
same here. i feel it is dangerous to use on delete cascade in production...as a mistake in deleting a parent records will trigger deletes from all the child records


We use triggers to delete the child records when a parent record is deleted. Is that generally considered a bad thing to do?

I don't want to be provocative or offensive here, but I really don't know how to avoid it: writing a trigger to do what "with cascade" would do is pure illogical insanity. If my saying that helps you change your mind, I'll have done you a favour, so I guess being blunt is excusable.


Tom
Post #1433805
Posted Thursday, March 21, 2013 8:34 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 8:11 AM
Points: 8,682, Visits: 9,209
Hugo Kornelis (3/21/2013)
Toreador (3/21/2013)
Using cascade delete. If the business rule says that is the appropriate behaviour, then why avoid using inbuilt functionality that achieves exactly what you need?

One possible reason is that you can't control the order of locks being taken with cascading deletes and cascading updates. That makes it harder to avoid deadlock scenarios if you are in a high concurrency scenario.

Other than that, I see no reason not to use this feature *IF* that is what the business wants. However, in my experience the number of scenarios where the business actually wants to delete dependent rows automatically is very small.

Let's be clear here: under what circumstances is the order indeterminate? If we are talking about a single constraint with cascade, the only order of interest is the order in which the referring rows in the single referring table are addressed - because the exclusive locks on the target table must be taken before what referring rows are affected can be determined. Unless you write row by agonizing row code instead of set-based code, you have no control at all over that order in a trigger. Assuming you don't write such code, the only case of interest must then be when there are multiple referring tables. This is pretty much a minority case, so usually there is no order issue, whether you are in a high concurrency scenarion or not.

So I stick by my statement that it is usually an error to eschew cascade.

When there are multiple referring tables, either you deal with all the referring tables in at most 3 triggers or you can't determine the order because you can't specify more than which tables are hit in the first trigger and which in the last, so your triggers may need to be a bit unwieldly if there are more than 3 referring tables and you want to force the order.

Of course if you are in a situation where you need instead of triggers (for some reason or other) you don't have the option of using cascade - but instead of triggers on tables as opposed to views are pretty rare.


Tom
Post #1433816
Posted Thursday, March 21, 2013 8:37 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 8:11 AM
Points: 8,682, Visits: 9,209
marlon.seton (3/21/2013)
Hi Hugo,
Thanks for your reply.

By 'why bother' I guess you're meaning why bother using triggers instead of cascade delete? Because the 4GL in which we write the application can't handle FK constraints (and please don't ask why we use such a 4GL, that's a whole other bag of hurt).

I guess if you are in a situation where you can't use foreign keys, which is what you appear to be saying, the question of using cascade doesn't arise at all - even I, an ardent supporter of cascade, wouldn't dream of suggesting you use cascade in that case; unlike many, I refuse to belive in or advocate impossible things (not even before breakfast).

edit: typing!!


Tom
Post #1433820
Posted Thursday, March 21, 2013 9:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:54 AM
Points: 5,967, Visits: 8,219
L' Eomot Inversé (3/21/2013)
Let's be clear here: under what circumstances is the order indeterminate?


I never investigated this myseslf. I have heard this from many people. People I value and trust sufficiently to believe them.
Is this a problem with a single foreign key? Could be - after all, there are still two tables involved in that case, and you can't control the order in which locks are taken.

More likely, this is indeed referring to a scenario with either one table referencing two or more other tables, or with a chain of tables. The more tables there are, the higher the chance that the order the optimizer chooses to access the table causes deadlocks with other transactions.

And for the record, I was indeed referring to the orde in which TABLES are accessed, not the order in whichs ROWS are processed. The common advice for avoiding deadlocks is to always access tables in the same order, and that can only be guaranteed if you wrote code that touches one table at a time.

When there are multiple referring tables, either you deal with all the referring tables in at most 3 triggers or you can't determine the order because you can't specify more than which tables are hit in the first trigger and which in the last, so your triggers may need to be a bit unwieldly if there are more than 3 referring tables and you want to force the order.

I never create more than a single trigger per operation on each table. If I have to do a lot of stuff on inserts of table X, then the ON INSERT trigger for table X will be long - but I have full control over the order in which operations are performed.
(I am in fact so used to writing triggers that way that I often forget that it's possible to have multiple triggers for the same table/operation combination)



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1433845
Posted Thursday, March 21, 2013 9:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:54 AM
Points: 5,967, Visits: 8,219
L' Eomot Inversé (3/21/2013)
I guess if you are in a situation where you can't use foreign keys, which is what you appear to be saying, the question of using cascade doesn't arise at all - even I, an ardent supporter of cascade, wouldn't dream of suggesting you use cascade in that case; unlike many, I refuse to belive in or advocate impossible things (not even before breakfast).

I seem to recalll that very old versions of SQL Server (I believe prior to 6.0 or so) did not support foreign keys. If you wanted a foreign key relationship, you built a trigger to enforce it. And since you wrote the trigger code yourself, you had the choice on what to do on violations - rollback and error ("NO ACTION"), or attempt to fix ("SET NULL" / "SET DEFAULT" / "CASCADE").

If Marlon is in a similar situation, then:
1) I don't envy her. At all.
2) I see no problem in using triggers to enforce the business need to cascade on deletion from a specific table.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1433849
Posted Thursday, March 21, 2013 9:46 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 8:11 AM
Points: 8,682, Visits: 9,209
Hugo Kornelis (3/21/2013)
L' Eomot Inversé (3/21/2013)
I guess if you are in a situation where you can't use foreign keys, which is what you appear to be saying, the question of using cascade doesn't arise at all - even I, an ardent supporter of cascade, wouldn't dream of suggesting you use cascade in that case; unlike many, I refuse to belive in or advocate impossible things (not even before breakfast).

I seem to recalll that very old versions of SQL Server (I believe prior to 6.0 or so) did not support foreign keys. If you wanted a foreign key relationship, you built a trigger to enforce it. And since you wrote the trigger code yourself, you had the choice on what to do on violations - rollback and error ("NO ACTION"), or attempt to fix ("SET NULL" / "SET DEFAULT" / "CASCADE").

If Marlon is in a similar situation, then:
1) I don't envy her. At all.
2) I see no problem in using triggers to enforce the business need to cascade on deletion from a specific table.

I think then that we agree on that much - when you have to enforce forign keys using triggers instead of constraints you have to use those triggers to enfoce whatever action you want, be it cascade or set null or set default or roll back or something else (if it's something else, you need triggers even if you can use constraints to enforce the foreign key).


Tom
Post #1433877
Posted Thursday, March 21, 2013 11:04 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:01 PM
Points: 3,290, Visits: 1,971
Very nice straight-forward question. This is something that I am going to discuss with our developers as I am sure there are systems where these could be useful. We generally just flag rows as invalid instead of allowing deletions but this is a good option to keep in mind too. Certainly better than using triggers in my opinion!
Post #1433916
Posted Friday, March 22, 2013 12:20 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:33 AM
Points: 21,620, Visits: 15,276
Thanks Ron



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1434460
Posted Monday, March 25, 2013 4:13 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, August 7, 2014 3:34 PM
Points: 2,282, Visits: 1,341
Came to the game late but got it right. Excellent discussion!

Not all gray hairs are Dinosaurs!
Post #1435197
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse