Best practice

Hi
What is the prefered practice to use when I have 2 or more related
tables and I want to delete a row in the master table and I want the
child tables to automatically delete their related rows, Should I use
triggers in the database, enable cascade delete in the dataset or
somthing else ?
I use visual studio 2005 and sql server 2005.
Thanks
RolfIt depends. If you want the child records deleted, then a cascade delete is
appropriate. If you want the application to deal with the child records
first, then a foreign key constraint that will stop deletes will be valid.
Both are best practices, depending on your application requirements.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<rolf-hje@.online.no> wrote in message
news:1139864939.431879.138190@.z14g2000cwz.googlegroups.com...
> Hi
> What is the prefered practice to use when I have 2 or more related
> tables and I want to delete a row in the master table and I want the
> child tables to automatically delete their related rows, Should I use
> triggers in the database, enable cascade delete in the dataset or
> somthing else ?
> I use visual studio 2005 and sql server 2005.
> Thanks
> Rolf
>|||Cascade with error handling could be one way as you mention.
Another way wich I prefer is to use transaction when deleting data that
needs to delete related data.
I do recommend that you dont use triggers since that easily can make
things go out of control.|||It depends. Cascading deletes are the simplest to implement, but can
complicate deadlock minimization because the order in which locks are
obtained across tables is not clearly defined. In addition, INSTEAD OF
triggers cannot exist on the referencing table of a cascading referential
action. Using FOR or AFTER triggers to cascade deletes is generally a bad
idea--not because of the performance impact, but because several triggers
can exist for an action on a table and the order in which they are executed
is not deterministic, IMO they should be avoided. I prefer to perform
deletes within a transaction in a stored procedure. It is then clear from
reading the text of the proc which tables will be affected, and it's easier
to control the order in which locks are obtained to minimize the likelyhood
of a deadlock. The use of INSTEAD OF DELETE triggers instead of stored
procedures may warrant investigation because any locks applied depend on the
order in which statements appear within the trigger (none are applied as a
direct result of the trigger firing) in the same way as statements within a
stored procedure, and unlike the stored procedure method, they do not
require preventing direct access to the tables. (On the other hand, many
would say that you should always prevent direct access to the tables and
require all modifications to be performed using stored procedures.)
<rolf-hje@.online.no> wrote in message
news:1139864939.431879.138190@.z14g2000cwz.googlegroups.com...
> Hi
> What is the prefered practice to use when I have 2 or more related
> tables and I want to delete a row in the master table and I want the
> child tables to automatically delete their related rows, Should I use
> triggers in the database, enable cascade delete in the dataset or
> somthing else ?
> I use visual studio 2005 and sql server 2005.
> Thanks
> Rolf
>|||OK, thanks for all replies
I think I will avvoid triggers and use cascading delete. But what is
more efficent. Cascading deletes in the database or cascading deletes
in the dataset. Is there a performance difference between these two
options ?
Thanks
Rolf