Créer une contrainte sur un attribut en fonction d'un autre aattribut [Résolu]

[topographe]

Bonjour,

j'ai une table T1 avec 3 attributs

ID integer
Vocation integer
TYPE integer

valeurs possible de VOCATION : 1,2,3,4,5
valeurs possibles de TYPE : 1,2,3,4,5,6,7,8

La valeurs de TYPE dépend de la valeur de VOCATION. Exemple: si VOCATION = 1 alors TYPE ne peut être que 2 ou 3 ou 5 ou 7 ou 8. SI vocation = 2 alors TYPE ne peut être que 3 ou 4. etc.

j'utilise sqlserver2008

je veux crée une contrainte sur TYPE de façon qu'il n'accepte que les valeurs appropriées à sa vocation.

j'ai créé le Trigger suivant mais il n'a rien donné.

Code sql :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 (Transact sql)
 
CREATE TRIGGER [dbo].[tg_T1]
ON [dbo].[T1]
after INSERT, UPDATE
AS
DECLARE @VOCATION int
DECLARE @TYPE int
BEGIN
IF 
@VOCATION =1 
SET @TYPE = 2
END
GO

Commande(s) réussie(s).

mais dans la table rien n'a changé.

une idée s'il vous plait

Merci

[aieeeuuuuu]

Bonjour

C'est normal, un trigger se déclenche lorsqu'une des commandes pour lesquelles il a été spécifié est lancée. dans votre cas, le trigger se déclenchera après un INSERT ou un UPDATE

De plus, vous déclarez des variables qui ne servent a rien, et qui ne sont pas valuées. votre trigger ne fait donc rien, car la condition n'est jamais avérée.

Mais...
Que voulez vous faire ?
vérifier lors des ordres de manipulation de données que les données insérées ou modifiées sont valides, ou voulez-vous modifier les données présentes dans votre table afin de les valider ?

[topographe]

bonjour aieeeuuuuu

Merci pour votre réponse

en effet je veux vérifier lors des ordres de manipulation de données que les données insérées ou modifiées sont valides

une idée SVP

Merci

[aieeeuuuuu]

alors, il faut modifier votre trigger pour travailler sur la pseudo table INSERTED, qui contient vos données insérées ou modifiées.

mais que voulez vous faire des données non valides, les corriger, ne pas le inserer de facon silencieuse, ou générer une erreur ?

que voulez vous faire également si dans un jeu de données, certaines sont valides et pas d'autres ?

je veux crée une contrainte sur TYPE de façon qu'il n'accepte que les valeurs appropriées à sa vocation.

plutôt qu'un trigger, pourquoi ne pas créer justement une contrainte check ?

Comment sont définies votre règles fonctionnelles ? avez vous des tables qui contiennent les valeurs de type acceptées en fonction des valeurs de VOCATION ?

[topographe]

bonjour aieeeuuuuu

il faut modifier votre trigger pour travailler sur la pseudo table INSERTED, qui contient vos données insérées ou modifiées.

oui j'ai modifié mon trigger en travaillant sur INSERTED.

mais que voulez vous faire des données non valides, les corriger, ne pas le inserer de facon silencieuse, ou générer une erreur ?

ne pas les insérer et générer une erreur.

"que voulez vous faire également si dans un jeu de données, certaines sont valides et pas d'autres ?

ne pas accepter le jeu de données

plutôt qu'un trigger, pourquoi ne pas créer justement une contrainte check ?

la contrainte check vérifie les conditions après la fin de la mise à jour c-à-d lors de l'enregistrement des modifications, par contre on cherche que la vérification ce fait en temps réel c-à-d que la modification d'un autre jeu de données n'est autorisé que si tous les jeux de données (lignes ou enregistrement) modifiées (c-à-d chaque enregistrement ayant au moins une valeur not null) sont valides selon les conditions construites.

Comment sont définies votre règles fonctionnelles ? avez vous des tables qui contiennent les valeurs de type acceptées en fonction des valeurs de VOCATION ?

oui j'ai une table qui contient les valeurs acceptées.

Merci infiniment

[aieeeuuuuu]

Bonjour,

Avec un contrainte check, vous ne pourrez pas de toute façon faire de requete sur la table contenant les valeurs acceptées. C'est donc bien un trigger qu'il vous faut.
(Vous pourriez aussi interdire les INSERT/UPDATE sur la table et passer obligatoirement par des procédures stockées qui effectuent les vérifications avant d’insérer les données)

voici un exemple pour le trigger.
en considerant les regles suivantes :
Si a vaut 1, alors b doit valoir 1,2 ou 3
Si a vaut 2, alors b doit valoir 3 ou 4

Code SQL :

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
50
 
 
CREATE TABLE MaTable(
	a int NOT NULL,
	b int NOT NULL
)
 
GO
 
CREATE TABLE regles(
	a int NOT NULL,
	b int NOT NULL
)
 
GO
--insertion des valeurs acceptée
INSERT INTO regles (a,b) VALUES
(1,1),
(1,2),
(1,3),
(2,3),
(2,4)
 
GO
 
CREATE TRIGGER trg_MaTable 
ON MaTable
AFTER INSERT, UPDATE
AS
BEGIN
	IF(EXISTS(
		SELECT *
		FROM INSERTED I
		LEFT OUTER JOIN regles R
			ON I.a = R.a
			AND I.b = R.b
		WHERE R.a IS NULL
		)
	)
	BEGIN
		RAISERROR('Valeurs incorrectes', 10, 0)
		ROLLBACK
	END
END
 
GO
--test du trigger :
INSERT INTO MaTable(a,b) VALUES
(1,2), --ok
(1,4)  --ko

Je vous laisse adapter à votre modèle et à vos règles

[elsuket]

Bonjour,

On peut tout à fait implémenter une contrainte de domaine (CHECK), mais il fait dans le cas présent l'aide d'une fonction scalaire.
On peut faire de même avec les contraintes de valeur par défaut (DEFAULT).

Sans table, on peut écrire :

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
CREATE FUNCTION ufn_verifie_regle
	(
		@_vocation tinyint
		, @_type tinyint
	)
RETURNS bit
WITH SCHEMABINDING
AS
BEGIN
	DECLARE @ok bit
 
	SELECT	@ok = CASE @_vocation
				WHEN 1 THEN CASE
						WHEN @_type IN (2, 3, 5, 7, 8) THEN 1 
						ELSE 0
					END
				WHEN 2 THEN CASE
						WHEN @_type IN (3, 4) THEN 1 
						ELSE 0
					END
				ELSE 0
			END
	RETURN	@ok
END

Mais à chaque fois que vous aurez une nouvelle "règle", il faudra supprimer la contrainte, mettre à jour la fonction et la re-créer, ce qui, en plus d'être peu maintenable, peut causer des problèmes de consistance de données.

On peut aussi se servir de l'exemple d'aieeeuuuuu, avec la table suivante :

Code :

1
2
3
4
5
6
CREATE TABLE vocation_type_regle
(
	vocation tinyint NOT NULL
	, type tinyint NOT NULL
	, CONSTRAINT PKvocation_type_regle PRIMARY KEY (vocation, type)
)

Et la fonction qui 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
CREATE FUNCTION ufn_verifie_regle
	(
		@_vocation tinyint
		, @_type tinyint
	)
RETURNS bit
WITH SCHEMABINDING
AS
BEGIN
	DECLARE @ok bit = 0
 
	IF EXISTS
	(
		SELECT	1 -- on ne peut pas utiliser * ici car la fonction est liée au schéma
		FROM	dbo.vocation_type_regle
		WHERE	vocation = @_vocation
		AND	type = @_type
	)
	BEGIN
		SET @ok = 1
	END
 
	RETURN @ok
END

De cette façon vous n'avez qu'à ajouter ou supprimer des "règles" dans la table dbo.vocation_type_regle.

L'avantage sur le trigger est que la contrainte de domaine est vérifiée en amont, donc la transaction n'est pas exécutée si la règle n'est pas vérifiée.
En revanche avec le trigger, on exécute d'abord la transaction, puis on vérifie que les données insérées sont correctes.
Dans le cas où elle ne le sont pas, on annule la transaction, ce qui implique un parcours du fichier du journal des transactions en sens inverse de la séquence : c'est donc bien plus long que la vérification de la contrainte de domaine

Par ailleurs, le code du trigger tg_T1 n'est pas ensembliste, donc il est faux.

@++

[aieeeuuuuu]

Envoyé par elsuket

Bonjour,

On peut tout à fait implémenter une contrainte de domaine (CHECK), mais il fait dans le cas présent l'aide d'une fonction scalaire.
On peut faire de même avec les contraintes de valeur par défaut (DEFAULT).

tout à fait !

bien vu

[aieeeuuuuu]

Envoyé par elsuket

Sans table, on peut écrire : [...]

En fait sans table, on peut même se passer de fonction pour le coup et écrire directement

Code SQL :

1
2
3
4
5
6
7
8
9
 
ALTER TABLE MaTable
ADD  constraint ck_MaTable CHECK (
	a = CASE
			WHEN b IN (1,2,3) THEN 1
			WHEN b IN (3,4) THEN 2
			ELSE a - 1		
		END
	)

mais cela reste plus difficile à maintenir qu'avec une table de règles...

[elsuket]

mais cela reste plus difficile à maintenir qu'avec une table de règles...

Entièrement d'accord
Je vois que tu fais un calcul avec a dans la contrainte CHECK, ce que je crois ne pas être possible.
Étais-tu en train de penser à une colonne calculée implémentée par une fonction scalaire ?

@++

[aieeeuuuuu]

Si c'est possible, en plaçant la contrainte au niveau de la table, et non au niveau de la colonne b.

Par contre je ne suis pas très fier de mon ELSE a - 1 pour interdire toutes les autres valeurs.

ça serait peut etre plus propre comme ça :

Code SQL :

1
2
3
4
5
6
7
8
9
10
11
12
13
 
ALTER TABLE MaTable
ADD  constraint ck_MaTable CHECK (
	1 = CASE
			WHEN 
				a = 1 
				AND b IN (1,2,3) THEN 1
			WHEN 
				a= 2 
				AND b IN (3,4) THEN 1
			ELSE 0	
		END
	)

[elsuket]

Je n'ai pas écrit que ce n'est pas possible

En revanche ton second script pour la contrainte constraint ck_MaTable me semble tout à fait correct.
Le premier ne contient pas de prédicat

@++

[aieeeuuuuu]

Envoyé par elsuket

Je n'ai pas écrit que ce n'est pas possible

En revanche ton second script pour la contrainte constraint ck_MaTable me semble tout à fait correct.
Le premier ne contient pas de prédicat

@++

bah dans le premier, je vérifie la valeurs de a en fonction de la valeurs de b

dans le deuxième, je fais de même, mais d'une autre façon

[elsuket]

Effectivement, donc je prend mon zéro.
'devais être un peu fatigué

@++

[topographe]

bonjour
merci à tous