Perform .NET Action on Database Change

Question

We have a process that needs to fire when a change occurs to a specific database table in Oracle. At the moment a similar process has been developed using triggers and a bunch of subsequent database actions that occur when that trigger is fired.

However, in this solution we want to call a .NET component (most likely a service) when the change occurs to a row or bunch of rows in a database table. Now, you could implement a polling mechanism that will check the table at regular intervals for those modifications and then instantiate the service when it finds any. However, I would prefer a more event driven approach.

I assume this is something that has been done elsewhere so I was wondering what approaches other people have used for dealing with such requirements?

Thanks in advance

Edit: The process that fires when a change occurs to the underlying data is essentially a call to an external web service using some of the related data. I am beginning to think whether this call should occur as part of the same process that is submitting the data into the database, rather than being triggered by the data change itself.

Answer 1

You should look at Oracle Database Extensions for .NET.

From the linked article:

Oracle Database Extensions for .NET provides the following:

• A Common Language Runtime (CLR) host for Oracle Database
• Data access through Oracle Data Provider for .NET classes
• Oracle Deployment Wizard for Visual Studio .NET

You would still use triggers to detect the db changes but instead of firing all the db-side logic you describe you would now be able to execute that logic from a .NET module.

Answer 2

If you are using Oracle's .NET driver, you can use Oracle Continuous Query Notification (CQN) to do that.

You just give it a normal SELECT query, and it will fire a callback in your app whenever the resultset for that query changes.

The one caveot I know of is that when it initially runs the query to subscribe for continuous notification, it momentarily requires an exclusive lock. Usually its not a big deal since you just evecute it once at startup, so any other DB queries on the same table will be blocked for a fraction of a second.