Detecting new data in database

[GH23]

I'm developing a VB.NET application that is going to be checking if a table has been changed and if it has get the data and send it off to another application.

I was looking into ways for an application to know that there has been new data added to the database (Insert/update) or removed from the database (delete). There are only two methods that I found the first is querying the database constantly but this isn't very scalable and would probably take down our sever. The second approach is via a Query Notification but it seems that this is only do able with SQL Server 2005 and we use DB2 here.

My question is what is an efficient scalable method of checking for new data in a database that can be implemented using a DB2 database and VB.NET?

[JorgeM]

You may want to look into the use of SQL Triggers. A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server, such as an update.

[GH23]

Thank you so much. That is exactly what I needed.

Edit:

Alright well I was a bit early on sit was exactly what I needed. I'm curious on how you have a SQL Trigger notify an application that it was fired. This thread talks about having tigger throw an error and catch said error, which I can do. The problem is when you try and have another program that isn't the one doing the insert catch the exception. In that case there is no connect between the trigger and the program.

In other words, I have program A monitoring the database and program B updating the database. When program B changes the database I want the database to somehow tell program A. Any thoughts?

Oh and hee's the link mentioned above: Click Here

[JorgeM]

What I have used triggers for in the past is in a scenario similar to this: I have a database that gets updated from a handful of sources (applications). In a particular table, when a row is added, I have the trigger perform an action, such as inserted information in a different table. If this is what you are interested in, I would take a look at this article as it should be able to at least put you in the right direction.
http://www.sqlteam.com/article/an-introduction-to-triggers-part-i

[GH23]

I was actaully looking at having the trigger inform a program that there was new data that it needs to look at but I've discovered a round about way of doing it. I have the trigger tell the database to run a web service. The web service updates a file with a data time stamp that tells the other program when the last time new data was added to the database. The other program checks this file every so often and checks the time stamp, if they are different then it looks for the new data otherwise it sleeps and checks the file again later.

The file name is specific enough that only one program will be accessing it at a time and it's small enough that it shouldn't case a problem in terms of file size filling up the server. This isn't the ideal solution, but it works and it shouldn't cause too much of a problem.

Thanks for your help though. It did lead me in the right direction.

[JorgeM]

Glad to hear that you were able to resolve this.

[francis994]

Thought as much