How to tell if a record is still in a SQL table - using linq maybe ?

Question

Hi,

I have an SQL database, which is a "feeder" table. I put records in said table, a 3rd party package consumes (and deletes) them. All hunky dory - until the 3rd party package isn't running. In thinking about how to detect that, I thought to myself... "well... what if I read all the keys in the table (its not very big - max a few dozen records), and kept them, and then in, say, 5 minutes, I checked if any were still in the table ?"

It may not be a brilliant solution, but it sent me off thinking about Linq and whether you could do such a thing (I haven't used Linq before).

So, if I read all the record keys into a DataTable object and then, five minutes later, read all the records into another DataTable object, I can do a Linq select, joining the two DataTable objects on the key column, and then look at the results of "Count", and if one or more, chances are the data in the table isn't being consumed.

Or... is there a "cleverer" way than that ?

Answer 1

Create a DELETE trigger which records in a separate table the timestamp of the last delete. An additional INSERT trigger would record the timestamp of the last insert statement.

Compare the two timestamps.

Answer 2

You could return the identity column value (assuming there is one) after your insert and record it in a separate table along with its commit datetime they just pull outstanding records with;

SELECT * FROM feeder_table F
  INNER JOIN other_table T ON (F.id = T.id)
WHERE DATEDIFF(MINUTE, T.commitdate, GETDATE()) > 5

That way your not persisting data in memory so it will work between application restarts/across machines.

(If this is just for fault detection you would only need to store the last inserted id.)