Hi Everyone,
I have inherited maintenance of a SQL Server 2000 database and schema.
I am trying to write some admin procedures to clean up unwanted data and I have a problem with the above.
We have a Hardware table with 2 self-referencing links. A Definition fragment follows:
Hardware table:
Id bigint (Primary Key) Not Null
ComponentOf bigint (Foreign Key to Id) Null
PrevId bigint (Foreign Key to Id) Null
Basically the ComponentOf column can be null or contain the Id of a Hardware record that it is a component (child) of. The PrevId column can be null or contain the id of the previous version of the record.
This last is a little weird: when we change a record an Instead Of trigger intercepts the modification and copies the entire record to a new record with a new Id (generated by an Identity property). The trigger then sets the PrevId of the current record to be the Id of the just-inserted record, i.e. the initial hardware record remains the current record and history is always added as subsequent inserts (seem the wrong way round, but I inherited it and it works so I'm leaving it alone).
The problem is: how can I delete a hardware record if it is already referenced by component and history records ? For referential integrity I would like to delete the components and history at the same time.
I have tried instead of triggers with all sorts of logic, but I just can't get it to work.
Anyone figured this out before ???
TIA
Mark ;-)))
I have inherited maintenance of a SQL Server 2000 database and schema.
I am trying to write some admin procedures to clean up unwanted data and I have a problem with the above.
We have a Hardware table with 2 self-referencing links. A Definition fragment follows:
Hardware table:
Id bigint (Primary Key) Not Null
ComponentOf bigint (Foreign Key to Id) Null
PrevId bigint (Foreign Key to Id) Null
Basically the ComponentOf column can be null or contain the Id of a Hardware record that it is a component (child) of. The PrevId column can be null or contain the id of the previous version of the record.
This last is a little weird: when we change a record an Instead Of trigger intercepts the modification and copies the entire record to a new record with a new Id (generated by an Identity property). The trigger then sets the PrevId of the current record to be the Id of the just-inserted record, i.e. the initial hardware record remains the current record and history is always added as subsequent inserts (seem the wrong way round, but I inherited it and it works so I'm leaving it alone).
The problem is: how can I delete a hardware record if it is already referenced by component and history records ? For referential integrity I would like to delete the components and history at the same time.
I have tried instead of triggers with all sorts of logic, but I just can't get it to work.
Anyone figured this out before ???
TIA
Mark ;-)))
