CODEWITHSUNDEEP
sqlsql-server
k Koduru
k Koduru

Reputation: 19

How to create a trigger to send email when a new record is added in to the table

When I run an interface with name 'personimport' it will generate a new record in the table (run history).

There are 5 columns in the table:

interface-id, interface name, date, personid, error msg

Inserting "1, qwerty, 2019-09-11, a1" is successful, but inserting "2, person import, 2019-09-12, a2" throws an error .

Whenever a new record is added into this table with the name 'person import' I want to send an email to set of employees.

How to write a trigger for this in SQL Server?

Upvotes: 0

Views: 2844

Answers (1)

Rey
Rey

Reputation: 186

You need to have database mail configured: https://learn.microsoft.com/en-us/sql/relational-databases/database-mail/configure-database-mail?view=sql-server-2017

You need to create a trigger:

  1. From SSMS goto the table and expand
  2. Right click triggers, and "New Trigger"

  3. Name your trigger, on table name, and after insert

    CREATE TRIGGER SendTheEmails
   ON  runhistory
   AFTER insert
AS 
BEGIN
    -- Insert statements for trigger here
END
GO

    https://learn.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-2017

    Then for the SQL to email:

    Declare @message varchar(max)
Declare @subjectline varchar(60)
set @subjectline = 'Your Subject'
set @message = 'Your Message body'

EXEC msdb.dbo.sp_send_dbmail    @profile_name   =   'dbmailProfileName'
,                               @recipients     =   'youremails@yourdomain'
,                               @subject        =   @subjectline
,                               @body_format    =   'html'
,                               @body           =   @message

Obviously you can declare other variables, and use the insert table for further information.

Yeah, and as Fillburt said, this seems like a duplicate of Send e-mail from a trigger

Upvotes: 1

Related Questions