MySQL Forums
Forum List  »  Triggers

Re: Send email via trigger
Posted by: Benjamin Howarth
Date: September 12, 2007 05:39AM

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: triggers@yourmysqlserver.whatever",
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

Options: ReplyQuote


Subject
Views
Written By
Posted
69681
July 09, 2005 09:24AM
35517
July 09, 2005 04:00PM
23085
July 10, 2005 07:18AM
34808
October 19, 2005 08:53AM
27958
October 20, 2005 09:40AM
24354
October 23, 2005 05:16AM
15511
February 13, 2007 06:35AM
15121
May 13, 2007 10:39AM
11550
November 24, 2008 11:10PM
14409
February 22, 2007 03:44AM
12710
December 08, 2007 08:17AM
10233
September 09, 2008 04:00AM
8924
March 12, 2009 07:08AM
18973
February 27, 2007 07:53AM
9726
September 09, 2008 04:02AM
12690
May 14, 2007 01:07AM
11895
June 04, 2007 01:14PM
10989
June 27, 2007 10:10PM
10334
August 09, 2007 06:15AM
12702
August 25, 2007 09:13AM
13130
August 30, 2007 01:24AM
16498
August 30, 2007 11:43PM
Re: Send email via trigger
31797
September 12, 2007 05:39AM
11538
December 08, 2007 03:25AM
8924
December 08, 2007 05:19AM
7691
August 04, 2008 01:15AM
7231
January 12, 2009 05:10AM
7196
January 07, 2009 04:37AM
10489
March 07, 2009 05:11AM
6923
March 12, 2009 06:21AM
8391
March 12, 2009 06:38AM
15829
April 16, 2009 05:53PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.