Skip navigation links

MySQL Forums :: Triggers :: Send email via trigger


Advanced Search

Re: Send email via trigger
Posted by: Adrian Hondema ()
Date: March 07, 2009 05:11AM

I have to say: your solution works like a treat!. I only added some error-handling as I discovered that at some times the mail.eml file still exists in de pickup directory. So, I added this line: DECLARE EXIT HANDLER FOR 1083 SET FileExists = 1; I make sure that the status of the E-mail in my database table is not changed to 'transmitted' where the error-condition occurs. As a result, the E-mail will be adressed again during the next event. Thanks for getting me in the right direction! My Event looks like this:

DELIMITER $$

create event ev_zend_email on schedule every 10 second
do
begin
DECLARE FileExists INT DEFAULT 0;

-- Email
DECLARE Volgnr BIGINT;
DECLARE Dossiernr BIGINT;
DECLARE Aan CHAR(150);
DECLARE Cc CHAR(150);
DECLARE Bcc CHAR(150);
DECLARE Onderwerp CHAR(150);
DECLARE Body TEXT;

DECLARE cur1 CURSOR FOR SELECT email_volgnr,
email_to,
email_cc,
email_bcc,
email_titel,
email_tekst
FROM dossier_emails
WHERE email_verzonden_op is null limit 1;

OPEN cur1;

FETCH cur1 INTO Dossiernr, Volgnr, Aan, Cc, Bcc, Onderwerp, Body;

if aan <> "" then
BEGIN
DECLARE EXIT HANDLER FOR 1083 SET FileExists = 1;

SELECT concat("To: ", Aan),
"From: emailserver@mysqlevent.nl",
concat("Subject: ", Onderwerp),
"",
"Er wordt een aktie van u verwacht. De specificatie van deze aktie treft u hieronder aan:",
"",
concat("Gewenste aktie: ", body),
"",
"Disclaimer: The contents of this email and any attachments are confidential and exclusively intended for the named recipient(s). If you are not the intended recipient, you are requested not to use the contents and to notify the sender immediately. In that case, you are also requested to destroy this email and any attachments. Although the information has been compiled with great care, ProcesVolgSysteem does not guarantee the proper and complete transmission of the information contained in this email nor for any delay in its receipt."
INTO OUTFILE "/inetpub/mailroot/pickup/mail.eml"
FIELDS TERMINATED by '\r\n';

UPDATE dossier_emails set email_verzonden_op = current_timestamp where email_volgnr = Volgnr;
END;

end if;

CLOSE cur1;

END $$

DELIMITER ;

Options: ReplyQuote


Subject Views Written By Posted
Send email via trigger 43561 Chris Huber 07/09/2005 09:24AM
Re: Send email via trigger 25335 Jay Pipes 07/09/2005 04:00PM
Re: Send email via trigger 18209 Chris Huber 07/10/2005 07:18AM
Re: Send email via trigger 29864 Dave Revor 10/19/2005 08:53AM
Re: Send email via trigger 22223 Andrew Gilfrin 10/20/2005 09:40AM
Re: Send email via trigger 20404 Konstantin Osipov 10/23/2005 05:16AM
Re: Send email via trigger 14137 Mathan Kumar Kanagaraj 06/28/2006 11:53PM
Re: Send email via trigger 12918 Ravi Gujjarlapudi 02/13/2007 06:35AM
Re: Send email via trigger 12456 Philip Bannon 05/13/2007 10:39AM
Re: Send email via trigger 9209 kamal sekar 11/24/2008 11:10PM
Re: Send email via trigger 12174 Chintaoui Chintaoui 02/22/2007 03:44AM
Re: Send email via trigger 10583 Zigmund Bulinsh 12/08/2007 08:17AM
Re: Send email via trigger 8258 satyendra saini 09/09/2008 04:00AM
Re: Send email via trigger 6983 Eduard Filipas 03/12/2009 07:08AM
Re: Send email via trigger 16747 Chintaoui Chintaoui 02/27/2007 07:53AM
Re: Send email via trigger 7971 satyendra saini 09/09/2008 04:02AM
Re: Send email via trigger 10677 ifti khirul 05/14/2007 01:07AM
Re: Send email via trigger 9821 Jonas Reinhardt 06/04/2007 01:14PM
Re: Send email via trigger 9193 ifti khirul 06/27/2007 10:10PM
Re: Send email via trigger 8645 Karthikeyan karthi 08/09/2007 06:15AM
Re: Send email via trigger 9147 por por 08/25/2007 09:13AM
Re: Send email via trigger 10999 Matt H 08/30/2007 01:24AM
Re: Send email via trigger 13863 Jonas Reinhardt 08/30/2007 11:43PM
Re: Send email via trigger 15467 Benjamin Howarth 09/12/2007 05:39AM
Re: Send email via trigger 9717 Zigmund Bulinsh 12/08/2007 03:25AM
Re: Send email via trigger 7463 Zigmund Bulinsh 12/08/2007 05:19AM
Re: Send email via trigger 6215 shyamsundar Ayyanan 08/04/2008 01:15AM
Re: Send email via trigger 5528 Umar Md 01/12/2009 05:10AM
Re: Send email via trigger 5657 shafikhan mohammed 01/07/2009 04:37AM
Re: Send email via trigger 7837 Adrian Hondema 03/07/2009 05:11AM
Re: Send email via trigger 5272 Anthony Whitley 03/12/2009 06:21AM
Re: Send email via trigger 6339 Anthony Whitley 03/12/2009 06:38AM
Re: Send email via trigger 10817 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.