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?
Subscribe to:
Post Comments (Atom)
5 comments:
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.
Hi,
is it possible to send emails from UDFs in MySQL? I have never seen something like this.
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.
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.
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.
Post a Comment