Trigger sur tables ou vues

Version imprimable

16/06/2009, 09h53
Mothership

Trigger sur tables ou vues

Bonjour à tous,

Je cherche à mettre un déclencheur pour détecter des problèmes logistiques (ici des réceptions en erreur).
Nous avons deux tables utilisées pour cette manipulation métier, une pour la mise en validation et l'autre pour la validation.
Ma requête pour détecter les réceptions en erreur vérifie des concordances entre les deux tables (le N° de réception par exemple) et des paramètres différents (mis en validation d'un coté/ topé non validée de l'autre) et quelques autres paramètres souvent liés entre les deux tables.

D'après ce que j'ai lu, le fait d'avoir deux tables ne me permet pas de mettre un trigger sur la requête que j'effectue, par contre cela devrait marcher pour une vue est ce le cas ? J'ai aussi lu que la vue ne devait s'appuyer que sur une table pour que cela fonctionne correctement... J'avoue être un peu perdu.
16/06/2009, 13h38
agemis31

Bonjour,

A la lecture de votre question, j'avoue moi aussi être perdu.
Pourriez vous poster la structure de vos tables et un exemple ?

@+
16/06/2009, 15h13
Mothership

HOUU punaise !!

Alors j'ai deux tables ... (je suis peu à l'aise car j'ai plus un profil d'admin système qu'admin db il faut bien l'avouer)

reflex.hlreclp et reflex.hlrecpp
Dans la tables hlrecpp (qui correspond à la mise ne validation) aux colonnes
recact recdpo renann renrec RETRVA

Correspondant aux colonnes de hlreclp (qui reprend les entêtes des réceptions en attente de validation)
r1cact r1cdpo r1nann r1nrec R1QBVR

Je ne commenterai pas le choix de l'éditeur, je n'ai pas ce qu'il faut :).

Voici la requête exécutée pour récupérer la réception en erreur

Citation:

select * from reflex.hlreclp,reflex.hlrecpp where r1nann = '5' and R1QBVR = '0'
and recact = r1cact and recdpo = r1cdpo and renann = r1nann and renrec = r1nrec and retmev = '1' and RETRVA ='0'

Voici la vue que j'ai créé pour récupérer le nombre de réceptions non validée :

Citation:

CREATE VIEW [dbo].[ErrReceptionSSAU]
(Code_Activité,Code_dépôt_physique,Num_année,Num_de_réception,Num_Ligne_Recep,HL_QTE_B_V_LR,HL_COD_ACTIVITE,HL_COD_DEP_PHY,HL_NUM_ANNEE,HL_NUM_RECEPT,Top_recep_mise_en_valid,Top_réception_validée )
AS SELECT r1cact,r1cdpo,r1nann,r1nrec,r1nlir,R1QBVR,recact,recdpo,renann,renrec,retmev,RETRVA
FROM reflex.hlreclp,reflex.hlrecpp
where reflex.hlreclp.r1nann = '5'
and reflex.hlreclp.R1QBVR = '0'
and reflex.hlrecpp.recact = reflex.hlreclp.r1cact
and reflex.hlrecpp.recdpo = reflex.hlreclp.r1cdpo
and reflex.hlrecpp.renann = reflex.hlreclp.r1nann
and reflex.hlrecpp.renrec = reflex.hlreclp.r1nrec
and reflex.hlrecpp.retmev = '1'
and reflex.hlrecpp. RETRVA ='0'

La vue me donne le résultat actuellement nulle pour détecter une réception en erreur :
Une réception mise en validation (retmev = '1')
Une réception qui contient des manquants (R1QBVR = '0')
Une réception non validée (R1QBVR = '0') liée notamment aux manquants

Mon souhait serait de déclencher une alerte ou un email via un trigger lorsqu'une ligne apparait dans la vue ou qu'on puisse faire cette détection sur les deux tables.
Aujourd'hui, ne sachant pas comment faire et les outils à notre disposition, nous exécutons périodiquement une procédure stockée via un plan de maintenance (ouch je sent que ça ne va pas plaire cette méthode) pour ensuite nous remonter la réception en erreur.

J'aimerai qu'on trouve autre chose ... ho secours obiwan
16/06/2009, 20h15
agemis31

Bonsoir,

Toujours pas évident à comprendre côté métier, mais ca n'est pas grave.
Je ne pense pas que vous puissiez utiliser un trigger sur cette vue, car il faut que ce soit un trigger INSTEAD OF alors que votre vue est en lecture seule.
Vous devriez pouvoir coder 2 trigger AFTER INSERT sur chaque table.
Votre solution actuelle ne me choque pas (un plan de maintenance ou un job ?), si les tables sont bien indexées ca doit bien fonctionner et ça centralise le traitement.
Voici un autre post assez similaire au votre.

@+

Bonjour,

Je viens compléter et appuyer la réponse et le post vers lequel agemis31 vous envoie, avec un peu de code :

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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
 
-- Table qui contiendra les données éventuelles de problèmes logistiques
-- Purgée à chaque début et fin d'appel de la procédure qui suit
CREATE TABLE maTable
(
	Code_Activité [TypeDeDonnees],
	Code_dépôt_physique [TypeDeDonnees],
	Num_année [TypeDeDonnees],
	Num_de_réception [TypeDeDonnees],
	Num_Ligne_Recep [TypeDeDonnees],
	HL_QTE_B_V_LR [TypeDeDonnees],
	HL_COD_ACTIVITE [TypeDeDonnees],
	HL_COD_DEP_PHY [TypeDeDonnees],
	HL_NUM_ANNEE [TypeDeDonnees],
	HL_NUM_RECEPT [TypeDeDonnees],
	Top_recep_mise_en_valid [TypeDeDonnees],
	Top_réception_validée [TypeDeDonnees]
)
GO
 
CREATE PROCEDURE Ps_CheckErrReceptionSSAU
AS
BEGIN
	BEGIN TRY
		-- Purge la table
		DELETE FROM dbo.maTable
 
		-- Insère les données éventuelles de problèmes logistiques
		INSERT INTO dbo.maTable
		(
			Code_Activité,
			Code_dépôt_physique,
			Num_année,
			Num_de_réception,
			Num_Ligne_Recep,
			HL_QTE_B_V_LR,
			HL_COD_ACTIVITE,
			HL_COD_DEP_PHY,
			HL_NUM_ANNEE,
			HL_NUM_RECEPT,
			Top_recep_mise_en_valid,
			Top_réception_validée
		)
		SELECT LP.r1cact,
				LP.r1cdpo,
				LP.r1nann,
				LP.r1nrec,
				r1nlir,
				LP.R1QBVR,
				PP.recact,
				PP.recdpo,
				PP.renann,
				PP.renrec,
				retmev,
				PP.RETRVA
		FROM reflex.hlreclp AS LP
		JOIN reflex.hlrecpp AS PP
			ON PP.recact = LP.r1cact
			AND PP.recdpo = LP.r1cdpo
			AND PP.renann = LP.r1nann
			AND PP.renrec = LP.r1nrec
		WHERE LP.r1nann = '5'
		AND LP.R1QBVR = '0'
		AND PP.retmev = '1'
		AND PP. RETRVA ='0'
		AND (
				retmev = '1'
				OR R1QBVR = '0'
			)
 
		-- Si des lignes ont été insérées dans la table
		IF EXISTS
		(
			SELECT *
			FROM dbo.maTable
		)
		BEGIN
			Traitement et / ou envoi de mail
		END
 
		DELETE FROM dbo.maTable
	END TRY
	BEGIN CATCH
		Gestion des erreurs et / ou envoi de mail
	END CATCH
END

Avec la procédure appelée régulièrement dans un job ;)
Faites attention à ne pas nommer les colonnes avec des accents et autres caractères cabalistiques ;)

@++ ;)

17/06/2009, 09h02
Mothership

Mes développeurs sont assez frileux dans la création de table (ponctuelle ou d'administration), est ce un procédé "très utilisé" ?

Aujourd'hui voici le code contenu dans ma procédure stockée

Citation:

DECLARE @dbname VarChar (30)

-- =============================================
-- Déclaration des variables
DECLARE @Cpt Int
DECLARE @Act char(3)
DECLARE @Dep char(3)
DECLARE @Ann char(3)
DECLARE @Rec int
DECLARE @Lig int
DECLARE @Err varchar(256)
DECLARE @Mail varchar(1024)

DECLARE Curseur1 CURSOR
FOR select r1cact, r1cdpo, r1nann, r1nrec, r1nlir from reflex.hlreclp,reflex.hlrecpp where r1nann = '5' and R1QBVR = '0'

and recact = r1cact and recdpo = r1cdpo and renann = r1nann and renrec = r1nrec and retmev = '1' and RETRVA ='0'

-- =============================================

SET @dbname = 'RFXCOOPPROD'
EXEC ('USE ' + @dbname)

select @Cpt=count(*) from reflex.hlreclp,reflex.hlrecpp where r1nann = '5' and R1QBVR = '0'
and recact = r1cact and recdpo = r1cdpo and renann = r1nann and renrec = r1nrec and retmev = '1' and RETRVA ='0'

if @Cpt <> 0

-- =============================================
-- Si une ou plusieurs lignes contiennent les paramètres ci dessus, alors envoi
-- email à xxxxxxxx@mondomaine.lan
-- =============================================

begin
set @Mail = ''
OPEN Curseur1
FETCH Curseur1 INTO @Act, @Dep, @Ann, @Rec, @Lig

WHILE @@FETCH_STATUS = 0
BEGIN
set @Err = @Act + ' ' + @Dep + ' ' + convert(varchar,@Ann) + ' ' + convert(varchar,@Rec) + ' ' + convert(varchar,@Lig)
if not exists(select * from reflex.hlw000p where hlcssn = 'RECDEBUG' and wekobj=@Err)
begin
insert into reflex.hlw000p (hlcssn,WENRGP,wekobj) values('RECDEBUG','1',@Err)
set @Mail = @Mail + ' ' + @Err + '<br>'
end
FETCH Curseur1 INTO @Act, @Dep, @Ann, @Rec, @Lig
end
CLOSE Curseur1
DEALLOCATE Curseur1

if @Mail <> ''
begin
EXEC msdb.dbo.sp_send_dbmail @recipients='ssaumon@coopatl.lan',

@subject = 'Reception en erreur',
@body = @Mail,
@body_format = 'HTML'

end
end

Je suis peu à l'aise avec la déclaration de curseur sur une table et j'ai du mal à cerner l'avantage par rapport à la création d'une table temporaire.

Qu'en pensez vous ?

D'abord votre traitement est faux. En effet VARCHAR n'est bpas un type de données. C'est VARCHAR(n) qu'il faut mettre sinon un seul caractère sera récupéré.

Exemple :

Code:

1
2
3
4
5
6
7
DECLARE @C VARCHAR;
SET @C = 'toto'
SELECT @C AS OUTVALUE
 
OUTVALUE
--------
t

Ensuite préférez CAST a CONVERT : CAST c'est la norme et c'est plus rapide. CONVERT est spécifique à SQL Server.

Enfin pour des valeurs de type numérique IL NE FAUT PAS LES ENTOURER DE GUILLEMETS !
Exemple :

Citation:

where r1nann = '5' and R1QBVR = '0'

Nr'est pas correct.

Code:

where r1nann = 5 and R1QBVR = 0

Est correct !

Pourquoi ne pas faire cela en une seule requête ???

exemple :

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
DECLARE @T TABLE (wekobj VARCHAR(256)), @wekobjs VARCHAR(max);
 
USE RFXCOOPPROD;
 
-- insertion des erreurs (?)
insert into reflex.hlw000p (hlcssn, WENRGP, wekobj) 
OUTPUT inserted.wekobj INTO @T
select 'RECDEBUG', '1', r1cact + ' ' + r1cdpo + ' ' + CAST(r1nann AS varchar(32)) + ' ' + CAST(r1nrec AS varchar(32)) + ' ' + CAST(r1nlir AS varchar(32))
from   reflex.hlreclp
       INNER JOIN reflex.hlrecpp
             ON     recact = r1cact 
                and recdpo = r1cdpo 
                and renann = r1nann 
                and renrec = r1nrec
where r1nann = '5' 
  and R1QBVR = '0'
  and retmev = '1' 
  and RETRVA = '0';
 
-- concaténation des erreurs pour envoi d'un mail
SET @wekobjs = '';
SELECT @wekobjs = @wekobjs + wekobj
FROM   @T
WHERE  not exists(select * 
                 from   reflex.hlw000p 
                 where  hlcssn = 'RECDEBUG' 
                   and  wekobj=@Err)
 
-- enbvoi du mail
EXEC msdb.dbo.sp_send_dbmail @recipients='ssaumon@coopatl.lan',
                             @subject = 'Reception en erreur',
                             @body = @wekobjs,
                             @body_format = 'HTML';

A +

17/06/2009, 11h09
Mothership

Merci pour vos réponses !

question :

Citation:

WHERE r1nann = '5'
AND R1QBVR = '0'
AND retmev = '1'
AND RETRVA = '0';

N'est pas correct si j'ai bien tout suivi ?
Il faut enlever les quotes ?
17/06/2009, 12h09
SQLpro

oui
17/06/2009, 13h13
Mothership

Je test la requête proposée sur notre serveur de qualité, pour autant un mail vide m'est envoyé comme si nous ne gérons pas les cas ou aucune ligne ne correspond à notre select.

J'avoue être un peu perdu :)

Bonjour,

Encadrez l'envoi de mail d'un test sur @wekobjs:

Code:

1
2
3
4
5
6
7
IF ISNULL(@wekobjs, '') <> ''
BEGIN
EXEC msdb.dbo.sp_send_dbmail @recipients='ssaumon@coopatl.lan',
                             @subject = 'Reception en erreur',
                             @body = @wekobjs,
                             @body_format = 'HTML';
END

17/06/2009, 13h38
Mothership

Merci pour tout