One solution that will work in any database that supports triggers would be to setup a mail-queue table. Then make a trigger on inserts for this table who’s job it would be to take the data and send an e-mail. The table can be as simple as
create table mail_queue { to varchar(255), subject varchar(255), body LONGTEXT, headers TEXT }
The thing about that idea is that really using database triggers to send email is arguably worse than sending the email from the web page itself. In most (all?) database systems any triggers that fire as a result of your insert will still be in the same statement context as the insert itself (yeah [http://en.wikipedia.org/wiki/Atomicity atomicity]!), so you won’t get a return from the database until the trigger is done, meaning you still have to wait for the email to be sent. I say worse because it’s likely harder to write a good trigger function that will communicate with the mail server and be able to handle things like having the 3rd email out of 5 cause an error, or perhaps a temorary failure that requires a retry, without losing the whole thing. I’ll grant that using something like [http://people.planetpostgresql.org/xzilla/index.php?/archives/263-Slides-from-my-plphp-talk.html pl/php] could make that task a bit easier, but the table queue/notify method still beats it out hands down.
Practical Example of Using Notify
After talking it over with Wez, it turns out I am not turing complete enough to post a comment in his blog, so I thought I would just make a comment on my own instead. [[image /xzilla/templates/default/img/emoticons/smile.png alt=”:-)” style=”display: inline; vertical-align: bottom;” class=”emoticon” /]] It all revolves around a post he made about a week back showing [http://netevil.org/node.php?nid=925&SC=1 how to take advantage of PostgreSQL’s NOTIFY mechanism] to help with workflow processing. The example mentions the idea of avoiding sending emails from web pages and rather offloading that work for a background process to work on so as to not let your email sending interfere with your web serving. All good stuff, and notify makes it better.
One of the main reasons to point it out here is that, as I have discussed with [http://linuxforum.dk/2006/program/saturday/MagnusHagander.shtml Magnus] a couple of times, there really aren’t a lot of good examples showing the usefulness of NOTIFY; but I also wanted to follow up on one of the alternative suggestions made in the article comments. Basically it offers up the idea that you can use triggers to implement a similar solution to the NOTIFY mechanism: