Discussion :

[greg360]

Bonjour à tous, je souhaiterais créer un trigger avec une requête SQL contenant une clause WHERE NOT IN, pour cela, j'utilise le code suivant :

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
CREATE TRIGGER Trigger_test_nomachine
   ON  F_ARTICLE
   AFTER INSERT,DELETE,UPDATE
AS 
declare @no_machine as varchar(21)
declare @resultat as int
BEGIN
	SET NOCOUNT ON;
	select @no_machine=inserted.Machine_associe from inserted where not in (SELECT F_ENUMLIBRECIAL.EL_Intitule FROM F_ENUMLIBRECIAL where N_Info = 2)
	if (LEN(@no_machine)>0)
	begin
	raiserror('Machine inexistante ou nom de machine incorrect',11,1)
	rollback transaction
	return
	end
END
GO

Mon problème est le suivant : au moment de l’exécution, j'ai le message d'erreur suivant :

Msg 156, Level 15, State 1, Procedure Trigger_test_nomachine, Line 14
Syntaxe incorrecte vers le mot clé 'in'.

Merci pour vos réponses.

[elsuket]

Bonjour,

La construction de votre clause WHERE NOT IN est incorrecte.
Il faut la remplacer par WHERE maColonne NOT IN.

Mais votre trigger comporte d'autres fautes, dont la plus grosse est qu'il n'est pas ensembliste : il se comportera très bien pour une seule ligne ajoutée, supprimée ou modifiée, mais que se passera-t-il dès lors que vous modifierez au moins deux lignes ?
La réponse se trouve dans ce billet

La clause NOT IN n'étant pas SARGable, il est fort probable que votre requête procède à un scan de table pour trouver les lignes candidates, ce qui fait que le trigger peut être long à s'exécuter.
Compte tenu du fait le le trigger fait partie de la transaction qui l'a déclenché, votre transaction peut donc durer longtemps et poser de la contention sur la table F_ARTICLE, ce dont je doute que cela réjouisse qui que ce soit dans votre entreprise

Votre variable @resultat n'a aucune utilité, et vous n'utilisez pas la même casse pour les mots clé SQL, ce qui n'est pas très grave bien sûr, mais faciliterait la lecture de votre code

Enfin vous n'avez pas qualifié le nom des objets que vous manipulez par le nom du schéma auquel ils appartiennent, ce qui oblige SQL Server à interroger les tables de métadonnées pour le trouver

Donc on pourrait écrire votre trigger comme suit :

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
CREATE TRIGGER TR_A_IUD__F_ARTICLE
   ON  F_ARTICLE
   AFTER INSERT, DELETE, UPDATE
AS 
BEGIN
	DECLARE @liste_no_machine varchar(max)
 
	-- Concaténation des numéros de machine incorrects
	SELECT		@liste_no_machine = CASE
						WHEN @liste_no_machine IS NULL THEN ''
						ELSE @liste_no_machine + ',' + I.Machine_associe
					END
	FROM		inserted AS I
	LEFT JOIN	dbo.F_ENUMLIBRECIAL AS E
				ON I.Machine_associe = E.EL_Intitule
				AND E.N_Info = 2
				AND LEN(I.Machine_associe) > 0
	WHERE		E.EL_Intitule IS NULL
 
	IF @liste_no_machine IS NOT NULL
	BEGIN
		RAISERROR('Les machines dont le n° suit sont inexistantes ou leur nom est incorrect %s', 16, 1, @liste_no_machine)
		ROLLBACK TRANSACTION
		RETURN
	END
END
GO

Je n'ai pas compris pourquoi vous utilisez :

Code :

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

IF (LEN(@no_machine)>0)

@++

[greg360]

C'est just une réponse parfaite, merci !
J'aprécie beaucoup le temps que vous avez passé sur mon problème, qui est maintenant résolu.

Le test :

Code :

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

IF (LEN(@no_machine)>0)

servait en réalité à tester la présence - ou non - d'une valeur entrée par l'utilisateur.

Encore merci!

[Sergejack]

La clause NOT IN n'étant pas SARGable, il est fort probable que votre requête procède à un scan de table pour trouver les lignes candidates, ce qui fait que le trigger peut être long à s'exécuter.

Faux.
Sql Server va bel et bien employer les indexes disponibles avec ensuite un anti join (que l'on rencontre aussi avec l'opération EXCEPT).

[elsuket]

D'ailleurs, l'exemple très simple ci-dessous le montre bien :

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
48
49
CREATE TABLE dbo.TEST
(
	test int NOT NULL CONSTRAINT PK_TEST PRIMARY KEY
	, nom_test varchar(8) NOT NULL
)
GO
 
DECLARE @i int = 1
 
WHILE @i <= 10000
BEGIN
	INSERT	INTO dbo.TEST
	(
		test
		, nom_test
	)
	SELECT	@i
		, 'test' + CAST(@i % 10 AS varchar(8))
 
	SET @i += 1
END
GO
 
CREATE INDEX IX_TEST__nom_test
ON dbo.TEST (nom_test)
 
SELECT	nom_test
FROM	dbo.TEST
WHERE	test NOT IN (5, 6, 7)
GO
 
SELECT	test
	, nom_test
INTO	dbo.TEST2
FROM	dbo.TEST
GO
 
ALTER TABLE dbo.TEST2
ADD CONSTRAINT PK_TEST2 PRIMARY KEY (test)
GO
 
CREATE INDEX IX_TEST2__nom_test
ON dbo.TEST2 (nom_test)
GO
 
SELECT		T2.nom_test
FROM		dbo.TEST AS T1
INNER JOIN	dbo.TEST2 AS T2 ON T1.test = T2.test
WHERE		T1.test NOT IN (1,  3,  5,  7,  9,  11,  13,  15,  17,  19,  21,  23,  25,  27,  29,  31,  33,  35,  37,  39,  41,  43,  45,  47,  49,  51,  53,  55,  57,  59,  61,  63,  65,  67,  69,  71,  73,  75,  77,  79,  81,  83,  85,  87,  89,  91,  93,  95,  97,  99)

Si on regarde le plan de requête des deux SELECT, on ne voit pas de scan.