Skip navigation links

MySQL Forums :: Triggers :: Send email via trigger


Advanced Search

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
Send email via trigger 42626 Chris Huber 07/09/2005 09:24AM
Re: Send email via trigger 24942 Jay Pipes 07/09/2005 04:00PM
Re: Send email via trigger 18024 Chris Huber 07/10/2005 07:18AM
Re: Send email via trigger 29684 Dave Revor 10/19/2005 08:53AM
Re: Send email via trigger 22037 Andrew Gilfrin 10/20/2005 09:40AM
Re: Send email via trigger 20253 Konstantin Osipov 10/23/2005 05:16AM
Re: Send email via trigger 14028 Mathan Kumar Kanagaraj 06/28/2006 11:53PM
Re: Send email via trigger 12807 Ravi Gujjarlapudi 02/13/2007 06:35AM
Re: Send email via trigger 12366 Philip Bannon 05/13/2007 10:39AM
Re: Send email via trigger 9123 kamal sekar 11/24/2008 11:10PM
Re: Send email via trigger 12074 Chintaoui Chintaoui 02/22/2007 03:44AM
Re: Send email via trigger 10512 Zigmund Bulinsh 12/08/2007 08:17AM
Re: Send email via trigger 8187 satyendra saini 09/09/2008 04:00AM
Re: Send email via trigger 6911 Eduard Filipas 03/12/2009 07:08AM
Re: Send email via trigger 16650 Chintaoui Chintaoui 02/27/2007 07:53AM
Re: Send email via trigger 7906 satyendra saini 09/09/2008 04:02AM
Re: Send email via trigger 10614 ifti khirul 05/14/2007 01:07AM
Re: Send email via trigger 9757 Jonas Reinhardt 06/04/2007 01:14PM
Re: Send email via trigger 9125 ifti khirul 06/27/2007 10:10PM
Re: Send email via trigger 8591 Karthikeyan karthi 08/09/2007 06:15AM
Re: Send email via trigger 9066 por por 08/25/2007 09:13AM
Re: Send email via trigger 10937 Matt H 08/30/2007 01:24AM
Re: Send email via trigger 13751 Jonas Reinhardt 08/30/2007 11:43PM
Re: Send email via trigger 15112 Benjamin Howarth 09/12/2007 05:39AM
Re: Send email via trigger 9653 Zigmund Bulinsh 12/08/2007 03:25AM
Re: Send email via trigger 7430 Zigmund Bulinsh 12/08/2007 05:19AM
Re: Send email via trigger 6169 shyamsundar Ayyanan 08/04/2008 01:15AM
Re: Send email via trigger 5484 Umar Md 01/12/2009 05:10AM
Re: Send email via trigger 5614 shafikhan mohammed 01/07/2009 04:37AM
Re: Send email via trigger 7726 Adrian Hondema 03/07/2009 05:11AM
Re: Send email via trigger 5229 Anthony Whitley 03/12/2009 06:21AM
Re: Send email via trigger 6271 Anthony Whitley 03/12/2009 06:38AM
Re: Send email via trigger 10653 Howard Brinsmead 04/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.