Send email when certain Data is being inserted in MS SQL Server

Question

I realize that there is an integrated email notification system in SQLServer, where I can configure it to send an email using my organization's Exchange server.

I want to use that to send email on the event that certain data is added, for example if I have a table with a column called 'error no' in it; I want to be notified via email when a certain error no. is being added.

e.g. if error no is 5, I want to be sent an email via the notification system. If the error no is 4, then insert it to table with no notification.

I would love to be able to do it without writing any stored procedure or T-SQL, but if I have to, then I have no problem doing it.

Thank you guys.

• Robert Miller: Along with reporting, do you want the insert to continue or be interrupted with an error?
• Orca: I want it to continue.

Answer 1

The quickest solution I can think of would be to create on your specific table an after insert trigger where, based on your conditions (the error no..etc), you'd call the system procedure msdb.dbo.sp_send_dbmail to send a custom email.

You'd have first to configure the mail (create a profile and test it). I don't really think there's a simpler way.

I think that you could create an alert based on a custom error number. But this means that you'll have to modify your current code that inserts data in that table to throw this custom error no, to be able to see the alert.

• StanleyJohns: I agree with using the trigger. This also satisfies not having to create any stored procedures.
• Orca: Thanks, that's what I did. Do you have any idea how to automate the Database Mail setup procedure.?
• Marian: Please follow the article SQL Server Database Mail and SQL Agent Mail setup by using a script and you'll see the steps for automating the db mail.