Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

All our (my company) currently applications pull information from that database, is their a way to get the following types of databses to either push data or push an event to allow the application to pull data.

  • Access
  • SQL
  • Oracle
  • File systems (Files and folders)

The issue today is that most of our application spend a large amount of time constantly looking at databases and file system checking to see if data has changed . It would be better for the database to inform the application when data has changed. Are there tools within Visual studio to allow this or are there tools within the database / filesystem to do this?

All ready asked this on stack overflow but go no answer. I've been doing some more research but I cant seem to get any further. My manager has asked to investigate it as it would mean our applications are much quicker and efficient.

share|improve this question
    
What infrastructure do you have for pushing alerts/listening for alerts? You don't say what server type, but a scheduled task/chron job could query an "updates" table, like answered below, and push to the app. –  Michael Itzoe Sep 6 '12 at 14:00
add comment

2 Answers

up vote 2 down vote accepted

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.

share|improve this answer
add comment

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).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.