Trigger SQL Server avec Clause Where

Version imprimable

31/05/2017, 14h27
Tekkeur

Trigger SQL Server avec Clause Where

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.

Bonjour Tekkeur,

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

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 . . .

01/06/2017, 07h27
Tekkeur

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
01/06/2017, 10h09
aieeeuuuuu

Bonjour,

Citation:

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)
01/06/2017, 12h01
SQLpro

pour info sp_send_db_mail utilise service broker....

A +

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:

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

@++ ;)