before insert trigger en sql

**trovillione** · 30/11/2009, 19h40

bonsoir a tous,

j ai encore un probleme en sql. j ai une table client (nom, adresse) et j aimerai creer un trigger qui verifie d abord l adresse avant d inserer, mais j ai l erreur suivante : 'before' is not a recognized trigger. j aimerai donc savoir si un before insert est possible, si oui comment. merci de votre reponse

**elsuket** · 30/11/2009, 20h11

Bonjour,

Avec SQL Server il n'y a pas de trigger BEFORE, et vous verrez qu'en fait, vous n'en avez pas besoin: utilisez un trigger INSTEAD OF.

Voici un peu de lecture sur les triggers

Je ne sais pas quel type de vérification vous souhaitez faire sur l'adresse, mais s'il s'agit par exemple d'un format, vous pouvez le gérer avec une contrainte de domaine (CHECK).

@++

**trovillione** · 30/11/2009, 20h56

bonsoir elsuket,

[CODE= sql]en faire j aimerais d abord verifier si le nom entre existe deja si oui alors j envoie un message du genre "ce nom existe deja", sinon je l enregistre. avec INSTEAD OF, j ai lu mais pas vraiment compris a quoi ca sert et pourquoi et comment l utiliser. si tu peux l utiliser dans mon cas, je crois que ca pourra m aider a mieux comprendre.

merci d avance

**elsuket** · 30/11/2009, 21h40

Dans ce cas ajoutez une contrainte d'unicité sur le nom :

Code :

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

1
2
ALTER TABLE dbo.client
ADD CONSTRAINT UQ_client_nom UNIQUE (nom)

Mais êtes-vous sûr que le nom du client pourra rester unique ?
Je ne connais la problématique de votre base de données, mais il se peut aussi que vous ayez aussi deux clients distincts ayant même adresse ...

En utilisant un trigger, on aurait :

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
CREATE TRIGGER TR_IOF_I_client
	ON dbo.client
INSTEAD OF INSERT
AS
BEGIN
	-- Concatène la liste des noms qu'on tente d'insérer
	-- mais qui sont déjà dans la table client
	DECLARE @listeNoms VARCHAR(128)
	SELECT @listeNoms = ISNULL(@listeNoms, '') + C.nom + ','
	FROM dbo.client AS C
	JOIN INSERTED AS I
		ON C.nom = I.nom
 
	-- Si la liste n'est pas vide, on lève une exception
	IF @listeNoms IS NOT NULL
	BEGIN
		SELECT @listeNoms = LEFT(@listeNoms, LEN(@listeNoms) - 1)
		RAISERROR('Les noms %s existent déjà !', 16, 1, @listeNoms)
 
		ROLLBACK
	END
	ELSE -- Sinon on procède à l'insertion
	BEGIN
		INSERT INTO dbo.client
		(
			colonnesDeLaTableClient
		)
		SELECT colonnesDeLaTableClient
		FROM INSERTED
	END
END

Mais comme vous le voyez, nous devrons pour toute nouvelle insertion réaliser une jointure sur le nom du client.
Or, réaliser une jointure sur une chaîne de caractère est bien moins performant qu'une jointure sur des entiers, même si celle-ci est indexée ...

Néanmoins, tout dépend du nombre de clients que vous aurez quand la base de données sera arrivée à maturité (3-5 ans ...)

@++

**trovillione** · 30/11/2009, 22h06

Code sql :

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
j ai 2 encore 2 questions:
 
1- qu est ce que nous apporte le instead of insert?
 
2- quand tu fais ceci: 
ELSE -- Sinon on procède à l'insertion
	BEGIN
		INSERT INTO dbo.client
		(
			colonnesDeLaTableClient
		)
		SELECT colonnesDeLaTableClient
		FROM INSERTED
	END
 
est ce que ca ne signifie pas que on insert dans la tableclient tous les enregistrements de la table inserted?
je ne comprend pas bien ce que fais ce petit bout de code.
 
merci encore pour tes reponses

**elsuket** · 01/12/2009, 08h44

qu est ce que nous apporte le instead of INSERT?

Les triggers de type INSTEAD OF permettent de n'effectuer qu'une seule modification sur une table au lieu de deux, par exemple un trigger AFTER INSERT qui ferait dans son code un UPDATE sur la même table.

Il permet aussi, comme je vous l'ai montré dans le trigger que je vous propose, d'effectuer des vérifications avant de modifier la table cible.

quand tu fais ceci:

Code :

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

1
2
3
4
5
6
7
8
9
ELSE -- Sinon on procède à l'insertion
	BEGIN
		INSERT INTO dbo.client
		(
			colonnesDeLaTableClient
		)
		SELECT colonnesDeLaTableClient
		FROM INSERTED
	END

est ce que ca ne signifie pas que ON INSERT dans la tableclient tous les enregistrements de la TABLE inserted?
je ne comprend pas bien ce que fais ce petit bout de code.

Quand un trigger INSTEAD OF s'exécute, si on ne re-spécifie pas la modification de données à effectuer qui aurait du être faite si le trigger n'existait pas sur la table, alors l'effet de cette modification est perdu.

Si vous avez lu les deux liens que je vous ai donné sur les triggers, vous savez à quoi servent les tables virtuelles INSERTED et DELETED.

@++

**trovillione** · 01/12/2009, 10h08

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
bonjour,
 
quand tu fais ceci, c la mon probleme:
 
ELSE -- Sinon on procède à l'insertion
	BEGIN
		INSERT INTO dbo.client
		(
			colonnesDeLaTableClient
		)
		SELECT colonnesDeLaTableClient
		FROM INSERTED
	END
 
mais est ce que c n est pas apres avoir utilise le "insert into" que dans la table inserted ira s enregistrer la donnee. car avec la boucle qui est la au dessus j ai l impression la donnee est deja dans la table inserted.

**elsuket** · 01/12/2009, 10h20

Ne mettez pas tout dans la balise code, ça devient illisible

mais est ce que c n est pas apres avoir utilise le "insert into" que dans la TABLE inserted ira s enregistrer la donnee. car avec la boucle qui est la au dessus j ai l impression la donnee est deja dans la TABLE inserted.

INSERTED est une table virtuelle dont la structure est strictement la même que la table cible, et qui contient, dans le cas d'une insertion, les lignes qui allaient être insérées dans la table.
Donc :

Code :

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

1
2
3
4
5
6
INSERT INTO dbo.client
(
	colonnesDeLaTableClient
)
SELECT colonnesDeLaTableClient
FROM INSERTED

Insère effectivement dans la tables les lignes qui allaient l'être.
Si on ne met pas ce code, aucune ligne ne sera insérée dans la table

@++

**trovillione** · 01/12/2009, 11h20

rebonjour,

vous dites ceci :INSERTED est une table virtuelle dont la structure est strictement la même que la table cible, et qui contient, dans le cas d'une insertion, les lignes qui allaient être insérées dans la table.

donc cela signifie que si je fais 100 INSERT INTO, j aurai aussi 100 donnees enregistrees dans la table INSERTED? ou alors c la dernier donnee qui restera dans la table?

**elsuket** · 01/12/2009, 12h03

ça dépend

Si vous faites 100 INSERT d'une ligne, le trigger est exécuté 100 fois et la table virtuelle INSERTED contiendra à chaque fois une ligne.

Si vous faites un INSERT de 100 lignes, la table virtuelle INSERTED contiendra 100 lignes.

Sous SQL Server, les triggers sont ensemblistes

Faites donc les petits exemples qui sont dans le second lien pour voir comment les tables virtuelles se comportent

@++

**trovillione** · 10/12/2009, 12h08

Bonjour elsuket,

concernant la table INSERTED, je pense d apres la logique que la table INSERTED ne contient que la derniere ligne ajoutee, puisqu on ne peut pas la parcourir.

bon j ai encore une autre question: comment tester si un utilisateur a les droits sur une table (SELECT, INSERT; UPDATE; DELETE)

**elsuket** · 10/12/2009, 14h15

concernant la table INSERTED, je pense d apres la logique que la table INSERTED ne contient que la derniere ligne ajoutee, puisqu on ne peut pas la parcourir.

Je vous l'ai dit : cela est faux.
Si vous aviez lu le lien que je vous ai proposé et fait le test qui je donne, vous vous en seriez forcément rendu compte

Si nous exécutons :

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
CREATE TABLE TbTestTrigger
(
	val TINYINT
)
GO
 
CREATE TRIGGER TR_A_I_TbTestTrigger
	ON dbo.TbTestTrigger
	AFTER INSERT
AS
BEGIN
	SELECT val
	FROM INSERTED
END
GO
 
INSERT INTO dbo.TbTestTrigger (val)
	SELECT 1
UNION
	SELECT 2
UNION
	SELECT 3
UNION
	SELECT 4
UNION
	SELECT 5