Wednesday, March 22, 2006

Triggering emails

Today I received an interesting request from one of the db4free.net users: is it possible to trigger emails on particular events? So for example, if a record that fulfills certain conditions is being inserted, somebody should automatically receive an email.

As far as I know, it's not possible in MySQL, but in other RDBMS. So wouldn't this be a nice feature for e.g. MySQL 5.2?

The only solution that comes to my mind is rather tricky and not 100 % accurate (since it doesn't send the email immediately after change in the database happened). Somebody could write a little application that verifies the condition and if it does, it sends the email - so this app could be defined as a cronjob to be run at a regular basis. Not very elegant, indeed.

Are there better solutions for this?

6 comments:

Richard@Home said...

This is part of the business logic/application workflow, not the database schema.

I would change the behaviour of the program that is populating the data to perform the check and send the email.

If you start adding this kind of cruft to MySQL 5.2 you will rapidly head into the same hell that is SQLServer ;-)

gilf said...

You could write a UDF which sends the email and then call this from the trigger.

Having no experience with UDF's I'm not sure how easy or practical this is but thats the route I would look into.

Using an extrnal program to constantly monitor the db seems to be to be very waste full.

Oracle has a utl_smtp package which allows you to send emails from any Oracle function/procedure, while I agree it's in the buisness logic area and not part of the db I don't think the two need to be mutually exclusive.

Markus Popp said...

Hi,

is it possible to send emails from UDFs in MySQL? I have never seen something like this.

Roland Bouman said...

I'm quite certain you can do this from an UDF.

At least...in theory.

Check out: http://cvs.tangent.org/listing.php?repname=myjava&path=%2F&sc=0 (running java from inside an UDF) or http://cvs.tangent.org/listing.php?repname=myperl&path=%2F&sc=0 (running perl scripts from an udf)

Well, if you can do this - why not email...

BTW, I disagree that emailing -categorically - should be done from the application. Compare with stored procedures. They too can encapsulate business logic and application workflow - they would be pretty useless if they couldnt.

Isotopp said...

UDFs are pretty simple. They are also pretty limited, as they currently get no charset information and have no access to mysqld internal functions.

UDFs execute in a threaded context. Sending a mail in C or C++ is pretty simple, the only difficulty here being that you have to be sure that you do it in a threadsafe way.

Writing anything that starts an external program ("/usr/lib/sendmail -t") and passes data to it is a big opportunity to invite security security problems - I suggest that you try to find other solitions.

Markus Popp said...

I guess, it depends on the situation whether it's appropriate to send an automated email from the database server. In most situations it might be better to do it from the client side (I actually never had a situation where I really needed to send an email from the database server), but I could imagine that there are situations where you need to react immediately on changes within the DB server - and that would be easier to do if MySQL supported sending mails directly.