0

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
k Koduru
  • 19
  • 4
  • Are you asking how to create a trigger? Or send `dbmail`? Or both? – Jacob H Sep 12 '19 at 12:53
  • 4
    Possible duplicate of [Send e-mail from a trigger](https://stackoverflow.com/questions/10755469/send-e-mail-from-a-trigger) – Filburt Sep 12 '19 at 12:54
  • I want a create a trigger on the table when new record added into the table and send an email saying that "new record is added into the table" – k Koduru Sep 12 '19 at 12:59

1 Answers1

1

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

Rey
  • 186
  • 5