How to tell if new data is in the database

rdrunner, that approach MIGHT work, but does not address the case when one user ADDS a records, and another user DELETES a record, thus leaving the record COUNT the same????


Arthur Wood

Triggers are surely existing under Oracle. Write a trigger on Insert, Update and Delete that updates a 0-byte file somwhere on a server. If the timestamp of this file changes, you know that data have been modified.

I think emoreau is on the right track, but instead of writing to a file, I would write to an audit table.  Then you can pretty much keep your existing code and just query the audit table periodically instead of the full table.  You won't have to try to write code to read a file that is probably on a different machine, and most likely a different OS as well. You could keep a handful of things in the audit table at almost no cost such as the last person to change a record, the time it was done and the action.  All things that might be useful, but are expensive to dig out of a big table.  

-Wes

select count(*) from yourtable

Should also be quite fast since it can use Indexes(sp?)

So you would need no new table at all..

Maybe I should explain some more. The table I'm querying has a small number of records (always the same number). My concern is not the efficiency of the query, but the efficiency of querying the database so frequently (or does this matter). I could setup the uploading PC to send a message to any client PCs which are monitoring the table, that new data is available. In this way they would only query the DB when they receive the new data notification. Is this the best/ only method or is there some better way to tell the querying clients that the data is new.

regards Gerry

I'm not a experts of Oracle, but can give you some solution you can simulate:
in DB2 Database, have a Date Type Called TimeStamp it's detail to microsecond, and "Current TimeStamp" can get current datetime with microsecond.

1.In table, add a field Name=LastUpdateTS DateType=TimeStamp
2.Every time update any data in Table, must set LastUpdateTS = Current TimeStamp
3.In Watch application ("queries this table every 10 seconds")
  A)first time, select all record in table, and get max(LastUpdateTS) to a variable(example: sLastUpdateTS)
  B)every timer, Use Search-condition LastUpdate > sLastUpdateTS to get new data in table. if have record return, update sLastUpdateTS with maximal LastUpdateTS value again