Cascade Delete

Question

Cascade Delete

vasko102

SSC Enthusiast

Points: 136
More actions
April 14, 2009 at 6:44 am

#199448

Hi, i need stored procedure that will delete all informations from the first table and also delete the informations in the second table. The two tables are:
Vraboten
-----------------------------
Vraboten_IDint
Ime nvarchar(50)
Prezime nvarchar(50)
EMBG bigint
Adresa nvarchar(50)
Grad nvarchar(50)
and the second table is:
Honorar
----------------------------
Honorar_ID int
ID_Mesec int
ID_Tip int
BrutoHonorar decimal(18, 0)
PersonalenDanok nvarchar(50)
NetoHonorar decimal(18, 0)
ID_Vraboten int
the relationship is :FK_Honorar_Vraboten
I want to delete the informations from the first table.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

In your stored procedure you need to delete the child rows first (Honorar), then delete the parent row or rows (Vraboten). Something like this:

Begin Transaction

Delete

From

Honarar

Where

Vraboten_Id = @Vraboten_Id

Delete

From

Vraboten

Where

Vraboten_Id = @Vrabotan_Id

Commit Transaction

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 2

You can even think about using "ON DELETE CASCADE" to perform the deletes with out writing the code.

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

Abhijit More SSCertifiable Points: 6897 More actions · Answer 3

yeah it would be a suggetion to create "CASCADE DELETE" on child table in the scenarion you have mention but do remember to verify do you have any cyclical reference for child table.

Abhijit - http://abhijitmore.wordpress.com

Lowell SSC Guru Points: 323518 More actions · Answer 4

also, depending on the business model, you might not want to DELETE rows that contain a reference to some parent record, but simply set the value to NULL in the child record instead.

it requires more analysis than finding foreign key references and deleting rows.

example? any financial data that had # sold or something, i would assume cannot be deleted just because you want to delete a customer or product....

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!