Trigger qui vérifie inscription à un groupe

**Darkjovem** · 19/03/2011, 18h08

Bonjour, je souhaite créer un Trigger en SQL qui vérifie le nombre d'inscription maximal à un groupe précis(dans mon exemple, le maximum par groupe est de 4).

Voici le code qui pose problème, car je peux entrer autant de membre que je veux dans un groupe :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TRIGGER trigger_maximum_inscription
ON tbl_inscription
FOR INSERT AS
DECLARE @nb_membre int, @no_groupe int
SELECT @nb_membre=COUNT(no_membre)
FROM tbl_inscription
WHERE no_groupe = @no_groupe
IF (@nb_membre>3)
	BEGIN
		RAISERROR('Le nombre de maximal(4) pour ce groupe est atteint', 16, 1)
		ROLLBACK
	END

Merci de m'aider.. J'essais plein de choses, et ça ne semble pas fonctionner :-(

P.S. Ma table inscription contient 2 variables et clés primaires --> no_membre et no_groupe

**Darkjovem** · 19/03/2011, 19h07

J'ai fait quelques modifications qui semblent fonctionner, mais je ne suis pas certain que ce code soit performant :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TRIGGER trigger_maximum_inscription
	ON tbl_inscription
	FOR INSERT AS
	IF EXISTS(SELECT * FROM tbl_inscription
		GROUP BY no_groupe
		HAVING COUNT(*) > 4)
	BEGIN
		RAISERROR('Le nombre de maximal(4) pour ce groupe est atteint', 16, 1)
		ROLLBACK
	END
GO

**iberserk** · 20/03/2011, 11h19

Vous vérifiez toute la table...
Ne vérifiez la règle que pour les données insérées (table inserted...).

exemple: vous ajoutez une ligne, vous n'avez à vérifier la règle que pour le no_groupe appartenant à la ligne insérée.

**SQLpro** · 20/03/2011, 19h04

Ibersek à, raison, il faut faire comme ceci :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TRIGGER trigger_maximum_inscription
ON tbl_inscription
FOR INSERT AS
IF EXISTS(SELECT * 
          FROM   tbl_inscription AS T
                 INNER JOIN inserted AS I
                       ON T.no_group = I.no_group
          GROUP BY no_groupe
          HAVING COUNT(*) > 4)
BEGIN
   RAISERROR('Le nombre de maximal(4) pour ce groupe est atteint', 16, 1);
   ROLLBACK;
END
GO

A +

**iberserk** · 21/03/2011, 08h25

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TRIGGER trigger_maximum_inscription
ON tbl_inscription
FOR INSERT AS
IF EXISTS(SELECT * 
          FROM   tbl_inscription AS T
                 INNER JOIN inserted AS I
                       ON T.no_group = I.no_group
          GROUP BY no_groupe
          HAVING COUNT(*) > 4)
BEGIN
   RAISERROR('Le nombre de maximal(4) pour ce groupe est atteint', 16, 1);
   ROLLBACK;
END
GO

Euh... cette syntaxe ne fonctionnera pas sous SQL SERVER, il faut préciser dans le select:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TRIGGER trigger_maximum_inscription
ON tbl_inscription
FOR INSERT AS
IF EXISTS(SELECT I.no_groupe
          FROM   tbl_inscription AS T
                 INNER JOIN inserted AS I
                       ON T.no_group = I.no_group
          GROUP BY I.no_groupe
          HAVING COUNT(*) > 4)
BEGIN
   RAISERROR('Le nombre de maximal(4) pour ce groupe est atteint', 16, 1);
   ROLLBACK;
END

**aieeeuuuuu** · 21/03/2011, 10h36

Envoyé par iberserk

Euh... cette syntaxe ne fonctionnera pas sous SQL SERVER, il faut préciser dans le select:

Ha bon... pourquoi ?

En effet, je pense qu'il faut préciser l'alias pour le GROUP BY et puis écrire no_groupe partout...

Mais le "SELECT *" me semble tout à fait correct ?

**iberserk** · 21/03/2011, 10h38

Et pourtant non... testé sous SS 2008...
Bizarrement avec le *, il dit que no_groupe est invalide dans la liste etc...

**aieeeuuuuu** · 21/03/2011, 11h22

ne dit-il pas plutôt que le nom de colonne est invalide ?

Dans la requete de SQLPro, et dans la tienne, c'est écrit no_group/no_groupe...

comme je disais aussi, il manque un alias dans la group by, que tu as ajouté...

Mais, je ne vois pas pourquoi le SELECT * serait incorrect, tant que la requête est bien dans un opérateur EXISTS
Effectivement, la requête en elle même est incorrecte sinon, car toutes les colonnes ne sont pas dans le GROUP BY

**SQLpro** · 21/03/2011, 14h29

Effectivement le SELECT * est incorrecte du fait du GROUP BY. Faites SELECT 0 SELECT 1 ou SELECT NULL !

A +

**aieeeuuuuu** · 21/03/2011, 16h55

pour ma part (SqlServer 2008) cela fonctionne :

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
20
21
22
23
24
 
 
 
CREATE TABLE tbl_inscription(
	no_membre INT NOT NULL,
	no_groupe INT NOT NULL,
	CONSTRAINT PK_tbl_inscription PRIMARY KEY CLUSTERED (no_membre, no_groupe)
)
 
GO 
 
CREATE TRIGGER trigger_maximum_inscription
ON tbl_inscription
FOR INSERT AS
IF EXISTS(SELECT *
          FROM   tbl_inscription AS T
                 INNER JOIN inserted AS I
                       ON T.no_groupe = I.no_groupe
          GROUP BY I.no_groupe
          HAVING COUNT(*) > 4)
BEGIN
   RAISERROR('Le nombre de maximal(4) pour ce groupe est atteint', 16, 1);
   ROLLBACK;
END

Bizare...

Mais dans tous les cas, il faudrait plutot écrire le trigger comme ceci :

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
 
CREATE TRIGGER trigger_maximum_inscription
ON tbl_inscription
FOR INSERT AS
	IF EXISTS(
		SELECT *
		FROM inserted AS I
		WHERE (
			SELECT COUNT(*) 
			FROM tbl_inscription T 
			WHERE  T.no_groupe = I.no_groupe
		) > 4
	)
	BEGIN
		RAISERROR('Le nombre de maximal(4) pour ce groupe est atteint', 16, 1);
	ROLLBACK;
END

Afin de permettre les insertions multiples pour le même groupe, ce que le trigger avec jointure interdira pour plus de deux lignes...

**zoltix** · 26/04/2011, 16h35

Dans le même style j'ai un souci pour tester l'unicité d'un trigger
Exemple

J’ai une table avec Id, CC_Eu, CC_Us, on ne peut pas avoir de CC_EU dupliqué et CC_Us lorsqu’il y a un code.
Exemple dans la table (test)

Code :

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

1
2
3
4
5
6
ID	CC_EU	CC_US
1	EU_01	Null
2	EU_02	Null
3	Null	CC_01
4	Null	CC_03
5	EU_09	CC_14

Pour m’assurer, que les données sont bien uniques (CC_EU et CC_US) mais peuvent être dupliquer quand la valeur est à Null(Une cc peut ne pas exister US mais bien EU).

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
ALTER TRIGGER [dbo].[trigg_test]
ON dbo.Test 
FOR  INSERT
AS 	
	If (( Select count(*) 
           from inserted I inner join dbo.Test T on T on 
I. CC_EU = T. CC_EU)>0)
	Begin 
		RAISERROR ('CC_EU Already exists', 16, 1)
	End 
 
	If (( Select count(*) 
           from inserted I inner join dbo.Test T on T on 
I. CC_US = T. CC_US)>0)
	Begin 
		RAISERROR ('CC_US Already exists', 16, 1)
	End

Mais voilà, j’ai remarqué que dans la table dbo.Test la donnée existe déjà lorsque le trigger est appelé et donc avec mes conditions j’ai toujours une count(*)> 0(1). Et lorsque la donnée est dupliquée le count(*) est supérieur à 1. Donc j’ai changé dans le trigger les conditions en mettant >2. Mais est-ce correcte car je n’aime pas cette syntaxe.

Il n’y a pas une autre méthode plus jolie ou efficace....

Merci de votre aide

**aieeeuuuuu** · 26/04/2011, 17h03

Bonjour,

Si j'ai bien compris votre problème (CC_EU doit être NULL ou avoir une valeur unique, idem pour CC_US), il vous suffit d'un index unique filtrant les null sur chaque colonne

Code SQL :

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

1
2
3
4
 
CREATE UNIQUE NONCLUSTERED INDEX UIX_CCEU
ON test(CC_EU)
WHERE CC_EU IS NOT NULL

Code SQL :

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

1
2
3
4
 
CREATE UNIQUE NONCLUSTERED INDEX UIX_CCUS
ON test(CC_US)
WHERE CC_US IS NOT NULL

**SQLpro** · 26/04/2011, 17h06

http://blog.developpez.com/sqlpro/p5...norme-sql-iso/

Notamment index unique filtré !

A +

**zoltix** · 26/04/2011, 20h02

Encore une fois j’admire la pertinence de vos réponses..
Ps : si par hasard vous cherchez un boulot en Belgique ...... J'ai une bonne adresse pour vous ;-) ....

**Sergejack** · 28/04/2011, 14h56

Envoyé par aieeeuuuuu

Bonjour,

Si j'ai bien compris votre problème (CC_EU doit être NULL ou avoir une valeur unique, idem pour CC_US), il vous suffit d'un index unique filtrant les null sur chaque colonne

Code SQL :

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

1
2
3
4
 
CREATE UNIQUE NONCLUSTERED INDEX UIX_CCEU
ON test(CC_EU)
WHERE CC_EU IS NOT NULL

Code SQL :

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

1
2
3
4
 
CREATE UNIQUE NONCLUSTERED INDEX UIX_CCUS
ON test(CC_US)
WHERE CC_US IS NOT NULL

Elle est sympa la version 2008 ^^

Trigger qui vérifie inscription à un groupe

Développement SQL Server

Discussions similaires

Partager

Partager