The simple way to determine if the data has been altered is to put a timestamp column on each relevant table, and a trigger on insert and update that updates this row to Now. If you want to make searching for new data fast, put an index on this column. Then store in another table the last time you checked for updates.

When you check for updates, read the last update time, then run a query on each table for all rows where the timestamp > the last update time. The tricky part here is when you reset the last update time. If you reset it immediately after you read it, you could get notified of some changes twice, whereas if you reset it after you’re done processing updates, you could miss a few. You’ll have to decide which is more acceptable based on your business requirements. (Or, if you want to make sure not to miss any, you’ll need one row in the table that records the last update time for each table you’re monitoring this way.)

This isn’t exactly the database notifying you of what’s changed, but it’s getting the DB to do the bookkeeping for you, which greatly simplifies the work you have to do.

Doing it on a file system, follows the same basic principles, but it’s a bit simpler because you have a timestamp built in to the file system. You just need a recursive scan of the file system and filter out anything whose last update time is before the time you’re looking for.

You are not going to find anything Out of the box in any DB system that would do this for you.

I would recommend you Create a Stored procedure that checks for recently updated data a logs it to a Table. Have that SP run on a timer. Your APP would then only need to query only one table and see all the changes across the system.

(That SP should also do some basic Clean up on the Table such as Deleting “Old rows)

If you need this to be more real-time, you would have to create a set of DB triggers that update this table.

This would move the code to back-end where it belongs (and where you want it).

it would mean our applications are much quicker and efficient

In what ways are your applications slow or inefficient now?

• Is this a GUI application with a less-than-responsive front-end? If so, you’re querying the database wrong. You should not do it on the main thread, but on another thread (or task if you’re using .Net).

• Is your code already using threads for the database polling and yet your application is still too slow? Maybe you can give the threads a lower priority?

• If your database polling is still slowing down your application, can you set up a second application that handles the polling and set up some type of communication between the two applications? Maybe a message queue? This would be especially beneficial if you have N copies of your application running as there would be one program polling the database instead of N programs. Repeatedly polling a message queue should be faster than repeatedly polling a database.

I think that what you want just doesn’t exist. When you send a request to the database, it has to sit and wait for the request in order to acknowledge it. This means the the database is doing it’s own polling. Maybe it’s optimized to be super fast, but at some level there’s something spinning and waiting for a signal. Your application must have a mirror image of this in order to receive a response from the database.