SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CASCADE - 1


CASCADE - 1

Author
Message
demonfox
demonfox
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2185 Visits: 1192
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 Ermm
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26166 Visits: 12500
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 w00t


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

Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26166 Visits: 12500
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

Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26166 Visits: 12500
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

Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18951 Visits: 12426
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
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18951 Visits: 12426
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
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26166 Visits: 12500
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

Ken Wymore
Ken Wymore
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6618 Visits: 2398
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! :-D
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67495 Visits: 18570
Thanks Ron



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Miles Neale
Miles Neale
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4260 Visits: 1695
Came to the game late but got it right. Excellent discussion!

Not all gray hairs are Dinosaurs!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search