Re: Send email via trigger

[Benjamin Howarth]

Hi guys,

I'm using Windows Server 2003 and Exchange Server 2007 Beta2 and using the triggers fine using SELECT INTO OUTFILE - have tested and works perfectly.
This is the general format I am using (note - extension should be .eml not .txt for your outgoing mail server to successfully pickup):

CREATE TRIGGER send_emailverifier AFTER INSERT, UPDATE ON tbl_users
FOR EACH ROW BEGIN
SELECT * FROM email_bodies WHERE EmailID = 1;
SELECT * FROM tbl_users WHERE ClientID = @ClientID
INSERT INTO tbl_emailverify VALUES (UUID, tbl_users.ClientID, OLD.CltEmail, NEW.CltEmail)
SELECT concat("To: ",NEW.CltEmail & "," & OLD.CltEmail),
"From: [e-mail]",
concat("Subject: ",NEW.subject),
"",
email_bodies.EmailContent
INTO OUTFILE "/inetpub/mailroot/pickup/mail.eml"
FIELDS TERMINATED by '\r\n';
END

If you're using a hosting company with MySQL then ask your system admin to give you write perms to the mail pickup folder (a lot of hosts WILL be dubious about this though as it opens up a potential security vulnerability for spammers to exploit).
What I'd now like to know is - how can you do substitution within the body section? I'm storing my HTML-formatted emails within a table in the same DB and I'd like to be able to create a bunch of triggers/stored procs, then add parameters to it to retrieve the relevant email content, and then substitute values from a table (such as email confirmation, and display name/username). Any ideas?
Many thanks in advance,

medicineworker