On delete cascade

Hi,
1. To my knowledge from sql 2k, there is an option "On delete cascade" and
"On update cascade". But in almost all databases which I have seen till date
,
user defined SP's have been created for deleting table content instead of
checking this "On delete cascade" option. Is there any specific reason for
this? i.e., will there be any performance or anyother reason behind this?
2. Lets assume that I have enabled "On delete cascade" in my DB. If I delete
a record in master table it should delete corresponding details in 4 to 5
tables. What would happen if it fails for some reasons while deleting the 3r
d
table content. Will it rollback?
Regards
Pradeep1. "will there be any performance or anyother reason behind this?"
-No, some guys really want to have their own hand on the deleting of
the data, due to extra issues with logging business rules checking etc.
2. "Will it rollback?"
Sure, thats what a transactional database is for.
HTH, Jens Suessmeyer.|||If can be a safety issue too.
For example, if you use Enterprise Manager to delete a parent row,
referential integrity will prevent you from deleting it if there are child
rows. With cascade delete turned on, the parent and all related children ar
e
deleted. If it was deleted by mistake, it can be quite a complex task to
restore the parent and all the children.
Joe
"Jens" wrote:

> 1. "will there be any performance or anyother reason behind this?"
> -No, some guys really want to have their own hand on the deleting of
> the data, due to extra issues with logging business rules checking etc.
> 2. "Will it rollback?"
> Sure, thats what a transactional database is for.
> HTH, Jens Suessmeyer.
>|||There are technical reasons behind this as well. Cascading referential
actions complicate deadlock minimization because there isn't any way to
determine with certainty the order in which locks will be obtained. If you
issue the DELETE or UPDATE statements in a procedure, you have control over
the order in which locks are obtained and thus can prevent most deadlocks.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1133783057.518482.200520@.g47g2000cwa.googlegroups.com...
> 1. "will there be any performance or anyother reason behind this?"
> -No, some guys really want to have their own hand on the deleting of
> the data, due to extra issues with logging business rules checking etc.
> 2. "Will it rollback?"
> Sure, thats what a transactional database is for.
> HTH, Jens Suessmeyer.
>