SQL Server Performance

deleting records from multiple tables

Discussion in 'General Developer Questions' started by janice, Sep 23, 2003.

  1. janice New Member

    Dear experts,
    I have 7 tables:
    tblWeather, tblFactors, tblCondition, tblVehicle, tblDriver,tblEvent, tblInsurance

    tblWeather has:
    weatherID int pk,
    weatherDesc varchar(50)

    tblFactors has:
    factorsID int pk,
    factorsDesc varchar(50)

    tblCondition has:
    conditionID int pk,
    ConditionDesc varchar(50)

    tblInsurance has:
    insuranceID int pk identity,
    insuranceCo varchar(50),
    PolicyNumber varchar(50)

    tblEvent has several fields including:
    eventID pk identity int,
    factorsID (fk from tblFactors)
    weatherID (fk from tblWeather)

    tblVehicle has several fields including:
    insuranceID (fk from tblInsurance)
    eventID (fk from tblEvent)

    tblDriver has several fields including:
    driverID int pk identity,
    eventID (fk from tblEvent),
    conditionID (fk from tblCondition)

    What has is that whenever a record is created, naturally, you can
    perform a join to display records that a user wants to see.
    For instance, a user might want to see the name of a driver,
    the vehicle the driver is driving, the insurance on the vehicle,
    the factors contributing an accident (if one occurs), the condition of the driver (from tblCondition), etc.
    my question is, if a user enters incorrect info, or even if info entered is correct but the user wants to delete this info, how can I do this giving the table listings above?
    Please your assistance is greatly appreciated.

    I know that I can delete info entered on tblEvent but I need all records associated with a particular eventID to be deleted also.
  2. gaurav_bindlish New Member

    Try creating a view on the same and that itself or a instead of trigger on the same shall help deleting the record from all the tables.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  3. Jon M Member

    Hi,

    I see that you have PK and FK on your tables. Try to enforce "Cascade Delete Related Records" from the Foreign Key tables in such a way that when you delete a specific EventID from your Primary Key table (tblEvent) all records related to that EventID will be deleted.

    Jon M
  4. Twan New Member


    or have a stored procedure which deletes from

    tblVehicle where eventID matches
    tblDriver where eventID matches
    tblEvent where eventID matches

    The other tables look like lookup tables and so don't need to have records deleted...?

    Cheers
    Twan
  5. janice New Member

    These ideas are really great ideas.
    I am just wondering which one will be easier for me to implement.
    You see I am trying to use ASP (Active Server Pages) to perform these delete functions.
    Twan's idea was my original implementation idea but I am not sure if one click of a button will delete all associated values (from those tables).
    Jon's idea is really tempting because all I have to do is setup that up from enterprise manager. I did just that but when I wrote the asp code to perform a delete, it only deleted data from tblEvent.
    I thought that a code such as this:
    delete from tblEvent where eventId = paramEventID.
    Again, I have a situation where I have tried validate that on the backend and when it never did the job it was supposed to do on the front end.
    I know this is a sql server forum but has anyone of you had a situation where any of the ideas can be effectively implemented on the front end?
  6. janice New Member

    Ok, I just tested the delete cascade with my asp and it seems to have worked for me.
    Thanks Jon and thanks all you good experts for your timely contributions.
    This is a really nice and helpful forum!
  7. Luis Martin Moderator

    You have a final question, and you respond your self.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell

Share This Page