asked on

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.

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.

Olaf Doschke

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.

Brian Crowe

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

Steve Hougom

ASKER

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.

Steve Hougom

ASKER

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.

Brian Crowe

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

Steve Hougom

ASKER

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

Steve Hougom

ASKER

Here is some more schema related stuff regarding the above two errors.

Steve Hougom

ASKER

Here is the updated ERD. I forgot tbAccidentOtherVehicleDriver which is joined from tbAccidentOtherVehicle. Sorry

ASKER CERTIFIED SOLUTION

Brian Crowe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Steve Hougom

ASKER

Perfect. BriCrowe to the rescue again!

Olaf Doschke

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.

Olaf Doschke

I retract my question, problem solved.