Is Cascading deletes prefered to deleting thru a stored procedure?

I have a situation in which I have three tables - 'Table1', 'Table2' and 'Table3'.

'Table1' is a primary table for 'Table2' so that the the primary key of 'Table1' is a foreign key in 'Table2'. In the same way, 'Table1' is also the primary table for 'Table3'.

If I delete a row in 'Table1' then I could automatically let SQL Server also delete the related rows in 'Table2' and 'Table3' through 'cascading delete' option. Or I could run a trigger or stored procedure for deleting related rows in 'Table2' and 'Table3'. Which of these methods would be quicker? Also would cascading deletes option affect the  performance of database access since now SQL Server will have to always check for deleted rows, whenever 'Table1' was accessed.


sun21170
0
sun21170
5/11/2005 7:17:04 PM
asp.net.object-datasource 16182 articles. 0 followers. Follow

1 Replies
720 Views

Similar Articles

[PageSpeed] 59

Cascade Delete and Cascade Update is also called DRI(Declarative Referential Integrity).  You can use triggers to enforce them but triggers don't fire all the time but if it is enabled on the table property in SQL Server you are creating relationship that will be enforced by SQL Server through ANSI SQL Cascade delete on delete and Cascade update on update.    Performance depends on how many tables you are connecting.  Hope this helps.
Kind regards,
Gift Peddie
0
Caddre
5/12/2005 4:49:48 AM
Reply:

Similar Artilces:

Deletes are not being deleted.
Using ASA 7.03 I am having a problem in deleting field service orders that have been completed. Just getting started with sync so I am probably making a dumb mistake or just leaving out a step. The download_delete_cursor script is: Select ordernumber from ServiceOrderEntryMaster where Tech = ? and Order_Complete = 'Y' This based on the verbose reply from the MobiLink Synchronization server is picking the correct work order ( number 326 ) and showing a Delete Row line in the log. But the work order is not being deleted on the remote side. What could I be doing wron...

Deleting data by calling the stored procedure in the .NET
Hi, does anyone know how to delete data from the SQL database by calling the stored procedure in the Visual Basic.NET? Because I did the Delete hyperlink bounded inside a datagrid. I have already displayed the appointment date, time in the datagrid so I do not have to input any values inside it. These are my stored procedures code for deleting: ALTER PROCEDURE spCancelReservation(@AppDate DATETIME, @AppTime CHAR(4), @MemNRIC CHAR(9)) AS BEGIN IF NOT EXISTS     (SELECT MemNRIC, AppDate, AppTime     FROM DasAppointment     WHERE (Me...

Stored Procedure for Delete doesn't delete record -- any obvious errors?
I have made a simple stored procedure to delete a record.  While no errors are reported, clicking the word delete does not remove a record from the table.  Is there something obvious below that would explain why a delete action does not occur?   ALTER PROCEDURE sp_DeleteViolation0 @original_id smallint, @original_offensename nvarchar(10), @original_felony bit, @original_fine money As DELETE FROM [Violations0] WHERE [id] = @original_id AND [offensename] = @original_offensename AND (([felony] = @original_felony) OR ([felony] IS NULL AND @origina...

Cascading deletes - Which is better
I need to implement my cascading deletes on a SQL database.  Is it better (performance/reliablility-wise) to use the Foreign Key Cascading Deletes or to just write my own triggers to do the deletes?I was hoping someone had experimented and found which works best. DRI(declarative referential integrity) triggers are used when Cascade Deletes and Updates cannot be implemented in the RDBMS(relational database management system) as in SQL Server 7.0 and below.  In SQL Server 2000 using Cascade Delete and Cascade Update is the way to go.  The reason is reliability related because ...

Delete cascade procedure
I have a princical table on the root of my table structure. Now i want to delete a record of that table. Since this table has a lot of child and these childs have also a lot of child and so on...and all foreign keys are delete restrict, don't tell me to change my foreign keys to delete cascade though. I'm wondering if any of you already have any store procedure that allow to do that kind of delete cascade? Any help would be greatly appreciate. DD If you have designed the RI with "on delete restrict" and don't want to change it, then you will have to writ...

Deleting all stored procedures
Are there any negative effects to deleting all stored procedures, triggers in a database by allowing updates to the system tables, in this case sysobjects, and running "delete from sysobjects where type = "P" or type = "TR"? I know could select all the procedures and triggers, output it to a file and append drop procedure or drop trigger in front of them. Is the sysdepends table effects wrongly by doing it the above way? Yes. Doing a direct update to sysobjects does not drop the object, as it will have other references in other system catalogs, such as sy...

PTR delete == A delete
Hi, i want to delete PTR record but not de A record. if i delete the PTR the A-record is also deleted. (use iManger & DNS console) is there a way to only delete PTR-record's? Niels van de Haar Systeembeheer In article <QwvDd.17151$Ei5.2128@prv-forum2.provo.novell.com>, wrote: > if i delete the PTR the A-record is also deleted. (use iManger & DNS > console) > What version of NAMED? NetWare? Service Pack? bd NSC Volunteer SysOp Netware 6.5 Sp2 (named from that netware sp) >>> Brad Doster<bd@NSCSysOps.net> 7-1-2005 17:1...

procedure for deletion and motion of objects
Hi, I wonder if there is a more automated treatment for the deletion and motion of objects which are under source control. It always hurts me if a colleague says "Have moved some objects from LibA to LibB, refresh your target and don't forget to delete the moved objects in LibA to avoid duplication". In reality he only speak the first part, the latter comes automatically into my mind. Refreshing the target is no problem, the moved objects where copied to its new place, if LibB is a new one it gets created - fine. But now starts the search of the old ones which are no...

Delete Confirm not Deleting
I followed the instruction in this tutorial (http://asp.net/learn/dataaccess/tutorial22vb.aspx) to add delete confirm to a GridView's FieldCommand Delete button.  It will delete the record if I comment out this code but not if I don't.  I get the confirm dialog and a postback but the record is not deleted.  DataKeyNames are set.  What's the deal?Here's the code.Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs)         'Delete Confirm     &nbs...

Gridview Delete not Deleting
I am trying to use a command button to delete a row from a gridview. When I click the command button though, the page posts but my record is still there... no errors, just nothing seems to happen. Below is what I think is the relevant code, let me know if you need to see anything else.  I'm kinda new to working with gridview objects, it would be nice if I could see what SQL command is acctually ran... maybe there are other things I can check too, I'm just not sure how.   <asp:GridView ID="GridView1" runat="server" AllowSorting="True"&nbs...

Deleted items not deleting
We are running GW 7.0.1 W2K3 server agents and 7.0.1 workstation clients. Some users have thousands of items in their trash and other folders within their account. The problem is when you select all the messages and delete them, they don't actually delete. They stay in the users client. An expire/reduce doesn't do anything either. We also tried setting up in consoleone under groupwise>client>environment>cleanup -- the clients to automatically delete items and that didnt' work either. Does anyone have any suggestions??? Will, i will try to run a GW check...

Stored Procedures and GridView Deletes
I have been trying to get the gridview control to properly delete data using a stored procedure.  When I click on the delete link, it throws the exception: Procedure 'spdTypes' expects parameter '@Id', which was not supplied. Please Help. My ASP.Net Code: <%@ Page Language="C#" AutoEventWireup="true" CodeFile="test.aspx.cs" Inherits="Admin_test" Title="Untitled Page" %><form runat="server"><asp:GridView ID="GridView1" runat="server" AllowSorting="True" AutoGenerateColumns="False" DataSourceID="SqlDataSource1">   <Columns>    &nbs...

delete vs []delete
Hi all, I have some strange behavior in some code and I did see that I had delete when I should have []delete; Now I am not questioning the validity of []delete for an array of items as opposed to delete for a singularity. My question what kind of behavior would you expect if you mismatch new / delete as I described. Colin. On Thu, 5 Feb 2009 05:23:50 -0800 Colin Maharaj <noreply@nohost.com> wrote: > My question what kind of behavior would you expect > if you mismatch new / delete as I described. I wouldn't expect /anything/, it's undefined. -- ...

datagrid delete with stored procedure
I'm having trouble running a stored procedure when I click on the Delete button that is made visible for each row in the datagrid.  The problem appears in that one of the parameters I try to send are not being sent properly to the stored procedure.   What I want to do is take a value from the row I have clicked the Delete hyperlink in, and send it to the stored procedure.  I have tried using a parameter, and setting it to the name of the databinding column that I want the value to come from.  Any help would be great.  Thanks. Actually, I am working wit...

Web resources about - Is Cascading deletes prefered to deleting thru a stored procedure? - asp.net.object-datasource

Parliamentary procedure - Wikipedia, the free encyclopedia
... of the House of Commons of the Parliament of the United Kingdom , from which it derives its name. In the United States, parliamentary procedure ...

Procedure is more a snap than a snip
A QUEENSLAND doctor is bidding to set an unusual world record by performing the highest number of vasectomies in one day, with the help of fellow ...

Rushed cosmetic procedures a 'recipe for disaster'
&#8203;When Chanelle O'Hare went searching online for a deal on potential cosmetic procedures, she could not have imagined that what she ended ...

Jetstar procedures under investigation after planes took off too heavy
Jetstar's procedures for calculating the weight of its aircraft are under review by the Australian Transport Safety Bureau after two of its planes ...

Old Lady Lawyer: Uncivil Procedure
What is some of the worst behavior you've witnesses by attorneys?

NFL Announces Changes to Officiating Procedures for Playoffs - Bleacher Report
The NFL formally approved changes to its postseason officiating procedures to allow referees the opportunity to consult Vice President of Officiating ...

We need to reform the culture of law enforcement, not just the procedures
We need to reform the culture of law enforcement, not just the procedures by digby I have a new piece up at Salon this morning about police ...

Will Paul Ryan Make His Mark As Speaker By Instituting Impeachment Procedures Against President Obama ...
Wisconsin Ayn Rand devotee Paul Ryan just started his new job as Speaker of the dysfunctional House Republicans. And he's already headed for ...

TSA Updates Screening Procedure, Will Mandate Some Passengers Use Full-Body Scanners
... Imaging Technologies, or AIT, in favor of full-body pat-downs by TSA agents. Under the new mandate, not everyone can opt for the pat-down procedure. ...

'Painless' dental cavity procedure regrows tooth enamel
... "cavity" a lot of people sweat thinking about painful injections and relentless drilling. But scientists in Britain have developed a new procedure ...

Resources last updated: 1/10/2016 7:36:45 PM