• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

Stored proc help

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
Steve Hougom
Asked:
Steve Hougom
  • 6
  • 3
  • 3
1 Solution
 
Olaf DoschkeSoftware DeveloperCommented:
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
 
Brian CroweDatabase AdministratorCommented:
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
 
Steve HougomDeveloper IIAuthor Commented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Steve HougomDeveloper IIAuthor Commented:
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
 
Brian CroweDatabase AdministratorCommented:
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
 
Steve HougomDeveloper IIAuthor Commented:
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
 
Steve HougomDeveloper IIAuthor Commented:
Here is some more schema related stuff regarding the above two errors.

other vehicle driver
claimantparticipantid
0
 
Steve HougomDeveloper IIAuthor Commented:
Here is the updated ERD.  I forgot tbAccidentOtherVehicleDriver which is joined from tbAccidentOtherVehicle.  Sorry

updated ERD
0
 
Brian CroweDatabase AdministratorCommented:
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
 
Steve HougomDeveloper IIAuthor Commented:
Perfect.  BriCrowe to the rescue again!
0
 
Olaf DoschkeSoftware DeveloperCommented:
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
 
Olaf DoschkeSoftware DeveloperCommented:
I retract my question, problem solved.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now