How i can send email when a record is inserted into table

Kashif Ali

8y4.4kSQL

How i can send email when a record is inserted into database table.

First/-

user schedule an sql event.

This (script) has created by me.

Second/-

How to notify user when sql event occurs ? Please tell me how to manage this ?

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Suraj Kumar8y ago
Accepted answer
You can either create a sql server trigger on that table for sending mail. Another way is to write code just after inserting records into the table for sending email to user. You can also create a sql server job for both the activity.

Below code format is used to send the mail using SMTP
public void Send_Leave_Mail(string mFrom, string mTo, String mSubject, string mMsg, string mcc)
        {
            string mMailServer = "";
            int mPort;
            mMailServer = ConfigurationManager.AppSettings.Get("MyMailServer");
            mPort = Convert.ToInt32(ConfigurationManager.AppSettings.Get("MyMailServerPort"));
            try
            {
                if (mFrom != "" && mTo != "")
                {
                    MailMessage message = new MailMessage(mFrom, mTo, mSubject, mMsg);
                    SmtpClient mySmtpClient = new SmtpClient(mMailServer, mPort);
                    mySmtpClient.UseDefaultCredentials = true;
                    message.Priority = MailPriority.High;

                    message.IsBodyHtml = true;
                    mySmtpClient.Send(message);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
To send mail using Sql Server you can use the format like as shown below
EXEC bindh2.msdb.dbo.sp_send_dbmail
                @profile_name = 'profileName',
                @recipients = @UserEmail,
                @body = @Body,
                @body_format = 'HTML',
                @subject = @Subject;
Below code create a trigger which will automatically call when record will be inserted on the table.
You can either send mail using sql server mail or using SMTP server like gmail.
CREATE TRIGGER dbo.EmployeesTrigger ON dbo.Employees
FOR INSERT
AS
BEGIN
    -- Write here your code for sending email to user.
END
+2
Sourav Mukherjee8y ago
Hi Kashif, You can use a trigger to notify the moment any record is inserted in the table.
0
Kashif Ali8y ago
I'm using xamp. (using only for testing scripts)
and i want to send email through my purchased hosting service.

I just want when a record inserted (through an event) an email should be sent to some receipts (automatically).
Please help me, I'm on final stage of my project.
I'm building an task management system.

Some tasks are recurrance so for that i have created event sections,
In my webpage, but as you know an event will occur by its scheduled date, a notification should be sent when an will event occur.
Please how i can fix this one ?
0
Suraj Kumar8y ago
Triggers are based on single table but you can write your query to affect i.e., update, delete multiple table as well as you can write your select statment joining from several tables in that trigger. Please check these links to know for sending mail using sql server.

https://www.c-sharpcorner.com/article/configure-database-mail-send-email-from-sql-server-database/

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql?view=sql-server-2017

https://www.c-sharpcorner.com/article/configure-database-mail-send-email-from-sql-server-database/
0
Kashif Ali8y ago
thank you, I go there
Triggers are applicable only for one table or it requires couple of table ?
0
Suraj Kumar8y ago
You can insert suggested code after the line
echo "Event has been created.";

But, if you want to sent mail from sql server trigger then you don't need to insert code here.

You can learn more about trigger in sql server here - https://www.c-sharpcorner.com/UploadFile/a53f1a/triggers-in-sql-server/
0
Kashif Ali8y ago
I have this code for an event , where to insert your suggested code

$servername = "localhost";
$username = "root";
$password = "";
$db = "admin";
// Create connection
$conn = new mysqli($servername,$username,$password,$db);
if(isset($_POST['submitform'])){
$title = $_POST['title'];
$name = $_POST['name'];
$department = $_POST['department'];
$task = $_POST['task'];
$date =$_POST['date'];
$assign = $_POST['assign'];
$query = "CREATE EVENT IF NOT EXISTS $title
ON SCHEDULE AT '$date'
DO
INSERT INTO task(name,department,task,assign,deadline,created_at) VALUES ('$name','$department','$task','$assign','$date',Now())
;";
mysqli_query($conn,$query);
echo "
Event has been created.";
header("Refresh: 5; url=test2.php");
mysqli_close($conn);
};
?>

Please tell me where to add your suggested code in my code?
0