fonction exists toujours vrai transact sql

Version imprimable

bonjour a tous

voila j'ai un trigger sur l'insertion dans une table ligne composé d'un numéro et d'un nom
ce trigger me permet de vérifier si le nom existe déja dans la base et annule l'opération si oui
pour cela je fait
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 ALTER TRIGGER [dbo].[insertligne] ON [dbo].[LIGNE] for insert AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; if (exists(select INSERTED.libelle_ligne from ligne, INSERTED where ligne.libelle_ligne=INSERTED.libelle_ligne)) begin ROLLBACK RAISERROR 13000 'le nom ou le numéro de cette ligne existe déja' end
Le problème est que meme lorsque le nom n'est pas dans la base, il me sort l'erreur. Je ne voit vraiment pas de quoi cela peu venir
N'hesitez pas a me demander plus de précisions
merci d'avance pour vos réponses

26/03/2009, 13h33
jero44

Bonjour,

Les déclencheurs SQL server soont en mode AFTER lors de son exécution la ligne est déjà présenta dans la table cible.
La table inserted contient une "copie" des lignes insérées il est donc normal qu'EXIST retourne toujours VRAI
26/03/2009, 19h40
SQLpro

Citation:

j'ai un trigger sur l'insertion dans une table ligne composé d'un numéro et d'un nom ce trigger me permet de vérifier si le nom existe déja dans la base et annule l'opération

Il serait plus simple, mille fois plus performant et plus judicieux d'utiliser pour ce faire une contrainte d'unicité !

A +

Bonjour,

Voici ce que vous pouvez faire souhaitez insérer seulement les libelle_ligne qui ne sont pas déjà dans la table tout en insérant ce qui n'y sont pas déjà, et en vous signalant ceux qui y sont déjà :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
ALTER TRIGGER TR_IOF_LIGNE
	ON dbo.Ligne
INSTEAD OF INSERT
AS
BEGIN
	INSERT INTO dbo.Ligne
			SELECT libelle_ligne
			FROM INSERTED
		EXCEPT 
			SELECT libelle_ligne
			FROM dbo.Ligne
 
	DECLARE @Msg VARCHAR(512)
	SELECT @Msg = ISNULL(@Msg, '') + CAST(libelle_ligne AS VARCHAR) + ', '
	FROM dbo.Ligne AS L
	JOIN INSERTED AS I ON L.libelle_ligne = I.libelle_ligne
 
	IF @Msg IS NOT NULL
	BEGIN
		RAISERROR('Les libellés de ligne %s sont déjà stockés dans dbo.Ligne', 16, 1, @Msg)
	END
END

Si en revanche votre INSERT comporte plusieurs colonnes, vous pouvez écrire dès SQL Server 2005 :

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
 
ALTER TRIGGER TR_IOF_LIGNE
	ON dbo.Ligne
INSTEAD OF INSERT
AS
BEGIN
	WITH
		CTE AS
		(
				SELECT libelle_ligne
				FROM INSERTED
			EXCEPT 
				SELECT libelle_ligne
				FROM dbo.Ligne
		)
	INSERT INTO dbo.Ligne
	SELECT INSERTED.libelle_ligne,
			INSERTED.maColonne0,
			INSERTED.[...],
			INSERTED.maColonneN
	FROM CTE 
	JOIN INSERTED ON CTE.libelle_ligne = INSERTED.libelle_ligne
 
	DECLARE @Msg VARCHAR(512)
	SELECT @Msg = ISNULL(@Msg, '') + CAST(libelle_ligne AS VARCHAR) + ', '
	FROM dbo.Ligne AS L
	JOIN INSERTED AS I ON L.libelle_ligne = I.libelle_ligne
 
	IF @Msg IS NOT NULL
	BEGIN
		RAISERROR('Les libellés de ligne %s sont déjà stockés dans dbo.Ligne', 16, 1, @Msg)
	END
END

et dès SQL Server 2000 :

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
ALTER TRIGGER TR_IOF_LIGNE
	ON dbo.Ligne
INSTEAD OF INSERT
AS
BEGIN
	INSERT INTO dbo.Ligne
	SELECT INSERTED.libelle_ligne,
			INSERTED.maColonne0,
			INSERTED.[...],
			INSERTED.maColonneN
	FROM (
				SELECT libelle_ligne
				FROM INSERTED
			EXCEPT 
				SELECT libelle_ligne
				FROM dbo.Ligne
		) TMP
	JOIN INSERTED ON TMP.libelle_ligne = INSERTED.libelle_ligne
 
	DECLARE @Msg VARCHAR(512)
	SELECT @Msg = ISNULL(@Msg, '') + CAST(libelle_ligne AS VARCHAR) + ', '
	FROM dbo.Ligne AS L
	JOIN INSERTED AS I ON L.libelle_ligne = I.libelle_ligne
 
	IF @Msg IS NOT NULL
	BEGIN
		RAISERROR('Les libellés de ligne %s sont déjà stockés dans dbo.Ligne', 16, 1, @Msg)
	END
END

ou encore :

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
ALTER TRIGGER TR_IOF_LIGNE
	ON dbo.Ligne
INSTEAD OF INSERT
AS
BEGIN
	INSERT INTO dbo.Ligne
	SELECT INSERTED.libelle_ligne,
			INSERTED.maColonne0,
			INSERTED.[...],
			INSERTED.maColonneN
	FROM INSERTED
	WHERE libelle_ligne NOT IN (
									SELECT libelle_ligne
									FROM dbo.Ligne
								)
 
	DECLARE @Msg VARCHAR(512)
	SELECT @Msg = ISNULL(@Msg, '') + CAST(libelle_ligne AS VARCHAR) + ', '
	FROM dbo.Ligne AS L
	JOIN INSERTED AS I ON L.libelle_ligne = I.libelle_ligne
 
	IF @Msg IS NOT NULL
	BEGIN
		RAISERROR('Les libellés de ligne %s sont déjà stockés dans dbo.Ligne', 16, 1, @Msg)
	END
END

Mais il est clair que comme vous le dit SQLPro une contrainte d'unicité vous permet de faire cela sans un trigger, qui est gourmand en ressources :

Code:

1
2
ALTER TABLE dbo.Ligne
ADD CONSTRAINT UQ_Ligne_libelle_ligne UNIQUE (libelle_ligne)

Comme la création d'une contrainte d'unicité entraîne implicitement la créatin d'un index, vous y gagnerez même peut-être un peu lors de vos recherche sur la colonne libelle_ligne ;)

@++ ;)

27/03/2009, 08h52
bobby51
merci a tous pour vos réponse

Il s'est avéré que mon problème venait du for ligne, que j'ai remplacé par instead of ligne, je croyai pourtant que l'instruction for signalait qu'il fallait far le test avant l'opération
Sinon pour l'unicité j'y avais pensé mais le problème est que j'ai une multitude de triggers qui me renvoie un message d'erreur, et je me sert de ces messages d'erreurs pour les afficher dans une application, quelle sera le message d'erreur renvoyé par sql serveur automatiquement?, il faut qu'il soit clair pour l'utilisateur
Sinon elsuket je ne connait pas cette syntaxe en sql
Code:

1 2 3 4 5 6 7 8 CTE AS ( SELECT libelle_ligne FROM INSERTED EXCEPT SELECT libelle_ligne FROM dbo.Ligne )
pourrai m'en dire plus,a quoi ca sert,...
j'attend ta réponse et je met résolu
27/03/2009, 09h02
elsuket

Bonjour,

Voici un très bon tutoriel de SQLPro à ce sujet, et la documentation de MS ici.

Utiliser une expression de table commune non récursive aboutit au même plan de requête que la requête qui est juste en dessous dans ma réponse précédente.

Le message que reçoit l'utilisateur lors d'une violation de clé unique est :

Citation:

Msg*2627, Niveau*14, État*1, Ligne*1
Violation de la contrainte UNIQUE KEY 'UQ_nomContrainte'. Impossible d'insérer une clé en double dans l'objet 'monSchema.maTable'.
L'instruction a été arrêtée.

@++ ;)
27/03/2009, 11h09
bobby51

merci :king:
Je crois que j'ai du boulot car j'ai encore jamais utilisé les récursives
sinon pour le message d'erreur il est pas super clair je vais garder ma solution
le sujet est clos