IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Développement SQL Server Discussion :

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


Sujet :

Développement SQL Server

  1. #1
    Futur Membre du Club
    Homme Profil pro
    Aménagement
    Inscrit en
    Mars 2011
    Messages
    8
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Tunisie

    Informations professionnelles :
    Activité : Aménagement
    Secteur : Administration - Collectivité locale

    Informations forums :
    Inscription : Mars 2011
    Messages : 8
    Points : 9
    Points
    9
    Par défaut 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 sql : Sélectionner tout - Visualiser dans une fenêtre à part
    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

  2. #2
    Modérateur

    Profil pro
    dba
    Inscrit en
    Janvier 2010
    Messages
    5 643
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : Janvier 2010
    Messages : 5 643
    Points : 13 092
    Points
    13 092
    Par défaut
    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 ?

  3. #3
    Futur Membre du Club
    Homme Profil pro
    Aménagement
    Inscrit en
    Mars 2011
    Messages
    8
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Tunisie

    Informations professionnelles :
    Activité : Aménagement
    Secteur : Administration - Collectivité locale

    Informations forums :
    Inscription : Mars 2011
    Messages : 8
    Points : 9
    Points
    9
    Par défaut
    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

  4. #4
    Modérateur

    Profil pro
    dba
    Inscrit en
    Janvier 2010
    Messages
    5 643
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : Janvier 2010
    Messages : 5 643
    Points : 13 092
    Points
    13 092
    Par défaut
    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 ?

  5. #5
    Futur Membre du Club
    Homme Profil pro
    Aménagement
    Inscrit en
    Mars 2011
    Messages
    8
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Tunisie

    Informations professionnelles :
    Activité : Aménagement
    Secteur : Administration - Collectivité locale

    Informations forums :
    Inscription : Mars 2011
    Messages : 8
    Points : 9
    Points
    9
    Par défaut
    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

  6. #6
    Modérateur

    Profil pro
    dba
    Inscrit en
    Janvier 2010
    Messages
    5 643
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : Janvier 2010
    Messages : 5 643
    Points : 13 092
    Points
    13 092
    Par défaut
    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 : 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
    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

  7. #7
    Modérateur

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Janvier 2005
    Messages
    5 826
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2005
    Messages : 5 826
    Points : 12 371
    Points
    12 371
    Par défaut
    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 : 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 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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : 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 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.

    @++

  8. #8
    Modérateur

    Profil pro
    dba
    Inscrit en
    Janvier 2010
    Messages
    5 643
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : Janvier 2010
    Messages : 5 643
    Points : 13 092
    Points
    13 092
    Par défaut
    Citation Envoyé par elsuket Voir le message
    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

  9. #9
    Modérateur

    Profil pro
    dba
    Inscrit en
    Janvier 2010
    Messages
    5 643
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : Janvier 2010
    Messages : 5 643
    Points : 13 092
    Points
    13 092
    Par défaut
    Citation Envoyé par elsuket Voir le message
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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...

  10. #10
    Modérateur

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Janvier 2005
    Messages
    5 826
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2005
    Messages : 5 826
    Points : 12 371
    Points
    12 371
    Par défaut
    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 ?

    @++

  11. #11
    Modérateur

    Profil pro
    dba
    Inscrit en
    Janvier 2010
    Messages
    5 643
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : Janvier 2010
    Messages : 5 643
    Points : 13 092
    Points
    13 092
    Par défaut
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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
    	)

  12. #12
    Modérateur

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Janvier 2005
    Messages
    5 826
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2005
    Messages : 5 826
    Points : 12 371
    Points
    12 371
    Par défaut
    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

    @++

  13. #13
    Modérateur

    Profil pro
    dba
    Inscrit en
    Janvier 2010
    Messages
    5 643
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : Janvier 2010
    Messages : 5 643
    Points : 13 092
    Points
    13 092
    Par défaut
    Citation Envoyé par elsuket Voir le message
    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

  14. #14
    Modérateur

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Janvier 2005
    Messages
    5 826
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2005
    Messages : 5 826
    Points : 12 371
    Points
    12 371
    Par défaut
    Effectivement, donc je prend mon zéro.
    'devais être un peu fatigué

    @++

  15. #15
    Futur Membre du Club
    Homme Profil pro
    Aménagement
    Inscrit en
    Mars 2011
    Messages
    8
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Tunisie

    Informations professionnelles :
    Activité : Aménagement
    Secteur : Administration - Collectivité locale

    Informations forums :
    Inscription : Mars 2011
    Messages : 8
    Points : 9
    Points
    9
    Par défaut
    bonjour
    merci à tous

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Réponses: 9
    Dernier message: 19/07/2011, 13h39
  2. Réponses: 5
    Dernier message: 17/07/2008, 12h40
  3. Réponses: 2
    Dernier message: 02/04/2008, 20h05
  4. Réponses: 2
    Dernier message: 23/11/2007, 12h33
  5. [XSD] grouper une contrainte sur 2 attributs
    Par MathiasM dans le forum Valider
    Réponses: 3
    Dernier message: 19/04/2006, 13h01

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo