Trigger SQL Server avec Clause Where

**Tekkeur** · 31/05/2017, 14h27

Bonjour,

Je souhaiterai avoir votre aide pour créer un trigger sur SQL Server.

Ma situation :

J’ai une table dbo.ACTIONS, avec les colonnes : Action_ID, Person_ID, Pause_Code, Next_state.

• J’aimerai créer un trigger qui envoie automatiquement une notification par mail à chaque fois qu’il y aura une ligne ajoutée dans la table dbo.ACTIONS dont la Pause_Code=2. Si le Pause_code !=2, je n’envoie pas une notification mail.

• Deuxième point, 2min après l’insertion de la ligne, si le Next_State est toujours null (Next_State est initialisé à Null au moment de l’insertion), j’envoie une autre notification, dans le cas contraire, je n’ai pas besoin d’envoyer une notification (cas où Next_State not null)
Merci d’avance pour votre aide.

**jcdentons** · 31/05/2017, 20h43

Bonjour Tekkeur,

Pour le premier point, c'est assez simple, il suffit de créer un trigger d'insertion. Grosso modo :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
create trigger TriggerEnvoiMail 
ON TABLE 
FOR INSERT
as
BEGIN
DECLARE @LIGNE_INSEREE VARCHAR(30);
SET @LIGNE_INSEREE = SELECT CHAMP_1 + '' + CHAMP_2 ... FROM inserted;
EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'PROFIL',  
    @recipients = 'EMAIL',  
    @body = 'CONTENU_EMAIL' + @LIGNE_INSEREE,  
    @subject = 'OBJET' ;  
from inserted;

Pour le second point . . . ne serait-il pas mieux de fonctionner grâce à un programme externe ? Gérer un "WAIT" dans un Trigger ne me semble pas idéal . . .

**Tekkeur** · 01/06/2017, 07h27

Merci jcdentons,

Mais le souci avec votre trigger là, ce que ça m' envoie de notification à chaque fois qu'il y aura une insertion de données sur la table, alors que ce que je voulais, ce qu'on ne m'envoie de notification que lorsque Pause_Code=2

**aieeeuuuuu** · 01/06/2017, 10h09

Bonjour,

Envoyé par jcdentons

Gérer un "WAIT" dans un Trigger ne me semble pas idéal . . .

en effet, ni même envoyer un mail. Les verrous restent posés durant l'éxécution du trigger, et si le serveur de messagerie est un peu long à répondre, cela créera de la contention.

Il serait plus judicieux d'utiliser le service broker : le trigger empile un message lors de l'insertion d'une ligne qui vérifie les conditions définie, et un autre service récupère ces messages et les traite (envoi du mail et vérification des deux minutes)

**SQLpro** · 01/06/2017, 12h01

pour info sp_send_db_mail utilise service broker....

A +

**elsuket** · 01/06/2017, 14h42

Bonjour,

Par ailleurs le trigger n'est pas ensembliste, donc il est incorrect.
En effet, quand on ajoutera plusieurs lignes à la table, on n'aura qu'un seul e-mail qui correspondra aux valeurs de la dernière ligne du jeu de données ajouté à la table et qui correspond au filtre WHERE.

Il faudrait donc, par exemple, générer une chaîne HTML qui présente une table HTML avec les valeurs qui correspondent.
En utilisant la clause FOR XML, ça peut donner quelque chose comme :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
DECLARE @html_body varchar(max)
	, @html_table_header varchar(max)
	, @html_table_footer varchar(max)
	, @mail_subject varchar(128) = 'Sujet du mail';
 
SELECT	@html_table_footer = '</table></body></html>'
	, @html_table_header = '<html><head>' + 
		'<style>
	td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;}
	tr.even {background-color:white;}
	tr.odd {background-color:#eeeeee;}
		</style>' + 
		'</head>' + 
		'<body><table cellpadding=0 cellspacing=0 border=0>' + 
		'<tr bgcolor=#FFEFD8><td align=center><b>NomColonne1</b></td>' + 
		'<td align=center><b>NomColonne2</b></td></tr>';
 
SELECT	@html_body = 
	(
		SELECT		maColonne1 AS td
				, maColonne2 AS td
				, ...
				, maColonneN AS td
		FROM		INSERTED AS I
		...
		FOR		XML RAW('tr'), ELEMENTS
	);
 
IF @html_body IS NOT NULL AND LEN(@html_body) > 0
BEGIN
	SET @html_body = REPLACE(@html_body, '_x003D_', '=');
	SET @html_body = REPLACE(@html_body, '_x0020_', ' ');
	SET @html_body = REPLACE(@html_body, '<tr><TRRow>0</TRRow>', '<tr class="even">');
	SET @html_body = REPLACE(@html_body, '<tr><TRRow>1</TRRow>', '<tr class="odd">');
 
	SET @html_body = @html_table_header + @html_body + @html_table_footer;
 
	EXEC msdb.dbo.sp_send_dbmail
		@profile_name = '<PROFIL MAIL SQL>'
		, @recipients = 'mail'
		, @body = @html_body 
		, @subject = @mail_subject
		, @body_format = 'HTML'
END

@++

Trigger SQL Server avec Clause Where

Développement SQL Server

Discussions similaires

Partager

Partager