How to Send Email Notifications from MySQL 5.1

When a new row is inserted into a table, you aim to send emails to multiple recipients dynamically determined through a select statement. Additionally, you encounter limitations with Navicat's predetermined email notification system.

Using SMTP and OUTFILE

If an SMTP service is running, you can use the OUTFILE keyword to write the email body to a drop directory. However, this approach may result in duplicate file names if you have high volume.

UDF Solution

Another option is to create a User Defined Function (UDF). Here's a sample trigger solution:

CREATE TRIGGER test.autosendfromdrop BEFORE INSERT ON test.emaildrop
FOR EACH ROW BEGIN
  /* START THE WRITING OF THE EMAIL FILE HERE*/      
  SELECT
    concat("To: ", NEW.To),
    concat("From: ", NEW.From),
    concat("Subject: ", NEW.Subject),
    NEW.Body
  INTO OUTFILE
    "C:\\inetpub\\mailroot\\pickup\\mail.txt"
  FIELDS TERMINATED BY '\r\n' ESCAPED BY '';
END;

Marking Up the Email Body

To correctly format the email body for HTML content, use a function like this:

CREATE FUNCTION `HTMLBody`(Msg varchar(8192))
RETURNS varchar(17408) CHARSET latin1 DETERMINISTIC
BEGIN
  declare tmpMsg varchar(17408);
  set tmpMsg = cast(concat(
    'Date: ', date_format(NOW(), '%e %b %Y %H:%i:%S -0600'), '\r\n',
    'MIME-Version: 1.0', '\r\n',
    'Content-Type: multipart/alternative;', '\r\n',
    ' boundary=\"----=_NextPart_000_0000_01CA4B3F.8C263EE0\"', '\r\n',
    'Content-Class: urn:content-classes:message', '\r\n',
    'Importance: normal', '\r\n',
    'Priority: normal', '\r\n', '', '\r\n', '', '\r\n',
    'This is a multi-part message in MIME format.', '\r\n', '', '\r\n',
    '------=_NextPart_000_0000_01CA4B3F.8C263EE0', '\r\n',
    'Content-Type: text/plain;', '\r\n',
    '  charset=\"iso-8859-1\"', '\r\n',
    'Content-Transfer-Encoding: 7bit', '\r\n', '', '\r\n', '', '\r\n',
    Msg,
    '\r\n', '', '\r\n', '', '\r\n',
    '------=_NextPart_000_0000_01CA4B3F.8C263EE0', '\r\n',
    'Content-Type: text/html', '\r\n',
    'Content-Transfer-Encoding: 7bit', '\r\n', '', '\r\n',
    Msg,
    '\r\n', '------=_NextPart_000_0000_01CA4B3F.8C263EE0--'
  ) as char);
  RETURN tmpMsg;
END;