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

Schéma Discussion :

Gestion de blindtests; comment faire ?


Sujet :

Schéma

  1. #41
    Expert éminent sénior
    Citation Envoyé par Mike888 Voir le message

    si je résume, voici les corrections à apporter par rapport à tout ce qui a été posté dans les messages #18 à #21


    La structure de la table BLINDTEST est effectivement à modifier comme vous l’avez fait :

    CREATE TABLE BLINDTEST 
    (
            blindtestId      INT  IDENTITY      NOT NULL
          , Numero           VARCHAR(8)         NOT NULL
          , animateurId      INT                NOT NULL 
        , CONSTRAINT BLINDTEST_PK PRIMARY KEY (blindtestId)
        , CONSTRAINT BLINDTEST_AK UNIQUE (Numero)
        , CONSTRAINT BLINDTEST_ANIMATEUR_FK FOREIGN KEY (animateurId)
              REFERENCES ANIMATEUR (animateurId)
    ) ;
    
    Mais les vues et les triggers sont aussi à mettre à niveau car blindtestCode y est présent, donc à remplacer par Numero.

    A cette occasion, je joins le code où la paire {artiste, titre} devient clé alternative, tandis que disparaît l’attribut extraitCode.

    Création de la table EXTRAIT

    
    DROP TABLE IF EXISTS EXTRAIT ;
    
    GO
    
    CREATE TABLE EXTRAIT 
    (
            extraitId        INT  IDENTITY      NOT NULL 
          , artiste          VARCHAR(48)        NOT NULL
          , titre            VARCHAR(48)        NOT NULL
        , CONSTRAINT EXTRAIT_PK PRIMARY KEY (extraitId)
        , CONSTRAINT EXTRAIT_AK UNIQUE (artiste, titre)
    ) ;
    

    Création des vues PARTICIPER_V et COMPOSER_V

    DROP VIEW IF EXISTS PARTICIPER_V ;
    
    GO
    
    CREATE VIEW PARTICIPER_V (pseudo, Numero)
    AS
        SELECT  pseudo, Numero
        FROM    PARTICIPER as x 
           JOIN BLINDEUR as y ON x.joueurId = y.blindeurId  -- pour avoir le pseudo
           JOIN BLINDTEST as z ON x.blindtestId = z.blindtestId  -- pour avoir le Numero
    ;
    GO
    
    DROP VIEW IF EXISTS COMPOSER_V ;
    
    GO
    
    CREATE VIEW COMPOSER_V (Numero, artiste, titre)
    AS
        SELECT  Numero, artiste, titre
        FROM    COMPOSER as x 
           JOIN BLINDTEST as y ON x.blindtestId = y.blindtestId  -- pour avoir le Numero
           JOIN EXTRAIT as z ON x.extraitId = z.extraitId  -- pour avoir l'artiste et le titre
    ;
    GO


    Création du trigger PARTICIPER_TR sur la vue PARTICIPER_V :

    DROP TRIGGER IF EXISTS PARTICIPER_TR ;
    GO
     CREATE TRIGGER PARTICIPER_TR ON PARTICIPER_V INSTEAD OF INSERT, UPDATE
    AS
    
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @n as INT ;
    DECLARE @inserted TABLE
    (
        joueurId     INT
      , blindtestId  INT
    ) ;
    DECLARE @deleted TABLE
    (
        joueurId     INT
      , blindtestId  INT
    ) ;
    ---- pour debug ---- select '' as INSERTED, * from INSERTED  
    ---- pour debug ---- select '' as DELETED, * from DELETED  
    
    INSERT INTO @inserted (joueurId, blindtestId)
        SELECT DISTINCT y.blindeurId, z.blindtestId
        FROM   INSERTED AS x
          JOIN BLINDEUR AS y ON x.pseudo = y.pseudo
          JOIN BLINDTEST AS z ON x.Numero = z.Numero
    ;
    ---- pour debug ---- select '' as '@inserted', * from @inserted 
    
    -------------------------------------------------------
    -- comptage des viols de la contrainte d'exclusion
    -------------------------------------------------------
    SET @n = 
        (SELECT COUNT(*) 
         FROM   @inserted AS x
                  JOIN BLINDTEST AS y  
                       ON x.blindtestId = y.blindtestId
                       AND x.joueurId = y.animateurId)
    
    ---- pour debug ---- select @n as '@n' 
    
    ----------------------------------------------------
    -- Si on constate une tentative de viol de la
    -- contrainte d'exclusion, on rouspète 
    -- et on s'en va.
    ----------------------------------------------------
    IF @n > 0
        BEGIN
            SET @Engueulade = 'Un blinder ne peut pas participer au blindtest qu''il anime !'
            SELECT @Engueulade AS Engueulons  --, * FROM INSERTED
            RAISERROR (@Engueulade, 0,1)  -- state = 0 pour les tests
     ----        RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
            RETURN  
        END
    ----------------------------------------------------
    -- On n'a pas constaté d'anomalie : 
    -- on met la table PARTICIPER à jour.
    ----------------------------------------------------
    
    ---------------------------------------------------
    -- S’il y a quelque chose dans DELETED
    -- alors c’est qu’un UPDATE est en cours,
    -- on commence par faire le ménage dans COMPOSER.
    ---------------------------------------------------
    
    IF (select COUNT(*) FROM DELETED) > 0
        BEGIN 
            INSERT INTO @deleted (joueurId, blindtestId)
                SELECT DISTINCT y.blindeurId, z.blindtestId
                FROM   DELETED AS x
                  JOIN BLINDEUR AS y ON x.pseudo = y.pseudo
                  JOIN BLINDTEST AS z ON x.Numero = z.Numero
            ;
    ---- pour debug ---- select '' as '@deleted' , * from @deleted
    ---- pour debug ---- select '' as 'participer avant delete', * from PARTICIPER
    
            DELETE FROM PARTICIPER
                WHERE joueurId IN (SELECT joueurId FROM @deleted)
                  AND blindtestId IN (SELECT blindtestId FROM @deleted) 
    
    ---- pour debug ---- select '' as 'participer après delete', * from PARTICIPER
        END
    
    ---------------------------------
    -- On est en INSERT ou UPDATE
    ---------------------------------
    
    INSERT INTO PARTICIPER (joueurId, blindtestId)
        SELECT joueurId, blindtestId 
        FROM @inserted
    ;
    ---- pour debug ----  select '' as 'participer après insert', * from PARTICIPER
    
    GO
    

    Création du trigger COMPOSER_TR sur la vue COMPOSER_V :

    DROP TRIGGER IF EXISTS COMPOSER_TR ;
    GO
    
    CREATE TRIGGER COMPOSER_TR ON COMPOSER_V INSTEAD OF INSERT, UPDATE
    AS
    
    DECLARE @theTrigger as VARCHAR(64) = 'COMPOSER_TR - '
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @nbDeleted as INT ;
    DECLARE @NumeroKount as INT ;
    DECLARE @extraitCodeKount as INT ;
    
    DECLARE @t1 as TABLE 
    (
        Numero varchar(48)
      , artiste varchar(48)
      , titre varchar(48)
    )
    
    DECLARE @t2 as TABLE 
    (
        Numero varchar(48)
    )
    
    DECLARE @t3 as TABLE 
    (
        Numero varchar(48)
      , artiste varchar(48)
      , titre varchar(48)
    )
    
    -----------------------------------------------------------
    -- tables temporaires pour les clés primaires
    -----------------------------------------------------------
    
    DECLARE @inserted as TABLE
    (
        blindtestId  INT
      , extraitId    INT
    ) ;
    DECLARE @deleted as TABLE
    (
        blindtestId  INT
      , extraitId    INT
    ) ;
    ---- pour debug ---- select '' as INSERTED, * from INSERTED  
    ---- pour debug ---- select '' as DELETED, * from DELETED  
    
    -------------------------------------------------------
    -- S’il y a quelque chose dans DELETED
    -- alors c’est qu’on effectue un UPDATE,
    -- on commence par effectuer quelques contrôles.
    -------------------------------------------------------
    
    SET @nbDeleted = (SELECT COUNT(*) FROM DELETED)
    
    IF @nbDeleted > 0
        BEGIN
            ---------------------------------------------------
            -- si un Numero est candidat au
            -- remplacement d'un autre Numero,
            -- il doit être connu dans la table BLINDTEST
            ---------------------------------------------------
    
            ---------------------------------------
            -- Les codes blintests inconnus
            ---------------------------------------
    
            INSERT INTO @t1 (Numero, artiste, titre)
                SELECT DISTINCT Numero, artiste, titre 
                FROM   INSERTED AS x
                WHERE  NOT EXISTS (SELECT Numero
                                   FROM   BLINDTEST AS y
                                   WHERE  x.Numero = y.Numero)
            ;
            INSERT INTO @t2 (Numero) 
                SELECT DISTINCT Numero FROM @t1 ;
    
            SET @NumeroKount = (SELECT COUNT(*) FROM @t2)
    
            IF @NumeroKount > 0
                BEGIN
                    IF @NumeroKount = 1
                        BEGIN
                            SET @Engueulade 
                              = @theTrigger + 'update composition - '
                              + 'blindtest nouveau : inconnu.' 
                        END
                    ELSE
                        BEGIN
                           SET @Engueulade 
                             = @theTrigger + 'update composition - '
                             + 'Il y a '
                             + CAST(@NumeroKount as VARCHAR(5)) 
                             + ' blindtests inconnus.'
                        END
                    SELECT @Engueulade AS Engueulons
                         , Numero as 'Numero (nouveau mais inconnu)'
                         , artiste, titre 
                    FROM @t1
                    RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
        ----        RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
                    RETURN  
              END
            ---------------------------------------------------
            -- si une paire  {artiste, titre} est candidate au
            -- remplacement d'une autre paire,
            -- elle doit être connue dans la table EXTRAIT
            ---------------------------------------------------
    
            -----------------------------------------
            -- Les paires {artiste, titre} inconnues
            -----------------------------------------
    
            INSERT INTO @t1 (Numero, artiste, titre)
                SELECT DISTINCT Numero, artiste, titre 
                FROM   INSERTED AS x
                WHERE  NOT EXISTS (SELECT artiste, titre
                                   FROM   EXTRAIT AS y
                                   WHERE  x.artiste = y.artiste 
                                      AND x.titre = y.titre)
            ;
            INSERT INTO @t3 (Numero, artiste, titre) 
                SELECT DISTINCT Numero, artiste, titre FROM @t1 ;
    
            SET @extraitCodeKount = (SELECT COUNT(*) FROM @t3)
    
            IF @extraitCodeKount > 0
                BEGIN
                    IF @extraitCodeKount = 1
                        BEGIN
                            SET @Engueulade 
                              = @theTrigger + 'update composition - '
                              + 'Paire nouvelle {artiste, titre} : inconnue.' 
                        END
                    ELSE
                        BEGIN
                           SET @Engueulade 
                             = @theTrigger + 'update composition - '
                             + 'Il y a ' + cast(@NumeroKount as varchar(5)) 
                             + ' paire nouvelle {artiste, titre}  inconnues.'
                        END
                    SELECT @Engueulade AS Engueulons
     --                    , artiste + ' ' + titre as 'paire (nouvelle mais inconnue)'
                           , Numero, artiste, titre FROM @t3
                    RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
        ----        RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
                    RETURN  
              END
    
        END
    
    ------------------------------------------------------
    -- traduction des clés alternatives (naturelles) en
    -- clés primaires (artificielles)
    ------------------------------------------------------
    INSERT INTO @inserted (blindtestId, extraitId)
        SELECT DISTINCT y.blindtestId, z.extraitId
        FROM   INSERTED AS x
          JOIN BLINDTEST AS y ON x.Numero = y.Numero
          JOIN EXTRAIT AS z ON x.artiste = z.artiste
                           AND x.titre = z.titre
    ;
    ---- pour debug ---- select '' as '@inserted', * from @inserted 
    
    ---------------------------------------------------
    -- S’il y a quelque chose dans DELETED
    -- alors c’est qu’on effectue un UPDATE,
    -- on commence par faire le ménage dans COMPOSER.
    ---------------------------------------------------
    IF (SELECT COUNT(*) FROM DELETED) > 0
        BEGIN
            INSERT INTO @deleted (blindtestId, extraitId)
                SELECT DISTINCT y.blindtestId, z.extraitId
                FROM   DELETED AS x
                  JOIN BLINDTEST AS y ON x.Numero = y.Numero
                  JOIN EXTRAIT AS z ON x.artiste = z.artiste
                                   AND x.titre = z.titre
            ;
    ---- pour debug ---- select '' as '@deleted' , * from @deleted
    
            DELETE FROM COMPOSER
                WHERE blindtestId IN (SELECT blindtestId FROM @deleted)
                  AND extraitId IN (SELECT extraitId FROM @deleted)  
           ;
    
    ---- pour debug ---- select '' as 'composer après delete', * from COMPOSER
    
        END
    
    ---------------------------------
    -- On est en INSERT ou UPDATE :
    -- on charge.
    ---------------------------------
    INSERT INTO COMPOSER (blindtestId, extraitId)
        SELECT blindtestId, extraitId 
        FROM @inserted
    ;
    
    ---- pour debug ---- select '' as 'composer après insert', * from COMPOSER
    
    GO
    Un bout de jeu d’essai

    DELETE FROM TROUVER ;
    DELETE FROM PARTICIPER ;
    DELETE FROM COMPOSER ;
    DELETE FROM EXTRAIT ;
    DELETE FROM BLINDTEST ;
    DELETE FROM BLINDEUR ;
    
    GO
    
    ----------------------------------------------
    -- blindeurs
    ----------------------------------------------
    INSERT INTO BLINDEUR (pseudo) VALUES
        ('fernand'), ('raoul'), ('paul'), ('mado')
      , ('folace'), ('jean'), ('antoine'), ('patricia') 
    ;
    SELECT pseudo from BLINDEUR
    ;
    ----------------------------------------
    -- animateurs
    ----------------------------------------
    INSERT INTO ANIMATEUR (animateurId) 
        SELECT blindeurId FROM BLINDEUR WHERE pseudo IN ('fernand', 'raoul', 'paul', 'mado')
    ;
    SELECT pseudo AS Animateur 
    FROM ANIMATEUR AS x
    JOIN BLINDEUR AS y on x.animateurId = y.blindeurId
    ;
    ----------------------------------------
    -- joueurs
    ----------------------------------------
    INSERT INTO JOUEUR (joueurId)
        SELECT blindeurId 
        FROM BLINDEUR 
        WHERE pseudo IN ('fernand', 'mado', 'folace', 'jean', 'antoine', 'patricia')
    ;
    ------------------------------
    -- pour voir
    ------------------------------
    SELECT pseudo AS Joueur 
    FROM JOUEUR AS x
    JOIN BLINDEUR AS y on x.joueurId = y.blindeurId
    ;
    ------------------------------------------------
    -- Les blindtests
    ------------------------------------------------
    INSERT INTO BLINDTEST (Numero, animateurId)
        SELECT 'blindt01', blindeurId
        FROM    BLINDEUR
        WHERE pseudo = 'fernand'    
    ;
    INSERT INTO BLINDTEST (Numero, animateurId)
        SELECT 'blindt02', blindeurId
        FROM    BLINDEUR
        WHERE pseudo = 'mado'    
    ;  
    INSERT INTO BLINDTEST (Numero, animateurId)
        SELECT 'blindt03', blindeurId
        FROM    BLINDEUR
        WHERE pseudo = 'fernand'    
    ;  
    INSERT INTO BLINDTEST (Numero, animateurId)
        SELECT 'blindt04', blindeurId
        FROM    BLINDEUR
        WHERE pseudo = 'fernand'    
    ;
    INSERT INTO BLINDTEST (Numero, animateurId)
        SELECT 'blindt05', blindeurId
        FROM    BLINDEUR
        WHERE pseudo = 'fernand'    
    ;
    
    ------------------------------
    -- pour voir
    ------------------------------  
    SELECT '' as blindtest, Numero, pseudo
    FROM   BLINDTEST AS x
      JOIN BLINDEUR AS y ON x.animateurId = y.blindeurId
    ;
    ---------------------------------------------------
    -- participation des joueurs aux blindtests
    ---------------------------------------------------
    INSERT INTO PARTICIPER_V (pseudo, Numero) VALUES 
        ('folace', 'blindt01') 
      , ('folace', 'blindt02') 
      , ('jean', 'blindt01') 
    ;
    -- Le blindeur fernand anime le blindtest blindt01 
    -- et ne pourra donc y participer, le trigger veillant au grain 
    INSERT INTO PARTICIPER_V (pseudo, Numero) VALUES 
        ('fernand', 'blindt01')
    ;
    -- Le blindeur mado a le droit de participer
    -- au blindtest blindt01,
    -- mais mado anime le  blindtest blindt02 
    -- et ne pourra donc y participer, le trigger veillant au grain. 
    INSERT INTO PARTICIPER_V (pseudo, Numero) VALUES 
        ('mado', 'blindt01')
    ;
    UPDATE PARTICIPER_V
        SET Numero = 'blindt02' WHERE pseudo = 'mado'
    ;
    -- pour jean, pas de problème
    update PARTICIPER_V
        SET Numero = 'blindt02' WHERE pseudo = 'jean'
    ;
    ------------------------------
    -- pour voir
    ------------------------------  
    SELECT '' as participer, Numero, pseudo 
    FROM PARTICIPER_V
    ;
    ---------------------------------
    -- des extraits
    ---------------------------------
    
    --delete from EXTRAIT  ;
    
    INSERT INTO EXTRAIT (artiste, titre) VALUES
        ('Animals', 'House of rising sun'), ('Brassens', 'Old Leon'), ('Corneille', 'Marquise')
      , ('Ferré', 'Le scaphandrier'), ('Brel', 'Le plat pays')
      , ('G. Brassens', 'Le vieux Léon')
    ;
    ------------------------------
    -- pour voir
    ------------------------------  
    SELECT artiste, titre , *
    FROM EXTRAIT ;
    ;
    -------------------------------------------
    -- compositions blindtests-extraits
    --------------------------------------------
    INSERT INTO COMPOSER_V (Numero, artiste, titre) VALUES
          ('blindt01','Brassens', 'Old Leon')
        , ('blindt01', 'Brel', 'Le plat pays')
        , ('blindt02', 'Brassens', 'Old Leon')
        , ('blindt02', 'Corneille', 'Marquise')
       ;
    ------------------------------
    -- pour voir
    ------------------------------  
    SELECT Numero, artiste, titre
    FROM   COMPOSER_V 
    ; 
    ----------------------------------------
    -- vue COMPOSER_V : updates
    ----------------------------------------
    
    -- tentative anomalie
    
    UPDATE COMPOSER_V 
       SET Numero  = '$lindt03'
       WHERE Numero = 'blindt01' 
       AND artiste = 'Brel' AND titre = 'Le plat pays'
    ;
    
    -- tentative anomalie
    
    UPDATE COMPOSER_V 
       SET Numero  = '$$' + right(Numero,6)
    ;
    
    -- correct
    
    UPDATE COMPOSER_V 
       SET Numero  = 'blindt03'
       WHERE artiste = 'Brel' AND titre = 'Le plat pays'
    ;
    
    -- anomalie
    
    UPDATE COMPOSER_V 
       set artiste  = 'Brassens', titre =  'Marquise'
         , Numero  = 'blindt04'
       WHERE artiste  = 'Corneille' AND titre =  'Marquise'
         AND Numero = 'blindt02'   
    ;
    
    -- correct
    
    INSERT INTO EXTRAIT (artiste, titre) VALUES
        ('Brassens', 'Marquise')
    ;
    UPDATE COMPOSER_V 
       set artiste  = 'Brassens', titre =  'Marquise'
         , Numero  = 'blindt05'
       WHERE artiste  = 'Corneille' AND titre =  'Marquise'
         AND Numero = 'blindt02'   
    ;
    
    -- anomalie
    --
    update composer_v set Numero = 'xx' where Numero = 'blindt01'
    
    ------------------------------
    -- pour voir
    ------------------------------  
    SELECT '' as 'COMPOSER après update'
         , Numero, x.artiste, x.titre
    FROM   COMPOSER_V as x
      JOIN EXTRAIT as y ON x.artiste = y.artiste 
                       AND x.titre = y.titre 
    ;
    
    

     
    Faites simple, mais pas plus simple ! (A. Einstein)
    E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    Je ne réponds pas aux questions techniques par MP. Les forums sont là pout ça.
    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench

  2. #42
    Membre régulier
    Merci beaucoup pour tout ce travail fourni !

  3. #43
    Expert éminent sénior
    Bonsoir Mike,


    Citation Envoyé par Mike888 Voir le message
    je vais avoir des milliers d'extraits à encoder et j'aimerais que ça soit le plus automatisé possible.


    Vous devriez donc en principe vous en sortir au moyen de la paire {artiste, titre}. Concernant la mise en relation de ces milliers d’extraits avec les blindtests, prévoyez-vous de faire les « branchements » un par un ?


     
    Faites simple, mais pas plus simple ! (A. Einstein)
    E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    Je ne réponds pas aux questions techniques par MP. Les forums sont là pout ça.
    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench

  4. #44
    Expert éminent sénior
    En complément :

    Les vues sont bien utiles pour les requêtes un peu copieuses, notamment en jointures...

    Ainsi, il peut être opportun d’en créer une pour la table TROUVER :

    DROP VIEW IF EXISTS TROUVER_V ;
    
    GO
    
    CREATE VIEW TROUVER_V (pseudo, Numero, artiste, titre)
    AS
        SELECT  pseudo, Numero, artiste, titre
        FROM    TROUVER as x 
           JOIN BLINDEUR as y ON x.joueurId = y.blindeurId  
           JOIN COMPOSER as z ON x.blindtestId = z.blindtestId 
                             AND x.extraitId = z.extraitId 
           JOIN BLINDTEST as t ON z.blindtestId = t.blindtestId
           JOIN EXTRAIT as u on z.extraitId = u.extraitId
    ;
    GO
    
    Sans cete vue, on arrive encore à maîtriser l’insertion de lignes dans la table TROUVER :

    -----------------------------------------------
    -- quelques participations supplémentaires
    -----------------------------------------------
    INSERT INTO PARTICIPER_V (pseudo, Numero) VALUES 
        ('folace', 'blindt05')
      , ('jean', 'blindt05')
    ;
    ------------------------------------------------------
    -- une ligne supplémentaire pour la table COMPOSER
    ------------------------------------------------------
    INSERT INTO COMPOSER_V (Numero, artiste, titre) VALUES
          ('blindt01','Animals', 'House of rising sun')
    ;
    -----------------------------------------------
    -- ils ont trouvé
    -----------------------------------------------
    
    INSERT INTO TROUVER (joueurId, blindtestId, extraitId) VALUES
          ((SELECT blindeurId FROM BLINDEUR WHERE pseudo = 'folace')
         , (SELECT blindtestId FROM BLINDTEST WHERE Numero = 'blindt05')
         , (SELECT extraitId FROM EXTRAIT WHERE artiste = 'Brassens' 
                                            AND titre = 'Marquise'))
    ;
    INSERT INTO TROUVER (joueurId, blindtestId, extraitId) VALUES
          ((SELECT blindeurId FROM BLINDEUR WHERE pseudo = 'jean')
         , (SELECT blindtestId FROM BLINDTEST WHERE Numero = 'blindt02')
         , (SELECT extraitId FROM EXTRAIT WHERE artiste = 'Brassens' 
                                            AND titre = 'Old Leon'))
    ;
    INSERT INTO TROUVER (joueurId, blindtestId, extraitId) VALUES
          ((SELECT blindeurId FROM BLINDEUR WHERE pseudo = 'jean')
         , (SELECT blindtestId FROM BLINDTEST WHERE Numero = 'blindt05')
         , (SELECT extraitId FROM EXTRAIT WHERE artiste = 'Brassens' 
                                            AND titre = 'Marquise'))
    ;
    INSERT INTO TROUVER (joueurId, blindtestId, extraitId) VALUES
          ((SELECT blindeurId FROM BLINDEUR WHERE pseudo = 'mado')
         , (SELECT blindtestId FROM BLINDTEST WHERE Numero = 'blindt01')
         , (SELECT extraitId FROM EXTRAIT WHERE artiste = 'Animals' 
                                            AND titre = 'House of rising sun'))
    ;
    
    Avec la vue, l’accès aux données est simplifié :

    SELECT * FROM TROUVER_V
    
    =>

    pseudo    Numero      artiste      titre
    mado      blindt01    Animals      House of rising sun
    folace    blindt05    Brassens     Marquise
    jean      blindt02    Brassens     Old Leon
    jean      blindt05    Brassens     Marquise
    

    Cela dit, si vous préférez que les inserts dans la table passent par la vue TROUVER_V, donc sous forme simplifiée :

    INSERT INTO TROUVER_V (pseudo, Numero, artiste, titre) VALUES
          ('mado', 'blindt01', 'Animals', 'House of rising sun')
        , ('folace', 'blindt05', 'Brassens', 'Marquise')
        , ('jean', 'blindt02', 'Brassens', 'Old Leon')
        , ('jean', 'blindt05', 'Brassens', 'Marquise')
    ;
    
    Alors pas de problème, on codera le trigger qui va bien


     
    Faites simple, mais pas plus simple ! (A. Einstein)
    E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    Je ne réponds pas aux questions techniques par MP. Les forums sont là pout ça.
    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench

  5. #45
    Membre régulier
    Citation Envoyé par fsmrel Voir le message
    Bonsoir Mike,
    Vous devriez donc en principe vous en sortir au moyen de la paire {artiste, titre}. Concernant la mise en relation de ces milliers d’extraits avec les blindtests, prévoyez-vous de faire les « branchements » un par un ?
    Qu'entendez-vous par branchement un par un ?

    Par rapport à votre dernier message, passer par la nouvelle vue me semble plus logique, non ?

  6. #46
    Expert éminent sénior
    Bonsoir Mike,


    Citation Envoyé par Mike888 Voir le message
    Qu'entendez-vous par branchement un par un ?
    Une fois que vous avez chargé les centaines (?) de blindtests et les milliers d’extraits, il s’agit de procéder à l’opération les associant (la table COMPOSER a pour vocation de contenir les associations résultant de cette opération) : branchement est ici synonyme d’association. Quand je dis « un par un », cela veut dire : pour chaque blindtest lui associer des extraits, opération qui peut être longue manuellement parlant, mais peut-être avez-vous un moyen de simplifier ça efficacement ?


    Citation Envoyé par Mike888 Voir le message
    Par rapport à votre dernier message, passer par la nouvelle vue me semble plus logique, non ?
    Certes, mais il faut prendre le temps de développer le trigger nécessaire pour effectuer les inserts ! Ça n’est pas bien compliqué (il est dans le style de COMPOSER__TR), mais il faut tester...


    En passant, pourriez-vous donner une définition du blindtest tel que vous le percevez ?

     
    Faites simple, mais pas plus simple ! (A. Einstein)
    E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    Je ne réponds pas aux questions techniques par MP. Les forums sont là pout ça.
    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench

  7. #47
    Membre régulier
    Citation Envoyé par fsmrel Voir le message
    Bonsoir Mike,
    Une fois que vous avez chargé les centaines (?) de blindtests et les milliers d’extraits, il s’agit de procéder à l’opération les associant (la table COMPOSER a pour vocation de contenir les associations résultant de cette opération) : branchement est ici synonyme d’association. Quand je dis « un par un », cela veut dire : pour chaque blindtest lui associer des extraits, opération qui peut être longue manuellement parlant, mais peut-être avez-vous un moyen de simplifier ça efficacement ?
    Pour le moment, je n'ai pas encore beaucoup réfléchi à la manière d'automatiser l'encodage... Ca va être un travail titanesque vu le nombre d'extraits qu'il y a !

    Citation Envoyé par fsmrel Voir le message

    Certes, mais il faut prendre le temps de développer le trigger nécessaire pour effectuer les inserts ! Ça n’est pas bien compliqué (il est dans le style de COMPOSER__TR), mais il faut tester...En passant, pourriez-vous donner une définition du blindtest tel que vous le percevez ?
    Alors, sur un autre forum, j'ai créé un topic Blindtest en mai/juin 2005. Chacun peut venir apporter un blindtest et y faire participer les autres ! Un BT (blindtest) se compose en moyenne de 25 extraits, et le nombre de participants varie fort mais en moyenne, il y en a 5/6.

    Voici à quoi ressemble un récapitulatif en fin de jeu :



    Donc, un Blindtest, c'est un jeu de connaissance musicale.
    Le programme et la DB sur lesquels on bosse pour le moment vont me servir (et aux autres aussi) à accéder à une multitude d'infos, une fois tout l'encodage terminé ! Bien sur, ça va me prendre des mois, j'en suis bien conscient...

  8. #48
    Membre régulier
    Bonjour Fsmrel,

    Comprenez-vous mieux le principe du blindtest à présent ?
    Sinon, je continue à expliquer

  9. #49
    Expert éminent sénior
    Bonsoir Mike,


    Citation Envoyé par Mike888 Voir le message
    Ca va être un travail titanesque vu le nombre d'extraits qu'il y a !


    Travail de titan en effet !


    Citation Envoyé par Mike888 Voir le message

    Comprenez-vous mieux le principe du blindtest à présent ?
    Sinon, je continue à expliquer
    A 10 000 mètres d’altitude, je commence à voir à peu près


    Cela dit, je suis en train d’aménager les triggers pour que vous puissiez disposer des données des lignes concernées en cas d’erreur (pseudo inconnu, etc.), le but est d’afficher des messages plus explicites que ceux de SQL Server qui en reste assez souvent au minimum syndical (signification seulement de l’erreur, par exemple dans le cas des clés étrangères), ce qui est irritant dans le cas des inserts et updates en masse, quand on veut savoir quels pseudos, numéros de blindtests, titres et artistes sont la cause des erreurs.

    Le code des triggers devient assez verbeux, mais n’est pas rendu compliqué pour autant. Je joindrai au fur et à mesure les nouvelles versions. Commençons par le commencement, avec le trigger PARTICIPER_TR qui s’enrichît...


    Trigger PARTICIPER_TR

    DROP TRIGGER IF EXISTS PARTICIPER_TR ;
    GO
    
     CREATE TRIGGER PARTICIPER_TR ON PARTICIPER_V INSTEAD OF INSERT, UPDATE
    AS
    
    DECLARE @tableCible as VARCHAR(48) = 'PARTICIPER'
    DECLARE @theTrigger as VARCHAR(64) = @tableCible + '_TR - '
    DECLARE @EngueuladeInit AS VARCHAR(128) = @theTrigger + 'insert/update ' + @tableCible + ' - ';
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @nbInserted as INT ;
    DECLARE @nbDeleted as INT ;
    DECLARE @NumeroKount as INT ;
    DECLARE @pseudoKount as INT ;
    DECLARE @blindtestConnu as CHAR(1) = 'Y'
    DECLARE @pseudoConnu as CHAR(1) = 'Y'
    DECLARE @pseudoJoueur as CHAR(1) = 'Y'
    DECLARE @exclusionRespectee as CHAR(1) = 'Y'
    
    DECLARE @nbExclusions as INT ;
    
    -----------------------------------------------------------
    -- tables temporaires pour les clés primaires
    -----------------------------------------------------------
    
    DECLARE @inserted TABLE
    (
        joueurId     INT
      , blindtestId  INT
    ) ;
    DECLARE @deleted TABLE
    (
        joueurId     INT
      , blindtestId  INT
    ) ;
    
    
    DECLARE @t1 as TABLE 
    (
        pseudo varchar(48)
      , Numero varchar(48)
    )
    
    DECLARE @t2 as TABLE 
    (
        Numero varchar(48)
    )
    
    DECLARE @t3 as TABLE 
    (
        pseudo varchar(48)
    )
    
    --------------------------------------------------
    -- c'est parti
    --------------------------------------------------
    
    ---- pour debug ---- select '' as INSERTED, * from INSERTED  
    ---- pour debug ---- select '' as DELETED, * from DELETED  
    
    ---------------------------------------------
    -- Si INSERTED est vide, on dégage
    ---------------------------------------------
    
    SET @nbInserted = (SELECT COUNT(*) FROM INSERTED) ;
    
    IF @nbInserted = 0
        BEGIN
            SET @Engueulade = @EngueuladeInit 
                            + 'La requête propose un contenu vide, ' 
                            + 'la table ' + @tableCible 
                            + ' ne sera donc pas mise à jour.' 
            RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
    ----    RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
            RETURN  
        END
    
    ------------------------------------------------------
    -- traduction des clés alternatives (naturelles) en
    -- clés primaires (artificielles)
    ------------------------------------------------------
    
    INSERT INTO @inserted (joueurId, blindtestId)
        SELECT DISTINCT y.blindeurId, z.blindtestId
        FROM   INSERTED AS x
          JOIN BLINDEUR AS y ON x.pseudo = y.pseudo
          JOIN BLINDTEST AS z ON x.Numero = z.Numero
    ;
    ---- pour debug ---- select '' as '@inserted', * from @inserted 
    
    ----------------------------------------
    -- les numéros des blindtests doivent
    -- exister
    ----------------------------------------
    
        INSERT INTO @t1 (pseudo, Numero)
            SELECT DISTINCT pseudo, Numero 
        FROM   INSERTED AS x
        WHERE  NOT EXISTS (SELECT Numero
                           FROM   BLINDTEST AS y
                           WHERE  x.Numero = y.Numero)
    ;
    ------------------------------------------
    -- Nombre exacts de numéros inconnus
    ------------------------------------------
    
    INSERT INTO @t2 (Numero) 
        SELECT DISTINCT Numero FROM @t1 ;
    
    SET @NumeroKount = (SELECT COUNT(*) FROM @t2)
    
    IF @NumeroKount > 0
        BEGIN
        -----------------------------------------
        -- Les numéros effectivement inconnus
        -----------------------------------------
            IF @NumeroKount = 1
                BEGIN
                    SET @Engueulade 
                      = @EngueuladeInit
                      + 'blindtest proposé : inconnu.' 
                END
            ELSE
                BEGIN
                    SET @Engueulade 
                      = @EngueuladeInit
                      + 'Il y a '
                      + CAST(@NumeroKount as VARCHAR(5)) 
                      + ' blindtests proposés mais inconnus.'
                END
            SELECT @Engueulade AS Engueulons
                 , pseudo 
                 , Numero as 'Numero proposé inconnu'
            FROM @t1
            SET @blindtestConnu = 'N'
            RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
    ----    RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
    ----------------------        RETURN
      
        END
    
    ----------------------------------------
    -- les pseudos des joueurs doivent
    -- exister
    ----------------------------------------
    
    DELETE FROM @t1 ;
    
    INSERT INTO @t1 (pseudo, Numero)
        SELECT DISTINCT pseudo, Numero 
        FROM   INSERTED AS x
        WHERE  NOT EXISTS (SELECT pseudo
                           FROM   BLINDEUR AS y
                           WHERE  x.pseudo = y.pseudo)
    ;
    ------------------------------------------
    -- Nombre exacts de pseudos inconnus
    ------------------------------------------
    
    INSERT INTO @t3 (pseudo) 
        SELECT DISTINCT pseudo FROM @t1 ;
    
    SET @pseudoKount = (SELECT COUNT(*) FROM @t3)
    
    IF @pseudoKount > 0
        BEGIN
        -----------------------------------------
        -- Les pseudos effectivement inconnus
        -----------------------------------------
            IF @pseudoKount = 1
                BEGIN
                    SET @Engueulade 
                      = @EngueuladeInit
                      + 'pseudo proposé : inconnu.' 
                END
            ELSE
                BEGIN
                    SET @Engueulade 
                      = @EngueuladeInit
                      + 'Il y a '
                      + CAST(@pseudoKount as VARCHAR(5)) 
                      + ' pseudos proposés mais inconnus.'
                END
            SELECT @Engueulade AS Engueulons
                 , pseudo as 'pseudo proposé inconnu'
                 , Numero
            FROM @t1
            SET @pseudoConnu = 'N'
            RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
    ----    RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
    ----------------------        RETURN
      
        END
    
    ------------------------------------------
    -- les blindeurs doivent être des joueurs
    ------------------------------------------
    
    DELETE FROM @t1 ;
    DELETE FROM @t3 ;
    
    IF @pseudoConnu = 'Y' AND @blindtestConnu = 'y'
        BEGIN
            INSERT INTO @t1 (pseudo, Numero)
                SELECT DISTINCT pseudo, Numero 
                FROM   INSERTED AS x
                WHERE  NOT EXISTS (SELECT ''
                                   FROM   BLINDEUR AS y                            
                                     JOIN JOUEUR AS z ON y.blindeurId = z.joueurId 
                                   WHERE  x.pseudo = y.pseudo) 
            ;
            INSERT INTO @t3 (pseudo) 
                SELECT DISTINCT pseudo FROM @t1 ;
    
            SET @pseudoKount = (SELECT COUNT(*) FROM @t3)
    
            IF @pseudoKount > 0
                -----------------------------------------------------
                -- Les blindeurs visant à participer aux blindtests 
                -- alors qu'ils ne sont pas déclarés comme joueurs
                -----------------------------------------------------
                BEGIN
                    IF @pseudoKount = 1
                        BEGIN
                            SET @Engueulade 
                              = @EngueuladeInit
                              + 'le pseudo proposé n''est pas celui d'' un joueur' 
                        END
                    ELSE
                        BEGIN
                           SET @Engueulade 
                             = @EngueuladeInit
                             + 'Il y a '
                             + CAST(@pseudoKount as VARCHAR(5)) 
                             + ' pseudos proposés qui ne sont pas ceux de joueurs.'
                        END
                    SELECT @Engueulade AS Engueulons
                         , pseudo as 'blindeur non joueur'
                         , Numero
                    FROM @t1
                    SET @pseudoJoueur = 'N' ; 
                    RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
    ----            RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer 
    -------------------                RETURN  
                END
        END
    
    -------------------------------------------------------
    -- comptage des viols de la contrainte d'exclusion :
    -- l'animateur d'un blindtest ne peut pas y participer
    -------------------------------------------------------
    
    IF @blindtestConnu = 'Y' AND @pseudoConnu = 'Y' 
      AND @pseudoJoueur = 'Y'
    
        BEGIN   
            DELETE FROM @t1 ;
    
            INSERT INTO @t1 (pseudo, Numero)
                SELECT DISTINCT pseudo, Numero 
                FROM   @inserted AS x
                  JOIN BLINDTEST AS y ON x.blindtestId = y.blindtestId 
                   AND x.joueurId = y.animateurId
                  JOIN BLINDEUR AS z on x.joueurId = z.blindeurId                                  
            ;
    ---- pour debug ---- select '' as '@inserted', * from @inserted
    ---- pour debug ---- select '' as '@t1', * from @t1
    
            INSERT INTO @t3 (pseudo) 
                SELECT DISTINCT pseudo FROM @t1 ;
    
            SET @nbExclusions = (SELECT COUNT(*) FROM @t3)
    
            ---- pour debug ---- select @nbExclusions as '@nbExclusions' 
    
            ----------------------------------------------------
            -- Si on constate une tentative de viol de la
            -- contrainte d'exclusion, on rouspète 
            -- et on s'en va.
            ----------------------------------------------------
    
            IF @nbExclusions > 0
                BEGIN
                        
                    IF @nbExclusions = 1
                        BEGIN  
                            SET @Engueulade 
                              = @EngueuladeInit
                              + 'le pseudo proposé est celui de ' 
                              + 'l''animateur du blindtest.' 
                        END  
                    ELSE
                        BEGIN  
                            SET @Engueulade 
                              = @EngueuladeInit
                              + 'Il y a '
                              + CAST(@nbExclusions as VARCHAR(5)) 
                              + ' pseudos proposés qui sont ceux '
                              + 'd''animateurs des blindtests.'
                        END
      
                    SELECT @Engueulade AS Engueulons
                         , pseudo as 'pseudo déjà animateur du blindtest'
                         , Numero
                    FROM @t1
    
                    SET @exclusionRespectee = 'N'
                    RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
            ----        RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
    ----------------------        RETURN
                END  
        END 
    
    ----------------------------------------------------
    -- On n'a pas constaté d'anomalie : 
    -- on met la table PARTICIPER à jour.
    ----------------------------------------------------
    
    IF @blindtestConnu = 'Y' AND @pseudoConnu = 'Y' 
      AND @pseudoJoueur = 'Y' AND @exclusionRespectee = 'Y'
    
        BEGIN
     
            ---------------------------------------------------
            -- S’il y a quelque chose dans DELETED
            -- alors c’est qu’un UPDATE est en cours.
            ---------------------------------------------------
    
            IF (SELECT COUNT(*) FROM DELETED) > 0
                BEGIN 
                     INSERT INTO @deleted (joueurId, blindtestId)
                        SELECT DISTINCT y.blindeurId, z.blindtestId
                        FROM   DELETED AS x
                          JOIN BLINDEUR AS y ON x.pseudo = y.pseudo
                          JOIN BLINDTEST AS z ON x.Numero = z.Numero
    
                    ---- pour debug ----  select '' as '@inserted avant update', * from @inserted
                    ---- pour debug ----  select '' as '@deleted avant update', * from @deleted
                    ---- pour debug ----  select '' as 'participer avant update', * from PARTICIPER
    
                     UPDATE PARTICIPER 
                        SET  
                            joueurId =  
                            (SELECT DISTINCT joueurId
                             from   @inserted)
                          ,  blindtestId = 
                            (SELECT DISTINCT blindtestId 
                             from   @inserted)
                           WHERE joueurId IN (SELECT DISTINCT joueurId FROM @deleted) 
                             AND blindtestId IN (SELECT DISTINCT blindtestId FROM @deleted)
                            
                ---- pour debug ----  select '' as 'participer après update', * from PARTICIPER
    
                END
            ELSE
                BEGIN
                ---------------------------------
                -- On est en INSERT
                ---------------------------------
    
                ---- pour debug ---- select '' as 'participer avant insert', * from PARTICIPER
    
                    INSERT INTO PARTICIPER (joueurId, blindtestId)
                        SELECT joueurId, blindtestId 
                        FROM @inserted
                    ;
                ---- pour debug ---- select '' as 'participer après insert', * from PARTICIPER
                END
        END
    
    GO
    

    Un début de jeu d’essai :

    ----------------------------------------------
    -- blindeurs
    ----------------------------------------------
    INSERT INTO BLINDEUR (pseudo) VALUES
        ('fernand'), ('raoul'), ('paul'), ('mado')
      , ('folace'), ('jean'), ('antoine'), ('patricia') 
    ;
    ----------------------------------------
    -- joueurs
    ----------------------------------------
    INSERT INTO JOUEUR (joueurId)
        SELECT blindeurId 
        FROM BLINDEUR 
        WHERE pseudo IN ('fernand', 'mado', 'folace'
                       , 'jean', 'antoine', 'patricia')
    ;
    ------------------------------------------------
    -- Les blindtests
    ------------------------------------------------
    INSERT INTO BLINDTEST (Numero, animateurId)
        SELECT 'blindt01', blindeurId
        FROM    BLINDEUR
        WHERE pseudo = 'fernand'    
    ;
    INSERT INTO BLINDTEST (Numero, animateurId)
        SELECT 'blindt02', blindeurId
        FROM    BLINDEUR
        WHERE pseudo = 'mado'    
    ;  
    INSERT INTO BLINDTEST (Numero, animateurId)
        SELECT 'blindt03', blindeurId
        FROM    BLINDEUR
        WHERE pseudo = 'fernand'    
    ;  
    INSERT INTO BLINDTEST (Numero, animateurId)
        SELECT 'blindt04', blindeurId
        FROM    BLINDEUR
        WHERE pseudo = 'fernand'    
    ;
    INSERT INTO BLINDTEST (Numero, animateurId)
        SELECT 'blindt05', blindeurId
        FROM    BLINDEUR
        WHERE pseudo = 'fernand'    
    ;
    -------------------------------------
    -- joueurs participant aux blindtests
    -------------------------------------
    INSERT INTO PARTICIPER_V (pseudo, Numero) VALUES 
        ('folace', 'blindt01') 
      , ('folace', 'blindt02') 
      , ('folace', 'blindt05') 
      , ('jean', 'blindt02') 
      , ('jean', 'blindt05') 
      , ('mado', 'blindt01') 
    ;
    

    Dans l’état actuel du trigger, on ne peut modifier par UPDATE qu’une seule ligne de la table. A moins qu’un champion de SQL passe par ici et donne la solution miracle de l’update ensembliste, Je mettrai probablement en oeuvre un curseur pour updater cacahuète par cacahuète...


    En prime : le trigger PARTICIPER_TR_DELETE qui permet de supprimer des lignes de la table PARTICIPER au moyen des clés naturelles.

    Trigger PARTICIPER_TR_DELETE

    DROP TRIGGER IF EXISTS PARTICIPER_TR_DELETE ;
    
    GO
    
    CREATE TRIGGER PARTICIPER_TR_DELETE ON PARTICIPER_V INSTEAD OF DELETE
    AS
    
    DECLARE @tableCible as VARCHAR(48) = 'PARTICIPER'
    DECLARE @theTrigger as VARCHAR(64) = @tableCible + '_TR_DELETE - '
    DECLARE @EngueuladeInit AS VARCHAR(128) = @theTrigger + 'delete dans ' + @tableCible + ' - ';
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @nbDeleted as INT ;
    
    -----------------------------------------------------------
    -- tables temporaires pour les clés primaires
    -----------------------------------------------------------
    
    DECLARE @deleted as TABLE
    (
        joueurId       INT
      , blindtestId    INT
    ) ;
    
    -----------------------------------------------------
    -- c'est parti !
    -----------------------------------------------------
    
    ---- pour debug ---- select '' as 'DELETED (on delete)', * from DELETED  
    
    ---------------------------------------------
    -- Si DELETED est vide, on dégage
    ---------------------------------------------
    
    SET @nbdeleted = (SELECT COUNT(*) FROM DELETED) ;
    
    IF @nbdeleted = 0
        BEGIN
            SET @Engueulade = @EngueuladeInit 
                            + 'La requête propose un contenu vide, ' 
                            + 'la table ' + @tableCible 
                            + ' ne sera donc pas mise à jour.' 
            RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
    ----    RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
            RETURN  
     
        END
    
    ------------------------------------------------------
    -- de DELETED vers @deleted :
    -- Traduction des clés alternatives (naturelles) en
    -- clés primaires (artificielles)
    ------------------------------------------------------
    
    INSERT INTO @deleted (joueurId, blindtestId)
        SELECT DISTINCT  y.blindeurId, z.blindtestId
        FROM   DELETED AS x
          JOIN BLINDEUR AS y ON x.pseudo = y.pseudo
          JOIN BLINDTEST AS z ON x.Numero = z.Numero
    ;
    
    ---- pour debug ---- select '' as '@deleted' , * from @deleted
    
    ------------------------------------------------------
    -- Mise à jour de la table PARTICIPER.
    ------------------------------------------------------
    
    DELETE FROM PARTICIPER
           WHERE  joueurId IN (SELECT joueurId FROM @deleted)
              AND blindtestId IN (SELECT blindtestId FROM @deleted)
    ;
    
    ---- pour debug ---- select '' as 'PARTICIPER après delete', * from PARTICIPER
    
    GO
    

    Exemple :

    -------------------------------------
    -- folace ne participe plus à rien
    -------------------------------------
    DELETE FROM PARTICIPER_v  where pseudo = 'folace';
    

    A noter que j’ai opté pour la suppression et la modification en cascade des participations (ON DELETE CASCADE, ON UPDATE CASCADE) :


    CREATE TABLE TROUVER 
    (
            joueurId         INT                NOT NULL
          , blindtestId      INT                NOT NULL
          , extraitId        INT                NOT NULL 
        , CONSTRAINT TROUVER_PK PRIMARY KEY (joueurId, blindtestId, extraitId) 
        , CONSTRAINT TROUVER_PARTICIPER_FK FOREIGN KEY (joueurId, blindtestId)
              REFERENCES PARTICIPER (joueurId,blindtestId)    ON DELETE CASCADE 
                                                              ON UPDATE CASCADE 
        , CONSTRAINT TROUVER_COMPOSER_FK FOREIGN KEY (blindtestId, extraitId)
              REFERENCES COMPOSER (blindtestId, extraitId) ON DELETE NO ACTION
    ) ;
    
    Autrement dit, si une participation est supprimée ou modifiée (changement par exemple de joueur pour tel blindtest), c’est répercuté dans la table TROUVER (propagation des suppressions/modifications). Si vous préférez au contraire que l’opération soit rejetée, il suffira de supprimer ON DELETE CASCADE et ON UPDATE CASCADE.


    Si vous avez besoin de précisions...


    Les triggers associés aux vues COMPOSER_V et TROUVER_V vont suivre.


     
    Faites simple, mais pas plus simple ! (A. Einstein)
    E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    Je ne réponds pas aux questions techniques par MP. Les forums sont là pout ça.
    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench

  10. #50
    Expert éminent sénior
    Bonsoir Mike,


    Triggers, suite, ravalement de façade :

    Vue COMPOSER_V : trigger COMPOSER_TR_DELETE pour supprimer des lignes dans COMPOSER :

    DROP TRIGGER IF EXISTS COMPOSER_TR_DELETE ;
    GO
    
    CREATE TRIGGER COMPOSER_TR_DELETE ON COMPOSER_V INSTEAD OF DELETE
    AS
    
    DECLARE @tableCible as VARCHAR(48) = 'COMPOSER'
    DECLARE @theTrigger as VARCHAR(64) = @tableCible + '_TR_DELETE - '
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @nbDeleted as INT ;
    
    -----------------------------------------------------------
    -- tables temporaires pour les clés primaires
    -----------------------------------------------------------
    
    DECLARE @deleted as TABLE
    (
        blindtestId  INT
      , extraitId    INT
    ) ;
    
    -----------------------------------------------------
    -- c'est parti !
    -----------------------------------------------------
    
    ---- pour debug ---- 
    select '' as 'DELETED (on delete)', * from DELETED  
    
    ---------------------------------------------
    -- Si DELETED est vide, on dégage
    ---------------------------------------------
    
    SET @nbdeleted = (SELECT COUNT(*) FROM DELETED) ;
    
    IF @nbdeleted = 0
        BEGIN
            SET @Engueulade = @theTrigger + 'La requête propose un contenu vide, ' 
                            + 'la table ' + @tableCible 
                            + ' ne sera donc pas mise à jour.' 
            RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
            ----    RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
            RETURN  
        END
    
    ------------------------------------------------------
    -- de DELETED vers @deleted :
    -- Traduction des clés alternatives (naturelles) en
    -- clés primaires (artificielles)
    ------------------------------------------------------
    
    INSERT INTO @deleted (blindtestId, extraitId)
        SELECT DISTINCT  z.blindtestId, t.extraitId
        FROM   DELETED AS x
          JOIN BLINDTEST AS z ON x.Numero = z.Numero
          JOIN EXTRAIT AS t ON x.artiste = t.artiste
                           AND x.titre = t.titre
    ;
    
    ---- pour debug ---- select '' as '@deleted' , * from @deleted
    
    ------------------------------------------------------
    -- Mise à jour de la table COMPOSER.
    ------------------------------------------------------
    
    DELETE FROM COMPOSER
           WHERE  blindtestId IN (SELECT blindtestId FROM @deleted)
              AND extraitId IN (SELECT extraitId FROM @deleted)  
    ;
    
    ---- pour debug ---- select '' as 'COMPOSER après delete', * from COMPOSER
    
    GO
    

    Vue COMPOSER_V : trigger COMPOSER_TR pour insérer, modifier des lignes dans COMPOSER :

    
    DROP TRIGGER IF EXISTS COMPOSER_TR ;
    
    GO
    
    CREATE TRIGGER COMPOSER_TR ON COMPOSER_V INSTEAD OF INSERT, UPDATE
    AS
    
    DECLARE @tableCible as VARCHAR(48) = 'COMPOSER'
    DECLARE @theTrigger as VARCHAR(64) = @tableCible + '_TR - '
    DECLARE @EngueuladeInit AS VARCHAR(128) = @theTrigger + 'insert/update ' + @tableCible + ' - ';
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @nbInserted as INT ;
    DECLARE @nbDeleted as INT ;
    DECLARE @NumeroKount as INT ;
    DECLARE @extraitCodeKount as INT ;
    DECLARE @blindtestConnu as CHAR(1) = 'Y'
    DECLARE @blindtestParticipant as CHAR(1) = 'Y'
    DECLARE @extraitConnu as CHAR(1) = 'Y'
    DECLARE @extraitParticipant as CHAR(1) = 'Y'
    
    DECLARE @t1 as TABLE 
    (
        Numero varchar(48)
      , artiste varchar(48)
      , titre varchar(48)
    )
    
    DECLARE @t2 as TABLE 
    (
        Numero varchar(48)
    )
    
    DECLARE @t3 as TABLE 
    (
        Numero varchar(48)
      , artiste varchar(48)
      , titre varchar(48)
    )
    
    -----------------------------------------------------------
    -- tables temporaires pour les clés primaires
    -----------------------------------------------------------
    
    DECLARE @inserted as TABLE
    (
        blindtestId  INT
      , extraitId    INT
    ) ;
    DECLARE @deleted as TABLE
    (
        blindtestId  INT
      , extraitId    INT
    ) ;
    
    --------------------------------------------------
    -- c'est parti
    --------------------------------------------------
    
    ---- pour debug ---- select '' as INSERTED, * from INSERTED  
    ---- pour debug ---- select '' as DELETED, * from DELETED  
    
    ---------------------------------------------
    -- Si INSERTED est vide, on dégage
    ---------------------------------------------
    
    SET @nbInserted = (SELECT COUNT(*) FROM INSERTED) ;
    
    IF @nbInserted = 0
        BEGIN
            SET @Engueulade = @EngueuladeInit 
                            + 'La requête propose un contenu vide, ' 
                            + 'la table ' + @tableCible 
                            + ' ne sera donc pas mise à jour.' 
            RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
        ----    RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
            RETURN  
        END
    
    ------------------------------------------------------
    -- traduction des clés alternatives (naturelles) en
    -- clés primaires (artificielles)
    ------------------------------------------------------
    INSERT INTO @inserted (blindtestId, extraitId)
        SELECT DISTINCT y.blindtestId, z.extraitId
        FROM   INSERTED AS x
          JOIN BLINDTEST AS y ON x.Numero = y.Numero
          JOIN EXTRAIT AS z ON x.artiste = z.artiste
                           AND x.titre = z.titre
    ;
    ---- pour debug ---- select '' as '@inserted', * from @inserted 
    
    ---------------------------------------------------
    -- si un Numero est candidat au
    -- remplacement d'un autre Numero,
    -- il doit être connu dans la table BLINDTEST
    ---------------------------------------------------
    
    ---------------------------------------
    -- @t1 : les codes blintests inconnus
    ---------------------------------------
    
    INSERT INTO @t1 (Numero, artiste, titre)
        SELECT DISTINCT Numero, artiste, titre 
        FROM   INSERTED AS x
        WHERE  NOT EXISTS (SELECT Numero
                           FROM   BLINDTEST AS y
                           WHERE  x.Numero = y.Numero)
    ;
    ------------------------------------------
    -- Nombre exacts de numéros inconnus
    ------------------------------------------
    
    INSERT INTO @t2 (Numero) 
        SELECT DISTINCT Numero FROM @t1 ;
    
    SET @NumeroKount = (SELECT COUNT(*) FROM @t2)
    
    IF @NumeroKount > 0
        BEGIN
        -----------------------------------------
        -- Les numéros effectivement inconnus
        -----------------------------------------
            IF @NumeroKount = 1
                BEGIN
                    SET @Engueulade = @EngueuladeInit
                      + 'blindtest proposé : inconnu.' 
                END
            ELSE
                BEGIN
                    SET @Engueulade = @EngueuladeInit 
                      + 'Il y a '
                      + CAST(@NumeroKount as VARCHAR(5)) 
                      + ' blindtests proposés mais inconnus.'
                END
            SELECT @Engueulade AS Engueulons
                 , Numero as 'Numero proposé inconnu'
                 , artiste, titre 
            FROM @t1
            SET @blindtestConnu = 'N'
            RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
    ----    RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
    ----------------------        RETURN
      
        END
    
    ---------------------------------------------------------
    -- recherche  dans INSERTED des paires {artiste, titre}
    -- inconnues, celles-ci figureront dans @t1
    ---------------------------------------------------------
    DELETE FROM @t1 ;
    
    INSERT INTO @t1 (Numero, artiste, titre)
        SELECT DISTINCT Numero, artiste, titre 
        FROM   INSERTED AS x
        WHERE  NOT EXISTS (SELECT artiste, titre
                           FROM   EXTRAIT AS y
                           WHERE  x.artiste = y.artiste 
                              AND x.titre = y.titre)
    ;
    INSERT INTO @t3 (artiste, titre) 
        SELECT DISTINCT artiste, titre FROM @t1 ;
    
    SET @extraitCodeKount = (SELECT COUNT(*) FROM @t3)
    
    IF @extraitCodeKount > 0
        BEGIN
        ------------------------------------------------------
        -- Les paires {artiste, titre} effectivement inconnues
        -------------------------------------------------------
            IF @extraitCodeKount = 1
                BEGIN
                    SET @Engueulade = @EngueuladeInit 
                      + 'extrait {artiste, titre} non reconnu.' 
                END
            ELSE
                BEGIN
                    SET @Engueulade = @EngueuladeInit 
                      + 'Il y a ' + cast(@NumeroKount as varchar(5)) 
                      + ' extraits {artiste, titre} proposés non reconnus.'
                END
            SELECT @Engueulade AS Engueulons
                 , Numero
                 , '{' + artiste + ', ' + titre + '}' as 'extrait proposé non reconnu' 
            FROM @t1
            SET @extraitConnu = 'N'
            RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
    ----    RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
    -------------       RETURN  
        END
    
    ------------------------------------------------------
    -- Mise à jour de la table COMPOSER.
    -- Si on a détecté des erreurs, on ne fait rien.
    ------------------------------------------------------
    
    IF @blindtestConnu = 'Y' AND @extraitConnu = 'Y'
        BEGIN
            -------------------------------------------------------
            -- S’il y a quelque chose dans DELETED
            -- alors c’est qu’un UPDATE est en cours.
            -------------------------------------------------------
            IF (SELECT COUNT(*) FROM DELETED) > 0
                BEGIN 
                    INSERT INTO @deleted (blindtestId, extraitId)
                        SELECT DISTINCT y.blindtestId, z.extraitId
                        FROM   DELETED AS x
                          JOIN BLINDTEST AS y ON x.Numero = y.Numero
                               JOIN EXTRAIT AS z ON x.artiste = z.artiste
                                                AND x.titre = z.titre
                    ;
    
                    ---- pour debug ---- select '' as '@deleted', * from @deleted 
                    ---- pour debug ---- select '' as 'composer avant delete', * from COMPOSER
    
                     UPDATE COMPOSER 
                        SET  
                            blindtestId = 
                                (SELECT DISTINCT blindtestId 
                                 FROM   @inserted)
                          , extraitId =  
                                (SELECT DISTINCT extraitId
                                 FROM   @inserted)
                           WHERE blindtestId IN (SELECT DISTINCT blindtestId FROM @deleted)
                             AND extraitId IN (SELECT DISTINCT extraitId FROM @deleted) 
                            
                ---- pour debug ----  select '' as 'composer après update', * from COMPOSER
    
                END
            ELSE
                BEGIN
                ---------------------------------
                -- On est en INSERT
                ---------------------------------
    
                ---- pour debug ---- select '' as 'composer avant insert', * from COMPOSER
    
                    INSERT INTO COMPOSER (blindtestId, extraitId)
                        SELECT blindtestId, extraitId 
                        FROM @inserted
                   ;
                ---- pour debug ---- select '' as 'composer après insert', * from COMPOSER
                END
        END
    
    GO
    
    

    La programmation de UPDATE ne me satisfait toujours pas, du fait qu’on ne puisse modifier qu’une seule ligne à la fois dans la table COMPOSER, je réfléchis à une programmation moins contraignante...


     
    Faites simple, mais pas plus simple ! (A. Einstein)
    E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    Je ne réponds pas aux questions techniques par MP. Les forums sont là pout ça.
    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench

  11. #51
    Membre régulier
    Merci pour tout ce travail !

  12. #52
    Expert éminent sénior
    triggers - associations blnidtests - joueurs
    Bonsoir Mike,


    Ci-joint du code qui était resté dans un tiroir...


    Comme disait Jean dans les Tontons flingueurs, « Quand ça change, ça change ! » J’ai finalement remplacé chaque trigger dédié à la fois aux inserts et aux updates par deux triggers, un pour les inserts et un pour les updates. Il s’ensuit certes plus de matière, mais malgré les apparences, la programmation en est moins complexe. J’ai factorisé dans des procédures le code commun aux triggers. Par exemple, les triggers PARTICIPER_INSERT_TR et PARTICIPER_UPDATE_TR appartenant à la vue PARTICIPER_V font appel aux procédures suivantes :

    — INSERTED_VIDE_PROC (s’assurer que le trigger reçoit autre chose que du vide)
    — JOUEUR_EXCLUSION_ANIMATEUR_PROC (s’assurer qu’un joueur n’est pas l’animateur du blindtest concerné)
    — PARTICIPER_BLINDEUR_EST_JOUEUR_PROC (s’assurer que le blindeur est un joueur)
    — PARTICIPER_BLINDEUR_EXISTENCE_PROC (s’assurer que le blindeur existe dans la table PARTICIPER)
    — PARTICIPER_BLINDTEST_EXISTENCE_PROC (s’assurer que le blindtest auquel le jour participe existe bien)
    — PARTICIPER_DOUBLON_PK_PROC (s’assurer qu’il n’y aura pas de tentative de clé primaire en double)

    Vous me direz que les trois dernières procédures effectuent des contrôles qui font double emploi, puisque le SGBD les effectuera lui aussi. En fait, le SGBD dit peu de choses en cas d’erreur, notamment en ce qui concerne les lignes à créer ou à modifier, et les procédures permettent d’en dire plus.

    Si vous utilisez les triggers et procédures que je propose, il faudra commencer par la création de types de tables utilisés pour la communication entre un trigger et une procédure.

    Pour tout ce qui suit, n’hésitez pas à poser vos questions quand quelque chose vous échappe

    Le code de création de ces types est le suivant :

    Les types de tables :

    -------------------------------
    -- type table des blindeurs
    -------------------------------
    DROP TYPE IF EXISTS blindeur_table_type ;
    GO
    CREATE TYPE blindeur_table_type AS TABLE
    (
        blindeurId     INT 
      , pseudo         VARCHAR (48)
    )
    ;
    GO
    
    -------------------------------
    -- type table des blindtests
    -------------------------------
    DROP TYPE IF EXISTS blindtest_table_type ;
    GO
    CREATE TYPE blindtest_table_type AS TABLE
    (
        blindtestId    INT 
      , Numero         VARCHAR (48)
    )
    ;
    GO
    
    -------------------------------------------------
    -- association blindtest - blindeur
    -------------------------------------------------
    
    DROP TYPE IF EXISTS joueur_blindtest_table_type ;
    GO
    CREATE TYPE joueur_blindtest_table_type AS TABLE
    (
        blindeurId    INT
      , blindtestId   INT
      , pseudo        VARCHAR (48)
      , Numero        VARCHAR (48)
    )
    ;
    GO
    
    -------------------------------------------------
    -- association blindtest - extrait
    -------------------------------------------------
    
    DROP TYPE IF EXISTS blindtest_extrait_table_type ;
    GO
    CREATE TYPE blindtest_extrait_table_type AS TABLE
    (
        blindtestId    INT
      , extraitId      INT
      , Numero         VARCHAR (48)
      , artiste        VARCHAR (48)
      , titre          VARCHAR (48)
    )
    ;
    GO
    
    ----------------------------------------------------------
    -- association joueur - blindtest - extrait
    --
    -- type de table utilisé fondamentalement
    -- pour les échanges entre les triggers et procédures
    -- traitant de la table TROUVER.
    -----------------------------------------------------------
    
    DROP TYPE IF EXISTS blindeur_blindtest_extrait_table_type ;
    GO
    CREATE TYPE blindeur_blindtest_extrait_table_type AS TABLE
    (
        blindeurId     INT
      , blindtestId    INT
      , extraitId      INT
      , pseudo         VARCHAR (48)
      , Numero         VARCHAR (48)
      , artiste        VARCHAR (48)
      , titre          VARCHAR (48)
    )
    ;
    GO

    Voici déjà les 3 triggers utilisés pour la vue PARTICIPER_V. Pour éviter de charger le post, les procédures associées feront l’objet du post suivant. Concernant les triggers utilisés pour les vues COMPOSER_V et TROUVER_V, ça suivra aussi.

    (1) Le trigger dédié aux inserts dans la vue PARTICIPER_V :

    DROP TRIGGER IF EXISTS PARTICIPER_INSERT_TR ;
    GO
    
     CREATE TRIGGER PARTICIPER_INSERT_TR ON PARTICIPER_V INSTEAD OF INSERT
    AS
    
    DECLARE @tableCible as VARCHAR(48) = 'PARTICIPER'
    DECLARE @theTrigger as VARCHAR(64) =  'Trigger ' + OBJECT_NAME(@@PROCID)
    DECLARE @typeOperation as VARCHAR(48) = 'insert' 
    DECLARE @blindtestConnu as CHAR(1) = 'Y'
    DECLARE @pseudoConnu as CHAR(1) = 'Y'
    DECLARE @pseudoDeJoueur as CHAR(1) = 'Y'
    DECLARE @exclusionRespectee as CHAR(1) = 'Y'
    DECLARE @insertedNonVide  as CHAR(1) = 'Y'
    DECLARE @integritePKrespectee as CHAR(1) = 'Y' ;
    
    --------------------------------------------------------------------
    -- La table @inserted est du type blindeur_blindtest_table_type :
    -- ceci permet de passser les données des tables aux procédures.
    -- Rappel du type blindeur_blindtest_table_type :
    -- (blinderId, blindtestId, pseudo, Numero).
    -- Pour sa part, la table @deleted est vide, elle ne sert 
    -- qu'en update. 
    ---------------------------------------------------------------------
    
    DECLARE @inserted AS blindeur_blindtest_table_type ;
    DECLARE @deleted AS blindeur_blindtest_table_type ;  -- table vide
    
    ---------------------------------------------
    -- Projection de @inserted sur le blindeur
    --------------------------------------------
    DECLARE @insertedBlindeur AS blindeur_table_type ;
    
    ---------------------------------------------
    -- Projection de @inserted sur le blindtest
    --------------------------------------------
    DECLARE @insertedBlindtest AS blindtest_table_type ;
    
    --------------------------------------------------
    -- c'est parti
    --------------------------------------------------
    
    ---- pour debug ---- select '' as INSERTED, * from INSERTED  
    
    ----------------------------------------------------------
    --  On alimente les tables utilisées par les procédures
    --  chargées d'effectuer les différents contrôles.
    ----------------------------------------------------------
    
    INSERT INTO @inserted (blindeurId, blindtestId, pseudo, Numero)
        SELECT COALESCE(blindeurId, -1), COALESCE(blindtestId, -1), x.pseudo, x.Numero  
        FROM   INSERTED as x
          LEFT JOIN BLINDEUR as y ON x.pseudo = y.pseudo
          LEFT JOIN BLINDTEST as z ON x.Numero = z.Numero 
    ;
     
    INSERT INTO @insertedBlindeur (blindeurId, pseudo)
        SELECT blindeurId, pseudo  
        FROM   @inserted
    ;
     
    INSERT INTO @insertedBlindtest (blindtestId, Numero)
        SELECT blindtestId, Numero  
        FROM   @inserted
    ;
    
    ---------------------------------------------
    -- Si INSERTED est vide, on dégage
    ---------------------------------------------
      
    EXEC  INSERTED_VIDE_PROC
        @theTrigger
      , @tableCible
      , @typeOperation
      , @insertedBlindtest
      , @insertedNonVide OUTPUT
    
    IF @insertedNonVide = 'N' 
        RETURN
    
    ----------------------------------------
    -- les numéros des blindtests doivent
    -- exister
    ----------------------------------------
      
    EXEC  PARTICIPER_BLINDTEST_EXISTENCE_PROC
        @theTrigger
      , @tableCible
      , @typeOperation
      , @inserted
      , @blindtestConnu OUTPUT
    
    ----------------------------------------
    -- les pseudos des joueurs doivent
    -- exister
    ----------------------------------------
      
    EXEC PARTICIPER_BLINDEUR_EXISTENCE_PROC
        @theTrigger
      , @tableCible
      , @typeOperation
      , @inserted
      , @pseudoConnu OUTPUT
    
    ------------------------------------------
    -- les blindeurs doivent être des joueurs
    ------------------------------------------
    
    IF @pseudoConnu = 'Y' AND @blindtestConnu = 'y'
        BEGIN
            EXEC PARTICIPER_BLINDEUR_EST_JOUEUR_PROC
              @theTrigger
            , @tableCible
            , @typeOperation
            , @inserted
            , @pseudoDeJoueur OUTPUT
    
        END
    
    -------------------------------------------------------
    -- comptage des viols de la contrainte d'exclusion :
    -- l'animateur d'un blindtest ne peut pas y participer
    -------------------------------------------------------
    
    IF @blindtestConnu = 'Y' AND @pseudoConnu = 'Y' 
      AND @pseudoDeJoueur = 'Y'
        BEGIN
            EXEC JOUEUR_EXCLUSION_ANIMATEUR_PROC
              @theTrigger
            , @tableCible
            , @typeOperation
            , @inserted
            , @exclusionRespectee OUTPUT
     
        END
    
    --------------------------------------------------------
    -- Détection des tentatives de clé primaire en double
    ---------------------------------------------------------
    
    IF @blindtestConnu = 'Y' AND @pseudoConnu = 'Y' 
        BEGIN
            EXEC PARTICIPER_DOUBLON_PK_PROC
              @theTrigger
            , @tableCible
            , @typeOperation
            , @inserted
            , @deleted
            , @integritePKrespectee OUTPUT
       
        END
    
    ----------------------------------------------------
    -- Si on n'a pas constaté d'anomalie, 
    -- on met la table PARTICIPER à jour.
    ----------------------------------------------------
    
    IF @blindtestConnu = 'Y' AND @pseudoConnu = 'Y' 
      AND @pseudoDeJoueur = 'Y' AND @exclusionRespectee = 'Y'
      AND @integritePKrespectee = 'Y'
    
        BEGIN
            INSERT INTO PARTICIPER (joueurId, blindtestId)
                SELECT DISTINCT blindeurId, blindtestId 
                FROM   @inserted
            ;
        END
    
    GO

    (2) Le trigger dédié aux updates de la vue PARTICIPER_V :

    DROP TRIGGER IF EXISTS PARTICIPER_UPDATE_TR ;
    GO
    
     CREATE TRIGGER PARTICIPER_UPDATE_TR ON PARTICIPER_V INSTEAD OF UPDATE
    AS
    
    DECLARE @tableCible as VARCHAR(48) = 'PARTICIPER'
    DECLARE @theTrigger as VARCHAR(64) =  'Trigger ' + OBJECT_NAME(@@PROCID)
    DECLARE @typeOperation as VARCHAR(48) = 'update' 
    DECLARE @EngueuladeInit AS VARCHAR(128) = @theTrigger 
                                            + 'Update rejeté de ' 
                                            + @tableCible + ' - ';
    DECLARE @Engueulade AS VARCHAR(512) ;
    
    DECLARE @nbDeleted as INT ;
    DECLARE @blindtestConnu as CHAR(1) = 'Y'
    DECLARE @pseudoConnu as CHAR(1) = 'Y'
    DECLARE @pseudoDeJoueur as CHAR(1) = 'Y'
    DECLARE @exclusionRespectee as CHAR(1) = 'Y'
    DECLARE @insertedNonVide  as CHAR(1) = 'Y'
    DECLARE @integritePKrespectee as CHAR(1) = 'Y' ;
    
    --------------------------------------------------------------------
    -- Les tables @inserted et @deleted sont du type 
    -- blindeur_blindtest_table_type :
    -- ceci permet de passser les données des tables aux procédures.
    -- Rappel du blindeur_blindtest_table_type :
    -- (blinderId, blindtestId, pseudo, Numero).
    ---------------------------------------------------------------------
    
    DECLARE @inserted AS blindeur_blindtest_table_type ;
    DECLARE @deleted AS blindeur_blindtest_table_type ;  -- table vide
    
    ---------------------------------------------
    -- Projection de @inserted sur le blindtest
    --------------------------------------------
    DECLARE @insertedBlindtest AS blindtest_table_type ;
    
    --------------------------------------------------
    -- c'est parti
    --------------------------------------------------
    
    ---- pour debug ---- select '' as INSERTED, * from INSERTED  
    ---- pour debug ---- select '' as DELETED, * from DELETED  
    
    ----------------------------------------------------------
    --  On alimente les tables utilisées par les procédures
    --  chargées d'effectuer les différents contrôles.
    ----------------------------------------------------------
    
    INSERT INTO @inserted (blindeurId, blindtestId, pseudo, Numero)
        SELECT COALESCE(blindeurId, -1), COALESCE(blindtestId, -1), x.pseudo, x.Numero  
        FROM   INSERTED as x
          LEFT JOIN BLINDEUR as y ON x.pseudo = y.pseudo
          LEFT JOIN BLINDTEST as z ON x.Numero = z.Numero 
    ;
    
    INSERT INTO @deleted (blindeurId, blindtestId, pseudo, Numero)
        SELECT COALESCE(blindeurId, -1), COALESCE(blindtestId, -1), x.pseudo, x.Numero  
        FROM   DELETED as x
          LEFT JOIN BLINDEUR as y ON x.pseudo = y.pseudo
          LEFT JOIN BLINDTEST as z ON x.Numero = z.Numero 
    ;
    
    SET @nbDeleted = (SELECT COUNT(*) from @deleted)
    
    INSERT INTO @insertedBlindtest (blindtestId, Numero)
        SELECT blindtestId, Numero  
        FROM   @inserted as x
    ;
    
    ---------------------------------------------
    -- Si INSERTED est vide, on dégage
    ---------------------------------------------
      
    EXEC  INSERTED_VIDE_PROC
        @theTrigger
      , @tableCible
      , @typeOperation
      , @insertedBlindtest
      , @insertedNonVide OUTPUT
    
    IF @insertedNonVide = 'N' 
        RETURN
    
    ----------------------------------------
    -- les numéros des blindtests doivent
    -- exister
    ----------------------------------------
      
    EXEC  PARTICIPER_BLINDTEST_EXISTENCE_PROC
        @theTrigger
      , @tableCible
      , @typeOperation
      , @inserted
      , @blindtestConnu OUTPUT
    
    ----------------------------------------
    -- les pseudos des joueurs doivent
    -- exister
    ----------------------------------------
      
    EXEC  PARTICIPER_BLINDEUR_EXISTENCE_PROC
        @theTrigger
      , @tableCible
      , @typeOperation
      , @inserted
      , @pseudoConnu OUTPUT
    
    ------------------------------------------
    -- les blindeurs doivent être des joueurs
    ------------------------------------------
    
    IF @pseudoConnu = 'Y' AND @blindtestConnu = 'y'
        BEGIN
            EXEC PARTICIPER_BLINDEUR_EST_JOUEUR_PROC
              @theTrigger
            , @tableCible
            , @typeOperation
            , @inserted
            , @pseudoDeJoueur OUTPUT
    
        END
    
    -------------------------------------------------------
    -- comptage des viols de la contrainte d'exclusion :
    -- l'animateur d'un blindtest ne peut pas y participer
    -------------------------------------------------------
    
    IF @blindtestConnu = 'Y' AND @pseudoConnu = 'Y' 
      AND @pseudoDeJoueur = 'Y'
        BEGIN
            EXEC JOUEUR_EXCLUSION_ANIMATEUR_PROC
              @theTrigger
            , @tableCible
            , @typeOperation
            , @inserted
            , @exclusionRespectee OUTPUT 
        END
    
    --------------------------------------------------------
    -- Détection des tentatives de clé primaire en double :
    -- Si INSERTED contient une paire {Numero, pseudo}
    -- déjà présente dans la table PARTICIPER, on évitera
    -- de mettre la table à jour...
    ---------------------------------------------------------
    
    IF @blindtestConnu = 'Y' AND @pseudoConnu = 'Y' 
        BEGIN
            EXEC PARTICIPER_DOUBLON_PK_PROC
              @theTrigger
            , @tableCible
            , @typeOperation
            , @inserted
            , @deleted
            , @integritePKrespectee OUTPUT
       
        END
    
    -------------------------------------------------------------
    -- Si on n'a pas constaté d'anomalie alors
    -- on va tenter de mettre la table PARTICIPER à jour.
    --------------------------------------------------------------
    
    IF @blindtestConnu = 'Y' AND @pseudoConnu = 'Y' 
      AND @pseudoDeJoueur = 'Y' AND @exclusionRespectee = 'Y'
      AND @integritePKrespectee = 'Y'
        BEGIN  
            ---------------------------------------------
            -- Si l'attribut Numero est touché par
            -- l'update, mais pas l'attribut pseudo,
            -- alors on se limite à l'update de Numero.
            ---------------------------------------------
    
            IF UPDATE (Numero) AND NOT UPDATE (pseudo)
                BEGIN                     
                    ;
                    WITH Restriction as 
                        (SELECT joueurId, blindtestId 
                         FROM   PARTICIPER                             
                         WHERE  joueurId IN (SELECT DISTINCT blindeurId FROM @deleted) 
                            AND blindtestId IN (SELECT DISTINCT blindtestId FROM @deleted))
                        UPDATE Restriction
                            SET blindtestId = x.blindtestId
                                FROM Restriction as y
                                JOIN @inserted as x on x.blindeurId = y.joueurid
                    ;
                    RETURN       
                END  
           
            ---------------------------------------------
            -- Si l'attribut Pseudo est touché par
            -- l'update, mais pas l'attribut Numero,
            -- alors on se limite à l'update de Pseudo.
            ---------------------------------------------
    
            IF UPDATE (pseudo) AND NOT UPDATE (Numero)
                BEGIN  
                    ;
                    WITH Restriction as 
                        (SELECT joueurId, blindtestId 
                         FROM   PARTICIPER                             
                         WHERE  joueurId IN (SELECT DISTINCT blindeurId FROM @deleted) 
                            AND blindtestId IN (SELECT DISTINCT blindtestId FROM @deleted))
                        UPDATE Restriction
                            SET joueurId = x.blindeurId
                                FROM Restriction as y
                                JOIN @inserted as x on x.blindtestId = y.blindtestId
                    ;
                    RETURN
                END 
     
            IF update(pseudo) and  update(numero)
                BEGIN
                    ---------------------------------------------
                    -- Les attributs Pseudo et Numero sont 
                    -- touchés par l'update.
                    ---------------------------------------------
     
                    IF @nbDeleted < 2
                        BEGIN 
                            UPDATE PARTICIPER 
                                SET  
                                    joueurId =  
                                        (SELECT DISTINCT blindeurId
                                         FROM   @inserted)
                                  , blindtestId = 
                                        (SELECT DISTINCT blindtestId 
                                         FROM   @inserted)
                                WHERE joueurId IN (SELECT DISTINCT blindeurId FROM @deleted) 
                                  AND blindtestId IN (SELECT DISTINCT blindtestId FROM @deleted)
                        END 
                    ELSE
                        BEGIN 
                            SET @Engueulade = @EngueuladeInit 
                                            + 'Update affectant ' 
                                            + 'simultanément plus d''une ligne. '
                                            + CHAR(13)
                                            + 'La table ' + @tableCible 
                                            + ' ne sera donc pas mise à jour.'
                            SELECT @Engueulade as ' ' ;
                            SELECT ' ' as 'échec donc, hélas ! (deleted)' 
                                 , pseudo as 'pseudo à remplacer'
                                 , Numero as 'numero à remplacer' 
                            FROM DELETED
    
                            SELECT '' as 'échec donc, hélas ! (inserted)' 
                                 , pseudo as 'pseudo remplaçant'
                                 , Numero as 'numero remplaçant' 
                            FROM INSERTED
    
                            RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
                     ----   RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
                            RETURN  
                        END 
                END 
        END 
    GO
    

    (3) Le trigger dédié aux deletes dans la vue PARTICIPER_V :

    DROP TRIGGER IF EXISTS PARTICIPER_DELETE_TR ;
    
    GO
    
    CREATE TRIGGER PARTICIPER_DELETE_TR ON PARTICIPER_V INSTEAD OF DELETE
    AS
    
    DECLARE @tableCible as VARCHAR(48) = 'PARTICIPER'
    
    DECLARE @theTrigger as VARCHAR(64) =  'Trigger ' + OBJECT_NAME(@@PROCID)
    DECLARE @EngueuladeInit AS VARCHAR(128) = @theTrigger 
                                            + ' - delete dans ' 
                                            + @tableCible + ' - ';
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @nbDeleted as INT ;
    
    -----------------------------------------------------------
    -- tables temporaires pour les clés primaires
    -----------------------------------------------------------
    
    DECLARE @deleted as TABLE
    (
        joueurId       INT
      , blindtestId    INT
    ) ;
    
    -----------------------------------------------------
    -- c'est parti !
    -----------------------------------------------------
    
    ---- pour debug ---- select '' as 'DELETED (on delete)', * from DELETED  
    
    ---------------------------------------------
    -- Si DELETED est vide, on dégage
    ---------------------------------------------
    
    SET @nbdeleted = (SELECT COUNT(*) FROM DELETED) ;
    
    IF @nbdeleted = 0
        BEGIN
            SET @Engueulade = @EngueuladeInit 
                            + 'Le trigger reçoit une sélection vide (DELETED), ' 
                            + CHAR(13)
                            + 'la table ' + @tableCible 
                            + ' ne sera donc pas mise à jour.' 
            SELECT @Engueulade as ' ' ;
            RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
    ----    RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
            RETURN  
        END
    
    ------------------------------------------------------
    -- de DELETED vers @deleted :
    -- Traduction des clés alternatives (naturelles) en
    -- clés primaires (artificielles)
    ------------------------------------------------------
    
    INSERT INTO @deleted (joueurId, blindtestId)
        SELECT DISTINCT  y.blindeurId, z.blindtestId
        FROM   DELETED AS x
          JOIN BLINDEUR AS y ON x.pseudo = y.pseudo
          JOIN BLINDTEST AS z ON x.Numero = z.Numero
    ;
    
    ------------------------------------------------------
    -- Mise à jour de la table PARTICIPER.
    ------------------------------------------------------
    
    DELETE FROM PARTICIPER
           WHERE  joueurId IN (SELECT joueurId FROM @deleted)
              AND blindtestId IN (SELECT blindtestId FROM @deleted)
    ;
    
    GO

     
    Faites simple, mais pas plus simple ! (A. Einstein)
    E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    Je ne réponds pas aux questions techniques par MP. Les forums sont là pout ça.
    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench

  13. #53
    Expert éminent sénior
    procédures - associations blnidtests - joueurs
    Bonsoir Mike,


    Les procédures appelées par les triggers PARTICIPER_INSERT_TR et PARTICIPER_UPDATE_TR :

    
    -------------------------------------------
    -- Réaction à la vacuité des données
    -- transmises par le SGBD
    -------------------------------------------
    DROP PROCEDURE IF EXISTS INSERTED_VIDE_PROC
    
    GO
    
    CREATE PROCEDURE INSERTED_VIDE_PROC
    (
      @theTrigger as VARCHAR(64)                 -- trigger appelant
    , @tableCible as VARCHAR(48)                 -- table mise à jour
    , @typeOperation as VARCHAR(48)              -- insert ou update
    , @inserted as blindtest_table_type READONLY -- colonnes utiles   
    , @insertedNonVide  as CHAR(1) OUTPUT        -- succès ou échec
    )
    AS
    BEGIN
    
    ---------------------------------------------
    -- Si INSERTED est vide, on le  signale
    ---------------------------------------------
    
    DECLARE @theProc as  VARCHAR(48) = OBJECT_NAME(@@PROCID)
    DECLARE @typeOperationLibelle as VARCHAR(48) = 'insert dans '
     
    IF LOWER(@typeOperation) = 'update' 
        SET @typeOperationLibelle = 'update de '  
    DECLARE @EngueuladeInit AS VARCHAR(128) = @theTrigger 
                                            + ' / Procédure ' + @theProc + ' - '
                                            + @typeOperationLibelle 
                                            + @tableCible 
                                            + ' - rejet : '
                                            + CHAR(13) ;
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @nbInserted as INT ;
    
    SET @nbInserted = (SELECT COUNT(*) FROM @inserted) ;
    
    IF @nbInserted = 0
        BEGIN
            SET @Engueulade = @EngueuladeInit 
                            + 'La procédure ' + @theProc 
                            + ' reçoit un contenu vide (INSERTED), '
                            + CHAR(13) 
                            + 'la table ' + @tableCible 
                            + ' ne sera donc pas mise à jour.'
            SELECT @Engueulade as ' '
            SET @insertedNonVide = 'N'
            RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
    ----    RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
     
        END
    
    END
    
    GO
    

    
    DROP PROCEDURE IF EXISTS PARTICIPER_BLINDEUR_EXISTENCE_PROC
    
    GO
    
    --------------------------------------------
    -- vérification de l'existence des pseudos 
    -- des blindeurs
    --------------------------------------------
    
    CREATE PROCEDURE PARTICIPER_BLINDEUR_EXISTENCE_PROC
    (
      @theTrigger as VARCHAR(64)         -- trigger appelant
    , @tableCible as VARCHAR(48)         -- table mise à jour
    , @typeOperation as VARCHAR(48)      -- insert ou update
    , @inserted as blindeur_blindtest_table_type READONLY -- colonnes utiles   
    , @pseudoConnu as CHAR(1) OUTPUT     -- succès ou échec
    )
    AS
    BEGIN
    
    DECLARE @theProc as  VARCHAR(48) = OBJECT_NAME(@@PROCID)
    DECLARE @typeOperationLibelle as VARCHAR(48) = 'insert dans ' 
    IF LOWER(@typeOperation) = 'update' 
        SET @typeOperationLibelle = 'update de '  
    DECLARE @EngueuladeInit AS VARCHAR(128) = @theTrigger        
                                            + ' / Procédure ' + @theProc + ' - '
                                            + @typeOperationLibelle 
                                            + @tableCible 
                                            + ' - rejet : ' 
                                            + CHAR(13) ;
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @pseudoKount as INT ;
    
    DECLARE @t1 as TABLE 
    (
        pseudo varchar(48)
      , Numero varchar(48)
    )
    DECLARE @t3 as TABLE 
    (
        pseudo varchar(48)
    )
    
    INSERT INTO @t1 (pseudo, Numero)
        SELECT DISTINCT pseudo, Numero 
        FROM   @inserted AS x
        WHERE  NOT EXISTS (SELECT pseudo
                           FROM   BLINDEUR AS y
                           WHERE  x.pseudo = y.pseudo)
    ;
    ------------------------------------------
    -- Nombre exacts de pseudos inconnus
    ------------------------------------------
    
    INSERT INTO @t3 (pseudo) 
        SELECT DISTINCT pseudo FROM @t1 ;
    
    SET @pseudoKount = (SELECT COUNT(*) FROM @t3)
    
    IF @pseudoKount > 0
        BEGIN
        -----------------------------------------   
        -- Les pseudos effectivement inconnus
        -----------------------------------------
            IF @pseudoKount = 1
                BEGIN
                    SET @Engueulade 
                      = @EngueuladeInit
                      + 'Le pseudo proposé est inconnu.' 
                END
            ELSE
                BEGIN
                    SET @Engueulade 
                      = @EngueuladeInit
                      + 'Il y a '
                      + CAST(@pseudoKount as VARCHAR(5)) 
                      + ' pseudos proposés qui sont inconnus.'
                END
    
            SELECT @Engueulade as ' ' ;
            SELECT pseudo as 'pseudo proposé inconnu'
                 , Numero
            FROM @t1
            ;
            SET @pseudoConnu = 'N'
            RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
    ----    RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
      
        END
    END
    GO
    

    
    DROP PROCEDURE IF EXISTS PARTICIPER_BLINDEUR_EST_JOUEUR_PROC
    
    GO
    
    --------------------------------------------
    -- vérifier que les blindeurs sont des 
    -- joueurs
    --------------------------------------------
    
    CREATE PROCEDURE PARTICIPER_BLINDEUR_EST_JOUEUR_PROC
    (
      @theTrigger as VARCHAR(64)           -- trigger appelant
    , @tableCible as VARCHAR(48)           -- table mise à jour
    , @typeOperation as VARCHAR(48)        -- insert ou update
    , @inserted as blindeur_blindtest_table_type READONLY   
    , @pseudoDeJoueur as CHAR(1) OUTPUT    -- succès ou échec
    )
    AS
    BEGIN
    
    DECLARE @theProc as  VARCHAR(48) = OBJECT_NAME(@@PROCID)
    DECLARE @typeOperationLibelle as VARCHAR(48) = 'insert dans ' 
    IF LOWER(@typeOperation) = 'update' SET @typeOperationLibelle = 'update de '  
    DECLARE @EngueuladeInit AS VARCHAR(128) = @theTrigger        
                                            + ' / Procédure ' + @theProc + ' - '
                                            + @typeOperationLibelle 
                                            + @tableCible 
                                            + ' - rejet : ' 
                                            + CHAR(13) ;
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @pseudoKount as INT ;
    
    DECLARE @t1 as TABLE 
    (
        pseudo varchar(48)
      , Numero varchar(48)
    )
    DECLARE @t3 as TABLE 
    (
        pseudo varchar(48)
    )
    
    INSERT INTO @t1 (pseudo, Numero)
        SELECT DISTINCT pseudo, Numero 
        FROM   @inserted AS x
        WHERE  NOT EXISTS (SELECT ''
                           FROM   BLINDEUR AS y                            
                             JOIN JOUEUR AS z ON y.blindeurId = z.joueurId 
                           WHERE  x.pseudo = y.pseudo) 
            ;
    INSERT INTO @t3 (pseudo) 
        SELECT DISTINCT pseudo FROM @t1 ;
    
    SET @pseudoKount = (SELECT COUNT(*) FROM @t3)
    
    IF @pseudoKount > 0
        -----------------------------------------------------
        -- Les blindeurs visant à participer aux blindtests 
        -- alors qu'ils ne sont pas déclarés comme joueurs
        -----------------------------------------------------
        BEGIN
            IF @pseudoKount = 1
                BEGIN
                    SET @Engueulade 
                      = @EngueuladeInit
                      + 'Le pseudo proposé n''est pas celui d''un joueur.' 
                END
            ELSE
                BEGIN
                   SET @Engueulade 
                     = @EngueuladeInit
                     + 'Il y a '
                     + CAST(@pseudoKount as VARCHAR(5)) 
                     + ' pseudos proposés qui ne sont pas ceux de joueurs.'
                END
    
            SELECT @Engueulade as ' ' 
            SELECT pseudo as 'blindeur non joueur'
                 , Numero
            FROM @t1
    
            SET @pseudoDeJoueur = 'N' ; 
            RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
    ----    RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer 
        END
    
    END
    GO
    

    DROP PROCEDURE IF EXISTS JOUEUR_EXCLUSION_ANIMATEUR_PROC
    
    GO
    
    --------------------------------------------
    -- vérifier que l'animateur d'un blindtest 
    -- n'y participe pas en tant que joueur
    --------------------------------------------
    
    CREATE PROCEDURE JOUEUR_EXCLUSION_ANIMATEUR_PROC
    (
      @theTrigger as VARCHAR(64)              -- trigger appelant
    , @tableCible as VARCHAR(48)              -- table mise à jour
    , @typeOperation as VARCHAR(48)           -- insert ou update
    , @inserted as blindeur_blindtest_table_type READONLY   -- colonnes utiles  
    , @exclusionRespectee as CHAR(1) OUTPUT   -- succès ou échec
    )
    AS
    BEGIN
    
    DECLARE @theProc as  VARCHAR(48) = OBJECT_NAME(@@PROCID)
    DECLARE @typeOperationLibelle as VARCHAR(48) = 'insert dans ' 
    IF LOWER(@typeOperation) = 'update' SET @typeOperationLibelle = 'update de '  
    DECLARE @EngueuladeInit AS VARCHAR(128) = @theTrigger        
                                            + ' / Procédure ' + @theProc + ' - '
                                            + @typeOperationLibelle 
                                            + @tableCible 
                                            + ' - rejet : ' 
                                            + CHAR(13) ;
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @nbExclusions as INT ;
    
    DECLARE @t1 as TABLE 
    (
        pseudo varchar(48)
      , Numero varchar(48)
    )
    DECLARE @t3 as TABLE 
    (
        pseudo varchar(48)
    )
    
    INSERT INTO @t1 (pseudo, Numero)
        SELECT DISTINCT x.pseudo, x.Numero 
        FROM   @inserted AS x
          JOIN BLINDTEST AS y ON x.numero = y.Numero
          JOIN BLINDEUR AS z ON y.animateurId = z.blindeurId
          JOIN BLINDEUR AS t on x.pseudo = t.pseudo                               
        WHERE z.pseudo = t.pseudo
            ;
    
    INSERT INTO @t3 (pseudo) 
        SELECT DISTINCT pseudo FROM @t1 ;
    
    SET @nbExclusions = (SELECT COUNT(*) FROM @t3)
    
    ----------------------------------------------------
    -- Si on constate une tentative de viol de la
    -- contrainte d'exclusion, on rouspète 
    -- et on s'en va.
    ----------------------------------------------------
    
    IF @nbExclusions > 0
        BEGIN
                        
            IF @nbExclusions = 1
                BEGIN  
                    SET @Engueulade 
                        = @EngueuladeInit
                        + 'le pseudo proposé est celui de ' 
                        + 'l''animateur du blindtest.' 
                END  
            ELSE
                BEGIN  
                    SET @Engueulade 
                      = @EngueuladeInit
                      + 'Il y a '
                      + CAST(@nbExclusions as VARCHAR(5)) 
                      + ' pseudos proposés qui sont ceux '
                      + 'd''animateurs des blindtests.'
                END
      
            SELECT @Engueulade as ' '
            SELECT pseudo as 'pseudo déjà animateur du blindtest'
                 , Numero
            FROM @t1
    
            SET @exclusionRespectee = 'N'
            RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
            ----        RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
        END  
    END
    GO
    

    DROP PROCEDURE IF EXISTS PARTICIPER_BLINDTEST_EXISTENCE_PROC
    
    GO
    
    --------------------------------------------
    -- vérification de l'existence des numéros 
    -- des blindtests
    --------------------------------------------
    
    CREATE PROCEDURE PARTICIPER_BLINDTEST_EXISTENCE_PROC
    (
      @theTrigger as VARCHAR(64)             -- trigger appelant
    , @tableCible as VARCHAR(48)             -- table mise à jour
    , @typeOperation as VARCHAR(48)          -- insert ou update
    , @inserted as blindeur_blindtest_table_type READONLY   
    , @blindtestConnu  as CHAR(1) OUTPUT     -- succès ou échec
    )
    AS
    BEGIN
    
    DECLARE @theProc as  VARCHAR(48) = OBJECT_NAME(@@PROCID)
    DECLARE @typeOperationLibelle as VARCHAR(48) = 'insert dans ' 
    IF LOWER(@typeOperation) = 'update' SET @typeOperationLibelle = 'update de '  
    DECLARE @EngueuladeInit AS VARCHAR(128) = @theTrigger        
                                            + ' / Procédure ' + @theProc + ' - '
                                            + @typeOperationLibelle 
                                            + @tableCible 
                                            + ' - rejet : ' 
                                            + CHAR(13) ;
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @NumeroKount as INT ;
    
    DECLARE @t1 as TABLE 
    (
        pseudo varchar(48)
      , Numero varchar(48)
    )
    
    DECLARE @t2 as TABLE 
    (
        Numero varchar(48)
    )
    
    INSERT INTO @t1 (pseudo, Numero)
        SELECT DISTINCT pseudo, Numero 
        FROM   @inserted AS x
        WHERE  NOT EXISTS (SELECT Numero
                           FROM   BLINDTEST AS y
                           WHERE  x.Numero = y.Numero)
    ;
    ------------------------------------------
    -- Nombre exact de numéros inconnus
    ------------------------------------------
    
    INSERT INTO @t2 (Numero) 
        SELECT DISTINCT Numero FROM @t1 ;
    
    SET @NumeroKount = (SELECT COUNT(*) FROM @t2)
    
    IF @NumeroKount > 0
        BEGIN
        -----------------------------------------
        -- Les numéros effectivement inconnus
        -----------------------------------------
            IF @NumeroKount = 1
                BEGIN
                    SET @Engueulade 
                      = @EngueuladeInit
                      + 'blindtest proposé : inconnu.' 
                END
            ELSE
                BEGIN
                    SET @Engueulade 
                      = @EngueuladeInit
                      + 'Il y a '
                      + CAST(@NumeroKount as VARCHAR(5)) 
                      + ' blindtests proposés mais inconnus.'
                END
            SELECT @Engueulade as ' ' ;
            SELECT
                   pseudo 
                 , Numero as 'Numero proposé inconnu'
            FROM @t1
            SET @blindtestConnu = 'N'
            RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
    ----    RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
      
        END
    
    END
    
    GO
    

    ----------------------------------------------------------
    -- Détection des tentatives de clé primaire en double 
    -- dans la table PARTICIPER.
    -----------------------------------------------------------
    
    DROP PROCEDURE IF EXISTS PARTICIPER_DOUBLON_PK_PROC
    
    GO
    
    CREATE PROCEDURE PARTICIPER_DOUBLON_PK_PROC
    (
      @theTrigger as VARCHAR(64)                -- trigger appelant
    , @tableCible as VARCHAR(48)                -- table mise à jour
    , @typeOperation as VARCHAR(48)             -- insert ou update
    , @inserted as blindeur_blindtest_table_type READONLY  -- colonnes utiles  
    , @deleted as blindeur_blindtest_table_type READONLY   -- colonnes utiles  
    , @integritePKrespectee as CHAR(1) OUTPUT   -- succès ou échec
    )
    AS
    BEGIN
    
    DECLARE @theProc as  VARCHAR(48) = OBJECT_NAME(@@PROCID)
    DECLARE @typeOperationLibelle as VARCHAR(48) = 'insert dans ' 
    IF LOWER(@typeOperation) = 'update' SET @typeOperationLibelle = 'update de '  
    DECLARE @EngueuladeInit AS VARCHAR(128) = @theTrigger        
                                            + ' / Procédure ' + @theProc + ' - '
                                            + @typeOperationLibelle 
                                            + @tableCible 
                                            + ' - rejet : ' 
                                            + CHAR(13) ;
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @NbPKenDoubleExterne as INT ;
    DECLARE @NbPKenDoubleInterne as INT ;
    
    ----------------------------------------------------------
    -- Détection des tentatives de clé primaire en double :
    -- on vérifie si @inserted ne contient pas une occurrence
    -- {Numero, pseudo} déjà présente dans PARTICIPER.
    -----------------------------------------------------------
    
    SET @NbPKenDoubleExterne = 
        (SELECT COUNT(*) 
         FROM   PARTICIPER as x 
         WHERE EXISTS 
             (SELECT *
              FROM  @inserted as y
              WHERE x.joueurId = y.blindeurId
                AND x.blindtestId = y.blindtestId))
        ;
    ------------------------------------------------------------
    -- Détection des tentatives de clé primaire en double :
    -- On vérifie si INSERTED ne contient pas deux occurences 
    -- {Numero, pseudo} égales.
    -------------------------------------------------------------
    
    SET @NbPKenDoubleInterne =
        COALESCE((SELECT COUNT(*) 
                  FROM   @inserted 
                  GROUP BY Numero, pseudo 
                  HAVING COUNT(*) > 1), 0)
    
    ----------------------------------------------------
    -- Si on constate une tentative de clé primaire
    -- en double, on rouspète et on signale.
    ----------------------------------------------------
    
    IF @NbPKenDoubleExterne > 0 OR @NbPKenDoubleInterne > 1
        BEGIN
            SET @Engueulade = @EngueuladeInit 
                    + 'Tentative de clé primaire en double : ' 
                    + 'la table ' + @tableCible 
                    + ' ne sera donc pas mise à jour.'
            SELECT @Engueulade as ' ' ;
            IF LOWER(@typeOperation) = 'insert'
                BEGIN
                    SELECT Numero as 'Numero', pseudo as 'pseudo'  
                    FROM @inserted
                END
            ELSE
                BEGIN  
                    SELECT Numero as 'Numero (à remplacer)', pseudo as 'pseudo (à remplacer)'  
                    FROM @deleted 
                    SELECT Numero as 'Numero (remplaçant)', pseudo as 'pseudo (remplaçant)'  
                    FROM @inserted
                END
            SET @integritePKrespectee = 'N'
            RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
    ----   RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
        END
    
    END
    
    GO
    

     
    Faites simple, mais pas plus simple ! (A. Einstein)
    E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    Je ne réponds pas aux questions techniques par MP. Les forums sont là pout ça.
    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench

  14. #54
    Membre régulier
    Merci une fois encore pour tout ce travail !

  15. #55
    Expert éminent sénior
    triggers - associations blnidtests - extraits
    Bonsoir Mike,


    Venons-en aux associations entre les blindtests et les extraits, c’est-à-dire à la table COMPOSER. On a trois triggers appartenant à la vue COMPOSER_V, à savoir COMPOSER_INSERT_TR, COMPOSER_UPDATE_TR, COMPOSER_DELETE_TR.

    les triggers PARTICIPER_INSERT_TR et PARTICIPER_UPDATE_TR font appel aux procédures suivantes :

    — INSERTED_VIDE_PROC (déjà décrite, cf. posts #52 et #53)

    — COMPOSER_BLINDTEST_EXISTENCE_PROC (s’assurer que le blindtest proposé existe bien)

    — COMPOSER_EXTRAIT_EXISTENCE_PROC (s’assurer que l’extrait proposé existe bien)

    — COMPOSER_DOUBLON_PK_PROC (s’assurer qu’il n’y aura pas de tentative de clé primaire en double)


    Voici le code des 3 triggers, les procédures auxquelles ils s’adressent feront l’objet du post suivant.


    (1) Le trigger dédié aux inserts via la vue COMPOSER_V :

    DROP TRIGGER IF EXISTS COMPOSER_INSERT_TR ;
    
    GO
    
    CREATE TRIGGER COMPOSER_INSERT_TR ON COMPOSER_V INSTEAD OF INSERT
    AS
    
    DECLARE @tableCible as VARCHAR(48) = 'COMPOSER'
    DECLARE @theTrigger as VARCHAR(64) =  'Trigger ' + OBJECT_NAME(@@PROCID)
    DECLARE @typeOperation as VARCHAR(48) = 'insert' 
    DECLARE @blindtestConnu as CHAR(1) = 'Y'
    DECLARE @blindtestParticipant as CHAR(1) = 'Y'
    DECLARE @extraitConnu as CHAR(1) = 'Y'
    DECLARE @extraitParticipant as CHAR(1) = 'Y'
    DECLARE @insertedNonVide  as CHAR(1) = 'Y'
    DECLARE @integritePKrespectee as CHAR(1) = 'Y' ;
    
    --------------------------------------------------------------------
    -- La table @inserted est du type blindtest_extrait_table_type :
    -- ceci permet de passser les données des tables aux procédures.
    -- Rappel du type blindtest_extrait_table_type :
    -- (blindtestId, extraitId, Numero, artiste, titre).
    -- Pour sa part, la table @deleted est vide, elle ne sert 
    -- qu'en update. 
    ---------------------------------------------------------------------
    
    DECLARE @inserted AS blindtest_extrait_table_type ;
    DECLARE @deleted AS blindtest_extrait_table_type ;  -- table vide
    
    ---------------------------------------------
    -- Projection de @inserted sur le blindtest
    --------------------------------------------
    
    DECLARE @insertedBlindtest AS blindtest_table_type ;
    
    --------------------------------------------------
    -- c'est parti
    --------------------------------------------------
    
    ---- pour debug ---- select '' as INSERTED, * from INSERTED  
    
    ----------------------------------------------------------
    --  On alimente les tables utilisées par les procédures
    --  chargées d'effectuer les différents contrôles.
    ----------------------------------------------------------
    
    INSERT INTO @inserted (blindtestId, extraitId, Numero, artiste, titre)
        SELECT COALESCE(blindtestId, -1), COALESCE(extraitId, -1)
             , x.Numero, x.artiste, x.titre  
        FROM   INSERTED as x
          LEFT JOIN BLINDTEST as y ON x.Numero = y.Numero 
          LEFT JOIN EXTRAIT as z ON x.artiste = z.artiste
                                AND x.titre = z.titre
    ;
    
    INSERT INTO @insertedBlindtest (blindtestId, Numero)
        SELECT blindtestId, Numero  
        FROM   @inserted
    ;
    
    ---------------------------------------------
    -- Si INSERTED est vide, on dégage
    ---------------------------------------------
      
    EXEC  INSERTED_VIDE_PROC
        @theTrigger
      , @tableCible
      , @typeOperation
      , @insertedBlindtest
      , @insertedNonVide OUTPUT
    
    IF @insertedNonVide = 'N' 
        RETURN
    
    ---------------------------------------------------
    -- si un Numero est candidat au
    -- remplacement d'un autre Numero,
    -- il doit être connu dans la table BLINDTEST
    ---------------------------------------------------
      
    EXEC  COMPOSER_BLINDTEST_EXISTENCE_PROC
        @theTrigger
      , @tableCible
      , @typeOperation
      , @inserted
      , @blindtestConnu OUTPUT
    
    ---------------------------------------------------
    -- si un extrait est candidat au
    -- remplacement d'un autre extrait,
    -- il doit être connu dans la table EXTRAIT
    ---------------------------------------------------
      
    EXEC  COMPOSER_EXTRAIT_EXISTENCE_PROC
        @theTrigger
      , @tableCible
      , @typeOperation
      , @inserted
      , @extraitConnu OUTPUT
    
    --------------------------------------------------------
    -- Détection des tentatives de clé primaire en double
    ---------------------------------------------------------
    
    IF @blindtestConnu = 'Y' 
        BEGIN
            EXEC COMPOSER_DOUBLON_PK_PROC
              @theTrigger
            , @tableCible
            , @typeOperation
            , @inserted
            , @deleted
            , @integritePKrespectee OUTPUT   
        END
    
    ------------------------------------------------------
    -- Mise à jour de la table COMPOSER.
    -- Si on a détecté des erreurs, on ne fait rien.
    ------------------------------------------------------
    
    IF @blindtestConnu = 'Y' AND @extraitConnu = 'Y'
        AND @integritePKrespectee = 'Y'
        BEGIN
            ---- pour debug ---- select '' as 'composer avant insert', * from COMPOSER
    
            INSERT INTO COMPOSER (blindtestId, extraitId)
                SELECT blindtestId, extraitId 
                FROM @inserted
            ;
        END
    
    GO
    

    (2)Le trigger dédié aux updates via la vue COMPOSER_V :

    DROP TRIGGER IF EXISTS COMPOSER_UPDATE_TR ;
    
    GO
    
    CREATE TRIGGER COMPOSER_UPDATE_TR ON COMPOSER_V INSTEAD OF UPDATE
    AS
    
    DECLARE @tableCible as VARCHAR(48) = 'COMPOSER'
    DECLARE @theTrigger as VARCHAR(64) = 'Trigger ' + OBJECT_NAME(@@PROCID)
    DECLARE @typeOperation as VARCHAR(48) = 'update' 
    DECLARE @EngueuladeInit AS VARCHAR(128) = @theTrigger 
                                            + 'Update rejeté de ' 
                                            + @tableCible + ' - ';
    DECLARE @Engueulade AS VARCHAR(512) ;
    
    DECLARE @nbDeleted as INT ;
    DECLARE @blindtestConnu as CHAR(1) = 'Y'
    DECLARE @extraitConnu as CHAR(1) = 'Y'
    DECLARE @integritePKrespectee as CHAR(1) = 'Y' ;
    DECLARE @insertedNonVide  as CHAR(1) = 'Y'
    
    --------------------------------------------------------------------
    -- Les tables @inserted et @deleted sont du type 
    -- blindtest_extrait_table_type :
    -- ceci permet de passser les données des tables aux procédures.
    -- Rappel du blindtest_extrait_table_type :
    -- (blindtestId, extraitId, Numero, artiste, titre)..
    ---------------------------------------------------------------------
    
    DECLARE @inserted AS blindtest_extrait_table_type ;
    DECLARE @deleted AS blindtest_extrait_table_type ;  -- table vide
    
    ---------------------------------------------
    -- Projection de @inserted sur le blindtest
    --------------------------------------------
    
    DECLARE @insertedBlindtest AS blindtest_table_type ;
    
    --------------------------------------------------
    -- c'est parti
    --------------------------------------------------
    
    ---- pour debug ---- select '' as INSERTED, * from INSERTED  
    ---- pour debug ---- select '' as DELETED, * from DELETED  
    
    ----------------------------------------------------------
    --  On alimente les tables utilisées par les procédures
    --  chargées d'effectuer les différents contrôles.
    ----------------------------------------------------------
    
    INSERT INTO @inserted (blindtestId, extraitId, Numero, artiste, titre)
        SELECT COALESCE(blindtestId, -1), COALESCE(extraitId, -1)
             , x.Numero, x.artiste, x.titre  
        FROM   INSERTED as x
          LEFT JOIN BLINDTEST as y ON x.Numero = y.Numero 
          LEFT JOIN EXTRAIT as Z ON x.artiste = z.artiste
                                AND x.titre = z.titre
    ;
    
    INSERT INTO @deleted (blindtestId, extraitId, Numero, artiste, titre)
        SELECT COALESCE(blindtestId, -1), COALESCE(extraitId, -1)
             , x.Numero, x.artiste, x.titre  
        FROM   DELETED as x
          LEFT JOIN BLINDTEST as y ON x.Numero = y.Numero 
          LEFT JOIN EXTRAIT as Z ON x.artiste = z.artiste
                                AND x.titre = z.titre
    ;
     
    SET @nbDeleted = (SELECT COUNT(*) from @deleted)
     
    INSERT INTO @insertedBlindtest (blindtestId, Numero)
        SELECT blindtestId, Numero  
        FROM   @inserted
    ;
    
    ---------------------------------------------
    -- Si INSERTED est vide, on dégage
    ---------------------------------------------
      
    EXEC  INSERTED_VIDE_PROC
        @theTrigger
      , @tableCible
      , @typeOperation
      , @insertedBlindtest
      , @insertedNonVide OUTPUT
    
    IF @insertedNonVide = 'N' 
        RETURN
    
    ---------------------------------------------------
    -- si un Numero est candidat au
    -- remplacement d'un autre Numero,
    -- il doit être connu dans la table BLINDTEST
    ---------------------------------------------------
      
    EXEC  COMPOSER_BLINDTEST_EXISTENCE_PROC
        @theTrigger
      , @tableCible
      , @typeOperation
      , @inserted
      , @blindtestConnu OUTPUT
    
    ---------------------------------------------------
    -- si un extrait est candidat au
    -- remplacement d'un autre extrait,
    -- il doit être connu dans la table EXTRAIT
    ---------------------------------------------------
      
    EXEC  COMPOSER_EXTRAIT_EXISTENCE_PROC
        @theTrigger
      , @tableCible
      , @typeOperation
      , @inserted
      , @extraitConnu OUTPUT
     
    -----------------------------------------------------------
    -- Détection des tentatives de clé primaire en double :
    -- Si INSERTED contient une ligne {Numero,artiste, titre}
    -- déjà présente dans la table COMPOSER, on évitera
    -- de mettre celle-ci à jour...
    ------------------------------------------------------------
    
    IF @blindtestConnu = 'Y' AND @extraitConnu = 'Y'
        BEGIN
            EXEC COMPOSER_DOUBLON_PK_PROC
              @theTrigger
            , @tableCible
            , @typeOperation
            , @inserted
            , @deleted
            , @integritePKrespectee OUTPUT   
        END
    
    ------------------------------------------------------
    -- Mise à jour de la table COMPOSER.
    -- Si on a détecté des erreurs, on ne fait rien.
    ------------------------------------------------------
    
    IF @blindtestConnu = 'Y' AND @extraitConnu = 'Y'
        AND @integritePKrespectee = 'Y'
        BEGIN
            ---------------------------------------------
            -- Si l'attribut Numero est touché par
            -- l'update, mais pas les attributs artiste
            -- et titre,
            -- alors on se limite à l'update de Numero.
            ---------------------------------------------
    
            IF UPDATE (Numero) AND NOT UPDATE (artiste) AND NOT UPDATE (titre)
                BEGIN  
                    ;
                    WITH Restriction as 
                        (SELECT blindtestId, extraitId 
                         FROM   COMPOSER                             
                         WHERE blindtestId IN (SELECT DISTINCT blindtestId FROM @deleted)
                           AND extraitId IN (SELECT DISTINCT extraitId FROM @deleted)) 
                        UPDATE Restriction
                            SET blindtestId = x.blindtestId
                                FROM Restriction as y
                                JOIN @inserted as x on x.extraitId = y.extraitId
                    ;
                    RETURN       
                END  
    
            ---------------------------------------------
            -- Si l'attribut artiste ou l'attribut titre 
            -- est touché par l'update, mais pas 
            -- l'attribut Numero,
            -- alors on se limite à l'update de artiste
            -- et titre.
            ---------------------------------------------
    
            IF NOT UPDATE (Numero) AND (UPDATE (artiste) OR UPDATE (titre))
                BEGIN  
                    ;
                    WITH Restriction as 
                        (SELECT blindtestId, extraitId 
                         FROM   COMPOSER                             
                         WHERE blindtestId IN (SELECT DISTINCT blindtestId FROM @deleted)
                           AND extraitId IN (SELECT DISTINCT extraitId FROM @deleted)) 
                        UPDATE Restriction
                            SET extraitId = x.extraitId
                                FROM Restriction as y
                                JOIN @inserted as x on x.blindtestId = y.blindtestId
                    ;
                    RETURN       
                END  
    
            IF UPDATE (Numero) AND (UPDATE (artiste) OR UPDATE (titre))
                BEGIN
                   ------------------------------------------------
                    -- Les attributs Numero, artiste, titre sont 
                    -- touchés par l'update.
                    -- On se sert de l'UPDATE classique
                    -- mais bien moins puissant que celui
                    -- pilotable par WITH, lequel ne permet pas
                    -- de mettre à joursimultanément deux
                    -- colonnes...
                    ------------------------------------------------
    
                     IF @nbDeleted < 2
                        BEGIN 
                            UPDATE COMPOSER 
                                SET  
                                    blindtestId = 
                                        (SELECT DISTINCT blindtestId 
                                         FROM   @inserted)
                                  , extraitId =  
                                        (SELECT DISTINCT extraitId
                                         FROM   @inserted)
                                WHERE blindtestId IN (SELECT DISTINCT blindtestId FROM @deleted)
                                  AND extraitId IN (SELECT DISTINCT extraitId FROM @deleted) 
                        END 
                    ELSE
                        BEGIN 
                            SET @Engueulade = @EngueuladeInit 
                                            + 'Update affectant ' 
                                            + 'simultanément plus d''une ligne. '
                                            + CHAR(13)
                                            + 'La table ' + @tableCible 
                                            + ' ne sera donc pas mise à jour.'
                            SELECT @Engueulade AS ' ' ;
                            SELECT ' ' as 'échec donc, hélas ! (deleted)' 
                                 , Numero  as 'numero à remplacer' 
                                 , artiste as 'artiste à remplacer'
                                 , titre as 'titre à remplacer'
                            FROM DELETED
    
                            SELECT ' ' as 'échec donc, hélas ! (inserted)' 
                                 , Numero as 'numero remplaçant' 
                                 , artiste as 'artiste remplaçant'
                                 , titre as 'titre remplaçant'
                            FROM INSERTED
    
                            RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
                     ----   RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
                            RETURN  
                        END 
                END
        END
    
    GO
    

    (3) Le trigger dédié aux updates via la vue COMPOSER_V :

    DROP TRIGGER IF EXISTS COMPOSER_DELETE_TR ;
    GO
    
    CREATE TRIGGER COMPOSER_DELETE_TR ON COMPOSER_V INSTEAD OF DELETE
    AS
    
    DECLARE @tableCible as VARCHAR(48) = 'COMPOSER'
    
    DECLARE @theTrigger as VARCHAR(64) =  'Trigger ' + OBJECT_NAME(@@PROCID)
    DECLARE @EngueuladeInit AS VARCHAR(128) = @theTrigger + ' - delete dans ' + @tableCible + ' - ';
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @nbDeleted as INT ;
    
    -----------------------------------------------------------
    -- tables temporaires pour les clés primaires
    -----------------------------------------------------------
    
    DECLARE @deleted as TABLE
    (
        blindtestId  INT
      , extraitId    INT
    ) ;
    
    -----------------------------------------------------
    -- c'est parti !
    -----------------------------------------------------
    
    ---- pour debug ---- select '' as 'DELETED (on delete)', * from DELETED  
    
    ---------------------------------------------
    -- Si DELETED est vide, on dégage
    ---------------------------------------------
    
    SET @nbdeleted = (SELECT COUNT(*) FROM DELETED) ;
    
    IF @nbdeleted = 0
        BEGIN
            SET @Engueulade = @EngueuladeInit 
                            + 'Le trigger reçoit une sélection vide (DELETED), ' 
                            + CHAR(13)
                            + 'la table ' + @tableCible 
                            + ' ne sera donc pas mise à jour.' 
            SELECT @Engueulade as ' ' ;
            RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
            ----    RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
            RETURN  
        END
    
    ------------------------------------------------------
    -- de DELETED vers @deleted :
    -- Traduction des clés alternatives (naturelles) en
    -- clés primaires (artificielles)
    ------------------------------------------------------
    
    INSERT INTO @deleted (blindtestId, extraitId)
        SELECT DISTINCT  z.blindtestId, t.extraitId
        FROM   DELETED AS x
          JOIN BLINDTEST AS z ON x.Numero = z.Numero
          JOIN EXTRAIT AS t ON x.artiste = t.artiste
                           AND x.titre = t.titre
    ;
    
    ------------------------------------------------------
    -- Mise à jour de la table COMPOSER.
    ------------------------------------------------------
    
    DELETE FROM COMPOSER
           WHERE  blindtestId IN (SELECT blindtestId FROM @deleted)
              AND extraitId IN (SELECT extraitId FROM @deleted)  
    ;
    
    GO
    
    Les procédures vont suivre.



     
    Faites simple, mais pas plus simple ! (A. Einstein)
    E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    Je ne réponds pas aux questions techniques par MP. Les forums sont là pout ça.
    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench

  16. #56
    Expert éminent sénior
    procédures - associations blnidtests - extraits
    Bonsoir Mike,


    Voici donc les procédures utilisées par les triggers de la vue COMPOSER_V :


    — INSERTED_VIDE_PROC cf. : posts #52 et #53


    — COMPOSER_BLINDTEST_EXISTENCE_PROC :

    DROP PROCEDURE IF EXISTS COMPOSER_BLINDTEST_EXISTENCE_PROC
    
    GO
    
    --------------------------------------------
    -- vérification de l'existence des numéros 
    -- des blindtests
    --------------------------------------------
    
    CREATE PROCEDURE COMPOSER_BLINDTEST_EXISTENCE_PROC
    (
      @theTrigger as VARCHAR(64)         -- trigger appelant
    , @tableCible as VARCHAR(48)         -- table mise à jour
    , @typeOperation as VARCHAR(48)      -- insert ou update
    , @inserted as blindtest_extrait_table_type READONLY -- colonnes utiles    
    , @blindtestConnu as CHAR(1) OUTPUT  -- succès ou échec
    )
    AS
    BEGIN
    
    DECLARE @theProc as  VARCHAR(48) = OBJECT_NAME(@@PROCID)
    DECLARE @typeOperationLibelle as VARCHAR(48) = 'insert dans ' 
    IF LOWER(@typeOperation) = 'update' SET @typeOperationLibelle = 'update de '  
    DECLARE @EngueuladeInit AS VARCHAR(128) = @theTrigger        
                                            + ' / Procédure ' + @theProc + ' - '
                                            + @typeOperationLibelle 
                                            + @tableCible 
                                            + ' - rejet : ' 
                                            + CHAR(13) ;
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @NumeroKount as INT ;
    
    DECLARE @t1 as TABLE 
    (
        Numero varchar(48)
      , artiste varchar(48)
      , titre varchar(48)
    )
    
    DECLARE @t2 as TABLE 
    (
        Numero varchar(48)
    )
    
    ---------------------------------------------------
    -- si un Numero est candidat au
    -- remplacement d'un autre Numero,
    -- il doit être connu dans la table BLINDTEST
    ---------------------------------------------------
    
    ---------------------------------------
    -- @t1 : les codes blintests inconnus
    ---------------------------------------
    
    INSERT INTO @t1 (Numero, artiste, titre)
        SELECT DISTINCT Numero, artiste, titre 
        FROM   @inserted AS x
        WHERE  NOT EXISTS (SELECT Numero
                           FROM   BLINDTEST AS y
                           WHERE  x.Numero = y.Numero)
    ;
    ------------------------------------------
    -- Nombre exact de numéros inconnus
    ------------------------------------------
    
    INSERT INTO @t2 (Numero) 
        SELECT DISTINCT Numero FROM @t1 ;
    
    SET @NumeroKount = (SELECT COUNT(*) FROM @t2)
    
    IF @NumeroKount > 0
        BEGIN
        -----------------------------------------
        -- Les numéros effectivement inconnus
        -----------------------------------------
            IF @NumeroKount = 1
                BEGIN
                    SET @Engueulade = @EngueuladeInit
                      + 'blindtest proposé : inconnu.' 
                END
            ELSE
                BEGIN
                    SET @Engueulade = @EngueuladeInit 
                      + 'Il y a '
                      + CAST(@NumeroKount as VARCHAR(5)) 
                      + ' blindtests proposés mais inconnus.'
                END
            SELECT @Engueulade AS ' ' ;
            SELECT Numero as 'Numero proposé inconnu'
                 , artiste, titre 
            FROM @t1
            SET @blindtestConnu = 'N'
            RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
    ----    RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer   
        END
    END
    
    GO
    

    — COMPOSER_EXTRAIT_EXISTENCE_PROC :

    DROP PROCEDURE IF EXISTS COMPOSER_EXTRAIT_EXISTENCE_PROC
    
    GO
    
    --------------------------------------------
    -- vérification de l'existence des extraits 
    -- (paires {artiste, titre})
    --------------------------------------------
    
    CREATE PROCEDURE COMPOSER_EXTRAIT_EXISTENCE_PROC
    (
      @theTrigger as VARCHAR(64)          -- trigger appelant
    , @tableCible as VARCHAR(48)          -- table mise à jour
    , @typeOperation as VARCHAR(48)       -- insert ou update
    , @inserted as blindtest_extrait_table_type READONLY -- colonnes utiles      
    , @blindtestConnu  as CHAR(1) OUTPUT  -- succès ou échec
    )
    AS
    BEGIN
    
    DECLARE @theProc as  VARCHAR(48) = OBJECT_NAME(@@PROCID)
    DECLARE @typeOperationLibelle as VARCHAR(48) = 'insert dans ' 
    IF LOWER(@typeOperation) = 'update' SET @typeOperationLibelle = 'update de '  
    DECLARE @EngueuladeInit AS VARCHAR(128) = @theTrigger        
                                            + ' / Procédure ' + @theProc + ' - '
                                            + @typeOperationLibelle 
                                            + @tableCible 
                                            + ' - rejet : ' 
                                            + CHAR(13) ;
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @extraitCodeKount as INT ;
    
    DECLARE @t1 as TABLE 
    (
        Numero varchar(48)
      , artiste varchar(48)
      , titre varchar(48)
    )
    DECLARE @t2 as TABLE 
    (
        Numero varchar(48)
      , artiste varchar(48)
      , titre varchar(48)
    )
    
    ---------------------------------------
    -- @t1 : les extraits inconnus
    ---------------------------------------
    
    INSERT INTO @t1 (Numero, artiste, titre)
        SELECT DISTINCT Numero, artiste, titre 
        FROM   @inserted AS x
        WHERE  NOT EXISTS (SELECT artiste, titre
                           FROM   EXTRAIT AS y
                           WHERE  x.artiste = y.artiste 
                              AND x.titre = y.titre)
    ;
    ------------------------------------------
    -- Nombre exact d'extraits inconnus
    ------------------------------------------
    
    INSERT INTO @t2 (artiste, titre) 
        SELECT DISTINCT artiste, titre FROM @t1 ;
    
    SET @extraitCodeKount = (SELECT COUNT(*) FROM @t2)
    
    IF @extraitCodeKount > 0
        BEGIN
        ------------------------------------------------------
        -- Les paires {artiste, titre} effectivement inconnues
        -------------------------------------------------------
            IF @extraitCodeKount = 1
                BEGIN
                    SET @Engueulade = @EngueuladeInit 
                      + 'extrait {artiste, titre} non reconnu.' 
                END
            ELSE
                BEGIN
                    SET @Engueulade = @EngueuladeInit 
                      + 'Il y a ' + cast(@extraitCodeKount as varchar(5)) 
                      + ' extraits {artiste, titre} proposés non reconnus.'
                END
    
            SELECT @Engueulade AS ' ' ;
            SELECT  Numero
                 , '{' + artiste + ', ' + titre + '}' as 'extrait proposé non reconnu' 
            FROM @t1
    
            SET @blindtestConnu = 'N'
            RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
    ----    RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
     
        END
    END
    
    GO
    

    — COMPOSER_DOUBLON_PK_PROC :

    ----------------------------------------------------------
    -- Détection des tentatives de clé primaire en double 
    -- dans la table COMPOSER.
    -----------------------------------------------------------
    
    DROP PROCEDURE IF EXISTS COMPOSER_DOUBLON_PK_PROC
    
    GO
    
    CREATE PROCEDURE COMPOSER_DOUBLON_PK_PROC
    (
      @theTrigger as VARCHAR(64)               -- trigger appelant
    , @tableCible as VARCHAR(48)               -- table mise à jour
    , @typeOperation as VARCHAR(48)            -- insert ou update
    , @inserted as blindtest_extrait_table_type READONLY -- colonnes utiles   
    , @deleted as blindtest_extrait_table_type READONLY  -- colonnes utiles   
    , @integritePKrespectee as CHAR(1) OUTPUT  -- succès ou échec
    )
    AS
    BEGIN
    
    DECLARE @theProc as  VARCHAR(48) = OBJECT_NAME(@@PROCID)
    DECLARE @typeOperationLibelle as VARCHAR(48) = 'insert dans ' 
    IF LOWER(@typeOperation) = 'update' SET @typeOperationLibelle = 'update de '  
    DECLARE @EngueuladeInit AS VARCHAR(128) = @theTrigger        
                                            + ' / Procédure ' + @theProc + ' - '
                                            + @typeOperationLibelle 
                                            + @tableCible 
                                            + ' - rejet : ' 
                                            + CHAR(13) ;
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @NbPKenDoubleExterne as INT ;
    DECLARE @NbPKenDoubleInterne as INT ;
    
    ----------------------------------------------------------
    -- Détection des tentatives de clé primaire en double :
    -- on vérifie si @inserted ne contient pas une occurrence
    -- {Numero, extrait} déjà présente dans COMPOSER.
    -----------------------------------------------------------
    
    SET @NbPKenDoubleExterne = 
        (SELECT COUNT(*) 
         FROM   COMPOSER as x 
         WHERE EXISTS 
             (SELECT *
              FROM  @inserted as y
              WHERE x.blindtestId = y.blindtestId
                AND x.extraitId = y.extraitId))
    
    ------------------------------------------------------------
    -- Détection des tentatives de clé primaire en double :
    -- On vérifie si INSERTED ne contient pas deux occurences 
    -- {Numero, extrait} égales.
    -------------------------------------------------------------
    
    SET @NbPKenDoubleInterne =
        COALESCE((SELECT COUNT(*) 
                  FROM   @inserted 
                  GROUP BY blindtestId, extraitId 
                  HAVING COUNT(*) > 1), 0)
    
    ----------------------------------------------------
    -- Si on constate une tentative de clé primaire
    -- en double, on rouspète et on signale.
    ----------------------------------------------------
    
    IF @NbPKenDoubleExterne > 0 OR @NbPKenDoubleInterne > 1
        BEGIN
            SET @Engueulade = @EngueuladeInit 
                    + 'Tentative de clé primaire en double : ' 
                    + 'la table ' + @tableCible 
                    + ' ne sera donc pas mise à jour.'
            SELECT @Engueulade as ' '
            IF LOWER(@typeOperation) = 'insert'
                BEGIN
                    SELECT Numero as 'Numero'
                         , artiste as 'artiste'  
                         , titre as 'titre'  
                    FROM @inserted
                END
            ELSE
                BEGIN  
                    SELECT Numero as 'Numero (à remplacer)'
                         , artiste as 'artiste (à remplacer)'  
                         , titre as 'titre (à remplacer)'  
                    FROM @deleted 
                    SELECT Numero as 'Numero (remplaçant)'
                         , artiste as 'artiste (remplaçant)'  
                         , titre as 'titre (remplaçant)'  
                    FROM @inserted
                END
            SET @integritePKrespectee = 'N'
            RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
    ----   RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
        END
    END
    
    GO
    




     
    Faites simple, mais pas plus simple ! (A. Einstein)
    E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    Je ne réponds pas aux questions techniques par MP. Les forums sont là pout ça.
    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench

  17. #57
    Expert éminent sénior
    triggers - associations joueurs - blnidtests - extraits
     

    Venons-en aux associations entre les joueurs, les blindtests et les extraits, c’est-à-dire à la table TROUVER. On a trois triggers appartenant à la vue TROUVER_V, à savoir TROUVER_INSERT_TR, TROUVER_UPDATE_TR, TROUVER_DELETE_TR.

    les triggers TROUVER_INSERT_TR et TROUVER_UPDATE_TR font appel aux procédures suivantes :

    — INSERTED_VIDE_PROC (déjà décrite, cf. posts #52 et #53)

    — TROUVER_BLINDTEST_EXISTENCE_PROC (s’assurer que le blindtest proposé existe bien)

    — TROUVER_BLINDEUR_EXISTENCE_PROC (s’assurer que le blindeur proposé existe bien)

    — TROUVER_BLINDEUR_EST_JOUEUR_PROC (s’assurer que le blindeur est bien un joueur)

    — TROUVER_EXTRAIT_EXISTENCE_PROC (s’assurer que l’extrait proposé existe bien)

    — TROUVER_BLINDTEST_BLINDEUR_EXISTENCE_PROC (s’assurer que le joueur supposé trouver participe au blindtest)

    — TROUVER_DOUBLON_PK_PROC (s’assurer qu’il n’y aura pas de tentative de clé primaire en double)


    Voici le code des 3 triggers, les procédures auxquelles ils s’adressent feront l’objet du post suivant.


    (1) Le trigger dédié aux inserts via la vue TROUVER_V :

    --------------------------------------------------------
    -- inserts dans la table TROUVER, via la vue TROUVER_V
    ---------------------------------------------------------
    
    DROP TRIGGER IF EXISTS TROUVER_INSERT_TR ;
    GO
    
    CREATE TRIGGER TROUVER_INSERT_TR ON TROUVER_V INSTEAD OF INSERT
    AS
    DECLARE @tableCible as VARCHAR(48) = 'TROUVER'
    DECLARE @theTrigger as VARCHAR(64) =  'Trigger ' + OBJECT_NAME(@@PROCID)
    DECLARE @typeOperation as VARCHAR(48) = 'insert' 
    DECLARE @tableComposer as VARCHAR(48) = 'COMPOSER'
    DECLARE @tableParticiper as VARCHAR(48) = 'PARTICIPER'
    
    DECLARE @pseudoConnu as CHAR(1) = 'Y'
    DECLARE @pseudoParticipant as CHAR(1) = 'Y'
    DECLARE @blindtestConnu as CHAR(1) = 'Y'
    DECLARE @blindtestPseudoParticipant as CHAR(1) = 'Y' 
    DECLARE @extraitConnu as CHAR(1) = 'Y'
    DECLARE @blindtestExtraitParticipant as CHAR(1) = 'Y'
    DECLARE @blindeurEstJoueur  as CHAR(1) = 'Y'
    DECLARE @integritePKrespectee as CHAR(1) = 'Y'
    DECLARE @insertedNonVide  as CHAR(1) = 'Y'
    
    ----------------------------------------------------------------------------
    -- La table @inserted est du type blindeur_blindtest_extrait_table_type :
    -- ceci permet de passer les données des tables aux procédures.
    -- Rappel du type blindeur_blindtest_extrait_table_type :
    -- (blinderId, blindtestId, extraitId, pseudo, Numero, artiste, titre).
    -- Pour sa part, la table @deleted est vide, elle ne sert 
    -- qu'en update. 
    -----------------------------------------------------------------------------
    
    DECLARE @inserted AS blindeur_blindtest_extrait_table_type ;
    DECLARE @deleted AS blindeur_blindtest_extrait_table_type ;  -- table vide
    
    ---------------------------------------------
    -- Projection de @inserted sur le blindtest
    --------------------------------------------
    DECLARE @insertedBlindtest AS blindtest_table_type ;
    
    -----------------------------------------------------
    -- c'est parti !
    -----------------------------------------------------
    
    ---- pour debug ---- select '' as INSERTED, * from INSERTED  
    
    ----------------------------------------------------------
    --  On alimente les tables utilisées par les procédures
    --  chargées d'effectuer les différents contrôles.
    ----------------------------------------------------------
    
    INSERT INTO @inserted (blindeurId, blindtestId, extraitId
                         , pseudo, Numero, artiste, titre)
        SELECT COALESCE(blindeurId, -1)
             , COALESCE(blindtestId, -1)
             , COALESCE(extraitId, -1)
             , x.pseudo, x.Numero, x.artiste, x.titre  
        FROM   INSERTED as x
          LEFT JOIN BLINDEUR as y ON x.pseudo = y.pseudo
          LEFT JOIN BLINDTEST as z ON x.Numero = z.Numero 
          LEFT JOIN EXTRAIT as t ON x.artiste = t.artiste
                                AND x.titre = t.titre
    ;
    
    ----------------------------------------------
    -- pour test de vacuité de INSERTED
    ---------------------------------------------- 
    
    INSERT INTO @insertedBlindtest (blindtestId, Numero)
        SELECT blindtestId, Numero  
        FROM   @inserted
    ;
    ---------------------------------------------
    -- Si INSERTED est vide, on dégage
    ---------------------------------------------
      
    EXEC  INSERTED_VIDE_PROC
        @theTrigger
      , @tableCible
      , @typeOperation
      , @insertedBlindtest
      , @insertedNonVide OUTPUT
    
    IF @insertedNonVide = 'N' 
        RETURN
    
    ------------------------------------------------------------
    -- on vérifie les valeurs fournies dans INSERTED :
    -- les joueurs doivent exister en tant que blindeurs,
    -- les numeros des blindtests doivent exister,
    -- les paires (artiste, titre) des extraits doivent exister.
    -------------------------------------------------------------
    
    ----------------------------------------
    -- les numéros des blindtests doivent
    -- exister
    ----------------------------------------
      
    EXEC  TROUVER_BLINDTEST_EXISTENCE_PROC
        @theTrigger
      , @tableCible
      , @typeOperation
      , @inserted
      , @blindtestConnu OUTPUT
    
    ----------------------------------------
    -- les pseudos des joueurs doivent
    -- exister
    ----------------------------------------
      
    EXEC TROUVER_BLINDEUR_EXISTENCE_PROC
        @theTrigger
      , @tableCible
      , @typeOperation
      , @inserted
      , @pseudoConnu OUTPUT
    
    ---------------------------------------------------
    -- si un extrait est candidat au
    -- remplacement d'un autre extrait,
    -- il doit être connu dans la table EXTRAIT
    ---------------------------------------------------
      
    EXEC  TROUVER_EXTRAIT_EXISTENCE_PROC
        @theTrigger
      , @tableCible
      , @typeOperation
      , @inserted
      , @extraitConnu OUTPUT
    
    ----------------------------------------------------------------
    -- recherche des blindeurs connus qui disent participer à des 
    -- blintests mais en fait qui ne participent pas
    ----------------------------------------------------------------
    
    IF @pseudoConnu = 'Y' AND @blindtestConnu = 'Y' 
        BEGIN
            EXEC  TROUVER_BLINDEUR_EST_JOUEUR_PROC
                @theTrigger
              , @tableCible
              , @typeOperation
              , @tableParticiper
              , @inserted
              , @blindeurEstJoueur  OUTPUT
        END
    
    ----------------------------------------------------------------
    -- recherche des paires blindtest/extrait inexistantes 
    -- dans la table de référence COMPOSER
    ----------------------------------------------------------------
    
    IF @blindtestConnu = 'Y' AND @extraitConnu = 'Y'
        BEGIN
            EXEC  TROUVER_BLINDTEST_EXTRAIT_EXISTENCE_PROC
                @theTrigger
              , @tableCible
              , @typeOperation
              , @tableComposer
              , @inserted
              , @blindtestExtraitParticipant OUTPUT
        END
    
    ----------------------------------------------------------------
    -- recherche des paires blindtest/pseudo inexistantes 
    -- dans la table de référence PARTICIPER
    ----------------------------------------------------------------
    
    IF @blindtestConnu = 'Y' AND @pseudoConnu = 'Y'
        BEGIN
            EXEC  TROUVER_BLINDTEST_BLINDEUR_EXISTENCE_PROC
                @theTrigger
              , @tableCible
              , @typeOperation
              , @tableParticiper
              , @inserted
              , @blindtestPseudoParticipant OUTPUT
        END
    
    --------------------------------------------------------------------------
    -- Détection des tentatives de clé primaire en double :
    -- Si INSERTED contient une ligne {pseudo, Numero, artiste, titre}
    -- déjà présente dans la table TROUVER, on évitera
    -- de mettre celle-ci à jour...
    --------------------------------------------------------------------------
    
    IF @pseudoConnu  = 'Y' AND @pseudoParticipant = 'Y'
        AND @blindeurEstJoueur  = 'Y'
        AND @blindtestConnu = 'Y' AND @extraitConnu = 'Y'  
        AND @blindtestExtraitParticipant = 'Y'
        AND @blindtestPseudoParticipant = 'Y' 
    
        BEGIN
            EXEC  TROUVER_DOUBLON_PK_PROC
                @theTrigger
              , @tableCible
              , @typeOperation
              , @inserted
              , @deleted
              , @integritePKrespectee OUTPUT
        END
    
    ------------------------------------------------------
    -- Mise à jour de la table TROUVER.
    -- Si on a détecté des erreurs, on ne fait rien.
    ------------------------------------------------------
    
    IF @pseudoConnu  = 'Y' AND @pseudoParticipant = 'Y'
        AND @blindeurEstJoueur  = 'Y'
        AND @blindtestConnu = 'Y' AND @extraitConnu = 'Y' 
        AND @blindtestExtraitParticipant = 'Y' 
        AND @blindtestPseudoParticipant = 'Y' 
        AND @integritePKrespectee = 'Y'
    
        BEGIN
            INSERT INTO TROUVER (joueurId, blindtestId, extraitId)
                SELECT blindeurId, blindtestId, extraitId 
                FROM @inserted
            ;     
        END
    
    GO
    

    (2) Le trigger dédié aux updates via la vue TROUVER_V :

    
    DROP TRIGGER IF EXISTS TROUVER_UPDATE_TR ;
    GO
    
    CREATE TRIGGER TROUVER_UPDATE_TR ON TROUVER_V INSTEAD OF UPDATE
    AS
    
    DECLARE @tableCible as VARCHAR(48) = 'TROUVER'
    DECLARE @theTrigger as VARCHAR(64) = 'Trigger ' + OBJECT_NAME(@@PROCID)
    DECLARE @typeOperation as VARCHAR(48) = 'update' 
    DECLARE @tableComposer as VARCHAR(48) = 'COMPOSER'
    DECLARE @tableParticiper as VARCHAR(48) = 'PARTICIPER'
    
    DECLARE @EngueuladeInit AS VARCHAR(128) = @theTrigger 
                                            + 'Update rejeté de ' 
                                            + @tableCible 
                                            + ' - ' ;
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @nbDeleted as INT ;
    DECLARE @pseudoConnu as CHAR(1) = 'Y'
    DECLARE @pseudoParticipant as CHAR(1) = 'Y'
    DECLARE @blindeurEstJoueur as CHAR(1) = 'Y'
    DECLARE @blindtestConnu as CHAR(1) = 'Y'
    DECLARE @blindtestPseudoParticipant as CHAR(1) = 'Y'
    DECLARE @extraitConnu as CHAR(1) = 'Y'
    DECLARE @integritePKrespectee as CHAR(1) = 'Y'
    DECLARE @blindtestExtraitParticipant as CHAR(1) = 'Y'
    DECLARE @insertedNonVide  as CHAR(1) = 'Y'
    ----------------------------------------------------------------------------
    -- Les tables @inserted et @deleted sont du type 
    -- blindeur_blindtest_extrait_table_type
    -- Ceci permet de passer les données des tables aux procédures.
    -- Rappel du type blindeur_blindtest_extrait_table_type :
    -- (blinderId, blindtestId, extraitId, pseudo, Numero, artiste, titre).
    -----------------------------------------------------------------------------
    
    DECLARE @inserted AS blindeur_blindtest_extrait_table_type ;
    DECLARE @deleted AS blindeur_blindtest_extrait_table_type ;  -- table vide
    
    ---------------------------------------------
    -- Projection de @inserted sur le blindtest
    --------------------------------------------
    DECLARE @insertedBlindtest AS blindtest_table_type ;
    
    -----------------------------------------------------
    -- c'est parti !
    -----------------------------------------------------
    
    ---- pour debug ---- select '' as INSERTED, * from INSERTED  
    ---- pour debug ---- select '' as DELETED, * from DELETED  
    
    ----------------------------------------------------------
    --  On alimente les tables utilisées par les procédures
    --  chargées d'effectuer les différents contrôles.
    ----------------------------------------------------------
    
    INSERT INTO @inserted (blindeurId, blindtestId, extraitId
                         , pseudo, Numero, artiste, titre)
        SELECT COALESCE(blindeurId, -1)
             , COALESCE(blindtestId, -1)
             , COALESCE(extraitId, -1)
             , x.pseudo, x.Numero, x.artiste, x.titre  
        FROM   INSERTED as x
          LEFT JOIN BLINDEUR as y ON x.pseudo = y.pseudo
          LEFT JOIN BLINDTEST as z ON x.Numero = z.Numero 
          LEFT JOIN EXTRAIT as t ON x.artiste = t.artiste
                                AND x.titre = t.titre
    ;
    
    INSERT INTO @deleted (blindeurId, blindtestId, extraitId
                         , pseudo, Numero, artiste, titre)
        SELECT COALESCE(blindeurId, -1)
             , COALESCE(blindtestId, -1)
             , COALESCE(extraitId, -1)
             , x.pseudo, x.Numero, x.artiste, x.titre  
        FROM   DELETED as x
          LEFT JOIN BLINDEUR as y ON x.pseudo = y.pseudo
          LEFT JOIN BLINDTEST as z ON x.Numero = z.Numero 
          LEFT JOIN EXTRAIT as t ON x.artiste = t.artiste
                                AND x.titre = t.titre
    ;
    
    SET @nbdeleted = (SELECT COUNT(*) FROM @deleted)
    
    ----------------------------------------------
    -- pour test de vacuité de INSERTED
    ---------------------------------------------- 
    
    INSERT INTO @insertedBlindtest (blindtestId, Numero)
        SELECT blindtestId, Numero  
        FROM   @inserted
    ;
    ---------------------------------------------
    -- Si INSERTED est vide, on dégage
    ---------------------------------------------
      
    EXEC  INSERTED_VIDE_PROC
        @theTrigger
      , @tableCible
      , @typeOperation
      , @insertedBlindtest
      , @insertedNonVide OUTPUT
    
    IF @insertedNonVide = 'N' 
        RETURN
    
    ------------------------------------------------------------
    -- on vérifie les valeurs fournies dans INSERTED :
    -- les joueurs doivent exister en tant que blindeurs,
    -- les numeros des blindtests doivent exister,
    -- les paires (artiste, titre) des extraits doivent exister.
    -------------------------------------------------------------
    
    ----------------------------------------
    -- les numéros des blindtests doivent
    -- exister
    ----------------------------------------
      
    EXEC  TROUVER_BLINDTEST_EXISTENCE_PROC
        @theTrigger
      , @tableCible
      , @typeOperation
      , @inserted
      , @blindtestConnu OUTPUT
    
    ----------------------------------------
    -- les pseudos des joueurs doivent
    -- exister
    ----------------------------------------
      
    EXEC TROUVER_BLINDEUR_EXISTENCE_PROC
        @theTrigger
      , @tableCible
      , @typeOperation
      , @inserted
      , @pseudoConnu OUTPUT
    
    ---------------------------------------------------
    -- si un extrait est candidat au
    -- remplacement d'un autre extrait,
    -- il doit être connu dans la table EXTRAIT
    ---------------------------------------------------
      
    EXEC  TROUVER_EXTRAIT_EXISTENCE_PROC
        @theTrigger
      , @tableCible
      , @typeOperation
      , @inserted
      , @extraitConnu OUTPUT
    
    ----------------------------------------------------------------
    -- recherche des blindeurs connus qui disent participer à des 
    -- blintests mais en fait qui ne participent pas
    ----------------------------------------------------------------
    
    IF @pseudoConnu = 'Y' AND @blindtestConnu = 'Y' 
        BEGIN
            EXEC  TROUVER_BLINDEUR_EST_JOUEUR_PROC
                @theTrigger
              , @tableCible
              , @typeOperation
              , @tableParticiper 
              , @inserted
              , @blindeurEstJoueur OUTPUT
        END
    
    ----------------------------------------------------------------
    -- recherche des paires blindtest/pseudo inexistantes 
    -- dans la table de référence PARTICIPER
    ----------------------------------------------------------------
    
    IF @blindtestConnu = 'Y' AND @pseudoConnu = 'Y' 
       AND @blindeurEstJoueur = 'Y'
        BEGIN
            EXEC  TROUVER_BLINDTEST_BLINDEUR_EXISTENCE_PROC
                @theTrigger
              , @tableCible
              , @typeOperation
              , @tableParticiper
              , @inserted
              , @blindtestPseudoParticipant  OUTPUT
        END
    
    ----------------------------------------------------------------
    -- recherche des paires blindtest/extrait inexistantes 
    -- dans la table de référence COMPOSER
    ----------------------------------------------------------------
    
    IF @blindtestConnu = 'Y' AND @extraitConnu = 'Y'
        BEGIN
            EXEC  TROUVER_BLINDTEST_EXTRAIT_EXISTENCE_PROC
                @theTrigger
              , @tableCible
              , @typeOperation
              , @tableComposer
              , @inserted
              , @blindtestExtraitParticipant OUTPUT
        END
    
    --------------------------------------------------------------------------
    -- Détection des tentatives de clé primaire en double.
    --------------------------------------------------------------------------
    
    IF @pseudoConnu = 'Y' AND @pseudoParticipant = 'Y'
        AND @blindeurEstJoueur = 'Y'   
        AND @blindtestConnu = 'Y' AND @extraitConnu = 'Y'
        AND @blindtestExtraitParticipant = 'Y'
        AND @blindtestPseudoParticipant = 'Y' 
    
        BEGIN
            EXEC TROUVER_DOUBLON_PK_PROC
                @theTrigger
              , @tableCible
              , @typeOperation
              , @inserted
              , @deleted
              , @integritePKrespectee OUTPUT
        END
    
    -----------------------------------------------------
    -- Mise à jour de la table TROUVER.
    -- Si on a détecté des erreurs, on ne fait rien.
    ------------------------------------------------------
    
    IF @pseudoConnu  = 'Y' AND @pseudoParticipant = 'Y'
        AND @blindeurEstJoueur = 'Y'   
        AND @blindtestConnu = 'Y' AND @extraitConnu = 'Y' 
        AND @blindtestExtraitParticipant = 'Y'
        AND @blindtestPseudoParticipant = 'Y' 
        AND @integritePKrespectee = 'Y'
    
        BEGIN
    
            ---------------------------------------------
            -- Si l'attribut pseudo est touché par
            -- l'update, mais pas les attributs 
            -- Numero, artiste et titre,
            -- alors on se limite à l'update de pseudo.
            ---------------------------------------------
    
            IF UPDATE (pseudo) AND NOT UPDATE (Numero) 
               AND NOT UPDATE (artiste) AND NOT UPDATE (titre)
                BEGIN  
                    ;
                    WITH Restriction as 
                        (SELECT joueurId, blindtestId, extraitId 
                         FROM   TROUVER                             
                         WHERE joueurId IN (SELECT DISTINCT blindeurId FROM @deleted)
                           AND blindtestId IN (SELECT DISTINCT blindtestId FROM @deleted)
                           AND extraitId IN (SELECT DISTINCT extraitId FROM @deleted)) 
                        UPDATE Restriction
                            SET joueurId = x.blindeurId
                                FROM Restriction as y
                                JOIN @inserted as x ON x.blindtestId = y.blindtestId 
                                                   AND x.extraitId = y.extraitId
                    ;
                    RETURN       
                END  
    
            ---------------------------------------------
            -- Si l'attribut Numero est touché par
            -- l'update, mais pas les attributs 
            -- pseudo, artiste et titre,
            -- alors on se limite à l'update de Numero.
            ---------------------------------------------
    
            IF UPDATE (Numero) AND NOT UPDATE (pseudo) 
               AND NOT UPDATE (artiste) AND NOT UPDATE (titre)
    
                BEGIN  
                    ;
                    WITH Restriction as 
                        (SELECT joueurId, blindtestId, extraitId 
                         FROM   TROUVER                             
                         WHERE joueurId IN (SELECT DISTINCT blindeurId FROM @deleted)
                           AND blindtestId IN (SELECT DISTINCT blindtestId FROM @deleted)
                           AND extraitId IN (SELECT DISTINCT extraitId FROM @deleted)) 
                        UPDATE Restriction
                            SET blindtestId = x.blindtestId
                                FROM Restriction as y
                                JOIN @inserted as x ON x.blindeurId = y.joueurId  
                                                   AND x.extraitId = y.extraitId
                    ;
                    RETURN       
                END  
    
            ---------------------------------------------
            -- Si l'attribut artiste ou l'attribut titre 
            -- sont touchés par l'update, mais ni 
            -- l'attribut Numero ni l'attribut pseudo,
            -- alors on se limite à l'update de artiste
            -- et titre.
            ---------------------------------------------
    
            IF NOT UPDATE (pseudo) AND NOT UPDATE (Numero) AND (UPDATE (artiste) OR UPDATE (titre))
                BEGIN  
                    ;
                    WITH Restriction as 
                        (SELECT joueurId, blindtestId, extraitId 
                         FROM   TROUVER                             
                         WHERE joueurId IN (SELECT DISTINCT blindeurId FROM @deleted)
                           AND blindtestId IN (SELECT DISTINCT blindtestId FROM @deleted)
                           AND extraitId IN (SELECT DISTINCT extraitId FROM @deleted)) 
                        UPDATE Restriction
                            SET extraitId = x.extraitId
                                FROM Restriction as y
                                JOIN @inserted as x ON x.blindeurId = y.joueurId
                                                   AND x.blindtestId = y.blindtestId
                    ;
                    RETURN       
                END  
    
                --------------------------------------------
                -- Dans les cas non traités ci-dessus,
                -- on effectue un update classque, sous
                -- réserve qu'il soit déterministe
                -- (une seule ligne à updater). 
                --------------------------------------------
    
                IF @nbDeleted < 2
    
                    BEGIN 
                        UPDATE TROUVER 
                            SET  
                               joueurId = 
                                   (SELECT DISTINCT blindeurId 
                                    FROM   @inserted) 
                             , blindtestId = 
                                   (SELECT DISTINCT blindtestId 
                                    FROM   @inserted)
                             , extraitId =  
                                   (SELECT DISTINCT extraitId
                                    FROM   @inserted)
                            WHERE joueurId IN (SELECT DISTINCT blindeurId FROM @deleted)
                            AND blindtestId IN (SELECT DISTINCT blindtestId FROM @deleted)
                            AND extraitId IN (SELECT DISTINCT extraitId FROM @deleted) 
                    END 
    
                ELSE
                    BEGIN 
                        SET @Engueulade = @EngueuladeInit 
                                        + 'Update affectant ' 
                                        + 'simultanément plus d''une ligne. '
                                        + CHAR(13)
                                        + 'La table ' + @tableCible 
                                        + ' ne sera donc pas mise à jour.'
                        SELECT @Engueulade AS ' ' ;
    
                        SELECT ' ' as 'échec donc, hélas ! (deleted)' 
                             , pseudo as 'pseudo (à remplacer)'
                             , numero as 'numero (à remplacer)' 
                             , artiste as 'artiste (à remplacer)' 
                             , titre as 'titre (à remplacer)' 
                        FROM DELETED
    
                        SELECT ' ' as 'échec donc, hélas ! (inserted)' 
                             , pseudo  as 'pseudo (remplaçant)'
                             , Numero  as 'numero (remplaçant)' 
                             , artiste as 'artiste (remplaçant)' 
                             , titre as 'titre (remplaçant)' 
                        FROM INSERTED
    
                        RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
                     ----   RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
                        RETURN  
                    END 
        END
    
    GO

    (3) Le trigger dédié aux deletes via la vue TROUVER_V :

    
    ---------------------------------------
    -- Contrôle des delete, table TROUVER
    ----------------------------------------
    
    DROP TRIGGER IF EXISTS TROUVER_DELETE_TR ;
    GO
    
    CREATE TRIGGER TROUVER_DELETE_TR ON TROUVER_V INSTEAD OF DELETE
    AS
    
    DECLARE @tableCible as VARCHAR(48) = 'TROUVER'
    DECLARE @theTrigger as VARCHAR(64) = @tableCible + '_DELETE_TR - '
    DECLARE @EngueuladeInit AS VARCHAR(128) = @theTrigger + 'delete dans ' + @tableCible + ' - ';
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @nbDeleted as INT ;
    
    -----------------------------------------------------------
    -- tables temporaires pour les clés primaires
    -----------------------------------------------------------
    
    DECLARE @deleted as TABLE
    (
        blindeurId   INT   
      , blindtestId  INT
      , extraitId    INT
    ) ;
    
    -----------------------------------------------------
    -- c'est parti !
    -----------------------------------------------------
    
    ---- pour debug ---- select '' as 'DELETED (on delete)', * from DELETED  
    
    ---------------------------------------------
    -- Si DELETED est vide, on dégage
    ---------------------------------------------
    
    SET @nbdeleted = (SELECT COUNT(*) FROM DELETED) ;
    
    IF @nbdeleted = 0
        BEGIN
            SET @Engueulade = @theTrigger + 'Le programme reçoit une sélection vide (DELETED), ' 
                            + 'la table ' + @tableCible 
                            + ' ne sera donc pas mise à jour.' 
            SELECT @Engueulade as '@Engueulade'
            RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
    ----    RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
            RETURN  
        END
    
    ------------------------------------------------------
    -- de DELETED vers @deleted :
    -- Traduction des clés alternatives (naturelles) en
    -- clés primaires (artificielles)
    ------------------------------------------------------
    
    INSERT INTO @deleted (blindeurId, blindtestId, extraitId)
        SELECT DISTINCT y.blindeurId, z.blindtestId, t.extraitId
        FROM   DELETED AS x
          JOIN BLINDEUR AS y ON x.pseudo = y.pseudo
          JOIN BLINDTEST AS z ON x.Numero = z.Numero
          JOIN EXTRAIT AS t ON x.artiste = t.artiste
                           AND x.titre = t.titre
    ;
    ------------------------------------------------------
    -- Mise à jour de la table TROUVER.
    ------------------------------------------------------
    
    DELETE FROM TROUVER
           WHERE joueurId IN (SELECT blindeurId FROM @deleted)
             AND blindtestId IN (SELECT blindtestId FROM @deleted)
             AND extraitId IN (SELECT extraitId FROM @deleted)  
    ;
    
    GO
    


     
    Faites simple, mais pas plus simple ! (A. Einstein)
    E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    Je ne réponds pas aux questions techniques par MP. Les forums sont là pout ça.
    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench

  18. #58
    Expert éminent sénior
    procédures - associations joueurs - blnidtests - extraits
     

    Voici donc les procédures utilisées par les triggers de la vue TROUVER_V :

    Rappel :

    Les triggers TROUVER_INSERT_TR et TROUVER_UPDATE_TR font appel aux procédures suivantes :

    — INSERTED_VIDE_PROC (déjà décrite, cf. posts #52 et #53)

    — TROUVER_BLINDTEST_EXISTENCE_PROC (s’assurer que le blindtest proposé existe bien)

    — TROUVER_BLINDEUR_EXISTENCE_PROC (s’assurer que le blindeur proposé existe bien)

    — TROUVER_BLINDEUR_EST_JOUEUR_PROC (s’assurer que le blindeur est bien un joueur)

    — TROUVER_BLINDTEST_EXTRAIT_EXISTENCE_PROC (s'assurer que les extraits sont bien associés aux blindtests)

    — TROUVER_EXTRAIT_EXISTENCE_PROC (s’assurer que l’extrait proposé existe bien)

    — TROUVER_BLINDTEST_BLINDEUR_EXISTENCE_PROC (s’assurer que le joueur supposé trouver participe au blindtest)

    — TROUVER_DOUBLON_PK_PROC (s’assurer qu’il n’y aura pas de tentative de clé primaire en double)


    Code des procédures :


    — TROUVER_BLINDTEST_EXISTENCE_PROC :

    --------------------------------------------
    -- vérification de l'existence des numéros 
    -- des blindtests
    --------------------------------------------
    
    DROP PROCEDURE IF EXISTS TROUVER_BLINDTEST_EXISTENCE_PROC
    
    GO
    
    CREATE PROCEDURE TROUVER_BLINDTEST_EXISTENCE_PROC
    (
      @theTrigger as VARCHAR(64)             -- trigger appelant
    , @tableCible as VARCHAR(48)             -- table mise à jour
    , @typeOperation as VARCHAR(48)          -- insert ou update
    , @inserted as blindeur_blindtest_extrait_table_type READONLY  -- colonnes utiles   
    , @blindtestConnu  as CHAR(1) OUTPUT     -- succès ou échec
    )
    AS
    BEGIN
    
    DECLARE @theProc as  VARCHAR(48) = OBJECT_NAME(@@PROCID)
    DECLARE @typeOperationLibelle as VARCHAR(48) = 'insert dans ' 
    IF LOWER(@typeOperation) = 'update' SET @typeOperationLibelle = 'update de '  
    DECLARE @EngueuladeInit AS VARCHAR(128) = @theTrigger        
                                            + ' / Procédure ' + @theProc + ' - '
                                            + @typeOperationLibelle 
                                            + @tableCible 
                                            + ' - rejet : ' 
                                            + CHAR(13) ;
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @NumeroKount as INT ;
    
    DECLARE @t1 as TABLE 
    (
        pseudo varchar(48)
      , Numero varchar(48)
      , artiste varchar(48)
      , titre varchar(48)
    )
    
    DECLARE @t2 as TABLE 
    (
        Numero varchar(48)
    )
    
    ---------------------------------------------------
    -- si un Numero est candidat au
    -- remplacement d'un autre Numero,
    -- il doit être connu dans la table BLINDTEST
    ---------------------------------------------------
    
    ---------------------------------------
    -- @t1 : les codes blintests inconnus
    ---------------------------------------
    
    INSERT INTO @t1 (pseudo, Numero, artiste, titre)
        SELECT DISTINCT pseudo, Numero, artiste, titre 
        FROM   @inserted AS x
        WHERE  NOT EXISTS (SELECT Numero
                           FROM   BLINDTEST AS y
                           WHERE  x.Numero = y.Numero)
    ;
    ------------------------------------------
    -- Nombre exact de numéros inconnus
    ------------------------------------------
    
    INSERT INTO @t2 (Numero) 
        SELECT DISTINCT Numero FROM @t1 ;
    
    SET @NumeroKount = (SELECT COUNT(*) FROM @t2)
    
    IF @NumeroKount > 0
        BEGIN
        -----------------------------------------
        -- Les numéros effectivement inconnus
        -----------------------------------------
            IF @NumeroKount = 1
                BEGIN
                    SET @Engueulade = @EngueuladeInit
                      + 'blindtest proposé : inconnu.' 
                END
            ELSE
                BEGIN
                    SET @Engueulade = @EngueuladeInit 
                      + 'Il y a '
                      + CAST(@NumeroKount as VARCHAR(5)) 
                      + ' blindtests proposés mais inconnus.'
                END
            SELECT @Engueulade AS ' ' ;
            SELECT  pseudo
                 , Numero as 'Numero proposé inconnu'
                 , artiste, titre 
            FROM @t1
            SET @blindtestConnu = 'N'
            RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
    ----    RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
      
        END
    END
    
    GO
    

    — TROUVER_BLINDEUR_EXISTENCE_PROC

    --------------------------------------------
    -- vérification de l'existence des pseudos 
    -- des blindeurs
    --------------------------------------------
    
    DROP PROCEDURE IF EXISTS TROUVER_BLINDEUR_EXISTENCE_PROC
    
    GO
    
    CREATE PROCEDURE TROUVER_BLINDEUR_EXISTENCE_PROC
    (
      @theTrigger as VARCHAR(64)         -- trigger appelant
    , @tableCible as VARCHAR(48)         -- table mise à jour
    , @typeOperation as VARCHAR(48)      -- insert ou update
    , @inserted as blindeur_blindtest_extrait_table_type READONLY  -- colonnes utiles  
    , @pseudoConnu as CHAR(1) OUTPUT     -- succès ou échec
    )
    AS
    BEGIN
    
    DECLARE @theProc as  VARCHAR(48) = OBJECT_NAME(@@PROCID)
    DECLARE @typeOperationLibelle as VARCHAR(48) = 'insert dans ' 
    IF LOWER(@typeOperation) = 'update' SET @typeOperationLibelle = 'update de '  
    DECLARE @EngueuladeInit AS VARCHAR(128) = @theTrigger        
                                            + ' / Procédure ' + @theProc + ' - '
                                            + @typeOperationLibelle 
                                            + @tableCible 
                                            + ' - rejet : ' 
                                            + CHAR(13) ;
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @pseudoKount as INT ;
    
    DECLARE @t1 as TABLE 
    (
        pseudo varchar(48)
      , Numero varchar(48)
      , artiste varchar(48)
      , titre varchar(48)
    )
    DECLARE @t2 as TABLE 
    (
        pseudo varchar(48)
    )
    
    INSERT INTO @t1 (pseudo, Numero, artiste, titre)
        SELECT DISTINCT pseudo, Numero, artiste, titre
        FROM   @inserted AS x
        WHERE  NOT EXISTS (SELECT pseudo
                           FROM   BLINDEUR AS y
                           WHERE  x.pseudo = y.pseudo)
    ;
    ------------------------------------------
    -- Nombre exacts de pseudos inconnus
    ------------------------------------------
    
    INSERT INTO @t2 (pseudo) 
        SELECT DISTINCT pseudo FROM @t1 ;
    
    SET @pseudoKount = (SELECT COUNT(*) FROM @t2)
    
    IF @pseudoKount > 0
        BEGIN
        -----------------------------------------   
        -- Les pseudos effectivement inconnus
        -----------------------------------------
            IF @pseudoKount = 1
                BEGIN
                    SET @Engueulade 
                      = @EngueuladeInit
                      + 'pseudo proposé : inconnu.' 
                END
            ELSE
                BEGIN
                    SET @Engueulade 
                      = @EngueuladeInit
                      + 'Il y a '
                      + CAST(@pseudoKount as VARCHAR(5)) 
                      + ' pseudos proposés mais inconnus.'
                END
            SELECT @Engueulade AS ' ' ;
            SELECT pseudo as 'pseudo proposé inconnu'
                 , Numero
                 , artiste, titre 
            FROM @t1
            SET @pseudoConnu = 'N'
            RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
    ----    RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
      
        END
    END
    
    GO
    

    — TROUVER_BLINDEUR_EST_JOUEUR_PROC

    --------------------------------------------
    -- vérifier que les blindeurs sont des 
    -- joueurs
    --------------------------------------------
    
    DROP PROCEDURE IF EXISTS TROUVER_BLINDEUR_EST_JOUEUR_PROC
    
    GO
    
    CREATE PROCEDURE TROUVER_BLINDEUR_EST_JOUEUR_PROC
    (
        @theTrigger as VARCHAR(64)             -- trigger appelant
      , @tableCible as VARCHAR(48)             -- table mise à jour
      , @typeOperation as VARCHAR(48)          -- insert ou update
      , @tableParticiper as VARCHAR(48)
      , @inserted as blindeur_blindtest_extrait_table_type READONLY  -- colonnes utiles   
      , @blindeurEstJoueur as CHAR(1) OUTPUT     -- succès ou échec
    )
    AS
    BEGIN
    
    DECLARE @theProc as  VARCHAR(48) = OBJECT_NAME(@@PROCID)
    DECLARE @typeOperationLibelle as VARCHAR(48) = 'insert dans ' 
    IF LOWER(@typeOperation) = 'update' SET @typeOperationLibelle = 'update de '  
    DECLARE @EngueuladeInit AS VARCHAR(128) = @theTrigger        
                                            + ' / Procédure ' + @theProc + ' - '
                                            + @typeOperationLibelle 
                                            + @tableCible 
                                            + ' - rejet : ' 
                                            + CHAR(13) ;
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @pseudoKount as INT ;
    
    DECLARE @t1 as TABLE 
    (
        blindeurId int
      , pseudo varchar(48)
      , Numero varchar(48)
      , artiste varchar(48)
      , titre varchar(48)
    )
    DECLARE @t2 as TABLE 
    (
        blindeurId int
    )
        --------------------------------------------------
        -- recherche des blindeurs qui ne jouent pas
        --------------------------------------------------
    
    INSERT INTO @t1 (blindeurId, pseudo, Numero, artiste, titre)
        SELECT DISTINCT blindeurId, pseudo, Numero, artiste, titre 
        FROM   @inserted AS x
        WHERE  NOT EXISTS (SELECT ''
                           FROM   JOUEUR AS y                            
                           WHERE x.blindeurId = y.joueurId)
    
    INSERT INTO @t2 (blindeurId) 
        SELECT DISTINCT blindeurId FROM @t1 ;
    
    SET @pseudoKount = (SELECT COUNT(*) FROM @t2)
    
    IF @pseudoKount > 0
        --------------------------------------------------
        -- Les blindeurs qui ne jouent pas
        --------------------------------------------------
        BEGIN
            IF @pseudoKount = 1
                BEGIN
                    SET @Engueulade = @EngueuladeInit 
                      + 'le pseudo proposé n''est pas celui d''un joueur.' 
                END
            ELSE
                BEGIN
                    SET @Engueulade = @EngueuladeInit 
                      + 'Il y a '
                      + CAST(@pseudoKount as VARCHAR(5)) 
                      + ' pseudos proposés qui ne sont pas ceux de joueurs.'
                END
    
            SELECT @Engueulade AS ' ' ;
            SELECT pseudo as 'pseudo de non joueur'
                 , Numero
                 , artiste, titre 
            FROM @t1
    
            SET @blindeurEstJoueur = 'N' ; 
            RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
    ----            RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer 
        END
    END
    
    GO
    

    — TROUVER_EXTRAIT_EXISTENCE_PROC

    --------------------------------------------
    -- vérification de l'existence des extraits 
    -- (paires {artiste, titre})
    --------------------------------------------
    
    DROP PROCEDURE IF EXISTS TROUVER_EXTRAIT_EXISTENCE_PROC
    
    GO
    
    CREATE PROCEDURE TROUVER_EXTRAIT_EXISTENCE_PROC
    (
      @theTrigger as VARCHAR(64)             -- trigger appelant
    , @tableCible as VARCHAR(48)             -- table mise à jour
    , @typeOperation as VARCHAR(48)          -- insert ou update
    , @inserted as blindeur_blindtest_extrait_table_type READONLY -- colonnes utiles    
    , @blindtestConnu  as CHAR(1) OUTPUT     -- succès ou échec
    )
    AS
    BEGIN
    
    DECLARE @theProc as  VARCHAR(48) = OBJECT_NAME(@@PROCID)
    DECLARE @typeOperationLibelle as VARCHAR(48) = 'insert dans ' 
    IF LOWER(@typeOperation) = 'update' SET @typeOperationLibelle = 'update de '  
    DECLARE @EngueuladeInit AS VARCHAR(128) = @theTrigger        
                                            + ' / Procédure ' + @theProc + ' - '
                                            + @typeOperationLibelle 
                                            + @tableCible 
                                            + ' - rejet : ' 
                                            + CHAR(13) ;
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @extraitCodeKount as INT ;
    
    DECLARE @t1 as TABLE 
    (
        pseudo varchar(48)
      , Numero varchar(48)
      , artiste varchar(48)
      , titre varchar(48)
    )
    DECLARE @t2 as TABLE 
    (
        Numero varchar(48)
      , artiste varchar(48)
      , titre varchar(48)
    )
    
    ---------------------------------------
    -- @t1 : les extraits inconnus
    ---------------------------------------
    
    INSERT INTO @t1 (pseudo, Numero, artiste, titre)
        SELECT DISTINCT pseudo, Numero, artiste, titre 
        FROM   @inserted AS x
        WHERE  NOT EXISTS (SELECT artiste, titre
                           FROM   EXTRAIT AS y
                           WHERE  x.artiste = y.artiste 
                              AND x.titre = y.titre)
    ;
    ------------------------------------------
    -- Nombre exact d'extraits inconnus
    ------------------------------------------
    
    INSERT INTO @t2 (artiste, titre) 
        SELECT DISTINCT artiste, titre FROM @t1 ;
    
    SET @extraitCodeKount = (SELECT COUNT(*) FROM @t2)
    
    IF @extraitCodeKount > 0
        BEGIN
        -------------------------------------------------------
        -- Les paires {artiste, titre} effectivement inconnues
        -------------------------------------------------------
            IF @extraitCodeKount = 1
                BEGIN
                    SET @Engueulade = @EngueuladeInit 
                      + 'extrait {artiste, titre} non reconnu.' 
                END
            ELSE
                BEGIN
                    SET @Engueulade = @EngueuladeInit 
                      + 'Il y a ' + CAST(@extraitCodeKount as varchar(5)) 
                      + ' extraits {artiste, titre} proposés non reconnus.'
                END
    
            SELECT @Engueulade AS ' ' ; 
            SELECT pseudo
                 , Numero
                 , '{' + artiste + ', ' + titre + '}' as 'extrait proposé non reconnu' 
            FROM @t1
    
            SET @blindtestConnu = 'N'
            RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
    ----    RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
     
        END
    END
    
    GO
    

    — TROUVER_BLINDTEST_EXTRAIT_EXISTENCE_PROC

    --------------------------------------------
    -- vérification de l'existence des  
    -- paires {blindtest, extrait}
    --------------------------------------------
    
    DROP PROCEDURE IF EXISTS TROUVER_BLINDTEST_EXTRAIT_EXISTENCE_PROC
    
    GO
    
    CREATE PROCEDURE TROUVER_BLINDTEST_EXTRAIT_EXISTENCE_PROC
    (
        @theTrigger as VARCHAR(64)         -- trigger appelant
      , @tableCible as VARCHAR(48)         -- table mise à jour
      , @typeOperation as VARCHAR(48)      -- insert ou update
      , @tableComposer as VARCHAR(48)      -- table parente (COMPOSER)
      , @inserted as blindeur_blindtest_extrait_table_type READONLY  -- colonnes utiles  
      , @blindtestExtraitParticipant  as CHAR(1) OUTPUT -- succès ou échec
    )
    AS
    BEGIN
    
    DECLARE @theProc as  VARCHAR(48) = OBJECT_NAME(@@PROCID)
    DECLARE @typeOperationLibelle as VARCHAR(48) = 'insert dans ' 
    IF LOWER(@typeOperation) = 'update' SET @typeOperationLibelle = 'update de '  
    DECLARE @EngueuladeInit AS VARCHAR(128) = @theTrigger        
                                            + ' / Procédure ' + @theProc + ' - '
                                            + @typeOperationLibelle 
                                            + @tableCible 
                                            + ' - rejet : ' 
                                            + CHAR(13) ;
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @composerKount as INT ;
    
    DECLARE @t1 as TABLE 
    (
        pseudo varchar(48)
      , Numero varchar(48)
      , artiste varchar(48)
      , titre varchar(48)
    )
    DECLARE @t2 as TABLE 
    (
        Numero varchar(48)
      , artiste varchar(48)
      , titre varchar(48)
    )
    
    -----------------------------------------------------------
    -- Vérification de l'existence des paires blindtest/extrait 
    -----------------------------------------------------------
    
    INSERT INTO @t1 (pseudo, Numero, artiste, titre)
        SELECT DISTINCT pseudo, Numero, artiste, titre 
        FROM   @inserted AS x
        WHERE  NOT EXISTS (SELECT ''
                           FROM   COMPOSER_V AS y             
                           WHERE  x.Numero = y.Numero 
                             AND  x.artiste = y.artiste
                             AND  x.titre = y.titre) 
            ;
    INSERT INTO @t2 (Numero, artiste, titre) 
        SELECT DISTINCT Numero, artiste, titre FROM @t1 ;
    
    SET @composerKount = (SELECT COUNT(*) FROM @t2)
    
    IF @composerKount > 0
        --------------------------------------------------
        -- Les paires blindtest/extrait inexistantes
        --------------------------------------------------
        BEGIN
            IF @composerKount = 1
                BEGIN
                    SET @Engueulade = @EngueuladeInit 
                      + 'paire blindtest/extrait absente' 
                      + ' de la table ' + @tableComposer
                END
            ELSE
                BEGIN
                    SET @Engueulade = @EngueuladeInit 
                      + 'Il y a '
                      + CAST(@composerKount as VARCHAR(5)) 
                      + ' paires blindtest/extrait absentes' 
                      + ' de la table ' + @tableComposer
                END
    
            SELECT @Engueulade AS ' ' ;
            SELECT pseudo 
                 , Numero as 'Paire blindtest/extrait inexistante'
                 , artiste, titre 
            FROM @t1
    
            SET @blindtestExtraitParticipant = 'N' ; 
            RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
    ----            RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer 
        END
    END
    
    GO
    

    — TROUVER_BLINDTEST_BLINDEUR_EXISTENCE_PROC

    --------------------------------------------
    -- vérification de l'existence des  
    -- paires {blindtest, joueur}
    --------------------------------------------
    
    DROP PROCEDURE IF EXISTS TROUVER_BLINDTEST_BLINDEUR_EXISTENCE_PROC
    
    GO
    
    CREATE PROCEDURE TROUVER_BLINDTEST_BLINDEUR_EXISTENCE_PROC
    (
        @theTrigger as VARCHAR(64)         -- trigger appelant
      , @tableCible as VARCHAR(48)         -- table mise à jour
      , @typeOperation as VARCHAR(48)      -- insert ou update
      , @tableParticiper as VARCHAR(48)    -- table parente (PARTICIPER)
      , @inserted as blindeur_blindtest_extrait_table_type READONLY   
      , @blindtestPseudoParticipant as CHAR(1) OUTPUT -- succès ou échec
    )
    AS
    BEGIN
    
    DECLARE @theProc as  VARCHAR(48) = OBJECT_NAME(@@PROCID)
    DECLARE @typeOperationLibelle as VARCHAR(48) = 'insert dans ' 
    IF LOWER(@typeOperation) = 'update' SET @typeOperationLibelle = 'update de '  
    DECLARE @EngueuladeInit AS VARCHAR(128) = @theTrigger        
                                            + ' / Procédure ' + @theProc + ' - '
                                            + @typeOperationLibelle 
                                            + @tableCible 
                                            + ' - rejet : ' 
                                            + CHAR(13) ;
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @participerKount as INT ;
    
    DECLARE @t1 as TABLE 
    (
        pseudo varchar(48)
      , Numero varchar(48)
      , artiste varchar(48)
      , titre varchar(48)
    )
    DECLARE @t2 as TABLE 
    (
        Numero varchar(48)
      , pseudo varchar(48)
    )
    
    ------------------------------------------------------------
    -- vérification de l'existence des paires blindtest/pseudo
    ------------------------------------------------------------
    
    INSERT INTO @t1 (Numero, pseudo, artiste, titre)
        SELECT DISTINCT Numero, pseudo, artiste, titre 
        FROM   @inserted AS x
        WHERE  NOT EXISTS (SELECT ''
                           FROM   PARTICIPER_V AS y                            
                           WHERE  x.Numero = y.Numero 
                             AND  x.pseudo = y.pseudo)
    ;
    
    INSERT INTO @t2 (Numero, pseudo) 
        SELECT DISTINCT Numero, pseudo FROM @t1 ;
    
    SET @participerKount = (SELECT COUNT(*) FROM @t2)
    
    IF @participerKount > 0
        --------------------------------------------------
        -- Les paires blindtest/pseudo inexistantes
        --------------------------------------------------
        BEGIN
            IF @participerKount = 1
                BEGIN
                    SET @Engueulade = @EngueuladeInit 
                      + 'paire blindtest/pseudo absente' 
                      + ' de la table ' + @tableParticiper
                END
            ELSE
                BEGIN
                    SET @Engueulade = @EngueuladeInit 
                      + 'Il y a '
                      + CAST(@participerKount as VARCHAR(5)) 
                      + ' paires blindtest/pseudo absentes' 
                      + ' de la table ' + @tableParticiper
                END
    
            SELECT @Engueulade AS ' ' ;
            SELECT pseudo 
                 , Numero as 'Paire blindtest/pseudo inexistante'
                 , artiste, titre 
            FROM @t1
    
            SET @blindtestPseudoParticipant = 'N' ; 
            RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
    ----            RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer 
        END
    END
    
    GO
    

    — TROUVER_DOUBLON_PK_PROC

    ----------------------------------------------------------
    -- Détection des tentatives de clé primaire en double 
    -- dans la table TROUVER.
    -----------------------------------------------------------
    
    DROP PROCEDURE IF EXISTS TROUVER_DOUBLON_PK_PROC
    
    GO
    
    CREATE PROCEDURE TROUVER_DOUBLON_PK_PROC
    (
      @theTrigger as VARCHAR(64)         -- trigger appelant
    , @tableCible as VARCHAR(48)         -- table mise à jour
    , @typeOperation as VARCHAR(48)      -- insert ou update
    , @inserted as blindeur_blindtest_extrait_table_type READONLY -- colonnes utiles   
    , @deleted as blindeur_blindtest_extrait_table_type READONLY  -- colonnes utiles   
    , @integritePKrespectee as CHAR(1) OUTPUT -- succès ou échec
    )
    AS
    BEGIN
    
    DECLARE @theProc as  VARCHAR(48) = OBJECT_NAME(@@PROCID)
    DECLARE @typeOperationLibelle as VARCHAR(48) = 'insert dans ' 
    IF LOWER(@typeOperation) = 'update' SET @typeOperationLibelle = 'update de '  
    DECLARE @EngueuladeInit AS VARCHAR(128) = @theTrigger        
                                            + ' / Procédure ' + @theProc + ' - '
                                            + @typeOperationLibelle 
                                            + @tableCible 
                                            + ' - rejet : ' 
                                            + CHAR(13) ;
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @NbPKenDoubleExterne as INT ;
    DECLARE @NbPKenDoubleInterne as INT ;
    
    ----------------------------------------------------------
    -- Détection des tentatives de clé primaire en double :
    -- on vérifie si @inserted ne contient pas une occurrence
    -- {joueur, Numero, extrait} déjà présente dans TROUVER.
    -----------------------------------------------------------
    
    SET @NbPKenDoubleExterne = 
        (SELECT COUNT(*) 
         FROM   TROUVER as x 
         WHERE EXISTS 
             (SELECT *
              FROM  @inserted as y 
              WHERE x.joueurId = y.blindeurId
                AND x.blindtestId = y.blindtestId
                AND x.extraitId = y.extraitId))
    ;
    
    ------------------------------------------------------------
    -- Détection des tentatives de clé primaire en double :
    -- On vérifie si @inserted ne contient pas deux occurences 
    -- {joueur, Numero, extrait} égales.
    -------------------------------------------------------------
    
    SET @NbPKenDoubleInterne =
        COALESCE((SELECT COUNT(*) 
                  FROM   @inserted 
                  GROUP BY pseudo, Numero, artiste, titre 
                  HAVING COUNT(*) > 1), 0)
    
    ----------------------------------------------------
    -- Si on constate une tentative de clé primaire
    -- en double, on rouspète.
    ----------------------------------------------------
    
    IF @NbPKenDoubleExterne > 0 OR @NbPKenDoubleInterne > 1
        BEGIN
            SET @Engueulade = @EngueuladeInit 
                    + 'Tentative de clé primaire en double : ' 
                    + 'la table ' + @tableCible 
                    + ' ne sera donc pas mise à jour.'
            SELECT @Engueulade as ' ' ;
    
            IF LOWER(@typeOperation) = 'insert'
                BEGIN
                    SELECT pseudo as 'pseudo'
                         , Numero as 'Numero'
                         , artiste as 'artiste'  
                         , titre as 'titre'  
                    FROM @inserted
                END
            ELSE
                BEGIN  
                    SELECT pseudo as 'pseudo (à remplacer)'
                     , Numero as 'Numero (à remplacer)'
                     , artiste as 'artiste (à remplacer)'  
                     , titre as 'titre (à remplacer)'  
                    FROM @deleted 
    
                    SELECT pseudo as 'pseudo (remplaçant)'
                         , Numero as 'Numero (remplaçant)'
                         , artiste as 'artiste (remplaçant)'  
                         , titre as 'titre (remplaçant)'  
                    FROM @inserted
                END
    
                SET @integritePKrespectee = 'N'
                RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
        ----    RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
        END
    END
    
    GO
    


     
    Faites simple, mais pas plus simple ! (A. Einstein)
    E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    Je ne réponds pas aux questions techniques par MP. Les forums sont là pout ça.
    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench

  19. #59
    Membre régulier
    Pfiou, comment vous vous démenez pour moi, c'est fou !!
    Mais j'ai l'impression que vous n'aurez jamais fini ; y'a toujours quelque chose de nouveau !

  20. #60
    Expert éminent sénior
    Rappels (CREATE TABLE, CREATE VIEW)
     

    Pour vérifier que nous sommes synchrones, Je rappelle ici quelles instructions ont été utilisées pour définir la structure des objets (tables, vues).

    A cette occasion, je rappelle que la clé étrangère TROUVER_PARTICIPER_FK (table TROUVER) est dotée d’une action de compensation ON DELETE CASCADE : si une ligne de la table PARTICIPER est supprimée, il en va de même pour les lignes de la table TROUVER. Cette clé étrangère est en outre dotée d’une action de compensation ON UPDATE CASCADE : si la clé primaire d’une une ligne de la table PARTICIPER change de valeur, ce changement se propage dans la table TROUVER, c’est-à-dire dans les colonnes composant la clé étrangère TROUVER_PARTICIPER_FK.

    Par contre, la clé étrangère TROUVER_COMPOSER_FK de la table TROUVER est dotée (par défaut) de l’action de compensation NO ACTION : supprimer une ligne de la table COMPOSER n’est possible que si la clé étrangère TROUVER_COMPOSER_FK (table TROUVER) ne contient aucune valeur référençant la clé primaire de la ligne à supprimer dans COMPOSER ; même restriction concernant les updates.

    Il va de soi que les tables ANIMATEUR et JOUEUR sont dotées d’actions de compensation CASCADE par rapport à la table BLINDEUR (normal, un animateur est un blindeur, un joueur est un blindeur).

    A noter encore que la clé étrangère COMPOSER_BLINDTEST_FK de la table COMPOSER est dotée d’une action de compensation ON DELETE CASCADE, ce qui veut donc dire que si on supprime un blindtest dans la table BLINDTEST, l’association avec les extraits correspondants est supprimée elle aussi, mais seulement si aucune ligne de la table TROUVER ne fait référence aux lignes à supprimer dans COMPOSER : en effet, l’action de compensation est NO ACTION dans l’association entre COMPOSER et TROUVER, la tentative de suppression échouera donc lors de la propagation du stimulus (vous me suivez ? ) Pour des raisons de symétrie, la clé étrangère COMPOSER_EXTRAIT_FK est dotée d’une action de compensation ON DELETE CASCADE, même causes, mêmes effets.

    Les tables :

    DROP TABLE IF EXISTS TROUVER ;
    DROP TABLE IF EXISTS PARTICIPER ;
    DROP TABLE IF EXISTS JOUEUR ;
    DROP TABLE IF EXISTS COMPOSER ;
    DROP TABLE IF EXISTS EXTRAIT ;
    DROP TABLE IF EXISTS BLINDTEST ;
    DROP TABLE IF EXISTS ANIMATEUR ;
    DROP TABLE IF EXISTS BLINDEUR ;
    
    -------------------------------------------------------------------------------------------------
    
    CREATE TABLE BLINDEUR 
    (
            blindeurId       INT IDENTITY       NOT NULL
          , pseudo           VARCHAR(16)        NOT NULL
        , CONSTRAINT BLINDEUR_PK PRIMARY KEY (blindeurId)
        , CONSTRAINT BLINDEUR_AK UNIQUE (pseudo)
    ) ;
    
    CREATE TABLE ANIMATEUR 
    (
            animateurId      INT                NOT NULL 
        , CONSTRAINT ANIMATEUR_PK PRIMARY KEY (animateurId)
        , CONSTRAINT ANIMATEUR_BLINDEUR_FK FOREIGN KEY (animateurId)
              REFERENCES BLINDEUR (blindeurId) ON DELETE CASCADE
                                               ON UPDATE CASCADE
    ) ;
    
    CREATE TABLE BLINDTEST 
    (
            blindtestId      INT  IDENTITY      NOT NULL
          , blindtestCode    VARCHAR(8)         NOT NULL
          , animateurId      INT                NOT NULL 
        , CONSTRAINT BLINDTEST_PK PRIMARY KEY (blindtestId)
        , CONSTRAINT BLINDTEST_AK UNIQUE (blindtestCode)
        , CONSTRAINT BLINDTEST_ANIMATEUR_FK FOREIGN KEY (animateurId)
              REFERENCES ANIMATEUR (animateurId)
    ) ;
    
    CREATE TABLE EXTRAIT 
    (
            extraitId        INT  IDENTITY      NOT NULL 
          , extraitCode      VARCHAR(8)         NOT NULL
          , titre            VARCHAR(48)        NOT NULL
        , CONSTRAINT EXTRAIT_PK PRIMARY KEY (extraitId)
        , CONSTRAINT EXTRAIT_AK UNIQUE (extraitCode)
    ) ;
    
    CREATE TABLE COMPOSER 
    (
            blindtestId      INT                NOT NULL
          , extraitId        INT                NOT NULL
        ,  CONSTRAINT COMPOSER_PK PRIMARY KEY (blindtestId, extraitId)
        , CONSTRAINT COMPOSER_BLINDTEST_FK FOREIGN KEY (blindtestId)
              REFERENCES BLINDTEST (blindtestId) ON DELETE CASCADE
        , CONSTRAINT COMPOSER_EXTRAIT_FK FOREIGN KEY (extraitId)
              REFERENCES EXTRAIT (extraitId) ON DELETE CASCADE
    ) ;
    
    CREATE TABLE JOUEUR 
    (
            joueurId         INT                NOT NULL
       CONSTRAINT JOUEUR_PK PRIMARY KEY (joueurId),
       CONSTRAINT JOUEUR_BLINDEUR_FK FOREIGN KEY (joueurId)
              REFERENCES BLINDEUR (blindeurId) ON DELETE CASCADE
                                               ON UPDATE CASCADE
    ) ;
    
    CREATE TABLE PARTICIPER 
    (
            joueurId         INT                NOT NULL
          , blindtestId      INT                NOT NULL
        , CONSTRAINT PARTICIPER_PK PRIMARY KEY (joueurId, blindtestId)
        , CONSTRAINT PARTICIPER_JOUEUR_FK FOREIGN KEY (joueurId)
              REFERENCES JOUEUR (joueurId)
        , CONSTRAINT PARTICIPER_BLINDTEST_FK FOREIGN KEY (blindtestId)
              REFERENCES BLINDTEST (blindtestId) ON DELETE NO ACTION
    ) ;
    
    CREATE TABLE TROUVER 
    (
            joueurId         INT                NOT NULL
          , blindtestId      INT                NOT NULL
          , extraitId        INT                NOT NULL 
        , CONSTRAINT TROUVER_PK PRIMARY KEY (joueurId, blindtestId, extraitId) 
        , CONSTRAINT TROUVER_PARTICIPER_FK FOREIGN KEY (joueurId, blindtestId)
              REFERENCES PARTICIPER (joueurId,blindtestId)    ON DELETE CASCADE
                                                              ON UPDATE CASCADE
        , CONSTRAINT TROUVER_COMPOSER_FK FOREIGN KEY (blindtestId, extraitId)
              REFERENCES COMPOSER (blindtestId, extraitId)
    ) ;
    

    Les vues :

    DROP VIEW IF EXISTS PARTICIPER_V ;
    
    GO
    
    CREATE VIEW PARTICIPER_V (pseudo, Numero)
    AS
        SELECT  pseudo, Numero
        FROM    PARTICIPER as x 
           JOIN BLINDEUR as y ON x.joueurId = y.blindeurId  -- pour avoir le pseudo
           JOIN BLINDTEST as z ON x.blindtestId = z.blindtestId  -- pour avoir le Numero
    ;
    GO
    
    DROP VIEW IF EXISTS COMPOSER_V ;
    
    GO
    
    CREATE VIEW COMPOSER_V (Numero, artiste, titre)
    AS
        SELECT  Numero, artiste, titre
        FROM    COMPOSER as x 
           JOIN BLINDTEST as y ON x.blindtestId = y.blindtestId  -- pour avoir le Numero
           JOIN EXTRAIT as z ON x.extraitId = z.extraitId  -- pour avoir l'artiste et le titre
    ;
    GO
    
    DROP VIEW IF EXISTS TROUVER_V ;
    
    GO
    
    CREATE VIEW TROUVER_V (pseudo, Numero, artiste, titre)
    AS
        SELECT  pseudo, Numero, artiste, titre
        FROM    TROUVER as x 
           JOIN BLINDEUR as y ON x.joueurId = y.blindeurId  
           JOIN COMPOSER as z ON x.blindtestId = z.blindtestId 
                             AND x.extraitId = z.extraitId 
           JOIN BLINDTEST as t ON z.blindtestId = t.blindtestId   -- pour avoir le Numero
           JOIN EXTRAIT as u ON z.extraitId = u.extraitId  -- pour avoir l'artiste et le titre
    ;
    GO
    

     
    Faites simple, mais pas plus simple ! (A. Einstein)
    E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    Je ne réponds pas aux questions techniques par MP. Les forums sont là pout ça.
    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench