Send email from MySQL trigger when a table updated

Question

Consider a table as table2, i like to add a trigger on this table update as

select Num into num from table1 where ID=new.id;
BEGIN
DECLARE done int default false;
DECLARE cur1 CURSOR FOR select EmailId from users where Num=num;
DECLARE continue handler for not found set done = true;
OPEN cur1;
my_loop: loop
    fetch cur1 into email_id;
    if done then
        leave my_loop;
    end if;
    //send mail to all email id.
end loop my_loop;
close cur1;
END;

Is there any simple method to write in the place commented? To send a email to all the email id retrieved from table users.

I am using MySQL in XAMPP(phpmyadmin).

Answer 1

I'm against that solution. Putting the load of sending e-mails in your Database Server could end very bad.

In my opinion, what you should do is create a table that queues the emails you would want to send and have a process that checks if there are emails to be sent and send them if positive.

Answer 2

As everyone said, making your database to deal with E-Mails is a bad idea. Making your database to write an external file is another bad idea as IO operations are time consuming compared to database operations.

There are several ways to deal with your issue, one that I like: Having a table to queue up your notifications/emails and a client program to dispatch the emails.

Before you continue, you should make sure you have an E-Mail account or service that you can use to send out emails. It could be SMTP or any other service.

If you are on .NET you can copy this, if not you get the idea how to rewrite in your platform

Create a table in your DB

CREATE TABLE `tbl_system_mail_pickup` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `mFrom` varchar(128) NOT NULL DEFAULT 'noreplyccms@YourDomain.co.uk',
  `mTo` varchar(128) NOT NULL,
  `mCC` varchar(128) DEFAULT NULL,
  `mBCC` varchar(128) DEFAULT NULL,
  `mSubject` varchar(254) DEFAULT NULL,
  `mBody` longtext,
  `added_by` varchar(36) DEFAULT NULL,
  `updated_by` varchar(36) DEFAULT NULL,
  `added_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `email_sent` bit(1) DEFAULT b'0',
  `send_tried` int(11) DEFAULT '0',
  `send_result` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

this table will hold all your notifications and emails. Your Table trigger or events which is trying to send an email will add a record to above table. Your client will then pickup the emails and dispatch at an interval you specify.

Here is a sample console app on .NET just to give you an idea how the client might look like. You can modify it according to your needs. ( I use Entity-framework as its much easier and simpler)

1. Create new Console Project in Visual Studio.
2. In package manager console, install following packages (install-package EntityFramework, install-package MySQL.Data.Entity)
3. Add a reference to System.Configuration
4. Now open "App.Config" file and add your connection-strings to your MySQL database.

5. In the same App.Config file, add your SMTP email settings. check your port and ssl option

     <system.net>
   <mailSettings>
     <smtp from="Noreplyccms@youractualEmailAccount.co.uk">
       <network host="smtp.office365.com(your host)" port="587" enableSsl="true" password="dingDong" userName="YourUsername@YourDomain" />
     </smtp>
   </mailSettings>
   

6. Create new Folder called Models in your project tree.

7. Add new item -> ADO.NET Entity Data Model -> EF Designer from database
8. Select your Connection-String -> tick the Save connection setting in App.Config - > Name your database.
9. Next - > Select only the above table from your database and complete the wizard.

Now you have a connection to your database and email configurations in place. Its time to read emails and dispatch them.

Here is a full program to understand the concept. You can modify this if you have win application, or web application an in cooperate this idea. This console application will check every minutes for new email records and dispatches them. You can extend above table and this script to be able to add attachments.

using System;
using System.Linq;
using System.Timers;
using System.Data;
using System.Net.Mail;
using ccms_email_sender_console.Models;

namespace ccms_email_sender_console
{
    class Program
    {

       static Timer aTimer = new Timer();
        static void Main(string[] args)
        {

            aTimer.Elapsed += new ElapsedEventHandler(OnTimedEvent);// add event to timer
            aTimer.Interval = 60000; // timer interval 
            aTimer.Enabled = true;

            Console.WriteLine("Press \'q\' to exit");
            while (Console.Read() != 'q');

        }

        private static void OnTimedEvent(object source, ElapsedEventArgs e)
        {
            var db_ccms_common = new DB_CCMS_COMMON(); // the database name you entered when creating ADO.NET Entity model
            Console.WriteLine(DateTime.Now+"> Checking server for emails:");
            aTimer.Stop(); /// stop the timer until we process current queue

            var query = from T1 in db_ccms_common.tbl_system_mail_pickup where T1.email_sent == false select T1; // select everything that hasn't been sent or marked as sent.

            try
            {
                foreach (var mRow in query)
                {
                    MailMessage mail = new MailMessage();
                    mail.To.Add(mRow.mTo.ToString());
                    if (mRow.mCC != null && !mRow.mCC.ToString().Equals(""))
                    {
                        mail.CC.Add(mRow.mCC.ToString());

                    }
                    if (mRow.mBCC != null && !mRow.mBCC.ToString().Equals(""))
                    {
                        mail.CC.Add(mRow.mBCC.ToString());

                    }
                    mail.From = new MailAddress(mRow.mFrom.ToString());
                    mail.Subject = mRow.mSubject.ToString();
                    mail.Body = mRow.mBody.ToString();
                    mail.IsBodyHtml = true;

                    SmtpClient smtp = new SmtpClient();

                    smtp.Send(mail);

                    mRow.email_sent = true;
                    Console.WriteLine(DateTime.Now + "> email sent to: " + mRow.mTo.ToString());
                }

                db_ccms_common.SaveChanges(); // mark all sent emails as sent. or you can also delete the record.

                aTimer.Start(); // start the timer for next batch
            }
            catch (Exception ex)
            {

                Console.WriteLine(DateTime.Now + "> Error:" + ex.Message.ToString());
                // you can restart the timer here if you want

            }

        }

    }
}

to Test: Run above code and insert a record into your tbl_system_mail_pickup table. In about 1 minute you will receive an email to the sender you've specified.

Hope this helps someone.

Answer 3

You asked, "is there a simple method to ... send a email" from a trigger?

The answer is no, not in standard MySQL.

This is usually done by creating some kind of pending_message table and writing a client program to poll that table regularly and send the messages.

Or, you could consider using a user-defined MySQL function. But most production apps don't much like building network functions like email sending into their databases, because performance can be unpredictable.