Cascading Delete - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Cascading Delete

Author

Topic

shveta
Starting Member

3 Posts

Posted - 2002-01-29 : 05:22:08

How do I do a cascading delete using one query.

I have two tables Orders(OrderId, CustomerID,...) and OrderItems(OrderItemID, OrderID,..) OrderItemsID.OrderID is a FK to Orders table. I want to delete all Orders of a particular OrderID.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-29 : 06:28:45

If you perform the delete via a Stored Procedure (recommend all DB access through stored procedures) then it is simple

begin tran
delete OrderItems where OrderID = @id
if @@error <> 0
begin
raiserror('failed',16,-1)
rollback tran
return
end
delete Orders where OrderID = @id
if @@error <> 0
begin
raiserror('failed',16,-1)
rollback tran
return
end
commit tran

You can also probably do this using a trigger on the Orders table.
It will have to be an instead of trigger to get round the foreign key violation.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources