Gestion de blindtests; comment faire ?

**fsmrel** · 19/08/2019, 02h09

Envoyé par Mike888

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 
;

**Mike888** · 19/08/2019, 10h46

Merci beaucoup pour tout ce travail fourni !

**fsmrel** · 19/08/2019, 18h35

Bonsoir Mike,

Envoyé par Mike888

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 ?

**fsmrel** · 19/08/2019, 20h47

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

**Mike888** · 20/08/2019, 10h15

Envoyé par fsmrel

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 ?

**fsmrel** · 21/08/2019, 18h00

Bonsoir Mike,

Envoyé par Mike888

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 ?

Envoyé par Mike888

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 ?

**Mike888** · 22/08/2019, 09h57

Envoyé par fsmrel

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 !

Envoyé par fsmrel

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 :

Nom : 123.png
Affichages : 258
Taille : 44,0 Ko

Nom : 123.png
Affichages : 258
Taille : 44,0 Ko

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

**Mike888** · 26/08/2019, 17h01

Bonjour Fsmrel,

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

**fsmrel** · 27/08/2019, 00h54

Bonsoir Mike,

Envoyé par Mike888

Ca va être un travail titanesque vu le nombre d'extraits qu'il y a !

Travail de titan en effet !

Envoyé par Mike888

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.

**fsmrel** · 29/08/2019, 02h56

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

**Mike888** · 31/08/2019, 10h32

Merci pour tout ce travail !

**fsmrel** · 27/09/2019, 19h08

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

**fsmrel** · 28/09/2019, 01h03

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

**Mike888** · 28/09/2019, 09h00

Merci une fois encore pour tout ce travail !

**fsmrel** · 28/09/2019, 15h37

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.

**fsmrel** · 28/09/2019, 16h09

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

**fsmrel** · 28/09/2019, 16h51

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

**fsmrel** · 28/09/2019, 18h19

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

**Mike888** · 28/09/2019, 20h02

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 !

**fsmrel** · 28/09/2019, 20h58

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

Gestion de blindtests; comment faire ?

Schéma

Discussions similaires

Partager

Partager