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

Question

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 ?

Suraj Kumar · 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

Sourav Mukherjee · Answer

Hi Kashif, You can use a trigger to notify the moment any record is inserted in the table.

Kashif Ali · Answer

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 ?

Suraj Kumar · Answer

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/

Kashif Ali · Answer

thank you, I go there Triggers are applicable only for one table or it requires couple of table ?

Suraj Kumar · Answer

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/

Kashif Ali · Answer

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?

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

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (7)