Database Notification

[Armitage]

I have a database (currently MySQL, FWIW) that receives data from a variety of sources. I would like to execute a variety of external processes if data meeting specific criteria are inserted or updated to the database.

Does anybody know of a mechanism to do this?

I've looked into triggers & stored procedures, but if I'm understanding things correctly, they only work within the database, and can't execute external programs.

I could poll the database at intervals, but the number of criteria to be polled on could become large, and some of the stuff is time critical so the polling interval would have to be short, leading to possibly stomping the database pretty hard.

I could embed the notification stuff in the many mechanisms that can insert/update to the db, but that promises to be a maintenance nightmare, and I may not always have direct control over some of these mechanisms.

What I'm considering is writing a chunk of "middleware" that sits between the db, and the applications inserting/updating the db. These apps would connect to the middleware instead of the DB. The middleware would parse the query to look for the triggers, and perform whatever needs to be done if they are met. It would then pass the query on to the db.

Reasonable? Stupid? Already exists?

 

[Gunslinger08]

Why not check in the application that's pushing the data into the database? Before it does the insert or update, check for these criteria, and perform said events from the program.

 

[Armitage]

Originally posted by: joshsquall
Why not check in the application that's pushing the data into the database? Before it does the insert or update, check for these criteria, and perform said events from the program.

Yep - I mentioned that above.
There will be a wide variety of both local and remote processes pushing data into the DB. Keeping all of them up to date with respect to these triggers promises to be a maintenance nightmare, not to mention that I don't neccesarily have control of them.

Also, some of processes I want to kick off have to run on the local machine/network, while the processes pushing the data may be on remote machines which can't execute jobs locally.

 

[EagleKeeper]

Can you setup a monitoring program that would look for a trigger action and then performed the relevant triggers that would be required?

 

[Armitage]

Originally posted by: EagleKeeper
Can you setup a monitoring program that would look for a trigger action and then performed the relevant triggers that would be required?

Uhm ... yea, that's what I want to do. The question is how to go about it. Maybe I'm not following you

 

[AntiEverything]

Don't know about MySQL, more advanced commercial databases (SQL Server, Oracle) can start external processes from within triggers.

What EagleKeeper is saying is write a custom program that watches for triggers. Write triggers that watch for certain inserts, and when that happens the trigger can insert something into a table whose only function is to notify an external program that periodically checks that table that something has happened. That program will then take the appropriate action.

 

[kamper]

Originally posted by: AntiEverything
What EagleKeeper is saying is write a custom program that watches for triggers. Write triggers that watch for certain inserts, and when that happens the trigger can insert something into a table whose only function is to notify an external program that periodically checks that table that something has happened. That program will then take the appropriate action.

That's pretty smooth. I vote for that regardless of what else comes up because it doesn't make you dependent on any specific mysql functionality.

 

[Armitage]

Originally posted by: kamper

Originally posted by: AntiEverything
What EagleKeeper is saying is write a custom program that watches for triggers. Write triggers that watch for certain inserts, and when that happens the trigger can insert something into a table whose only function is to notify an external program that periodically checks that table that something has happened. That program will then take the appropriate action.

That's pretty smooth. I vote for that regardless of what else comes up because it doesn't make you dependent on any specific mysql functionality.

edit - oops - replied to the wrong message!
I suppose this could work - it would certainly reduce the load to poll only a single, likely small table for this kind of stuff then to poll all of the huge tables that might be otherwise involved.

While it may not matter in practice, it still irks me in principle that an event might have to wait for the next poll, rather then be kicked off immediately

 

[Armitage]

Originally posted by: AntiEverything
Don't know about MySQL, more advanced commercial databases (SQL Server, Oracle) can start external processes from within triggers.

Unfortunately, MySQL doesn't have this functionality as far as I can tell. Suggestions on what databases do? This might end u-p being a big enough issue to warrant switching database engines.

What EagleKeeper is saying is write a custom program that watches for triggers. Write triggers that watch for certain inserts, and when that happens the trigger can insert something into a table whose only function is to notify an external program that periodically checks that table that something has happened. That program will then take the appropriate action.