Perform .NET Action on Database Change

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 approach开发者_开发技巧es 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.

---

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.

---

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.

---

It sounds possible but will likely take some leg work. I think you want to look into the Oracle Access Manager http://download.oracle.com/docs/cd/E12530_01/oam.1014/e10355/toc.htm

---

This is similar to Paul's; but does not assume that you have Oracle installed on a Windows machine.

You may use dbms_scheduler to create a job that will call your external process.

You may directly call an remote external job from Oracle (this requires Oracle Scheduler Agent to be installed but nothing else)

it requires a bit of leg work to get the authentication set up and such, but this works.

then you utilize an event to start your job(called from your trigger).

This way, you may actually be able to utilize a lot of what you already have coded and just have the oracle scheduler to handle the rest.

---

Oracle provides two mechanisms to deal with what describe in a rather nice way... on the DB side you implement triggers for detecting the changes or whatever should result in an action on the .NET side...

For the communication you use a publish/subsribe mechanism based on the Oracle built-in queueing technology (called Advanced Queueing Technology, AQ) - for a nice example see http://www.devart.com/dotconnect/oracle/docs/AQ.html (not affiliated, just a happy customer).

Another option is to use built-in DBMS_ALERTER package for communication which is transactional and asynchronous - see for an example http://www.devart.com/dotconnect/oracle/docs/Devart.Data.Oracle~Devart.Data.Oracle.OracleAlerter.html

Just to be clear:
the above technologies (DBMS_ALERTER and AQ) are Oracle built-in ones, not specific to any 3rd-party libraries... you just need an ADO.NET provider supporting them...

EDIT - after the EDIT from the OP:

If you have control over the code or the call to the code that triggers the data change (WebService?) then it is indeed the best way to deal with it is purely on the .NET side of things... this also helps to deal with situations where such a change runs into an error etc.