Re: Send email via trigger

[Adrian Hondema]

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: [e-mail]",
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 ;