Discussion :

[Kropernic]

Bonjour,

J'aimerais faire en sorte d'envoyer un mail en cas d'ajout d'une ligne dans une table.

J'ai donc penser à créer un trigger. J'ai bien vu la procédure xp_sendmail.
Par contre, afin de ne pas devoir retapper toutes les adresses de destination et pour ne pas devoir modifier le trigger en cas de changement, j'aimerais utiliser un des opérateurs existants (que j'utilise déjà pour signaler que les jobs s'est planté par exemple).

Or d'après la MSDN, la liste des destinataire est remplie manuellement.

Y a-t-il donc un moyen d'utiliser les opérateurs que ce soit avec cette procédure ou non...

Merci d'avance,

Griftou.

[SQLpro]

utiliser un trigger pour envoyer un mail n'est pas une bonne pratique. En effet un trigger est à l'intérieur de la transaction. Vous allez donc allonger démesurément les temps de blocage de la table sur lequel porte le trigger. De plus en cas de rollback de la transaction, le mail ne sera pas envoyé (mais c'est peut être ce que vous voulez...).

Il est préférable de faire cela de manière asynchrone. Par exemple en utilisant une table tampon dans laquelle vous mettez tous les éléments de mails à envoyer.
Ceci couplé à une tâche de l'agent qui scrute cette table et effectue les envois de manière asynchrone.

Pour ce qui est de votre opérateur, vous pouvez récupérer le mail afférent à l'utilisateur prévu en interrogeant les tables systèmes de msdb, en particulier :

Code :

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

SELECT email_address FROM dbo.sysoperators WHERE name = '???'

Pour cela prévoyez une procédure qui encapsulera cette requête et l'appel à xp_sendmail.

A +

[Kropernic]

Merci pour vos conseils.

Il serait vraiment temps que je m'offre un ouvrage de référence traitant de ce genre de problématique... Tant que j'y suis, en auriez-vous un à me conseiller ? Mon souci est que je suis presque totalement débutant et j'ai donc du mal à choisir vers lequel me diriger...

J'aurais quand même encore une question. Une table tampon est-elle absolument nécessaire ?
En sachant que j'ai une colonne d'horodatage qui reçoit Getdate() comme valeur par défaut, y a-t-il un risque qu'une ligne ne soit pas prise en compte ?
Si je crée un job qui interroge cette table toutes les 10 minutes par exemple en filtrant les lignes sur base de

Code :

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

Getdate() - 10 minutes

(je dois encore voir comment écrire cela en Transact-SQL), est-il possible que le temps nécessaire au job pour se lancer/s'initialiser fasse qu'une ligne ne soit jamais traitée ?
Si vous me répondez oui, je créerai donc cette table tampon.

Bien à vous,

[SQLpro]

Ce n'est pas judicieux de taper toujours dans la même table, surtout pour des mises à jour. En effet, lorsque les données sont mises à jour, les lignes sont verrouillées de manière exclusive... Donc, personne ne peut plus les atteindre !
En découplant au maximum vos process, vous rendez votre application fluide et optimisez la montée en charge.

Lisez les articles que j'ai écrit à ce sujet :
http://blog.developpez.com/sqlpro/p1...e-donnees-rel/
http://blog.developpez.com/sqlpro/p1...ances-petites/

A +

[elsuket]

Bonjour,

Je ne pense pas que la table supplémentaire soit absolument nécessaire.
A la place, on peut créer une table auxiliaire d'une seule ligne et colonne, dans laquelle on stocke la plus grande valeur de la colonne d’horodatage lors de chaque appel de procédure stockée.
De cette façon à l'appel suivant, il suffit de filtrer la table par WHERE colonne_horodatage > @dernier_horodatage.

Par exemple :

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
45
46
47
CREATE TABLE horodatage_log
(
	dernier_horodatage datetime NOT NULL
)
GO
 
CREATE TRIGGER TR_IOF_ID
	ON dbo.horodatage_log
INSTEAD OF INSERT, DELETE
AS
BEGIN
	SET NOCOUNT ON
 
	RAISERROR('Il est impossible d''exécuter un INSERT ou un  DELETE sur la table dbo.horodatage_log', 16, 1)
END
GO
 
CREATE PROCEDURE usp_send_mail
AS
BEGIN
	SET NOCOUNT ON
 
	DECLARE @dernier_horodatage datetime
		, @horodatage_courant datetime
		, @corps_mail nvarchar(max)
 
	SELECT	@dernier_horodatage = dernier_horodatage
	FROM	dbo.horodatage_log
 
	SELECT	@horodatage_courant = MAX(colonne_horodatage)
	FROM	dbo.maTableSource
	WHERE	colonne_horodatage > @dernier_horodatage
 
	SELECT	@corps_mail = 'blablabla'
	FROM	dbo.uneTable
	...
	WHERE	colonne_horodatage > @dernier_horodatage
	AND	colonne_horodatage <= @horodatage_courant
 
	EXEC msdb.dbo.sp_send_dbmail
		@recipients = 'monOperateur@monEntreprise.com'
		, @mail = @corps_mail
		, @body_format = 'HTML'
 
	UPDATE	dbo.horodatage_log
	SET	dernier_horodatage = @horodatage_courant
END

J'ai bien vu la procédure xp_sendmail.

Si vous êtes sous SQL Server 2005 ou plus, utilisez la procédure stockée système msdb.dbo.sp_send_dbmail. Elle utilise la fonctionnalité Database Mail intégrée au moteur de base de données SQL Server.

je dois encore voir comment écrire cela en Transact-SQL

Voici :

Code :

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

WHERE	maColonne >= DATEADD(minute, -10, GETDATE())

@++

[Kropernic]

Je vais tacher de répondre à tout le monde

@sqlpro :
2 articles que j'avais déjà lu (mais un rappel ne fait jamais de mal).

Je comprends votre argument. Cependant, je pense que je me dois de fournir quelques précisions.

En fait, en tant que développeur (et DBA amateur par la force des choses), j'ai décidé de mettre en place une DB dans laquelle seront stockées les infos nécessaires lorsqu'une erreur non gérée survient dans une application car il arrive fréquemment que les utilisateurs les ignorent purement et simplement.

Cette DB ce compose des tables suivantes :

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
USE [ERROR]
GO
/****** Object:  Table [dbo].[T_APPLICATION]    Script Date: 07/30/2012 16:08:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[T_APPLICATION](
	[APP_ID] [int] IDENTITY(1,1) NOT NULL,
	[APP_NAME] [varchar](100) NOT NULL,
	[APP_CREATED_BY] [varchar](100) NOT NULL CONSTRAINT [DF_T_APPLICATION_APP_CREATED_BY]  DEFAULT (suser_sname()),
	[APP_CREATED_ON] [datetime] NOT NULL CONSTRAINT [DF_T_APPLICATION_APP_CREATED_ON]  DEFAULT (getdate()),
	[APP_MODIFIED_BY] [varchar](100) NULL,
	[APP_MODIFIED_ON] [datetime] NULL,
 CONSTRAINT [PK_T_APPLICATION] PRIMARY KEY CLUSTERED 
(
	[APP_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF

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
USE [ERROR]
GO
/****** Object:  Table [dbo].[T_ERROR]    Script Date: 07/30/2012 16:10:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[T_ERROR](
	[ERR_ID] [int] IDENTITY(1,1) NOT NULL,
	[APP_ID] [int] NOT NULL CONSTRAINT [DF_T_ERROR_APP_ID]  DEFAULT ((0)),
	[ERR_MESSAGE] [varchar](1000) NOT NULL,
	[ERR_STACK_TRACE] [varchar](1000) NOT NULL,
	[ERR_INNER_EXCEPTION_MESSAGE] [varchar](1000) NULL,
	[ERR_CREATED_BY] [varchar](100) NOT NULL CONSTRAINT [DF_T_ERROR_ERR_CREATED_BY]  DEFAULT (suser_sname()),
	[ERR_CREATED_ON] [datetime] NOT NULL CONSTRAINT [DF_T_ERROR_ERR_CREATED_ON]  DEFAULT (getdate()),
	[ERR_MODIFIED_BY] [varchar](100) NULL,
	[ERR_MODIFIED_ON] [datetime] NULL,
 CONSTRAINT [PK_T_ERROR] PRIMARY KEY CLUSTERED 
(
	[ERR_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[T_ERROR]  WITH CHECK ADD  CONSTRAINT [FK_T_ERROR_T_APPLICATION] FOREIGN KEY([APP_ID])
REFERENCES [dbo].[T_APPLICATION] ([APP_ID])
ON DELETE SET DEFAULT
GO
ALTER TABLE [dbo].[T_ERROR] CHECK CONSTRAINT [FK_T_ERROR_T_APPLICATION]

Et l'accès à ces deux tables ne se fait que via les deux procédures stockées suivantes.

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
USE [ERROR]
GO
/****** Object:  StoredProcedure [dbo].[UP_ERR_GetAppID]    Script Date: 07/30/2012 16:16:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UP_ERR_GetAppID]
	@APP_NAME	VARCHAR(100),
	@APP_ID		INT OUTPUT
as
 
IF NOT EXISTS	(
		SELECT 
				*
		FROM
				dbo.T_APPLICATION
		WHERE
				APP_NAME = @APP_NAME
				)
BEGIN
	INSERT INTO dbo.T_APPLICATION (APP_NAME)
	VALUES (@APP_NAME)
 
	SELECT @APP_ID = SCOPE_IDENTITY()
END
 
ELSE
 
BEGIN
	SELECT 
			@APP_ID = APP_ID
	FROM
			dbo.T_APPLICATION
	WHERE
			APP_NAME = @APP_NAME
END

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
USE [ERROR]
GO
/****** Object:  StoredProcedure [dbo].[UP_ERR_CreateErrorLog]    Script Date: 07/30/2012 16:18:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UP_ERR_CreateErrorLog]
		@APP_ID							int,
		@ERR_MESSAGE					varchar(1000), 
		@ERR_STACK_TRACE				varchar(1000),
		@ERR_INNER_EXCEPTION_MESSAGE	varchar(1000) = NULL
as
INSERT INTO dbo.T_ERROR (APP_ID, ERR_MESSAGE, ERR_STACK_TRACE, ERR_INNER_EXCEPTION_MESSAGE)
VALUES (@APP_ID, @ERR_MESSAGE, @ERR_STACK_TRACE, @ERR_INNER_EXCEPTION_MESSAGE)

De ce fait, les lignes de ces tables ne sont pas sensées être mise à jour. De plus, mais je peux me tromper, en créant un index cluster les colonnes [ERR_CREATED_ON] + [ERR_ID] en ordre descendant, les select qu'il y aurait à faire pour récupérer les lignes nécessitant un traitement devraient être assez performant.


@elsuket :
Votre idée me plait assez mais que se passerait-il si (je prends un cas extrême "on purpose")...
Imaginons 100 utilisateurs. Peu importe l'application qu'ils utilisent. Imaginons encore qu'exactement au même instant T, une erreur non gérée survient dans l'application qu'ils utilisent. J'ignore totalement quel laps de temps il va falloir à sql server pour insérer les lignes dans la table T_ERROR (et éventuellement dans la table T_APPLICATION). Mettons le temps nécessaire à une insertion comme unité. Les 100 lignes seront donc la table au temps T+100. Si d'aventure il arrive que le job s'exécute au temps T+50. Que se passe-t-il alors ?

-------------------------------------------------
Finalement, en écrivant, je me rends compte que, n'étant pas devin, je ne peux prédire que l'utilisation de ces tables ne sera jamais amenée à être modifiée et que donc, la méthode proposée par sqlpro est probablement la meilleur en terme de flexibilité. Je pense donc que je vais adopter la méthode de la table tampon. Néanmoins, j'aimerais avoir vos avis concernant les remarques que j'ai soulevées.

Bien à vous et merci d'avance,

P.S. : Je me rends compte que les deux procédures stockées pourraient être fusionnées en une seule. Mais plus d'opération dans une procédure signifie une transaction plus longue. Que faut-il préférer ?

[Kropernic]

Bonjour à tous,

Ce matin, je me décide à mettre en place la table tampon et dès le départ, je me retrouve confronté à un problème de conception...

Au premier abord, je me suis dit que j'allais mettre une colonne référençant la colonne ID de table T_ERROR de manière à savoir quelle erreur nécessite d'être envoyée par e-mail. Je me suis alors rappeler ceci :

Ce n'est pas judicieux de taper toujours dans la même table, surtout pour des mises à jour.

Je me suis alors dit que j'allais une sorte de clone à peu de chose près de la table T_ERROR mais je suis alors rappelé la première règle de cet article, à savoir :

1) AUCUNE REDONDANCE : sinon cela augmente le volume de la base et multiplie les mises à jour par autant de données redondées, donc augmente la durée du traitement, donc, celle des verrous posés sur les tables, donc dégrade les performances et diminue la concurrence d’accès possible.

Du coup, l'un me renvoyant sur l'autre, je tourne en rond et ne sais pas quelle option adopter... Bien que la table clone, étant amenée à être nettoyé après chaque envoi d'e-mail, ne sera pas vraiment redondante puisque, au final, les donnes ne se trouveront que dans la table T_ERROR.

Comment vous y prendriez-vous ?

[Serguei_TARASSOV]

Cf Envoyer les courriels depuis un trigger

[Kropernic]

Merci pour l'alerte par mail, j'avais oublié de mettre le flag RESOLU.

Sinon, pour l'envoie d'un courriel dans un trigger, comme dit plus haut par sqlpro, ce n'est pas une bonne idée même si d'un point de vue technique, c'est tout à fait possible.