Alert mail sps when table got inserted in sql server

To send an alert to a specific email address (e.g. "[email protected]") whenever a table is inserted in a SQL Server database, you can use a combination of SQL Server features:

SQL Server Agent: This is a built-in feature in SQL Server that allows you to schedule jobs, including sending emails.
SQL Server Triggers: These are database objects that automatically execute a set of statements when a specific event occurs, such as an insert operation.
SMTP: This is a protocol for sending emails.

Here's a step-by-step guide to achieve this:

Step 1: Create a SQL Server Agent Job

Open SQL Server Management Studio (SSMS) and connect to your SQL Server instance.
In the Object Explorer, expand the "SQL Server" node, then right-click on "SQL Server Agent" and select "New Job".
In the "New Job" dialog box, enter a job name (e.g. "Insert Alert Job") and a description.
Click "OK" to create the job.

Step 2: Create a SQL Server Trigger

In the Object Explorer, expand the "Databases" node, then right-click on the database that contains the table you want to monitor (e.g. "MyDatabase").
Select "New" > "Trigger" > "Table Trigger".
In the "New Trigger" dialog box, enter a trigger name (e.g. "InsertTrigger") and a description.
Select the table you want to monitor and the type of trigger you want to create (e.g. "Insert").

In the "Trigger" tab, enter the following code:

CREATE TRIGGER InsertTrigger
ON MyTable
AFTER INSERT
AS
BEGIN
 DECLARE @email nvarchar(255) = '[email protected]';
 DECLARE @subject nvarchar(255) = 'Insert Alert';
 DECLARE @body nvarchar(max) = 'A new record has been inserted into MyTable.';

 EXEC msdb.dbo.sp_send_dbmail
     @profile_name = 'Your Email Profile',
     @recipients = @email,
     @subject = @subject,
     @body = @body;
END;

This trigger will send an email to the specified email address whenever a new record is inserted into the "MyTable" table.

Step 3: Configure the SQL Server Agent Job

In the Object Explorer, expand the "SQL Server" node, then right-click on "SQL Server Agent" and select "Properties".
In the "SQL Server Agent" dialog box, click on the "Jobs" tab.
Select the job you created earlier (e.g. "Insert Alert Job") and click "Edit".
In the "Job Properties" dialog box, click on the "Steps" tab.
Click "New" to create a new step.
In the "New Job Step" dialog box, enter a step name (e.g. "Send Email") and a description.

Select "T-SQL" as the step type and enter the following code:

EXEC msdb.dbo.sp_send_dbmail
 @profile_name = 'Your Email Profile',
 @recipients = '[email protected]',
 @subject = 'Insert Alert',
 @body = 'A new record has been inserted into MyTable.';

This step will execute the trigger code and send the email.

Step 4: Configure the Email Profile

In the Object Explorer, expand the "SQL Server" node, then right-click on "SQL Server Agent" and select "Properties".
In the "SQL Server Agent" dialog box, click on the "Database Mail" tab.
Click "New" to create a new email profile.
In the "New Database Mail Profile" dialog box, enter a profile name (e.g. "Your Email Profile") and a description.
Enter the email server settings (e.g. SMTP server, username, password).

Step 5: Save and Schedule the Job

Click "OK" to save the job and step.
In the "SQL Server Agent" dialog box, click on the "Schedules" tab.
Click "New" to create a new schedule.
In the "New Job Schedule" dialog box, enter a schedule name (e.g. "Insert Alert Schedule") and a description.
Select the job you created earlier (e.g. "Insert Alert Job") and the frequency you want the job to run (e.g. "Once").
Click "OK" to save the schedule.

Now, whenever a new record is inserted into the "MyTable" table, the trigger will execute and send an email to the specified email address using the SQL Server Agent job.

Mail Knowledge

Alert mail sps when table got inserted in sql server