Introduction: Hello Guys, “Welcome back” Today, i am here with another one new great article. In this article I explained the implementation about how to send notification or email to the Database Administrator (DBA) when user will be inserted a record in the Table with SQL Server database.
Join our Channel on Whtsapp and Telagram for All Updates
Implementation: Through this article i will also explain that how to send email using Trigger using sp_send_dbmail Store Procedure in SQL Server which will send the notification or email to the Database Administrator (DBA) when user will be inserted the record in Table of the database.
This article will capture the following SQL Server versions i.e. 2008, 2008R2, 2012, 2014, 2016, 2017, 2019 and 2022.
Database
I created a table user_details with the mentioned columns as follows.
column name data Type
user_id int
user_name varchar(100)
user_email varchar(50)
user_address varchar(250)
I have already inserted few records in the table.
user_id user_name user_email user_address
1 Sanjeev sanjeev@gmail.com #250 Sector 127, Chandigarh
2 Sanjay sanjay@gmail.com #123 Sector 255, Chandigarh
3 Ruhika ruhika@gmail.com #256 Sector 855, Chandigarh
4 Rajan rajan@gmail.com #55 Sector 00, Chandigarh
Note: You can use the following database script to create the table by executing in the SQL Server.
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[user_details](
[user_id] [int] IDENTITY(1,1) NOT NULL,
[user_name] [varchar](100) NOT NULL,
[user_email] [varchar](50) NOT NULL,
[user_address] [varchar](250) NOT NULL,
CONSTRAINT [PK_userdetails] PRIMARY KEY CLUSTERED
(
[user_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT INTO Customers
SELECT 'Sanjeev', 'sanjee@gmail.com', ‘#250 Sector 127, Chandigarh’
UNION ALL
SELECT 'Sanjay', 'sanjay@gmail.com', ‘#123 Sector 255, Chandigarh’
UNION ALL
SELECT 'Ruhika', 'ruhika@gmail.com', ‘#256 Sector 855, Chandigarh’
UNION ALL
SELECT 'Rajan', 'rajan@gmail.com', ‘#55 Sector 00, Chandigarh’
Configuring SQL Server for sending emails
To send emails using the SQL Server, you need to configure the SQL Server. The explanation of configuration of the SQL Server, please check my article.
Sending Email in SQL Server using Store Procedure
For sending emails using the SQL Server Store Procedure check my article.
Sending Email from Trigger in SQL Server
When new record will be inserted in the table named user_details, the mentioned Trigger will be executed automatically.
The user_id of the newly inserted record will be available in the user_details table.
The mentioned Trigger will be fetched the user_id of the inserted record and it will send email with user_id using sp_send_dbmail Store Procedure automatically.
CREATE TRIGGER [dbo].[User_INSERT_Notification]
ON [dbo].[User]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @user_id INT
SELECT @user_id = INSERTED.CustomerId
FROM INSERTED
declare @body varchar(500) = 'User ID: ' + CAST(@user_id AS VARCHAR(5)) + ' inserted.'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SanjeevKumar_Email_Profile'
,@recipients = 'recipient@gmail.com'
,@subject = 'New User Record'
,@body = @body
,@importance ='HIGH'
END
Conclusion: In above code, I have been explained implementation about how to send notification or email to the Database Administrator (DBA) when user will be inserted a record in the Table with SQL Server database. This code is very helpful for every developer. Bye bye and take care of yourself Developers. We will come back shortly with the new article.
Regards
Using Asp.net
No comments:
Post a Comment