Envoyer les courriels depuis un trigger

L'objectif de cette exemple est d'être averti si les modifications ont été effectuées dans votre base de données.

En fait, l’implémentation d’envoi des courriels dans le trigger peut être un peu risqué pour la performance malgré le mode asynchrone d'envoi. Donc pour l’implémentation "classique" et 100% fiable utiliser plutôt le trigger comme "writer" pour remplir la queue (ex. la table des messages) et le processus séparé comme "reader" qui traite cette queue (ex. procédure stockée démarré par SQL Agent).

Dans notre exemple nous allons voir l’implémentation "simple" basée sur le trigger d'une table.

Tout d'abord, préparez votre serveur.

1. Configurez votre sous-système de messagerie (Database mail). Créez un compte et un profil afin d'envoyer les messages (voir MSDN pour le détails).

2. Vérifiez que le profil et son nom sont bien présentés :

SELECT * FROM msdb.dbo.sysmail_profile

3. Tester l'envoi d'un message

EXEC msdb.dbo.sp_send_dbmail
       @profile_name = 'My notifier',
       @recipients = 'prenom.nom@orange.fr',
       @body = 'Salut, je suis arrivé !',
       @subject = 'Modifications'

Maintenant, il est temps de créer notre trigger d'envoi. Je prend la BDD "AdventureWorks" pour cet exemple.

USE AdventureWorks
GO
CREATE TABLE DBMailTest (
   Id uniqueidentifier NOT NULL,
   Code nvarchar(10) NOT NULL,
   Caption nvarchar(255),
   Created datetime,
   CONSTRAINT PK_DBmailTest PRIMARY KEY (Id),
   CONSTRAINT AK1_DBmailTest UNIQUE (Code)
)
GO
 
CREATE TRIGGER DBMailTest_SendModifications 
ON DBMailTest
FOR INSERT, UPDATE, DELETE
AS BEGIN
   DECLARE @Modifs nvarchar(max) = NULL;
 
   SET @Modifs = (
      SELECT I.* 
      FROM inserted I LEFT OUTER JOIN deleted D ON I.Id = D.Id
      WHERE D.Id IS NULL
      FOR XML RAW, ROOT('InsertedRows')
      );
   IF @Modifs IS NULL BEGIN
      SET @Modifs = (
         SELECT I.* 
         FROM inserted I INNER JOIN deleted D ON I.Id = D.Id
         FOR XML RAW, ROOT('ModifiedRows')
         );
      IF @Modifs IS NULL BEGIN
         SET @Modifs = (
            SELECT D.* 
            FROM deleted D LEFT OUTER JOIN inserted I ON I.Id = D.Id
            WHERE I.Id IS NULL
            FOR XML RAW, ROOT('DeletedRows')
            );
      END;
   END;
   IF @Modifs IS NOT NULL BEGIN
      EXEC msdb.dbo.sp_send_dbmail
          @profile_name = 'My notifier',
          @recipients = 'prenom.nom@orange.fr',
          @body = @Modifs,
          @body_format = 'TEXT',
          @subject = 'DBMailtest table modifications';
   END;
END
GO

On en test maintenant.

INSERT INTO DBMailTest (Id, Code, Caption, Created)
SELECT newid(), 'L01', 'Line 1', getdate()
UNION ALL
SELECT newid(), 'L02', 'Line 2', getdate()
UNION ALL
SELECT newid(), 'L03', 'Line 3', getdate()
GO
 
UPDATE DBMailTest
   SET Caption = 'Line 02'
   WHERE Code = 'L02'
GO
 
DELETE FROM DBMailTest;
GO
 
DROP TABLE DBMailTest
GO

Commentaires

1. Vous ne pouvez pas utiliser le paramètre @query de la procédure sp_send_dbmail en faisant la requête sur les tables "inserted" et "deleted" puisque cette requête est exécute dans un autre processus qui n'a pas d’accès de ces tables spéciales.

2. Si vous pensez utiliser @query = 'SELECT ...', faites l'attention du contexte de sécurité. Cette requête sera exécute en contexte du "CALLER" (voir le texte de sp_send_dbmail). En case de la sécurité intégrée et ActiveDirectory le contrôleur du domaine doit être disponible.

3. Pour mieux formater votre message vous pouvez utiliser @body_format = 'HTML' et développer une requête qui produit HTML au lieu de XML ainsi qu'attacher les XMLs dans votre message comme les pièces joints ;-)