Créer une contrainte sur un attribut en fonction d'un autre aattribut

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:

---

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

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 ?

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

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 ?

Citation:

---

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 ?

bonjour aieeeuuuuu

Citation:

---

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.

Citation:

---

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.

Citation:

---

"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

Citation:

---

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.

Citation:

---

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

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:

---

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

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.

@++ ;)

Citation:

---

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 :ccool:

Citation:

---

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:

---

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

Citation:

---

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 ?

@++ ;)

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:

---

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 )

---

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

@++ ;)

Citation:

---

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

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

@++ ;)

bonjour
merci à tous