#9
Email System

Email System

In the Daphne database, there is a simple table called EMailQueue which contains the message subject, recipients, and message text fields.

When an insert is made into this table, the system immediately sends an email using SQL Server's DB Mail service.

As we will see in the triggered email notices section, it may not always be effective to send an email immediately.

For example, if a change has been made in the Corrective action table, we want to notify the relevant users.

In 1-2 minutes the user who made the change can make many updates to the table.

We don't want to send duplicate messages for each update.

Another reason is that a possible problem in the e-mail system prevents posting records in the forms.

We use another table called EmailSpooler to prevent these problems. In this table, nothing about the email message is kept.

The SQL table name that generates the message, the record ID of the SQL table, whether it is Insert or Update, and which email form template to use are stored.

Trg_Tablename_Email named SQL triggers insert to EmailSpooler table.

We will explain how triggers work in the next tutorial.

The records in the EmailSpooler table need to be converted into readable email messages.

The SQL procedure sp_PREPARE_MAIL_NOTICE converts the records in the EmailSpooler into readable messages and insert them to the EmailQueue Table.

More details are unnecessary for now. We recommend that you listen here again after the next training.