[SQL2008][TSQL] Clause WHERE NOT IN dans un trigger

Version imprimable

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:

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 :

Citation:

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

Merci pour vos réponses.

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:

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:

IF (LEN(@no_machine)>0)

@++ ;)

16/11/2010, 16h43
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:

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!
17/11/2010, 17h07
Sergejack

Citation:

Envoyé par elsuket

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

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

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