Cascading deletion
in [Programming SQL]
|
Prev: XML input Parameter C# (Stored Proc)
Next: SQL Server 2000 Maintenance Plan and Win2003 Server Backup schedule
From: scuba79 on 14 Dec 2009 20:49 I'm trying to find an example of cascading deletion via T-SQL code. Thanks in advance
From: Tom Cooper on 14 Dec 2009 21:05 Create Table FooCustomers (CustomerID int Primary Key, CustomerName varchar(50) Not Null); Create Table FooOrders (OrderID int Primary Key, CustomerID int Not Null, OrderDate datetime Not Null, Constraint OrdersCustomersFK Foreign Key (CustomerID) References FooCustomers On Delete Cascade); Insert FooCustomers (CustomerID, CustomerName) Select 1, 'Smith' Union All Select 2, 'Jones'; Insert FooOrders (OrderID, CustomerID, OrderDate) Select 1, 1, '20090506' Union All Select 2, 1, '20090817' Union All Select 3, 2, '20091010'; /* Notice that FooOrders has 3 orders 2 for CustomerID 1 and 1 for CustomerID 2 */ Select 'Before', * From FooOrders; /* Delete CustomerID 1 from FooCustomers and notice that the Orders For Customer 1 are automatically deleted from FooOrders */ Delete From FooCustomers Where CustomerID = 1; Select 'After', * From FooOrders; /* Cleanup */ Drop Table FooOrders; Drop Table FooCustomers; Tom "scuba79" <scuba79(a)discussions.microsoft.com> wrote in message news:43165D7D-76E0-4A92-B694-DEC01966B28C(a)microsoft.com... > I'm trying to find an example of cascading deletion via T-SQL code. > > Thanks in advance
From: scuba79 on 14 Dec 2009 23:28 I can't turn on the cascading delete due to reasons beyond my control... I wish I could I know that would solve the issue but I have to look at tackling this problem via code "Tom Cooper" wrote: > Create Table FooCustomers (CustomerID int Primary Key, CustomerName > varchar(50) Not Null); > > Create Table FooOrders (OrderID int Primary Key, CustomerID int Not Null, > OrderDate datetime Not Null, > Constraint OrdersCustomersFK Foreign Key (CustomerID) References > FooCustomers On Delete Cascade); > > Insert FooCustomers (CustomerID, CustomerName) > Select 1, 'Smith' > Union All Select 2, 'Jones'; > > Insert FooOrders (OrderID, CustomerID, OrderDate) > Select 1, 1, '20090506' > Union All Select 2, 1, '20090817' > Union All Select 3, 2, '20091010'; > > /* Notice that FooOrders has 3 orders 2 for CustomerID 1 and 1 for > CustomerID 2 */ > Select 'Before', * From FooOrders; > > /* Delete CustomerID 1 from FooCustomers and notice that the Orders For > Customer 1 are automatically deleted from FooOrders */ > Delete From FooCustomers Where CustomerID = 1; > Select 'After', * From FooOrders; > > /* Cleanup */ > Drop Table FooOrders; > Drop Table FooCustomers; > > Tom > > "scuba79" <scuba79(a)discussions.microsoft.com> wrote in message > news:43165D7D-76E0-4A92-B694-DEC01966B28C(a)microsoft.com... > > I'm trying to find an example of cascading deletion via T-SQL code. > > > > Thanks in advance > > . >
From: Tom Cooper on 15 Dec 2009 01:49
Two choices, one would be to create a stored procedure to do the deletes. Pass the stored procedure the primary key of the parent table. Then the stored procedure would first delete the child table rows, then delete the parent table row. Then instead of directly deleting the parent rows, your application would always call that stored proc. Given the sample DDL I gave before (except don't make the foreign key a cascading delete constraint), that stored procedure would look something like: Create Procedure DeleteFooCustomers (@CustomerID int) As Begin Delete From FooOrders Where FooOrders.CustomerID = @CustomerID; Delete From FooCustomers Where FooCustomers.CustomerID = @CustomerID; End Alternatively, you could create a INSTEAD OF trigger on the parent table. Then when your application code did a DELETE on the parent table, instead of the delete happening directly, SQL would fire the trigger. The trigger would then delete the child rows, and then do the actual delete of the parent. Something like: Create Trigger trFooCustomers On FooCustomers Instead Of Delete As Begin Delete From FooOrders Where FooOrders.CustomerID In (Select deleted.CustomerID From deleted); Delete From FooCustomers Where FooCustomers.CustomerID In (Select deleted.CustomerID From deleted); End In either case, don't forget to add appropriate error checking and transaction control to ensure either both the parent and child deletes are done, or neither. Tom "scuba79" <scuba79(a)discussions.microsoft.com> wrote in message news:877035A4-46D2-426C-941A-01E6BDBDD7E4(a)microsoft.com... >I can't turn on the cascading delete due to reasons beyond my control... I > wish I could I know that would solve the issue but I have to look at > tackling > this problem via code > > "Tom Cooper" wrote: > >> Create Table FooCustomers (CustomerID int Primary Key, CustomerName >> varchar(50) Not Null); >> >> Create Table FooOrders (OrderID int Primary Key, CustomerID int Not Null, >> OrderDate datetime Not Null, >> Constraint OrdersCustomersFK Foreign Key (CustomerID) References >> FooCustomers On Delete Cascade); >> >> Insert FooCustomers (CustomerID, CustomerName) >> Select 1, 'Smith' >> Union All Select 2, 'Jones'; >> >> Insert FooOrders (OrderID, CustomerID, OrderDate) >> Select 1, 1, '20090506' >> Union All Select 2, 1, '20090817' >> Union All Select 3, 2, '20091010'; >> >> /* Notice that FooOrders has 3 orders 2 for CustomerID 1 and 1 for >> CustomerID 2 */ >> Select 'Before', * From FooOrders; >> >> /* Delete CustomerID 1 from FooCustomers and notice that the Orders For >> Customer 1 are automatically deleted from FooOrders */ >> Delete From FooCustomers Where CustomerID = 1; >> Select 'After', * From FooOrders; >> >> /* Cleanup */ >> Drop Table FooOrders; >> Drop Table FooCustomers; >> >> Tom >> >> "scuba79" <scuba79(a)discussions.microsoft.com> wrote in message >> news:43165D7D-76E0-4A92-B694-DEC01966B28C(a)microsoft.com... >> > I'm trying to find an example of cascading deletion via T-SQL code. >> > >> > Thanks in advance >> >> . >> |