Can I execute any program outside mysql through mysql triggers?

views:

178

answers:

+2 Q:

Can I execute any program outside mysql through mysql triggers?

I’m building email notification service, requirement is to send an email to user when he is registered. Currently what I’m thinking of execution of some function defined in trigger, can be a java function, which have to be outside the mysql process, rather monitoring mysql from outside for row addition in database.

Is there any functionality available in mysql, so that I can execute my java function from database triggers?

You could via user defined functions, but it wouldn't be good practice IHMO, since your DB stores the data, while your application does (most of) the business logic. Adding email notifications to the business tier shouldn't be complicated either.

The MYYN 2009-12-22 10:56:56

+3 A:

If you're using Java for the front end to a database, why not do it in the Java middle tier code instead of the trigger? Databases are for persistence. If you decide to use another form of notification later on you'll find it's easier to add it to Java rather than MySQL.

duffymo 2009-12-22 10:58:02

My concern is time gap. It's really crucial for my app to send notification as early as possible. I can make a Java daemon also which can pole my database. Would it be fine if I keep polling to database without any sleep. Was just scared if my polling grades down my database performance.Any comments....

sumitarora 2009-12-22 13:50:08

No polling needed. Have the service that persists the record send the e-mail as part of the transaction. It's an event-driven activity that way - MUCH more efficient and elegant that polling. And this way you're sure that it only goes out when the transaction is successful.

duffymo 2009-12-22 17:43:55

By "service", I mean "Java POJO that implements an interface", not necessarily a web service.

duffymo 2009-12-22 17:44:41

I wish if I could afford this approach. I know this is good approach, but here my problem is, my user can create 100 accounts in one transaction, so if I try to send email to those 100 users in single tx, it will take a lot of time. I really don't want that. So I have only way i.e. database polling if I cannot "execute any program outside mysql through mysql triggers".

sumitarora 2009-12-23 06:49:45

Polling won't be faster. If you're worried about the time it'll take to send the e-mails, put the requests on a persistent JMS queue in "fire and forget mode" and simply return. Your app can go on its merry way and the e-mails can take as long as they need to. The queue will give you the added benefit of hanging onto send requests in the event that your e-mail server goes down temporarily.

duffymo 2009-12-23 10:33:22

ansaurus

tags:

views:

answers:

Can I execute any program outside mysql through mysql triggers?

related questions