Help 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 2005 Forums

Transact-SQL (2005)

Help cascading delete

Author

Topic

lucsky8
Posting Yak Master

105 Posts

Posted - 2009-10-21 : 08:58:07

Hi we have a report sheet for schools.
Just to let you know i did not design the table.
I need to create a stored procedure to delete a report.

Here the tables i trying to delete, in this example i want to delete the report with the id 89
I would like to create a SP
exe uspDelteReport 89 and will delete all the information that are relatated in all the table
I hope explain my self wright!
Sorry for my bad english

I know i need to loop in someway but i have no idea
Tks in advance
Luc

I need to delete them in this order because of the relation
-tblDescripteur
-tblCommentaire
-tblHabiletes_Attitudes
-tblObjet
-tblSction
-tblNomBull

The above example is the way that i do it manually

SELECT * FROM tblNomBull WHERE ID = 89

SELECT * FROM tblSection WHERE ID_Bull = 89

SELECT * FROM tblObjet WHERE ID_Sec = 277 OR ID_Sec = 279 OR ID_Sec = 280

SELECT * FROM tblHabiletes_Attitudes WHERE ID_Obj = 1061 OR ID_Obj = 1062 OR ID_Obj = 1063 OR ID_Obj = 1064 OR ID_Obj = 1070 OR ID_Obj = 1071 OR ID_Obj = 1072 OR ID_Obj = 1073 OR ID_Obj = 1074 OR ID_Obj = 1075 OR ID_Obj = 1076 OR ID_Obj = 1077 OR ID_Obj = 1078

SELECT * FROM tblCommentaire WHERE ID_Obj = 1061 OR ID_Obj = 1062 OR ID_Obj = 1063 OR ID_Obj = 1064 OR ID_Obj = 1070 OR ID_Obj = 1071 OR ID_Obj = 1072 OR ID_Obj = 1073 OR ID_Obj = 1074 OR ID_Obj = 1075 OR ID_Obj = 1076 OR ID_Obj = 1077 OR ID_Obj = 1078

SELECT * FROM tblDescripteur WHERE ID_Obj = 1061 OR ID_Obj = 1062 OR ID_Obj = 1063 OR ID_Obj = 1064 OR ID_Obj = 1070 OR ID_Obj = 1071 OR ID_Obj = 1072 OR ID_Obj = 1073 OR ID_Obj = 1074 OR ID_Obj = 1075 OR ID_Obj = 1076 OR ID_Obj = 1077 OR ID_Obj = 1078

lucsky8
Posting Yak Master

105 Posts

Posted - 2009-10-21 : 10:19:30

I think i got it :

--Get section
DECLARE @TempSection table
	(
		idSection int
	)
	
INSERT INTO @TempSection
SELECT ID FROM tblSection WHERE ID_Bull = 88


--Get object
DECLARE @TempObjet table
	(
		idObjet int
	)
	
INSERT INTO @TempObjet
SELECT ID FROM tblObjet INNER JOIN @TempSection on idSection = tblObjet.ID_Sec


 
DELETE tblHabiletes_Attitudes FROM tblHabiletes_Attitudes inner join @TempObjet on idObjet = tblHabiletes_Attitudes.ID_Obj
DELETE tblCommentaire FROM tblCommentaire inner join @TempObjet on idObjet = tblCommentaire.ID_Obj
DELETE tblDescripteur FROM tblDescripteur inner join @TempObjet on idObjet = tblDescripteur.ID_Obj
DELETE tblObjet FROM tblObjet inner join @TempSection on idSection = tblObjet.ID_Sec
DELETE tblSection FROM tblSection WHERE ID_Bull = 88
DELETE tblNomBull FROM tblNomBull WHERE ID = 88

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources