?
Solved

Stored proc help

Posted on 2013-12-20
12
Medium Priority
?
262 Views
Last Modified: 2013-12-20
Wondering if someone can help me with writing a stored proc that deletes some data.  Im not sure how to handle the fk relationships and the order of deletion etc.

Ive attached the ERD and a general sql statement involving the data and relationships involved.

accident ERD
All of the one to many relationships are bound to the master which is tbAccident and are joined on AccidentId.  There are some many to many relationships i have to be concerned about though so Its mostly about the order of delete and which params are required to pull this off.  

The goal is to remove an accident row and any associated child rows in the related tables.

Here is a general sql statement regarding the related tables.

SELECT        Accident.tbAccident.Id, Accident.tbAccidentDriver.DriverId, Accident.tbAccidentOtherVehicle.Id AS Expr1, Accident.tbAccidentOtherVehicle.Make, 
                         Accident.tbAccidentOtherVehicle.Model, Accident.tbAccidentOtherVehicle.Year, Accident.tbAccidentUnit.AccidentId, Accident.tbAccidentUnit.UnitNumber, 
                         Accident.tbAccidentTrailer.TrailerNumber, Accident.tbAccidentParticipant.ParticipantType, Accident.tbAccidentParticipant.Id AS Expr2
FROM            Accident.tbAccident INNER JOIN
                         Accident.tbAccidentDriver ON Accident.tbAccident.Id = Accident.tbAccidentDriver.AccidentId INNER JOIN
                         Accident.tbAccidentOtherVehicle ON Accident.tbAccident.Id = Accident.tbAccidentOtherVehicle.AccidentId INNER JOIN
                         Accident.tbAccidentUnit ON Accident.tbAccident.Id = Accident.tbAccidentUnit.AccidentId INNER JOIN
                         Accident.tbAccidentTrailer ON Accident.tbAccident.Id = Accident.tbAccidentTrailer.AccidentId INNER JOIN
                         Accident.tbAccidentParticipant ON Accident.tbAccident.Id = Accident.tbAccidentParticipant.AccidentId INNER JOIN
                         Accident.tbAccidentCitation ON Accident.tbAccidentDriver.Id = Accident.tbAccidentCitation.DriverId INNER JOIN
                         Accident.tbAccidentClaimant ON Accident.tbAccidentParticipant.Id = Accident.tbAccidentClaimant.ClaimantParticipantId INNER JOIN
                         Accident.tbAccidentOrderStop ON Accident.tbAccident.Id = Accident.tbAccidentOrderStop.AccidentId INNER JOIN
                         Accident.tbAccidentPoliceInformation ON Accident.tbAccident.Id = Accident.tbAccidentPoliceInformation.AccidentId AND 
                         Accident.tbAccidentParticipant.Id = Accident.tbAccidentPoliceInformation.AuthorityParticipantId
WHERE        (Accident.tbAccident.Id = 54)

Open in new window


According to sql management studio here are the dependencies for the master table.
0
Comment
Question by:hougie40
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 3
12 Comments
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 39732042
You always have to delete the master record last. That's about all you need to know.

In many to many raltionship tables both the referenced records have to be deleted first, before the relation record can be deleted.

You could also change referential integrity rules to cascading deletes, due to many-to-many relationships this isn't that straightforward, though.

so its'
DELETE FROM tbAccident<xyz> WHERE AccidentId = 54
in which <xyz> is a placeholder for any of the Driver, OtherVehicle etc tables

then finally DELETE FROM tbAccident Where ID = 54

In regard of the ERD you don't show the foreign keys of the tbAccidentCitation and tbAccidentClaimant. Records in these tables have to come off first, then you can delete in tbAccidentDriver and tbAccidentParticipant. It's impossible to guess what the keys are named, but you need a delete query joining the tbAccidentDriver and tbAccidentParticipant relations data to be able to filter for a certain AccidentID, of course, or you have to query IDs from tbAccidentDriver and tbAccidentParticipant to then use as parameters for DELETE queries on tbAccidentCitation and tbAccidentClaimant.

Clearer now?

Bye, Olaf.
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39732052
DECLARE @AccidentID      INT;

BEGIN TRY
      BEGIN TRANSACTION

      DELETE tbAccidentDriver
      WHERE AccidentID = @AccidentID;

      DELETE tbAccidentOtherVehicle
      WHERE AccidentID = @AccidentID;

      DELETE tbAccidentUnit
      WHERE AccidentID = @AccidentID;

      DELETE tbAccidentTrailer
      WHERE AccidentID = @AccidentID;

      DELETE tbAccidentClaimant
      FROM tbAccidentClaimant AS AC
      INNER JOIN tbAccidentParticipant AS AP
            ON AC.ClaimantParticipantID = AC.ID
      WHERE AP.AccidentID = @AccidentID;

      DELETE tbAccidentParticipant
      WHERE AccidentID = @AccidentID;

      DELETE tbAccidentCitation
      WHERE AccidentID = @AccidentID;

      DELETE tbAccidentOrderStop
      WHERE AccidentID = @AccidentID;

      DELETE tbAccidentPoliceInformation
      WHERE AccidentID = @AccidentID;

      COMMIT TRANSACTION
END TRY
BEGIN CATCH
      ROLLBACK TRANSACTION
      --Record or print out the @AccidentID that failed
END CATCH
0
 

Author Comment

by:hougie40
ID: 39732110
Olaf,

You could also change referential integrity rules to cascading deletes, due to many-to-many relationships this isn't that straightforward, though.


Tried that route but the dba wont allow due to performance issues.

Here are the columns from the tables you were seeking accidentClaimant and AccidentCitation.  

claimant
citation
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

Author Comment

by:hougie40
ID: 39732137
BriCrowe,

I think the issue I noticed was that your script removes drivers before citations.  Citations are the child most in the driver/citation relationship.  At least thats what it looked like to me anyway.
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39732174
oops...you're right

DECLARE @AccidentID      INT;

BEGIN TRY
      BEGIN TRANSACTION

      DELETE tbAccidentCitation
      FROM tbAccidentCitation AS AC
      INNER JOIN tbAccidentDriver AS AD
            ON AC.DriverId = AD.Id
      WHERE AD.AccidentID = @AccidentID;

      DELETE tbAccidentDriver
      WHERE AccidentID = @AccidentID;

      DELETE tbAccidentOtherVehicle
      WHERE AccidentID = @AccidentID;

      DELETE tbAccidentUnit
      WHERE AccidentID = @AccidentID;

      DELETE tbAccidentTrailer
      WHERE AccidentID = @AccidentID;

      DELETE tbAccidentClaimant
      FROM tbAccidentClaimant AS AC
      INNER JOIN tbAccidentParticipant AS AP
            ON AC.ClaimantParticipantID = AC.ID
      WHERE AP.AccidentID = @AccidentID;

      DELETE tbAccidentParticipant
      WHERE AccidentID = @AccidentID;

      DELETE tbAccidentOrderStop
      WHERE AccidentID = @AccidentID;

      DELETE tbAccidentPoliceInformation
      WHERE AccidentID = @AccidentID;

      COMMIT TRANSACTION
END TRY
BEGIN CATCH
      ROLLBACK TRANSACTION
      --Record or print out the @AccidentID that failed
END CATCH
0
 

Author Comment

by:hougie40
ID: 39732195
bricrowe,

Here is what I got when I set accidentid and ran it.

(0 row(s) affected)

(1 row(s) affected)
Msg 547, Level 16, State 0, Line 18
The DELETE statement conflicted with the REFERENCE constraint "FK_tbAccidentOtherVehicleDriver_tbAccidentOtherVehicle". The conflict occurred in  table "Accident.tbAccidentOtherVehicleDriver", column 'OtherVehicleId'.
The statement has been terminated.

(1 row(s) affected)

(1 row(s) affected)

(0 row(s) affected)
Msg 547, Level 16, State 0, Line 33
The DELETE statement conflicted with the REFERENCE constraint "FK_tbAccidentClaimant_tbAccidentParticipant". The conflict occurred in table "Accident.tbAccidentClaimant", column 'ClaimantParticipantId'.
The statement has been terminated.

(0 row(s) affected)

(0 row(s) affected)


Do you need more info from me regarding table design and fks
0
 

Author Comment

by:hougie40
ID: 39732204
Here is some more schema related stuff regarding the above two errors.

other vehicle driver
claimantparticipantid
0
 

Author Comment

by:hougie40
ID: 39732215
Here is the updated ERD.  I forgot tbAccidentOtherVehicleDriver which is joined from tbAccidentOtherVehicle.  Sorry

updated ERD
0
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 2000 total points
ID: 39732245
I believe I corrected both issues.  One was a missing table deletion and the other was a typo.

DECLARE @AccidentID      INT;

BEGIN TRY
      BEGIN TRANSACTION

      DELETE tbAccidentCitation
      FROM tbAccidentCitation AS AC
      INNER JOIN tbAccidentDriver AS AD
            ON AC.DriverId = AD.Id
      WHERE AD.AccidentID = @AccidentID;

      DELETE tbAccidentDriver
      WHERE AccidentID = @AccidentID;

      --Added this table deletion
      DELETE tbAccidentOtherVehicleDriver
      FROM tbAccidentOtherVehicleDriver AS OVD
      INNER JOIN tbAccidentOtherVehicle AS OV
            ON OVD.OtherVehicleId = OV.Id
      WHERE OV.AccidentID = @AccidentID;

      DELETE tbAccidentOtherVehicle
      WHERE AccidentID = @AccidentID;

      DELETE tbAccidentUnit
      WHERE AccidentID = @AccidentID;

      DELETE tbAccidentTrailer
      WHERE AccidentID = @AccidentID;

      DELETE tbAccidentClaimant
      FROM tbAccidentClaimant AS AC
      INNER JOIN tbAccidentParticipant AS AP
            ON AC.ClaimantParticipantID = AP.ID  --There was a typo here
      WHERE AP.AccidentID = @AccidentID;

      DELETE tbAccidentParticipant
      WHERE AccidentID = @AccidentID;

      DELETE tbAccidentOrderStop
      WHERE AccidentID = @AccidentID;

      DELETE tbAccidentPoliceInformation
      WHERE AccidentID = @AccidentID;

      COMMIT TRANSACTION
END TRY
BEGIN CATCH
      ROLLBACK TRANSACTION
      --Record or print out the @AccidentID that failed
END CATCH
0
 

Author Closing Comment

by:hougie40
ID: 39732318
Perfect.  BriCrowe to the rescue again!
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 39732323
If you still have an error, when finally deleting the record of tbAccident, the error will tell you the relation name and the relation will tell you primary and foreign key involved.

In principle BriCrowe has worked out, what you need, you just have to set @AccidentID of course or wrap that as a stored proc and make it a parameter.

The performance issue is given anyway, you see how many deletes you issue. The more important point against a cascading delete is, it can happen too easily with a wrong ID, and there is no stopping it.

I think you can resolve this sooner or later, or is there still an essential problem left over?

Bye, Olaf.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 39732334
I retract my question, problem solved.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question