In this article I will explain with an example, how to trigger
Email notification from
SQL Server.
Database
I have made use of the following table Customers with the schema as follows.
![Trigger Email notification from SQL Server]()
I have already inserted few records in the table.
![Trigger Email notification from SQL Server]()
Note: You can download the database table SQL by clicking the download link below.
Configuring SQL Server for sending emails
In order to send emails from
SQL Server, the
SQL Server needs to be configured. The details of configuration are covered in the following article.
Sending Email from Trigger in SQL Server
When a new row or record is inserted in the Customers table, the following Trigger will be executed.
The CustomerId of the newly inserted record is available in the INSERTED table.
The following
Trigger fetched the
CustomerId of the inserted record is sent through an email using
sp_send_dbmail Stored Procedure.
CREATE TRIGGER [dbo].[Customer_INSERT_Notification]
ON [dbo].[Customers]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CustomerId INT
SELECT @CustomerId = INSERTED.CustomerId
FROM INSERTED
DECLARE @body VARCHAR(500) = 'Customer with ID: ' + CAST(@CustomerId AS VARCHAR (5)) + ' inserted.'
-- For creating account and profile in SQL Server, refer http://aspsnip.pet/4782
-- For unblocking sp_send_dbmail, refer http://aspsnip.pet/4785
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Mudassar_Email_Profile'
,@recipients = 'recipient@gmail.com'
,@subject = 'New Customer Record'
,@body = @body
,@importance = 'HIGH'
END
GO
-- INSERT INTO Customers Table
INSERT INTO [dbo].[Customers]
([Name]
,[Country])
VALUES
('Mudassar Khan'
,'India')
GO
Screenshot
![Trigger Email notification from SQL Server]()
Received Email
![Trigger Email notification from SQL Server]()
Downloads
