Trigger in SQL to send email

Question

Trigger in SQL to send email

Navigation

#1 by (1 votes)

1

I need to create a trigger in an SQL table to send an email if the inserted record meets certain conditions.

That is, I create the trigger in Tabla1 to send an email to X if in the inserted register the IdCircuito= 53 , the IdTipoDoc = 45 and the Gestor = 'Gest1' . Also, in the body of the email message I want to show the value of a certain field of that inserted record.

I have done something like that but the trigger is always executed regardless of the inserted record:

CREATE TRIGGER dbo.SendEmail   
   ON  dbo.TitulosDoc 
   AFTER INSERT
AS 

BEGIN        
SET NOCOUNT ON;
IF EXISTS (SELECT 1 FROM TitulosDoc WHERE IdCircuito = 53 AND IdTipoDoc = 45 AND Gestor = 'Gest1')
BEGIN
    EXEC msdb.dbo.sp_send_dbmail
      @recipients = '[email protected]', 
      @subject = 'Requerimiento generado', 
      @body = 'Se ha generado un nuevo requerimiento: ';
END
END
GO

In the body is where I want the text to be displayed with the value of the inserted record field:

@body = 'Se ha generado un nuevo requerimiento: ' + TitulosDoc.NombreDocumento;

Can someone help me?

sql-server trigger

asked by Rsg 02.02.2017 в 18:58

source

1 answer

Read the names of the contents of an array and write them in an html tag Error executing gem install rails

score 1 · Accepted Answer

The key is to use the special temporary table inserted containing the record that was just inserted:

CREATE TRIGGER dbo.SendEmail   
   ON  dbo.TitulosDoc 
   AFTER INSERT
AS 
BEGIN        
  SET NOCOUNT ON;
  declare @body nvarchar(max);
  IF EXISTS (SELECT 1 FROM inserted WHERE IdCircuito = 53 AND IdTipoDoc = 45 AND Gestor = 'Gest1')
  BEGIN
    set @body = 'Se ha generado un nuevo requerimiento: ' + (select NombreDocumento from inserted);
    EXEC msdb.dbo.sp_send_dbmail
      @recipients = '[email protected]', 
      @subject = 'Requerimiento generado', 
      @body = @body;
  END
END
GO

But just to be clear, this trigger can only handle INSERT statements that only insert one record at a time. In theory, a correctly typed trigger should be able to handle INSERT statements that result in several records inserted (or even no records inserted). But as you mentioned in the comments that this is definitely not your case, this trigger should be enough for your need.