HELP! Missing records in SQL 7.0 table!

[dougcoulter]

Hi everyone - I have a problem that is baffling me.

In my SQL 7.0 database, I have a table (FaultHistory) that is "missing" some records. The table is written to via only one source, an insert trigger from another table (AlarmLog), and is used only for viewing purposes. There are numerous records in the AlarmLog table that are not in the FaultHistory table (when all should). For those records that do not exist in the FaultHistory table, there gaps in my identity field, which would indicate that the records may have in fact been there at one time!

The records are time/date stamped, so my question is this: Can I use the transaction log in some way to investigate the transactions surrounding the time of the missing record? Also, do gaps in the identity field necessarily indicate that the associated records were present at some time?

 

[redlam]

Gaps in identity do not necessarily mean that the records were successfully written to the table - it does, however, mean that an attempt was made to write them. Is it possible that under certain circumstances your trigger is unsuccessful? I'm not sure how to advise you with regard to the transaction log but I would test that trigger with the data from the AlarmLog that 'failed' to see if you can determine the problem.