Cascaded Delete with multiple tables

[JKDeveloper0718]

I have a 3 tables for example Nodes, Engine, Events and they are no relationships set but each has a primary key and in some way related by a primary and foreign relationship just not established in SQL. I did not design this its another vendor back-end build. Anyways heres a little better look at the tables and their elements.

Table Keys(Indexes)
----------------------------------
Nodes NodeID (PK), EngineID(FK)
Engines EngineID(PK)
Events EventID(PK), EngineID(FK)
----------------------------------

The Engines Table is the main primary table that starts it all and you can see all the elements with EngineID in the Nodes and Events tables. So basically I need to delete all of those elements with that engineID in the Nodes and Events table. But there is one more twist to this puzzle several other tables are associated to the Nodes table by NodeID this being the primary key in the Nodes table but foreign in the other tables. So there is like 10 more tables such as interface, Modules, etc... I would like to delete all those as well but I want to do this with ONE cascaded statement. I would appreciate a little help to construct this delete statement and I know that it will be a very nested type of sql.

For Example this:
delete * from Nodes where EngineID = 3

This is some of the logic but will only delete just the from Nodes table in the process if I run this the other tables will still have nodeID associated to that engine and I will not know what ones needed to be deleted.

Any help would be great.

 

[bborissov]

Make triger for DELETE of table Engine, in it put:

DELETE FROM Table1
FROM Table1
INNER JOIN (SELECT NodeId FROM Nodes
                  INNER JOIN DELETED ON Nodes.EngineId = Deleted.Id) Tbl1
ON Table1.NodesId = Tbl1.NodeId
....
DELETE FROM TableX
FROM TableX
INNER JOIN (SELECT NodeId FROM Nodes
                  INNER JOIN DELETED ON Nodes.EngineId = Deleted.Id) Tbl1
ON TableX.NodesId = Tbl1.NodeId

DELETE FROM NodesId
FROM NodesId
INNER JOIN DELETED ON Nodes.EngineId = Deleted.Id

-- The same for all events Tables

DELETE FROM Events
FROM Events
INNER JOIN DELETED ON Events.EngineId = Deleted.Id

MAKE A GOOD BAKCUP first, this is not tested.




Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[XCNTRYMtnBiker]

I also use DTS services to do a cascading delete. This way I can record errors to a log file and roll the whole transaction back.