none
[TRIGGER][SQL server 2008] Est ce qu'il y a une meilleure façon de faire ? RRS feed

  • Question

  • Bonjour,

    J'avais posté  une question pour les containtes d'une règle et on m'a recommandé de faire un trigger car la contrainte check ne pouvait pas répondre à la règle.

    voici le code fait sur le trigger (il passe en parse mais je ne l'ai pas encore executé).

    Je recupère donc la taille dans mon program_len et je la compare à la taille de ma propriété hc_anum lors d'une insertion ou d'un update

     

    CREATE TRIGGER tr_verif_anum
    ON PROGRAM_HC FOR INSERT, UPDATE
    as
    Declare @anumInUse int, @programLen int, @ErrMsg VARCHAR(128)
    SELECT @anumInUse=HC_ANUM, @programLen=PROGRAM_LEN
    		FROM PROGRAM P
    		INNER JOIN INSERTED I
    		ON P.PROGRAM_ID=I.PROGRAM_ID
    		WHERE P.PROGRAM_ID=PROFRAM_ID
    IF LEN(@anumInUse) > @programLen
    	BEGIN
    		SET @ErrMsg = 'Erreur sur la taille du numéro de machine!'
    	 ROLLBACK TRANSACTION
    	 RETURN RAISERROR (@ErrMsg,16,1)
    	END
    ELSE
    	COMMIT TRANSACTION
    	RETURN
    
    

    Est ce bon ?

    Je me demandais si c'était pas mieux de créer une procédure qui récupère récupère en paramètre une mon program_id et qui me retourne la taille du champs et j'ajoute à la table program_hc une contrainte check du style

    ALTER TABLE PROGRAM_HC
    ADD CONSTRAINT ckc
    CHECK (LEN(HC_ANUM)<=maprocedure(program_id))
    
    

    Est ce faisable ?

    Car j'ai lu un tutoriel de Sqlpro disant que c'était moins couteux qu'un trigger ?

     

    Merci beaucoup d'avance pour votre aide!

    mercredi 13 octobre 2010 08:42

Réponses

  • Le trigger est appelé une seule fois pour toutes les lignes concernées par un même UPDATE ou INSERT. La pseudo table "inserted" peut donc potentiellement contenir plusieurs lignes.

    Si c'est le cas, le code original récupère dans un seul couple de variables les informations lues sur de multiples lignes. Mais ces variables ne vont finalement retenir que les infos qui correspondent à la dernière ligne lue. Il est donc possible que des lignes erronées soient acceptées si la dernière ligne est correcte.

    On se retrouve un peu dans la situation suivante :

    create table t(data char(1))
    go
    insert into t values ('A')
    insert into t values ('B')
    go
    declare @c char(1)
    select @c=data from t
    if @c='A' print 'Erreur' else print 'Ok'
    

    Il est presque sûr que @c contiendra la lettre B. On va donc accepter les données car le test ne porte que sur la dernière ligne lue.

    Dans l'autre cas, j'utilise EXISTS pour voir si il existe au moins une ligne qui ne répond pas à la condition imposée et j'annule la transaction dans ce cas...


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    • Marqué comme réponse Olivierrrrrrr vendredi 15 octobre 2010 16:23
    jeudi 14 octobre 2010 13:36

Toutes les réponses

  • Bonjour,

    Le trigger peut porter sur plusieurs lignes si plusieurs lignes sont affectées par un UPDATE ou un INSERT. Il faudrait donc plutôt voir si au moins une ligne est erronée et dans ce cas annuler la transaction.

    IF EXISTS(
    SELECT 1 
      FROM PROGRAM P
    INNER JOIN INSERTED I
    ON P.PROGRAM_ID=I.PROGRAM_ID
    WHERE P.PROGRAM_ID=PROFRAM_ID AND LEN(HC_ANUM)>PROGRAM_LEN)
     BEGIN
    SET @ErrMsg = 'Erreur sur la taille du numéro de machine!'
    ROLLBACK TRANSACTION
    RETURN RAISERROR (@ErrMsg,16,1)
    END

    Sinon, difficile à dire sans contexte. J'imagine que l'idée du "coût" est peut-être lié au fait qu'un trigger sera exécuté systématiquement tandis qu'une procédure stockée peut-être invoquée ou non selon les besoins.

    De toute façon je doute que cela marche. L'idée générale est qu'une contrainte check est limitée à des tests de base qui notamment ne concernent que la ligne en cours. A mon avis il n'est pas possible d'appeler non plus une procédure dans une contrainte check.


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    mercredi 13 octobre 2010 11:23
  • Pourrais tu m'expliquer la différence entre les deux codes ?

    "Il faudrait donc plutôt voir si au moins une ligne est erronée et dans ce cas annuler la transaction."

    Qu'est ce que faisait mon trigger ? Car pour moi il faisait ça ... (Je suis débutant en Trigger, procédure stockée..)


    Cordialement, Albertini Olivier http://cv.albertini-olivier.com
    mercredi 13 octobre 2010 12:48
  • Le trigger est appelé une seule fois pour toutes les lignes concernées par un même UPDATE ou INSERT. La pseudo table "inserted" peut donc potentiellement contenir plusieurs lignes.

    Si c'est le cas, le code original récupère dans un seul couple de variables les informations lues sur de multiples lignes. Mais ces variables ne vont finalement retenir que les infos qui correspondent à la dernière ligne lue. Il est donc possible que des lignes erronées soient acceptées si la dernière ligne est correcte.

    On se retrouve un peu dans la situation suivante :

    create table t(data char(1))
    go
    insert into t values ('A')
    insert into t values ('B')
    go
    declare @c char(1)
    select @c=data from t
    if @c='A' print 'Erreur' else print 'Ok'
    

    Il est presque sûr que @c contiendra la lettre B. On va donc accepter les données car le test ne porte que sur la dernière ligne lue.

    Dans l'autre cas, j'utilise EXISTS pour voir si il existe au moins une ligne qui ne répond pas à la condition imposée et j'annule la transaction dans ce cas...


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    • Marqué comme réponse Olivierrrrrrr vendredi 15 octobre 2010 16:23
    jeudi 14 octobre 2010 13:36
  • Ok donc si j'ai compris c'est le IF EXISTS qui permet de vérifier pour chaque ligne insérée ou mise à jour ?
    Cordialement, Albertini Olivier http://cv.albertini-olivier.com
    jeudi 14 octobre 2010 21:15
  • EXISTS retourne vrai si la requête retourne au moins une ligne. Voir http://msdn.microsoft.com/fr-fr/library/ms188336.aspx.
    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    vendredi 15 octobre 2010 10:50
  • Contrairement à ce qui vous a été dit, il est parfaitement possible de faire une contrainte CHECK complexe (vérifiant des données externes à la table), mais dans ce cas, il faut utiliser une UDF.

    Voir l'exemple donné ici :

    http://social.msdn.microsoft.com/Forums/fr-FR/sqlserverfr/thread/0618bf5b-cd0b-44d0-ada9-c268c595585e?prof=required&ppud=4

     

    A +

    Frédéric BROUARD, Spécialiste modélisation, SGBR relationnel, optimisation, langage SQL.
    Le site sur le langage SQL et les SGBD relationnels : http://sqlpro.developpez.com/
    Expert SQL Server http://www.sqlspot.com : audit, optimisation, tuning, formation
    Le blog sur SQL / MS SQL Server http://blog.developpez.com/sqlpro
    * * * * * Enseignant au CNAM PACA et à l'ISEN à Toulon * * * * *


    Frédéric BROUARD, Spécialiste modélisation, SGBR relationnel, optimisation, langage SQL * * * Le site sur le langage SQL et les SGBD relationnels : http://sqlpro.developpez.com/ * * * Expert SQL Server http://www.sqlspot.com : audit, optimisation, tuning, formation * * * Le blog sur SQL / MS SQL Server http://blog.developpez.com/sqlpro * * * Enseignant CNAM PACA, ISEN Toulon, CESI/EXIA Aix En Provence
    dimanche 17 octobre 2010 11:04