Solved

Stored proc help

Posted on 2013-12-20
12
256 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
  • 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
 

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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 500 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now