Gestion de blindtests; comment faire ?

**fsmrel** · 29/09/2019, 23h03

Bonsoir Mike,

J’ai modifié le post #57 : au lieu de fournir les triggers dédiés aux updates et aux deletes via la vue TROUVER_V, j’y avais repris les triggers utilisés pour la vue PARTICIPER_V…

**fsmrel** · 30/09/2019, 04h58

Envoyé par Mike888

Pfiou, comment vous vous démenez pour moi, c'est fou !!

C’est pour moi l’occasion d’effectuer mon entraînement SQL !

Voici un début de jeu d’essai pour tester la mise à jour de la vur PARTICIPER_V :

----------------------------------
-- on vide les tables
----------------------------------
DELETE FROM TROUVER ;
DELETE FROM PARTICIPER ;
DELETE FROM COMPOSER ;
DELETE FROM EXTRAIT ;
DELETE FROM BLINDTEST ;
DELETE FROM BLINDEUR ;

SET IDENTITY_INSERT BLINDEUR OFF ;
DBCC CHECKIDENT (BLINDEUR, RESEED, 0) WITH NO_INFOMSGS ;

SET IDENTITY_INSERT BLINDTEST OFF ;
DBCC CHECKIDENT (BLINDTEST, RESEED, 0) WITH NO_INFOMSGS ;

SET IDENTITY_INSERT EXTRAIT OFF ;
DBCC CHECKIDENT (EXTRAIT, RESEED, 0) WITH NO_INFOMSGS ;

----------------------------------------------
-- Quelques blindeurs
----------------------------------------------
INSERT INTO BLINDEUR (pseudo) VALUES
    ('fernand'), ('raoul'), ('paul'), ('mado')
  , ('folace'), ('jean'), ('antoine'), ('patricia')
  , ('bastien'), ('pascal'), ('henri'), ('tomate') 
;
SELECT '' as 'les blindeurs    ',  pseudo from BLINDEUR
;
----------------------------------------
-- Quelques animateurs
----------------------------------------
INSERT INTO ANIMATEUR (animateurId) 
    SELECT blindeurId FROM BLINDEUR WHERE pseudo IN ('fernand', 'raoul', 'paul', 'mado')
;
SELECT  '' as 'les animateurs    ', pseudo AS Animateur 
FROM ANIMATEUR AS x
JOIN BLINDEUR AS y on x.animateurId = y.blindeurId
;
----------------------------------------
-- Quelques joueurs
----------------------------------------
INSERT INTO JOUEUR (joueurId)
    SELECT blindeurId 
    FROM BLINDEUR 
    WHERE pseudo IN ('fernand', 'mado', 'folace', 'jean', 'antoine'
                   , 'patricia', 'bastien', 'pascal', 'henri', 'tomate') 
;
SELECT '' as 'les joueurs    ', pseudo AS Joueur 
FROM JOUEUR AS x
JOIN BLINDEUR AS y on x.joueurId = y.blindeurId
;
------------------------------------------------
-- Des 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'    
;
INSERT INTO BLINDTEST (Numero, animateurId)
    SELECT 'blindt06', blindeurId
    FROM    BLINDEUR
    WHERE pseudo = 'fernand'    
;
SELECT '' as 'blindtest    ', Numero, pseudo
FROM   BLINDTEST AS x
  JOIN BLINDEUR AS y ON x.animateurId = y.blindeurId
;
---------------------------------------------------
-- participation des joueurs aux blindtests :
-- source vide
---------------------------------------------------
INSERT INTO PARTICIPER_V (pseudo, Numero)  
    SELECT 'x','y' 
    FROM   BLINDTEST 
    WHERE Numero = ''
;
SELECT '' as 'participer_v inserted vide    ' 
FROM PARTICIPER_V 

---------------------------------------------------
-- participation des joueurs aux blindtests :
-- présence de pseudos et numeros NULL
---------------------------------------------------
INSERT INTO PARTICIPER_V (pseudo, Numero) VALUES 
    ('folace', NULL) 
  , (NULL, 'blindt01')
  , (NULL, null)

SELECT '' as 'participer - insert NULL    ', * 
FROM PARTICIPER_V

---------------------------------------------------
-- participation des joueurs aux blindtests :
-- présence de pseudos et numeros inconnus
---------------------------------------------------
INSERT INTO PARTICIPER_V (pseudo, Numero) VALUES 
    ('folace', 'blindt^^') 
  , ('jean$$', 'blindt01')

SELECT '' as 'participer - pseudos, numéros folklos (a)    ', * 
FROM PARTICIPER_V
 
INSERT INTO PARTICIPER_V (pseudo, Numero) VALUES 
    ('jean££', 'blindt**') 

SELECT '' as 'participer - pseudos, numéros folklos (b)    ', * 
FROM PARTICIPER_V

INSERT INTO PARTICIPER_V (pseudo, Numero) VALUES 
    ('fola$$', 'blindt01') 
  , ('folace', 'blindt01') 
  , ('fola^^', 'blindt$$') 
  , ('folace', 'blindt££') 
  , ('folace', 'blindt01') 
  , ('folace', 'blindt02') 
  , ('jean', 'blindt01') 
  , ('jean$$', 'blindt$$') 
;
SELECT '' as 'participer - pseudos, numéros folklos (c)    ', * 
FROM PARTICIPER_V

---------------------------------------------------
-- participation des joueurs aux blindtests
-- pas d'erreur
---------------------------------------------------
INSERT INTO PARTICIPER_V (pseudo, Numero) VALUES 
    ('folace', 'blindt01') 
  , ('folace', 'blindt02') 
  , ('jean', 'blindt01') 
  , ('folace', 'blindt04')
  , ('mado', 'blindt04')
  , ('bastien', 'blindt01') 
--  , ('pascal', 'blindt05') 
  , ('henri', 'blindt06') 
  , ('tomate', 'blindt05') 
;
SELECT '' as 'participer - pseudos, numéros valides    ', * 
FROM PARTICIPER_V

---------------------------------------------------------
-- Des blindeurs non joueurs voulant participer 
-- aux blindtests
---------------------------------------------------------
INSERT INTO PARTICIPER_V (pseudo, Numero) VALUES 
    ('raoul', 'blindt01') 

INSERT INTO PARTICIPER_V (pseudo, Numero) VALUES 
    ('raoul', 'blindt01') 
  , ('paul', 'blindt02') 

SELECT '' as 'après participation de non joueurs (insert)    ', * 
FROM PARTICIPER_V

UPDATE PARTICIPER_V 
    SET pseudo = 'raoul' 
    WHERE pseudo = 'jean'

SELECT '' as 'après participation de non joueurs (update)    ', * 
FROM PARTICIPER_V

-----------------------------------------------------
-- Le blindeur fernand anime le blindtest blindt01 
-- et ne pourra donc y participer, le trigger veille 
------------------------------------------------------

INSERT INTO PARTICIPER_V (pseudo, Numero) VALUES 
    ('fernand', 'blindt01')
;
SELECT '' as 'participer_v fernand a dû être rejeté    ', * 
FROM PARTICIPER_V

-----------------------------------------------
-- Le blindeur mado a le droit de participer
-- aux blindtests, 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')
;
SELECT '' as 'mado peut participer à blindt01    ', * 
FROM PARTICIPER_V

UPDATE PARTICIPER_V -- mado ne peut pas animer blindt02
    SET Numero = 'blindt02' 
    WHERE pseudo = 'mado' AND Numero = 'blindt01'
;
SELECT '' as 'mado ne peut pas participer à blindt02    ', * 
FROM PARTICIPER_V

-----------------------------------
-- Pour jean, pas de problème
-----------------------------------
UPDATE PARTICIPER_V
    SET Numero = 'blindt02' 
    WHERE pseudo = 'jean'
;
SELECT '' as 'jean peut participer à blindt02    ', * 
FROM PARTICIPER_V

--------------------------------------------
-- test viol exclusion en masse 
-- en multi (viol exclusion)
--------------------------------------------
INSERT INTO PARTICIPER_V (pseudo, Numero) VALUES 
    ('fernand', 'blindt01')
  , ('mado', 'blindt02')
  , ('jean', 'blindt01')  -- pas coupable, mais rejeté...

SELECT '' as 'pauvre jean, rejeté à cause des autres    ', * 
FROM PARTICIPER_V

------------------------------
-- Récap des participations
------------------------------  
SELECT '' as 'participer_v récap    ',  Numero, pseudo, x.*
FROM PARTICIPER as x
JOIN BLINDTEST as y ON x.blindtestId = y.blindtestId
JOIN BLINDEUR as z ON x.joueurId = z.blindeurId 
;

-------------------------------------
-- clé primaire en double
-------------------------------------
INSERT INTO PARTICIPER_V (pseudo, Numero) VALUES 
    ('mado', 'blindt01')

SELECT '' as 'participer_v - après tentative pk doublon    ', * 
FROM PARTICIPER_V

---------------------------------------------------------
-- traitements de masse : update seulement de Numero
---------------------------------------------------------

-- bastien ne doit pas être rejeté

UPDATE PARTICIPER_V 
    SET Numero = 'blindt03' 
    WHERE Numero = 'blindt01' AND pseudo = 'bastien'

SELECT '' as 'participer_v masse 1 valide    ', * 
FROM PARTICIPER_V

----------------------------------------------
-- mado et folace doivent être rejetés à cause 
-- de folace qui provoque un doublon du fait 
-- du remplacement de <blindt01, folace> et 
-- <blindt02, folace> par <blindt03, folace>
----------------------------------------------

UPDATE PARTICIPER_V 
    SET Numero = 'blindt03' 
    WHERE Numero = 'blindt01' 
       OR Numero = 'blindt02' AND pseudo = 'folace'

SELECT '' as 'participer_v masse 2 après tentative pk doublon    ', * 
FROM PARTICIPER_V

----------------------------------------------
-- cette fois-ci c'est valide car folace ne 
-- provoque pas de <'blindt03, folace>
----------------------------------------------
UPDATE PARTICIPER_V 
    SET Numero = 'blindt03' 
    WHERE Numero = 'blindt01' 

SELECT '' as 'participer_v masse 3 valide    ', * 
    FROM PARTICIPER_V

----------------------------------------------
-- on remet les choses en ordre pour 
-- bastien, folace et mado
----------------------------------------------

UPDATE PARTICIPER_V 
    SET Numero = 'blindt01'
    WHERE Numero = 'blindt03' 

SELECT '' as 'participer_v masse 4 valide    ', * 
FROM PARTICIPER_V

---------------------------------------------------------
-- traitements de masse : update seulement de pseudo
---------------------------------------------------------

-- update à rejeter car provoque 
-- doublon <jean, blindt02>

UPDATE PARTICIPER_V 
    SET pseudo = 'jean' 
    WHERE Numero = 'blindt02' 

SELECT '' as 'participer_v masse 5 après tentative pk doublon    ', * 
FROM PARTICIPER_V

---------------------------------------------------------
-- update valide : mado participe à 2 numeros, 
-- mais légalement, donc pas de pb 
---------------------------------------------------------

UPDATE PARTICIPER_V 
    SET pseudo = 'pascal' 
    WHERE pseudo = 'mado' 

SELECT '' as 'participer_v masse 6 valide    ', * 
FROM PARTICIPER_V

---------------------------------------- 
-- update à rejeter : folace et jean 
-- car => deux <pascal, blindt02> 
---------------------------------------- 

UPDATE PARTICIPER_V 
    SET pseudo = 'pascal' 
    WHERE Numero = 'blindt02' 

SELECT '' as 'participer_v masse 7 après tentative pk doublon    ', * 
FROM PARTICIPER_V

--------------------------------------------
--  update à la fois de pseudo et Numero
--------------------------------------------

-- update valide car un seul pseudo et un seul numéro à modifier 

UPDATE PARTICIPER_V 
    SET pseudo = 'pascal'
      , numero = 'blindt05'
    WHERE pseudo = 'bastien' 

SELECT '' as 'participer_v masse 8 valide    ', * 
FROM PARTICIPER_V

--------------------------------------------
-- update à rejeter car source de clés en 
-- double <mado, blindt03>
--------------------------------------------

UPDATE PARTICIPER_V 
    SET pseudo = 'mado'
      , numero = 'blindt03'
WHERE pseudo = 'pascal' 

SELECT '' as 'participer_v masse 9 après tentative pk doublon    ', * 
FROM PARTICIPER_V

----------------------------------------------
-- Listes de numéros : update valide, 
-- même si on a 2 fois blindt05.
------------------------------------------------

UPDATE PARTICIPER_V 
    SET Numero = 'blindt03' 
    WHERE Numero IN ('blindt05', 'blindt06', 'blindt05')

SELECT '' as 'participer_v masse  10 valide    ', * 
FROM PARTICIPER_V

------------------------------------------------
--  listes de pseudos : updates valides
------------------------------------------------
UPDATE PARTICIPER_V 
    SET Numero = 'blindt01' 
    WHERE pseudo = 'tomate' -- pour le test à suivre

SELECT '' as 'participer_v masse  11 valide    ', * 
FROM PARTICIPER_V

UPDATE PARTICIPER_V 
    SET pseudo = 'jean' 
    WHERE pseudo IN ('henri', 'tomate', 'mado')

SELECT '' as 'participer_v masse  12 valide    ', * 
FROM PARTICIPER_V

--------------------------
-- quelques deletes
--------------------------

DELETE FROM PARTICIPER_V WHERE 1 = 0

SELECT '' as 'participer_v après delete rien   ', * 
FROM PARTICIPER_V

DELETE FROM PARTICIPER_V WHERE pseudo = 'folace'

SELECT '' as 'n - participer_v après delete folace    ', * 
FROM PARTICIPER_V
;
DELETE FROM PARTICIPER_V

SELECT '' as 'p - participer_v après delete total    ', * 
FROM PARTICIPER_V
;
---------------------------------
-- remise en état pour la suite 
-----------------------------------
INSERT INTO PARTICIPER_V (pseudo, Numero) VALUES 
    ('mado', 'blindt01') 
  , ('folace', 'blindt01') 
  , ('folace', 'blindt02') 
  , ('jean', 'blindt02') 
  , ('bastien', 'blindt01') 
  , ('henri', 'blindt06') 
  , ('tomate', 'blindt05') 
;
SELECT '' as 'participer_v après remise en état    ', * 
FROM PARTICIPER_V
;

**fsmrel** · 30/09/2019, 14h32

Bonsoir Mike,

Un début de jeu d’essai pour tester la mise à jour de la vue COMPOSER_V :

----------------------------------
-- on vide les tables
----------------------------------
DELETE FROM TROUVER ;
DELETE FROM PARTICIPER ;
DELETE FROM COMPOSER ;
DELETE FROM EXTRAIT ;
DELETE FROM BLINDTEST ;
DELETE FROM BLINDEUR ;

SET IDENTITY_INSERT BLINDEUR OFF ;
DBCC CHECKIDENT (BLINDEUR, RESEED, 0)  WITH NO_INFOMSGS ;

SET IDENTITY_INSERT BLINDTEST OFF ;
DBCC CHECKIDENT (BLINDTEST, RESEED, 0)  WITH NO_INFOMSGS ;

SET IDENTITY_INSERT EXTRAIT OFF ;
DBCC CHECKIDENT (EXTRAIT, RESEED, 0) WITH NO_INFOMSGS ;

----------------------------------------------
-- Quelques blindeurs
----------------------------------------------
INSERT INTO BLINDEUR (pseudo) VALUES
    ('fernand'), ('raoul'), ('paul'), ('mado')
  , ('folace'), ('jean'), ('antoine'), ('patricia')
  , ('bastien'), ('pascal'), ('henri'), ('tomate') 
;
SELECT '' as 'les blindeurs',  pseudo from BLINDEUR
;
----------------------------------------
-- Quelques animateurs
----------------------------------------
INSERT INTO ANIMATEUR (animateurId) 
    SELECT blindeurId FROM BLINDEUR WHERE pseudo IN ('fernand', 'raoul', 'paul', 'mado')
;
SELECT  '' as 'les animateurs', pseudo AS Animateur 
FROM ANIMATEUR AS x
JOIN BLINDEUR AS y ON x.animateurId = y.blindeurId
;
----------------------------------------
-- Quelques joueurs
----------------------------------------
INSERT INTO JOUEUR (joueurId)
    SELECT blindeurId 
    FROM BLINDEUR 
    WHERE pseudo IN ('fernand', 'mado', 'folace', 'jean', 'antoine', 'patricia', 'bastien', 'pascal', 'henri', 'tomate') 
;
SELECT '' as 'les joueurs', pseudo AS Joueur 
FROM JOUEUR AS x
JOIN BLINDEUR AS y on x.joueurId = y.blindeurId
;
------------------------------------------------
-- Quelques 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'    
;
INSERT INTO BLINDTEST (Numero, animateurId)
    SELECT 'blindt06', blindeurId
    FROM    BLINDEUR
    WHERE pseudo = 'fernand'    
;

SELECT '' as 'Les blindtests', 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 
    ('mado', 'blindt01') 
  , ('folace', 'blindt01') 
  , ('folace', 'blindt02') 
  , ('jean', 'blindt02') 
  , ('bastien', 'blindt01') 
  , ('henri', 'blindt06') 
  , ('tomate', 'blindt05') 
;
SELECT '' as 'participation des joueurs', Numero, pseudo 
FROM PARTICIPER_V
;

---------------------------------
-- des extraits
---------------------------------

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')
  , ('Animals', 'Nationale 7')
  , ('Brel', 'Nationale 7')
  , ('Leclerc', 'Contumace')
;
SELECT '' as 'des extraits', artiste, titre , *
FROM EXTRAIT ;
;
-------------------------------------------
-- compositions blindtests-extraits
--------------------------------------------

--------------------------------
-- quelques anomalies
--------------------------------
INSERT INTO COMPOSER_V (Numero, artiste, titre) 
    select 'x', 'y', 'z' 
          from   BLINDTEST where Numero = ''

INSERT INTO COMPOSER_V (Numero, artiste, titre) VALUES
      ('','', '')

SELECT ''as 'composer_v 1a', *
FROM   COMPOSER_V 

INSERT INTO COMPOSER_V (Numero, artiste, titre) VALUES
      ('nn','aa', 'tt')

INSERT INTO COMPOSER_V (Numero, artiste, titre) VALUES
      (NULL,NULL, NULL)

SELECT ''as 'composer_v 1b', *
FROM   COMPOSER_V 

INSERT INTO COMPOSER_V (Numero, artiste, titre) VALUES
      ('nn1','Brassens', 'tt1')
    , ('nn2','aa2', 'Old Leon')

SELECT ''as 'composer_v 1c, la table reste vide  ', *
FROM   COMPOSER_V 

--------------------------
-- correct
---------------------------
INSERT INTO COMPOSER_V (Numero, artiste, titre) VALUES
      ('blindt01','Brassens', 'Old Leon')
    , ('blindt01', 'Brel', 'Le plat pays')
    , ('blindt02', 'Brassens', 'Old Leon')
    , ('blindt02', 'Corneille', 'Marquise')
   ;
SELECT ''as 'composer_v 2, valide', *
FROM   COMPOSER_V 
; 

-------------------------------------------
-- extrait inconnu
-------------------------------------------
INSERT INTO COMPOSER_V (Numero, artiste, titre) VALUES
      ('blindt01','Brassens', 'Contumace')
SELECT ''as 'composer_v 2 - après tentative extrait inconnu', *
FROM   COMPOSER_V 

-------------------------------------------
-- numero inconnu
-------------------------------------------
INSERT INTO COMPOSER_V (Numero, artiste, titre) VALUES
      ('blindtxx','Brassens', 'Old Leon')
SELECT ''as 'composer_v 2 - après tentative numero inconnu', *
FROM   COMPOSER_V 

-----------------------------------
-- clé en double en insert
-----------------------------------

INSERT INTO COMPOSER_V (Numero, artiste, titre) VALUES
      ('blindt01','Brassens', 'Old Leon')

INSERT INTO COMPOSER_V (Numero, artiste, titre) VALUES
      ('blindt01', 'Brel', 'Nationale 7'), ('blindt01','Brel', 'Nationale 7')

SELECT ''as 'composer_v 2 - insert, après tentative pk en double',*
FROM   COMPOSER_V 

----------------------------------------
-- vue COMPOSER_V : updates
----------------------------------------

-- inserted vide

UPDATE COMPOSER_V 
   SET Numero  = 'blindt03'
   WHERE numero = ''

SELECT '' as 'composer_update_ 1, après tentative update vide', * 
FROM   COMPOSER_V 

-- tentative anomalie : blindtest inconnu

UPDATE COMPOSER_V 
   SET Numero  = '**indt03'

SELECT '' as 'composer_update_ 1b, après tentative blindtest inconnu',*
FROM   COMPOSER_V 

UPDATE COMPOSER_V 
   SET Numero  = '**indt03'
   WHERE Numero = 'blindt01' 
   AND artiste = 'Brel' AND titre = 'Le plat pays'
;
SELECT '' as 'composer_update_ 1c, après tentative blindtest inconnu', *
FROM   COMPOSER_V 

-- tentative anomalie : tous les composer inconnus

UPDATE COMPOSER_V 
   SET Numero  = 'xx' + RIGHT(Numero,6)
;
SELECT '' as 'composer_update_ 1d, après tentative blindtest inconnu', * 
FROM   COMPOSER_V 

-- correct

UPDATE COMPOSER_V 
   SET Numero  = 'blindt03'
   WHERE artiste = 'Brel' AND titre = 'Le plat pays'
;
SELECT ''as 'composer_update_ 3, ok', *
FROM   COMPOSER_V 

-- anomalie : la paire  (Brassens, Marquise) n'existe pas,
-- donc update COMPOSER pas possible

UPDATE COMPOSER_V 
   SET artiste  = 'Brassens', titre =  'Marquise'
     , Numero  = 'blindt04'
   WHERE artiste  = 'Corneille' AND titre =  'Marquise'
     AND Numero = 'blindt02'   
;
SELECT ''as 'composer_update_ 4, après update erroné', *
FROM   COMPOSER_V 

-- correct, si on crée d'abord la paire (Brassens, Marquise) 

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

SELECT ''as 'composer_update_  5, ok', *
FROM   COMPOSER_V 
;

--------------------------------
-- extrait inconnu
---------------------------------
UPDATE COMPOSER_V 
   SET artiste  = 'Brel', titre =  'Contumace'
     , Numero  = 'blindt04'
   WHERE artiste  = 'Brassens'
     AND Numero = 'blindt05'   
;
SELECT ''as 'composer_update_  5b, après update (extrait inconnu)', *
FROM   COMPOSER_V 

--------------------------------
-- blindtest inconnu
---------------------------------

UPDATE COMPOSER_V 
   SET artiste  = 'Brel', titre =  'Contumace'
     , Numero  = 'blindt$$'
   WHERE artiste  = 'Brassens' 
     AND Numero IN ('blindt02', 'blindt05')   
;
SELECT ''as 'composer_update_  5c, après update (Numero inconnu)', *
FROM   COMPOSER_V 
;
------------------------
---- correct
------------------------
INSERT INTO COMPOSER_V (Numero, artiste, titre) VALUES
      ('blindt01','Animals', 'House of rising sun')
;
SELECT ''as 'composer_v 6, ok', *
FROM   COMPOSER_V 

-- anomalie : numéro nase
--
UPDATE COMPOSER_V 
    SET Numero = 'xx' 
    WHERE Numero = 'blindt01'

-- anomalie : artiste nase
--
UPDATE COMPOSER_V 
    SET artiste = 'yy' 
    WHERE Numero = 'blindt01'

-- anomalie : titre nase
--
UPDATE COMPOSER_V 
    SET titre = 'zz' 
    WHERE Numero = 'blindt01'

SELECT ''as 'composer_v 7, tout faux', *
FROM   COMPOSER_V 

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

----------------------------------------
-- update de masse
----------------------------------------

--------------------------------------
--  update seulement de Numero
--------------------------------------
-- update valide, seul Numero est mis à jour, pas de clé en double

UPDATE COMPOSER_V 
    SET Numero = 'blindt03' 
    WHERE Numero = 'blindt01'

SELECT ''as 'update composer_v masse 1, ok', * 
FROM   COMPOSER_V 

-------------------------------------------
--  update seulement de la colonne artiste
-------------------------------------------

-- update valide, à condition de commencer par créer les paires
-- {Brel, Old Leon} et {Brel, Marquise}

INSERT INTO EXTRAIT VALUES 
      ('Brel', 'Old Leon')
    , ('Brel', 'Marquise')

UPDATE COMPOSER_V 
    SET artiste = 'Brel' 
    WHERE artiste = 'Brassens'

SELECT '' as 'update composer_v masse 2, ok', * FROM   COMPOSER_V 

-- update valide
UPDATE COMPOSER_V 
    SET artiste = 'Brassens'
    WHERE titre = 'Old Leon'

SELECT '' as 'update composer_v masse 3, ok', * 
FROM   COMPOSER_V 

--------------------------------------
--  update seulement de titre
--------------------------------------

-- pour les besoins de la cause 
INSERT INTO EXTRAIT VALUES 
      ('Brassens', 'Bonhomme'), ('Brassens', 'Marinette')
INSERT INTO COMPOSER_V VALUES 
      ('blindt02', 'Brassens', 'Bonhomme')

-- update provoquant clé en double <blindt02, Brassens, Old Leon>

UPDATE COMPOSER_V 
    SET titre = 'Old Leon' 
    WHERE titre = 'Bonhomme'

SELECT ''as 'update composer_v masse 4, après tentative pk en double', * 
FROM   COMPOSER_V 

-- update valide
UPDATE COMPOSER_V SET titre = 'Marinette' WHERE titre = 'Old Leon'

SELECT ''as 'update composer_v masse 5, ok', * 
FROM   COMPOSER_V 

--------------------------------------
--  update artiste, titre
--------------------------------------

-- update valide
UPDATE COMPOSER_V 
    SET titre = 'Old Leon'
      , artiste = 'Brel'
    WHERE titre = 'Marinette'

SELECT ''as 'update composer_v masse 6, ok', * FROM   COMPOSER_V 

-- update invalide : doublon <blindt03, Animals, House of rising sun>

UPDATE COMPOSER_V 
    SET titre = 'House of rising sun'
      , artiste = 'Animals'
    WHERE titre = 'Le plat pays'

SELECT ''as 'update composer_v masse 7, après tentative clé en double', * 
FROM   COMPOSER_V 

--------------------------------------
--  update Numero, artiste, titre
--------------------------------------

-- update valide

UPDATE COMPOSER_V 
    SET Numero = 'blindt01' 
      , titre = 'House of rising sun'
      , artiste = 'Animals'
 WHERE titre = 'Le plat pays'

SELECT ''as 'update composer_v masse 8, ok', * FROM   COMPOSER_V 

-- update invalide : doublon pk

UPDATE COMPOSER_V 
    SET Numero = 'blindt03' 
      , titre = 'House of rising sun'
      , artiste = 'Animals'
    WHERE artiste  = 'Brel' 
      AND Numero IN ('blindt03', 'blindt05')

SELECT ''as 'update composer_v masse 9, après tentative clé en double', * 
FROM   COMPOSER_V 


----------------------------------------
-- listes
----------------------------------------

--  listes de numéros : update valide

INSERT INTO COMPOSER_V VALUES 
    ('blindt06', 'Brel', 'Marquise')

UPDATE COMPOSER_V 
    SET Numero = 'blindt01' 
    WHERE Numero in ('blindt05', 'blindt06') 

SELECT '' as 'composer_v masse 10, ok', * 
FROM COMPOSER_V

--  listes d'extraits : update valide

INSERT INTO COMPOSER_V VALUES ('blindt04', 'Animals', 'House of rising sun')
INSERT INTO COMPOSER_V VALUES ('blindt05', 'Brel', 'Old Leon')
INSERT INTO COMPOSER_V VALUES ('blindt06', 'Brel', 'Marquise')

SELECT '' as 'composer_v Leclerc, ok', * FROM COMPOSER_V

UPDATE COMPOSER_V 
    SET artiste = 'Leclerc' 
      , titre = 'Contumace' 
    WHERE Numero in ('blindt04', 'blindt05', 'blindt06')

SELECT '' as 'composer_v masse 11, update ok', * 
FROM COMPOSER_V

--------------------------------
-- test delete dans COMPOSER_V
---------------------------------

DELETE FROM COMPOSER_V 
WHERE 1 = 0

SELECT '' as 'après delete rien', *
FROM COMPOSER_V

DELETE FROM COMPOSER_V 
WHERE Numero = 'blindt01'

SELECT '' as 'après delete blindt01', *
FROM COMPOSER_V

DELETE FROM COMPOSER_V 
WHERE artiste IN ('Brassens', 'Leclerc')

SELECT '' as 'après delete Brassens, Leclerc', * 
FROM COMPOSER_V

DELETE FROM COMPOSER_V

SELECT '' as 'après delete composer_v 3, tout doit disparaître', *
FROM COMPOSER_V

------------------------------
-- remise en ordre
------------------------------

INSERT INTO COMPOSER_V (Numero, artiste, titre) VALUES
    ('blindt01', 'Animals', 'House of rising sun')
  , ('blindt01', 'Brassens', 'Old Leon')
  , ('blindt02', 'Brassens', 'Old Leon')
  , ('blindt03', 'Brel', 'Le plat pays')
  , ('blindt05', 'Brassens', 'Marquise')
  , ('blindt04', 'Leclerc', 'Contumace')
  , ('blindt05', 'Leclerc', 'Contumace')
  , ('blindt06', 'Leclerc', 'Contumace')

SELECT '' as 'remise en ordre après delete composer', * 
FROM COMPOSER_V

GO

**fsmrel** · 30/09/2019, 22h35

Bonsoir Mike,

Attention à l’indigestion ! Ci-joint un jeu d’essai concernant la mise à jour de la vue TROUVER_V.

--------------------------------------------
-- tests de mise à jour de la table TROUVER
-- via la vue TROUVER_V
--------------------------------------------

----------------------------------
-- On vide les tables
----------------------------------

DELETE FROM TROUVER ;
DELETE FROM PARTICIPER ;
DELETE FROM COMPOSER ;
DELETE FROM EXTRAIT ;
DELETE FROM BLINDTEST ;
DELETE FROM BLINDEUR ;

SET IDENTITY_INSERT BLINDEUR OFF ;
DBCC CHECKIDENT (BLINDEUR, RESEED, 0)  WITH NO_INFOMSGS ;
;
SET IDENTITY_INSERT BLINDTEST OFF ;
DBCC CHECKIDENT (BLINDTEST, RESEED, 0)  WITH NO_INFOMSGS ;
;
SET IDENTITY_INSERT EXTRAIT OFF ;
DBCC CHECKIDENT (EXTRAIT, RESEED, 0)  WITH NO_INFOMSGS ;
;

----------------------------------------------
-- Quelques blindeurs
----------------------------------------------
INSERT INTO BLINDEUR (pseudo) VALUES
    ('fernand'), ('raoul'), ('paul'), ('mado')
  , ('folace'), ('jean'), ('antoine'), ('patricia')
  , ('bastien'), ('pascal'), ('henri'), ('tomate') 
;
SELECT '' as 'les blindeurs',  pseudo 
FROM BLINDEUR
;
----------------------------------------
-- Quelques animateurs
----------------------------------------
INSERT INTO ANIMATEUR (animateurId) 
    SELECT blindeurId 
    FROM BLINDEUR 
    WHERE pseudo IN ('fernand', 'raoul', 'paul', 'mado')
;
SELECT  '' as 'les animateurs', pseudo AS Animateur 
FROM ANIMATEUR AS x
JOIN BLINDEUR AS y ON x.animateurId = y.blindeurId
;
----------------------------------------
-- Quelques joueurs
----------------------------------------
INSERT INTO JOUEUR (joueurId)
    SELECT blindeurId 
    FROM BLINDEUR 
    WHERE pseudo IN ('fernand', 'mado', 'folace', 'jean', 'antoine', 'patricia', 'bastien', 'pascal', 'henri', 'tomate') 
;
------------------------------
-- pour voir
------------------------------
SELECT '' as 'les joueurs', pseudo AS Joueur 
FROM JOUEUR AS x
JOIN BLINDEUR AS y ON x.joueurId = y.blindeurId
;
------------------------------------------------
-- Quelques 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'    
;
INSERT INTO BLINDTEST (Numero, animateurId)
    SELECT 'blindt06', blindeurId
    FROM    BLINDEUR
    WHERE pseudo = 'fernand'    
;
------------------------------
-- pour voir
------------------------------  
SELECT '' as 'Les blindtests', Numero, pseudo
FROM   BLINDTEST AS x
  JOIN BLINDEUR AS y ON x.animateurId = y.blindeurId
;
-----------------------------------------------------------
-- Quelques participations des joueurs aux blindtests
------------------------------------------------------------
INSERT INTO PARTICIPER_V (pseudo, Numero) VALUES 
    ('mado', 'blindt01') 
  , ('folace', 'blindt01') 
  , ('folace', 'blindt02') 
  , ('folace', 'blindt05') 
  , ('jean', 'blindt02') 
  , ('jean', 'blindt05') 
  , ('bastien', 'blindt01') 
  , ('henri', 'blindt06') 
  , ('tomate', 'blindt05') 
;
SELECT '' as 'Quelques participations aux blindtests', Numero, pseudo 
FROM PARTICIPER_V
;

---------------------------------
-- Quelques extraits
---------------------------------

INSERT INTO EXTRAIT (artiste, titre) VALUES
    ('Animals', 'House of rising sun')
  , ('Brassens', 'Old Leon')
  , ('Brassens', 'Marquise')
  , ('Corneille', 'Marquise')
  , ('Ferré', 'Le scaphandrier')
  , ('Brel', 'Le plat pays')
  , ('G. Brassens', 'Le vieux Léon')
  , ('Animals', 'Nationale 7')
  , ('Brel', 'Nationale 7')
  , ('Leclerc', 'Contumace')
;
------------------------------
-- pour voir
------------------------------  
SELECT '' as 'Quelques extraits', artiste, titre , *
FROM EXTRAIT ;
;

----------------------------------------------
-- Quelques associations blindtets - extraits
----------------------------------------------

INSERT INTO COMPOSER_V (Numero, artiste, titre) VALUES
      ('blindt01', 'Animals', 'House of rising sun')
    , ('blindt01','Brassens', 'Old Leon')
    , ('blindt01', 'Brel', 'Le plat pays')
    , ('blindt02', 'Brassens', 'Old Leon')
    , ('blindt02', 'Corneille', 'Marquise')
    , ('blindt05', 'Brassens', 'Marquise')
   ;
SELECT '' as 'Quelques associations blindtets - extraits', * 
FROM COMPOSER_V

-------------------------------------------------------
-- Mise à jour de la table TROUVER
-------------------------------------------------------

---------------------------------------
-- INSERTED sera vide
---------------------------------------
INSERT INTO TROUVER_V (pseudo, Numero, artiste, titre)  
    SELECT pseudo, Numero, artiste, titre 
    FROM   TROUVER_V  WHERE 1 = 0 
;

UPDATE TROUVER_V  
    SET  pseudo = 'xyz'
    WHERE pseudo = ''
;     
SELECT '' as 'insert/update into TROUVER_V : vide - ', * 
FROM TROUVER_V

----------------------------------
-- pseudo inconnu
----------------------------------
INSERT INTO TROUVER_V (pseudo, Numero, artiste, titre) VALUES
      ('nada', 'blindt01', 'Animals', 'House of rising sun')
;
SELECT '' as 'INSERT INTO TROUVER_V - 2 - après pseudo inconnu - ', * 
FROM TROUVER_V

----------------------------------
-- blindtest inconnu
----------------------------------
INSERT INTO TROUVER_V (pseudo, Numero, artiste, titre) VALUES
      ('mado', 'blindtxx', 'Animals', 'House of rising sun')
;
INSERT INTO TROUVER_V (pseudo, Numero, artiste, titre) VALUES
      ('nada', 'blindtyy', 'Animals', 'House of rising sun')
;
SELECT '' as 'INSERT INTO TROUVER_V - 3 - après blindtest inconnu - ', * 
FROM TROUVER_V

----------------------------------
-- artiste inconnu
----------------------------------
INSERT INTO TROUVER_V (pseudo, Numero, artiste, titre) VALUES
      ('mado', 'blindt01', 'Jojo', 'House of rising sun')
;
SELECT '' as 'INSERT INTO TROUVER_V - 4 - après artiste inconnu - ', * 
FROM TROUVER_V

----------------------------------
-- titre inconnu
----------------------------------
INSERT INTO TROUVER_V (pseudo, Numero, artiste, titre) VALUES
      ('mado', 'blindt01', 'Animals', 'House of rising moon')
;
SELECT '' as 'INSERT INTO TROUVER_V - 5 - après titre inconnu - ', * 
FROM TROUVER_V

-----------------------------------------------
-- extrait inconnu (artiste et titre)
------------------------------------------------
INSERT INTO TROUVER_V (pseudo, Numero, artiste, titre) VALUES
      ('mado', 'blindt01', 'Jacques Brel', 'Old Leon')
;
SELECT '' as 'INSERT INTO TROUVER_V - 6 - après artiste & titre inconnus - ', * 
FROM TROUVER_V

---------------------------------------------------
-- rien de connu
----------------------------------------------------
INSERT INTO TROUVER_V (pseudo, Numero, artiste, titre) VALUES
      ('bof', 'blindxyz', 'Animals', 'House of rising moon')
;
SELECT '' as 'INSERT INTO TROUVER_V - 7 - après rien de connu - ', * 
FROM TROUVER_V

-----------------------------------------------
-- joueur ne participant pas au blindtest
------------------------------------------------
INSERT INTO TROUVER_V (pseudo, Numero, artiste, titre) VALUES
      ('patricia', 'blindt01', 'Animals', 'House of rising sun')
;
SELECT '' as 'INSERT INTO TROUVER_V - 8 - après rejet d''un joueur non participant - ', * 
FROM TROUVER_V

-----------------------------------------------
-- blindtest et extrait connus mais non associés
------------------------------------------------
INSERT INTO TROUVER_V (pseudo, Numero, artiste, titre) VALUES
      ('mado', 'blindt01', 'Brassens', 'Marquise')
;
INSERT INTO TROUVER_V (pseudo, Numero, artiste, titre) VALUES
      ('mado', 'blindt01', 'Brassens', 'Marquise')
    , ('nada', 'blindt02', 'Brassens', 'Marquise')
;
SELECT '' as 'INSERT INTO TROUVER_V - 9 - après rejet à cause de blindtest-extrait non associés - ', * 
FROM TROUVER_V

----------------------------------
-- INSERTED >  1 ligne
-- pseudos connus et inconnus
----------------------------------
INSERT INTO TROUVER_V (pseudo, Numero, artiste, titre) VALUES
      ('nada', 'blindt01', 'Animals', 'House of rising sun')
    , ('nada', 'blindt01', 'Animals', 'House of rising sun')
    , ('oops', 'blindt01', 'Animals', 'House of rising sun')
    , ('mado', 'blindt01', 'Animals', 'House of rising sun')
;
SELECT '' as 'INSERT INTO TROUVER_V - 10 - après rejet à cause de pseudos inconnus - ', * 
FROM TROUVER_V

----------------------------------
-- INSERTED >  1 ligne
-- blindtests inconnus
----------------------------------
INSERT INTO TROUVER_V (pseudo, Numero, artiste, titre) VALUES
      ('mado', 'blindtxx', 'Animals', 'House of rising sun')
    , ('mado', 'blindtxx', 'Animals', 'House of rising sun')
    , ('mado', 'blindtyy', 'Animals', 'House of rising sun')
    , ('mado', 'blindt01', 'Animals', 'House of rising sun')
;
SELECT '' as 'INSERT INTO TROUVER_V - 11 - après rejet à cause de blindtests inconnus - ', * 
FROM TROUVER_V

----------------------------------
-- INSERTED >  1 ligne
-- artistes inconnus
----------------------------------
INSERT INTO TROUVER_V (pseudo, Numero, artiste, titre) VALUES
      ('mado', 'blindt01', 'Jojo', 'House of rising sun')
    , ('mado', 'blindt01', 'Lili', 'House of rising sun')
    , ('mado', 'blindt01', 'Lulu', 'House of rising sun')
    , ('mado', 'blindt01', 'Lulu', 'House of rising sun')
    , ('mado', 'blindt01', 'Animals', 'House of rising sun')
;
SELECT '' as 'INSERT INTO TROUVER_V - 12 - après rejet à cause d''artistes inconnus - ', * 
FROM TROUVER_V

----------------------------------
-- INSERTED >  1 ligne
-- titres inconnus
----------------------------------
INSERT INTO TROUVER_V (pseudo, Numero, artiste, titre) VALUES
      ('mado', 'blindt01', 'Animals', 'House of rising moon')
    , ('mado', 'blindt01', 'Animals', 'House of rising earth')
    , ('mado', 'blindt01', 'Animals', 'House of rising sun')
    , ('mado', 'blindt01', 'Animals', 'House of rising sun')
    , ('mado', 'blindt01', 'Animals', 'House of rising sun')
;
SELECT '' as 'INSERT INTO TROUVER_V - 13 - après rejet à cause de titres inconnus - ', * 
FROM TROUVER_V

-----------------------------------------------
-- INSERTED >  1 ligne
-- extraits inconnus (artiste et titre)
------------------------------------------------
INSERT INTO TROUVER_V (pseudo, Numero, artiste, titre) VALUES
      ('mado', 'blindt01', 'Jacques Brel', 'Old Leon')
    , ('mado', 'blindt01', 'Jacques Brel', 'Old Leon')
    , ('mado', 'blindt01', 'Jacques Brassens', 'House of rising moon')
    , ('mado', 'blindt01', 'Jacques Brassens', 'House of rising Jo')
    , ('mado', 'blindt01', 'Animals', 'House of rising sun')
;
SELECT '' as 'INSERT INTO TROUVER_V - 14 - après rejet à cause d''extraits inconnus - ', * 
FROM TROUVER_V

---------------------------------------------------
-- INSERTED >  1 ligne
-- rien de connu
----------------------------------------------------
INSERT INTO TROUVER_V (pseudo, Numero, artiste, titre) VALUES
      ('bof', 'blindxyz', 'Animals', 'House of rising moon')
    , ('bof', 'blind01', 'Animals', 'House of rising moon')
    , ('paf', 'blindabc', 'Animals', 'House of rising moon')
    , ('mado', 'blindt01', 'Animals', 'House of rising sun')
    , ('mado', 'blindt01', 'Animals', 'House of rising sun')
;
SELECT '' as 'INSERT INTO TROUVER_V - 15 - après rejet à cause de rien de connu - ', * 
FROM TROUVER_V

-----------------------------------------------
-- INSERTED >  1 ligne
-- joueurs ne participant pas au blindtest
------------------------------------------------
INSERT INTO TROUVER_V (pseudo, Numero, artiste, titre) VALUES
      ('patricia', 'blindt01', 'Animals', 'House of rising sun')
    , ('paul', 'blindt01', 'Animals', 'House of rising sun')
;
SELECT '' as 'INSERT INTO TROUVER_V - 16 - après rejet à cause de joueurs ne participant pas au blindtest - ', * 
FROM TROUVER_V

-----------------------------------------------
-- INSERTED >  1 ligne
-- blindtests et extraits connus mais non associés
------------------------------------------------
INSERT INTO TROUVER_V (pseudo, Numero, artiste, titre) VALUES
      ('mado', 'blindt05', 'Animals', 'House of rising sun')
    , ('mado', 'blindt02', 'Animals', 'House of rising sun')
    , ('nada', 'blindt04', 'Animals', 'House of rising sun')
;

SELECT '' as 'INSERT INTO TROUVER_V - 17 - après rejet à cause de blindtests et extraits non associés  - ', * 
FROM TROUVER_V

--------------------------------------
-- Rappels
--------------------------------------

SELECT '' as 'Rappels'

SELECT '' as 'PARTICIPER_V (avant TROUVER_V correct)', * 
FROM PARTICIPER_V ;
SELECT '' as 'COMPOSER_V (avant TROUVER_V correct)', * 
FROM COMPOSER_V ;

---------------------------------
-- inserts valides
---------------------------------

SELECT '' as 'On insert dans TROUVER'

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')
;
SELECT '' as 'TROUVER_V (INSERT initial)', * 
FROM TROUVER_V ;

--*/
----------------------------------------------
-- INSERT erroné : NULL est un pseudo inconnu
----------------------------------------------  
INSERT INTO TROUVER_V (pseudo, Numero, artiste, titre) VALUES
      (NULL, 'blindt01', 'Animals', 'House of rising sun')
    , (NULL, NULL, NULL, 'House of rising sun')
;
SELECT '' as 'TROUVER_V - e1 - après tentative d''insert de NULL - ', * 
FROM TROUVER_V
;
----------------------------------------------
-- update erroné : NULL est un pseudo inconnu
----------------------------------------------  
UPDATE TROUVER_V 
    SET pseudo = NULL 
;
SELECT '' as 'TROUVER_V - e2 - après tentative d''update par pseudo NULL - ', * 
FROM TROUVER_V

-----------------------------------------------------
-- update erroné : jean ne participe pas toujours
--                 NULL provoque Numero inconnu 
-----------------------------------------------------  
UPDATE TROUVER_V 
    SET pseudo = 'jean' 
;
SELECT '' as 'TROUVER_V - e3 - après rejet, car jean ne participe pas toujours - ', * 
FROM TROUVER_V
;
UPDATE TROUVER_V 
    SET pseudo = 'jean'
      , Numero = NULL
;
SELECT '' as 'TROUVER_V - e4 - après rejet, car Numero ne peut pas être marqué NULL - ', * 
FROM TROUVER_V
;
---------------------------------------------------
-- folace va participer à blindt03
---------------------------------------------------
INSERT INTO PARTICIPER_V (pseudo, Numero) VALUES 
    ('folace', 'blindt03') 
SELECT '' as 'TROUVER_V - e5 - ok, folace est devenu participant de blindt03. - ', * 
FROM PARTICIPER_V

------------------------------------------------------------
-- Folace peut avoir trouvé, mais
-- sous réserve que COMPOSER soit à niveau !
-- or <blindt03, Barassens, Marquise> manque => rejet
-------------------------------------------------------------

UPDATE TROUVER_V 
    SET Numero = 'blindt03'
    WHERE pseudo = 'folace' AND Numero = 'blindt05'

SELECT '' as 'TROUVER_V - e6 - après rejet, association blindtest-extrait inconnue -  ', * 
FROM TROUVER_V

--------------------------------------
-- On met Composer  à niveau !
--------------------------------------
INSERT INTO COMPOSER_V (Numero, artiste, titre) VALUES 
    ('blindt03', 'Brassens', 'Marquise') 
SELECT '' as 'TROUVER_V - e7 - ok, l''association blindtest-extrait existe maintenant -  ', * 
FROM COMPOSER_V

--------------------------------
-- on peut faire l'update
--------------------------------

UPDATE TROUVER_V 
    SET Numero = 'blindt03' 
    WHERE pseudo = 'folace' AND Numero = 'blindt05'

SELECT '' as 'TROUVER_V - e8 - après que la paire blindtest-extrait a été validée - ', * 
FROM TROUVER_V
;

----------------------------------------
-- clé en double
----------------------------------------

INSERT INTO TROUVER_V VALUES ('jean', 'blindt05', 'Brassens', 'Marquise')

SELECT '' as 'TROUVER_V - pk1 - après tentative de doublon pk (insert) - ', * 
FROM TROUVER_V
;
----------------------------------------
-- clé en double (dans INSERTED)
----------------------------------------
INSERT INTO PARTICIPER_V VALUES ('jean', 'blindt01'), ('jean', 'blindt04'), ('jean', 'blindt06')
SELECT '' as 'TROUVER_V - pk2 - INSERT dans PARTICIPER, pour tenter ensuite un doublon de pk ddans TROUVER - ', * 
FROM PARTICIPER_V

INSERT INTO COMPOSER_V VALUES 
    ('blindt04', 'Brassens', 'Marquise') 
  , ('blindt04', 'Brassens', 'Old Leon')
  , ('blindt05', 'Brassens', 'Old Leon') 
  , ('blindt06', 'Brassens', 'Marquise')
; 
SELECT '' as 'TROUVER_V - pk3 - INSERT dans COMPOSER, pour tenter ensuite un doublon de pk dans TROUVER - ', * 
FROM COMPOSER_V

SELECT '' as 'TROUVER_V - pk4 - TROUVER avant update - ', * 
FROM TROUVER_V

-- pas moyen de doubler dans INSERTED !

UPDATE TROUVER_V 
   SET  Numero = 'blindt03'
   WHERE pseudo = 'jean' and Numero = 'blindt05'

UPDATE TROUVER_V 
   SET  pseudo = 'folace'
   WHERE pseudo = 'jean' OR pseudo = 'folace'

SELECT '' as 'TROUVER_V - pk5 - update - doublon pk inner : pas moyen, donc update ok... ', * 
FROM TROUVER_V
;

-----------------------------------------------------
-- updates de masse
-----------------------------------------------------

-----------------------------------------------
--  update seulement de la colonne pseudo
-----------------------------------------------
-- update valide, seul pseudo est mis à jour, pas de clé en double

-- On complète
INSERT INTO PARTICIPER_V VALUES ('jean', 'blindt03')
-- On update
UPDATE TROUVER_V 
    SET pseudo = 'jean' 
    WHERE pseudo IN ('mado', 'folace', 'bastien') 

SELECT ''as 'TROUVER_V masse m1, ok -  ', * 
FROM   TROUVER_V 

-----------------------------------------------
--  update seulement de la colonne Numero
-----------------------------------------------
-- On complète au besoin 
-- INSERT INTO PARTICIPER_V VALUES ('jean', 'blindt04')
-- INSERT INTO COMPOSER_V VALUES ('blindt05', 'Animals', 'House of rising sun'), ('blindt05', 'Brassens', 'Old Leon')
INSERT INTO COMPOSER_V VALUES ('blindt06', 'Animals', 'House of rising sun'), ('blindt06', 'Brassens', 'Old Leon')

-- update valide, seul Numero est mis à jour, pas de clé en double
UPDATE TROUVER_V 
    SET Numero = 'blindt06' 
    WHERE numero IN ('blindt01', 'blindt02', 'blindt04') 

SELECT ''as 'TROUVER_V masse m2, ok -  ', * 
FROM   TROUVER_V 

------------------------
-- divers
-----------------------
INSERT INTO EXTRAIT (artiste, titre) VALUES
    ('Brassens', 'Bonhomme')
 
INSERT INTO COMPOSER_V VALUES ('blindt01', 'Leclerc', 'Contumace')
INSERT INTO COMPOSER_V VALUES ('blindt01', 'Brassens', 'Bonhomme')

UPDATE TROUVER_V 
    SET pseudo = 'mado', Numero = 'blindt01', titre = 'Bonhomme' 
    WHERE pseudo = 'jean' AND numero = 'blindt03' 

SELECT ''as 'update TROUVER_V divers d1 - ok -  ', * 
FROM   TROUVER_V 


------------------------------------------------------------
-- NO ACTION - Pour le moment on laisse le SGBD 
-- râler pour l'update
-------------------------------------------------------------

INSERT INTO EXTRAIT (artiste, titre) VALUES
    ('Brassens', 'Vieux Leo')
-------------UPDATE COMPOSER_V 
-------------   SET titre  = 'Vieux Leo'
-------------   WHERE titre =  'Old Leon'
;
-----------------------------------------
-- remise en état
------------------------------------------

-- CASCADE joue plein pot !

DELETE FROM PARTICIPER_V

SELECT ''as 'TROUVER_V - divers - d2, delete cascade ok -  ', * 
FROM   TROUVER_V 

INSERT INTO PARTICIPER_V (pseudo, Numero) VALUES 

    ('mado', 'blindt01') 
  , ('folace', 'blindt01') 
  , ('folace', 'blindt02') 
  , ('folace', 'blindt05') 
  , ('jean', 'blindt02') 
  , ('jean', 'blindt05') 
  , ('bastien', 'blindt01') 
  , ('henri', 'blindt06') 
  , ('tomate', 'blindt05') 

SELECT ''as 'TROUVER_V - divers - d4, insert ok -  ', * 
FROM   PARTICIPER_V 

-------------------------------------
-- on recharge COMPOSER_V
-------------------------------------

DELETE FROM COMPOSER_V

INSERT INTO COMPOSER_V (Numero, artiste, titre) VALUES
      ('blindt01', 'Animals', 'House of rising sun')
    , ('blindt01','Brassens', 'Old Leon')
    , ('blindt01', 'Brel', 'Le plat pays')
    , ('blindt02', 'Brassens', 'Old Leon')
    , ('blindt02', 'Corneille', 'Marquise')
    , ('blindt05', 'Brassens', 'Marquise')
   ;
SELECT '' as ' vue COMPOSER_V -  ', * 
FROM COMPOSER_V

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')
;
SELECT '' as 'TROUVER_V (INSERT initial) -  ', * 
FROM TROUVER_V ;

------------------------------------------
-- UPDATE PARTICIPER CASCADE sur TROUVER
------------------------------------------

UPDATE PARTICIPER_V 
    SET pseudo = 'jean' 
    WHERE pseudo = 'mado'

SELECT '' as 'TROUVER_V (update, cascade) - ok  ', * 
FROM TROUVER_V ;

-- deleted vide
DELETE FROM TROUVER_V WHERE 1 = 0
SELECT '' as 'TROUVER_V (deleted vide), rejet -  ', * 
FROM TROUVER_V ;

-- delete de quelques
DELETE FROM TROUVER_V WHERE titre = 'Marquise'
SELECT '' as 'TROUVER_V (après delete Marquise), ok -  ', * 
FROM TROUVER_V 
;

**fsmrel** · 04/10/2019, 01h20

Bonsoir Mike,

Je reviens sur la contrainte d’exclusion mise en évidence avec le le diagramme proposé dans le post #5, selon laquelle un blindeur ne peut être à la fois animateur d’un blindtest et y participer en tant que joueur :
Pièce jointe 495685

au stade SQL, on a vu qu’une assertion (cf. post #7) permettait de garantir cette contrainte :

CREATE ASSERTION ASSERT01 CHECK
    (NOT EXISTS (
                 SELECT y.animateurId
                 FROM   PARTICIPER AS x
                   JOIN BLINDTEST AS y  
                        ON x.blindtestId = y.blindtestId
                        AND x.joueurId = y.animateurId))
;

Bien que cette instruction CREATE ASSERTION soit proposée par la norme SQL depuis 1992 (un bail !), on sait qu’aucun SGBD SQL n’a suivi. Il nous a donc malheureusement fallu descendre dans la soute et avoir recours à des triggers, solution bien lourde et piégeuse, d’autant que la syntaxe de l’instruction CREATE TRIGGER varie d’un SGBD à l’autre.

Ainsi nous a-t-il fallu coder les triggers PARTICIPER_INSERT_TR et PARTICIPER_UPDATE_TR accrochés à la vue PARTICIPER_V (cf. post #52), mais cela ne suffit pas, car rien n’empêche de remplacer l’animateur légal d’un blindtest par un blindeur qui jusqu’ici ne fait qu’y participer. Pour combler cette lacune, on accroche un trigger BLINDTEST_UPDATE_TR à la table BLINDTEST, dédié aux updates de cette table (il n’y a pas de trigger à prévoir pour les inserts dans cette table, puisqu’une participation à un blindtest ne peut avoir lieu tant que ce blindtest n’existe pas...)

(1) Code du trigger BLINDTEST_UPDATE_TR (de type AFTER) :


DROP TRIGGER IF EXISTS BLINDTEST_UPDATE_TR ;
GO
CREATE TRIGGER BLINDTEST_UPDATE_TR ON BLINDTEST AFTER UPDATE AS
BEGIN

DECLARE @n as INT ;
DECLARE @tableCible as VARCHAR(48) = 'BLINDTEST'
DECLARE @theTrigger as VARCHAR(64) =  'Trigger ' + OBJECT_NAME(@@PROCID)
DECLARE @EngueuladeInit as VARCHAR(128) = @theTrigger 
                                        + ' - update de ' 
                                        + @tableCible + ' - ';
DECLARE @Engueulade AS VARCHAR(512) ;

-------------------------------------------------
-- Comptage des joueurs qui voudraient animer
-- des blindtests auxquels ils participent déjà
-------------------------------------------------

SET @n = (SELECT COUNT(*) 
          FROM   JOUEUR 
          WHERE  joueurId IN 
              (SELECT joueurId
               FROM   PARTICIPER AS x
                 JOIN BLINDTEST AS y  
                     ON x.blindtestId = y.blindtestId
                    AND x.joueurId = y.animateurId)) 
;

IF @n > 0
    BEGIN
        SET @Engueulade = @EngueuladeInit 
                        + 'Le blindeur ne peut pas être animateur du blindtest car il y participe, ' 
                        + CHAR(13)
                        + 'la table ' + @tableCible 
                        + ' ne sera donc pas mise à jour.'
         
        SELECT @Engueulade as 'engueulade', Numero, pseudo 
        FROM   INSERTED as x 
          JOIN BLINDEUR as y ON x.animateurId = y.blindeurId
        
        SELECT '' as 'INSERTED =>  ', Numero, pseudo as 'pseudo peccamineux !' 
        FROM   INSERTED as x 
          JOIN BLINDEUR as y ON x.animateurId = y.blindeurId
        
        SELECT '' as 'DELETED =>  ', Numero, pseudo as 'pseudo à remplacer'
        FROM   DELETED as x 
          JOIN BLINDEUR as y ON x.animateurId = y.blindeurId

        SELECT ' ' as 'puisque tentative de viol, on rollback !'

        RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
----    RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer 
 
        ROLLBACK   
    END
END
RETURN
GO

(2) Un bout de jeu d’essai.

Au départ la situation est par exemple la suivante :

BLINDTEST =>   Numero     pseudo
               --------   --------
               blindt01   fernand
               blindt02   mado
               blindt03   fernand
               blindt04   fernand
               blindt05   fernand
               blindt06   fernand

BLINDEUR =>   pseudo
              ---------
              antoine
              bastien
              fernand
              folace
              henri
              jean
              mado
              pascal
              patricia
              paul
              raoul
              tomate

JOUEUR =>   pseudo
            ----------
            antoine
            bastien
            fernand
            folace
            henri
            jean
            mado
            pascal
            patricia
            tomate

PARTICIPER =>   pseudo           Numero
                ------           --------
                bastien          blindt01
                folace           blindt01
                folace           blindt02
                henri            blindt06
                jean             blindt02
                mado             blindt01
                tomate           blindt05

ANIMATEUR =>   pseudo           Numero
-------------- ---------------- --------
               fernand          blindt01
               fernand          blindt03
               fernand          blindt04
               fernand          blindt05
               fernand          blindt06
               mado             blindt02
               paul                     
               raoul                    
               tomate

(3) Allons-y :


---------------------------------------------------
-- Le BEGIN TRANSACTION a pour pendant soit COMMIT
-- si tout se passe bien, soit ROLLBACK
-- dans le trigger pour update si viol de
-- la contrainte d'exclusion.
---------------------------------------------------

BEGIN TRANSACTION  -- balancé par COMMIT ou ROLLBACK

-------------------------------------------
-- Complément sur la contrainte d'exclusion
-- selon laquelle un animateur ne peut pas
-- participer à un blindtest qu'il anime.
-- 
-- Pour que tomate puisse participer
-- à un blindtest, il faut que ce dernier
-- existe préalablement : autrement dit,
-- inutile de prévoir un trigger INSERT
-- pour la table BLINDTEST, dédié à la
-- contrainte d'exclusion, prévoir 
-- seulement un trigger UPDATE...
-------------------------------------------

------------------------------------------
-- Au départ, fernand est animateur
-- du blindtest blindt05. S'il ne l'est
-- plus on rétablit la situation pour
-- le besoin des tests.  
------------------------------------------

UPDATE BLINDTEST 
    SET animateurId = (SELECT blindeurId 
                       FROM   BLINDEUR 
                       WHERE  pseudo = 'fernand') 
    WHERE blindtestId = (SELECT blindtestId  
                         FROM   BLINDTEST 
                         WHERE  Numero = 'blindt05')
;
----------------------------------
-- Les blindtests actuels
----------------------------------

SELECT '' as 'les blindtests =>  ', Numero, pseudo 
FROM   BLINDTEST as x
  JOIN BLINDEUR as y ON x.animateurId = y.blindeurId
;
--------------------------------
-- Les blindeurs
--------------------------------

SELECT '' as 'les blindeurs =>  ', pseudo 
FROM   BLINDEUR 
ORDER BY pseudo
;
--------------------------------
-- Les joueurs
--------------------------------

SELECT '' as 'les joueurs =>  ', pseudo
FROM   JOUEUR as x 
  JOIN BLINDEUR as y ON x.joueurId = y.blindeurId
ORDER BY pseudo
;
--------------------------------
-- Les joueurs qui participent
-- aux blindtests
--------------------------------

SELECT '' as 'participations =>  ', * 
FROM   PARTICIPER_V 
ORDER BY pseudo
;
--------------------------------------------
-- tomate devient animateur
-- (par précaution, on commence par
-- le virer au cas où il le serait déjà)
--------------------------------------------

DELETE FROM ANIMATEUR 
    WHERE animateurId = (SELECT blindeurId  
                         FROM   BLINDEUR 
                         WHERE  pseudo = 'tomate')
;
INSERT INTO ANIMATEUR (animateurId) 
    SELECT blindeurId 
    FROM   BLINDEUR 
    WHERE  pseudo = 'tomate'
;
-----------------------------
-- Les animateurs
-----------------------------

SELECT '' as 'les animateurs dont tomate =>  '
     , pseudo, COALESCE(Numero, ' ') as Numero
FROM   ANIMATEUR as x 
  JOIN BLINDEUR as y ON x.animateurId = y.blindeurId
  LEFT JOIN blindtest as z ON x.animateurId = z.animateurId
ORDER BY pseudo
;

COMMIT ;

-------------------------------
-- Nouvelle transaction, 
-- se terminant par ROLLBACK
-- dans le trigger, à cause de 
-- tomate qui provoque le viol
-- de la contrainte d'exclusion.
-------------------------------

BEGIN TRANSACTION

----------------------------------------
-- Partie rollbackable, test du trigger
-- pour update.
----------------------------------------
----------------------------------------
-- mado peut remplacer fernand pour
-- animer blindt05 car elle n'y
-- participe pas, en l'occurrence ça
-- doit bien se passer.
----------------------------------------

UPDATE BLINDTEST 
    SET animateurid = (SELECT blindeurId 
                       FROM   BLINDEUR 
                       WHERE  pseudo = 'mado') 
    WHERE blindtestid = (SELECT blindtestId
                         FROM   BLINDTEST 
                         WHERE  Numero = 'blindt05')
;
SELECT '' as 'les blindtests après update (mado/blindt05) =>  '
     , Numero, pseudo 
FROM   BLINDTEST as x
  JOIN BLINDEUR as y ON x.animateurId = y.blindeurId
;
----------------------------------------
-- Mais tomate ne peut pas à son tour
-- animer blindt05 car il y participe,
-- ça va gueuler !
----------------------------------------

UPDATE BLINDTEST 
    SET animateurid = (SELECT blindeurId 
                       FROM   BLINDEUR 
                       WHERE  pseudo = 'tomate') 
    WHERE blindtestid = (SELECT blindtestId 
                         FROM   BLINDTEST 
                         WHERE  Numero = 'blindt05')
;

---------------------------------
-- Après la tentative de viol de
-- la contrainte d'exclusion par
-- tomate, le blindtest blindt05 
-- doit retrouver son animateur 
-- initial, fernand, les updates 
-- mado et tomate ayant été  
-- rollbacked.
---------------------------------

GO

SELECT '' as 'les blindtests après tentative de viol =>  '
     , Numero, pseudo 
FROM   BLINDTEST as x
  JOIN BLINDEUR as y ON x.animateurId = y.blindeurId

GO

A vous de jouer !

**Mike888** · 04/10/2019, 10h45

Donc, cette fois, tout est ok ?

Vu de loin, ça a quand même l'air fort complexe tout votre travail !
Merci encore une fois à vous !
Je reviens vers vous si j'ai des soucis !

**Mike888** · 29/10/2019, 21h13

Bonjour !
Me revoilà enfin...

J'ai remis toutes les instructions (de ces 4 pages bien remplies) dans l'ordre (et ça n'a pas été facile

).

Voici ce que ça donne en résumé, dans l'ordre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
CREATE DATABASE
CREATE TABLE
CREATE VIEW 
CREATE TYPE

CREATE TRIGGER PARTICIPER_TR
CREATE TRIGGER PARTICIPER_DELETE_TR
CREATE TRIGGER PARTICIPER_INSERT_TR
CREATE TRIGGER PARTICIPER_UPDATE_TR

CREATE PROCEDURE INSERTED_VIDE_PROC
CREATE PROCEDURE PARTICIPER_BLINDEUR_EXISTENCE_PROC
CREATE PROCEDURE PARTICIPER_BLINDEUR_EST_JOUEUR_PROC
CREATE PROCEDURE JOUEUR_EXCLUSION_ANIMATEUR_PROC
CREATE PROCEDURE PARTICIPER_BLINDTEST_EXISTENCE_PROC
CREATE PROCEDURE PARTICIPER_DOUBLON_PK_PROC

CREATE TRIGGER COMPOSER_TR
CREATE TRIGGER COMPOSER_DELETE_TR
CREATE TRIGGER COMPOSER_INSERT_TR
CREATE TRIGGER COMPOSER_UPDATE_TR

CREATE PROCEDURE COMPOSER_BLINDTEST_EXISTENCE_PROC
CREATE PROCEDURE COMPOSER_EXTRAIT_EXISTENCE_PROC
CREATE PROCEDURE COMPOSER_DOUBLON_PK_PROC

CREATE TRIGGER TROUVER_INSERT_TR
CREATE TRIGGER TROUVER_UPDATE_TR
CREATE TRIGGER TROUVER_DELETE_TR

CREATE PROCEDURE TROUVER_BLINDTEST_EXISTENCE_PROC
CREATE PROCEDURE TROUVER_BLINDEUR_EXISTENCE_PROC
CREATE PROCEDURE TROUVER_BLINDEUR_EST_JOUEUR_PROC
CREATE PROCEDURE TROUVER_EXTRAIT_EXISTENCE_PROC
CREATE PROCEDURE TROUVER_BLINDTEST_EXTRAIT_EXISTENCE_PROC
CREATE PROCEDURE TROUVER_BLINDTEST_BLINDEUR_EXISTENCE_PROC
CREATE PROCEDURE TROUVER_DOUBLON_PK_PROC

CREATE TRIGGER BLINDTEST_UPDATE_TR

Est-ce que cet ordre est bon ?

Après, j'ai tenté d'exécuter tout cela (4109 lignes quand même

) dans une requête (SSMS), et malheureusement, il y a des erreurs...

Voici la liste des erreurs renvoyées :

Msg 2715, Niveau 16, État 3, Procédure PARTICIPER_INSERT_TR, Ligne 24 [Ligne de départ du lot 700]
Colonne, paramètre ou variable #10*: type de données blindeur_blindtest_table_type introuvable.
Le paramètre ou la variable '@inserted' a un type de données non valide.
Msg 2715, Niveau 16, État 3, Procédure PARTICIPER_INSERT_TR, Ligne 25 [Ligne de départ du lot 700]
Colonne, paramètre ou variable #11*: type de données blindeur_blindtest_table_type introuvable.
Le paramètre ou la variable '@deleted' a un type de données non valide.
Msg 1087, Niveau 16, État 1, Procédure PARTICIPER_INSERT_TR, Ligne 48 [Ligne de départ du lot 700]
La variable de table "@inserted" doit être déclarée.
Msg 1087, Niveau 16, État 1, Procédure PARTICIPER_INSERT_TR, Ligne 57 [Ligne de départ du lot 700]
La variable de table "@inserted" doit être déclarée.
Msg 1087, Niveau 16, État 1, Procédure PARTICIPER_INSERT_TR, Ligne 62 [Ligne de départ du lot 700]
La variable de table "@inserted" doit être déclarée.
Msg 1087, Niveau 16, État 1, Procédure PARTICIPER_INSERT_TR, Ligne 163 [Ligne de départ du lot 700]
La variable de table "@inserted" doit être déclarée.
Msg 2715, Niveau 16, État 3, Procédure PARTICIPER_UPDATE_TR, Ligne 29 [Ligne de départ du lot 872]
Colonne, paramètre ou variable #13*: type de données blindeur_blindtest_table_type introuvable.
Le paramètre ou la variable '@inserted' a un type de données non valide.
Msg 2715, Niveau 16, État 3, Procédure PARTICIPER_UPDATE_TR, Ligne 30 [Ligne de départ du lot 872]
Colonne, paramètre ou variable #14*: type de données blindeur_blindtest_table_type introuvable.
Le paramètre ou la variable '@deleted' a un type de données non valide.
Msg 1087, Niveau 16, État 1, Procédure PARTICIPER_UPDATE_TR, Ligne 49 [Ligne de départ du lot 872]
La variable de table "@inserted" doit être déclarée.
Msg 1087, Niveau 16, État 1, Procédure PARTICIPER_UPDATE_TR, Ligne 56 [Ligne de départ du lot 872]
La variable de table "@deleted" doit être déclarée.
Msg 1087, Niveau 16, État 1, Procédure PARTICIPER_UPDATE_TR, Ligne 63 [Ligne de départ du lot 872]
La variable de table "@deleted" doit être déclarée.
Msg 1087, Niveau 16, État 1, Procédure PARTICIPER_UPDATE_TR, Ligne 67 [Ligne de départ du lot 872]
La variable de table "@inserted" doit être déclarée.
Msg 1087, Niveau 16, État 1, Procédure PARTICIPER_UPDATE_TR, Ligne 179 [Ligne de départ du lot 872]
La variable de table "@deleted" doit être déclarée.
Msg 1087, Niveau 16, État 1, Procédure PARTICIPER_UPDATE_TR, Ligne 180 [Ligne de départ du lot 872]
La variable de table "@deleted" doit être déclarée.
Msg 1087, Niveau 16, État 1, Procédure PARTICIPER_UPDATE_TR, Ligne 184 [Ligne de départ du lot 872]
La variable de table "@inserted" doit être déclarée.
Msg 1087, Niveau 16, État 1, Procédure PARTICIPER_UPDATE_TR, Ligne 201 [Ligne de départ du lot 872]
La variable de table "@deleted" doit être déclarée.
Msg 1087, Niveau 16, État 1, Procédure PARTICIPER_UPDATE_TR, Ligne 202 [Ligne de départ du lot 872]
La variable de table "@deleted" doit être déclarée.
Msg 1087, Niveau 16, État 1, Procédure PARTICIPER_UPDATE_TR, Ligne 206 [Ligne de départ du lot 872]
La variable de table "@inserted" doit être déclarée.
Msg 1087, Niveau 16, État 1, Procédure PARTICIPER_UPDATE_TR, Ligne 224 [Ligne de départ du lot 872]
La variable de table "@inserted" doit être déclarée.
Msg 1087, Niveau 16, État 1, Procédure PARTICIPER_UPDATE_TR, Ligne 227 [Ligne de départ du lot 872]
La variable de table "@inserted" doit être déclarée.
Msg 1087, Niveau 16, État 1, Procédure PARTICIPER_UPDATE_TR, Ligne 228 [Ligne de départ du lot 872]
La variable de table "@deleted" doit être déclarée.
Msg 1087, Niveau 16, État 1, Procédure PARTICIPER_UPDATE_TR, Ligne 229 [Ligne de départ du lot 872]
La variable de table "@deleted" doit être déclarée.
Msg 2715, Niveau 16, État 3, Procédure PARTICIPER_BLINDEUR_EXISTENCE_PROC, Ligne 12 [Ligne de départ du lot 1194]
Colonne, paramètre ou variable #4*: type de données blindeur_blindtest_table_type introuvable.
Le paramètre ou la variable '@inserted' a un type de données non valide.
Msg 1087, Niveau 16, État 1, Procédure PARTICIPER_BLINDEUR_EXISTENCE_PROC, Ligne 43 [Ligne de départ du lot 1194]
La variable de table "@inserted" doit être déclarée.
Msg 2715, Niveau 16, État 3, Procédure PARTICIPER_BLINDEUR_EST_JOUEUR_PROC, Ligne 12 [Ligne de départ du lot 1288]
Colonne, paramètre ou variable #4*: type de données blindeur_blindtest_table_type introuvable.
Le paramètre ou la variable '@inserted' a un type de données non valide.
Msg 1087, Niveau 16, État 1, Procédure PARTICIPER_BLINDEUR_EST_JOUEUR_PROC, Ligne 42 [Ligne de départ du lot 1288]
La variable de table "@inserted" doit être déclarée.
Msg 2715, Niveau 16, État 3, Procédure JOUEUR_EXCLUSION_ANIMATEUR_PROC, Ligne 12 [Ligne de départ du lot 1379]
Colonne, paramètre ou variable #4*: type de données blindeur_blindtest_table_type introuvable.
Le paramètre ou la variable '@inserted' a un type de données non valide.
Msg 1087, Niveau 16, État 1, Procédure JOUEUR_EXCLUSION_ANIMATEUR_PROC, Ligne 42 [Ligne de départ du lot 1379]
La variable de table "@inserted" doit être déclarée.
Msg 2715, Niveau 16, État 3, Procédure PARTICIPER_BLINDTEST_EXISTENCE_PROC, Ligne 12 [Ligne de départ du lot 1475]
Colonne, paramètre ou variable #4*: type de données blindeur_blindtest_table_type introuvable.
Le paramètre ou la variable '@inserted' a un type de données non valide.
Msg 1087, Niveau 16, État 1, Procédure PARTICIPER_BLINDTEST_EXISTENCE_PROC, Ligne 43 [Ligne de départ du lot 1475]
La variable de table "@inserted" doit être déclarée.
Msg 2715, Niveau 16, État 3, Procédure PARTICIPER_DOUBLON_PK_PROC, Ligne 7 [Ligne de départ du lot 1575]
Colonne, paramètre ou variable #4*: type de données blindeur_blindtest_table_type introuvable.
Le paramètre ou la variable '@inserted' a un type de données non valide.
Msg 2715, Niveau 16, État 3, Procédure PARTICIPER_DOUBLON_PK_PROC, Ligne 8 [Ligne de départ du lot 1575]
Colonne, paramètre ou variable #5*: type de données blindeur_blindtest_table_type introuvable.
Le paramètre ou la variable '@deleted' a un type de données non valide.
Msg 1087, Niveau 16, État 1, Procédure PARTICIPER_DOUBLON_PK_PROC, Ligne 38 [Ligne de départ du lot 1575]
La variable de table "@inserted" doit être déclarée.
Msg 1087, Niveau 16, État 1, Procédure PARTICIPER_DOUBLON_PK_PROC, Ligne 50 [Ligne de départ du lot 1575]
La variable de table "@inserted" doit être déclarée.
Msg 1087, Niveau 16, État 1, Procédure PARTICIPER_DOUBLON_PK_PROC, Ligne 69 [Ligne de départ du lot 1575]
La variable de table "@inserted" doit être déclarée.
Msg 1087, Niveau 16, État 1, Procédure PARTICIPER_DOUBLON_PK_PROC, Ligne 74 [Ligne de départ du lot 1575]
La variable de table "@deleted" doit être déclarée.
Msg 1087, Niveau 16, État 1, Procédure PARTICIPER_DOUBLON_PK_PROC, Ligne 76 [Ligne de départ du lot 1575]
La variable de table "@inserted" doit être déclarée.
Msg 2111, Niveau 16, État 1, Procédure COMPOSER_INSERT_TR, Ligne 2 [Ligne de départ du lot 1991]
Impossible de créer le déclencheur 'COMPOSER_INSERT_TR' sur vue 'COMPOSER_V', car un déclencheur INSTEAD OF INSERT existe déjà sur cet objet.
Msg 2111, Niveau 16, État 1, Procédure COMPOSER_UPDATE_TR, Ligne 2 [Ligne de départ du lot 2126]
Impossible de créer le déclencheur 'COMPOSER_UPDATE_TR' sur vue 'COMPOSER_V', car un déclencheur INSTEAD OF UPDATE existe déjà sur cet objet.
Le module 'TROUVER_INSERT_TR' dépend de l'objet manquant 'TROUVER_BLINDTEST_EXISTENCE_PROC'. Le module sera toutefois créé, mais il ne peut pas être exécuté correctement tant que l'objet n'existe pas.
Le module 'TROUVER_INSERT_TR' dépend de l'objet manquant 'TROUVER_BLINDEUR_EXISTENCE_PROC'. Le module sera toutefois créé, mais il ne peut pas être exécuté correctement tant que l'objet n'existe pas.
Le module 'TROUVER_INSERT_TR' dépend de l'objet manquant 'TROUVER_EXTRAIT_EXISTENCE_PROC'. Le module sera toutefois créé, mais il ne peut pas être exécuté correctement tant que l'objet n'existe pas.
Le module 'TROUVER_INSERT_TR' dépend de l'objet manquant 'TROUVER_BLINDEUR_EST_JOUEUR_PROC'. Le module sera toutefois créé, mais il ne peut pas être exécuté correctement tant que l'objet n'existe pas.
Le module 'TROUVER_INSERT_TR' dépend de l'objet manquant 'TROUVER_BLINDTEST_EXTRAIT_EXISTENCE_PROC'. Le module sera toutefois créé, mais il ne peut pas être exécuté correctement tant que l'objet n'existe pas.
Le module 'TROUVER_INSERT_TR' dépend de l'objet manquant 'TROUVER_BLINDTEST_BLINDEUR_EXISTENCE_PROC'. Le module sera toutefois créé, mais il ne peut pas être exécuté correctement tant que l'objet n'existe pas.
Le module 'TROUVER_INSERT_TR' dépend de l'objet manquant 'TROUVER_DOUBLON_PK_PROC'. Le module sera toutefois créé, mais il ne peut pas être exécuté correctement tant que l'objet n'existe pas.
Le module 'TROUVER_UPDATE_TR' dépend de l'objet manquant 'TROUVER_BLINDTEST_EXISTENCE_PROC'. Le module sera toutefois créé, mais il ne peut pas être exécuté correctement tant que l'objet n'existe pas.
Le module 'TROUVER_UPDATE_TR' dépend de l'objet manquant 'TROUVER_BLINDEUR_EXISTENCE_PROC'. Le module sera toutefois créé, mais il ne peut pas être exécuté correctement tant que l'objet n'existe pas.
Le module 'TROUVER_UPDATE_TR' dépend de l'objet manquant 'TROUVER_EXTRAIT_EXISTENCE_PROC'. Le module sera toutefois créé, mais il ne peut pas être exécuté correctement tant que l'objet n'existe pas.
Le module 'TROUVER_UPDATE_TR' dépend de l'objet manquant 'TROUVER_BLINDEUR_EST_JOUEUR_PROC'. Le module sera toutefois créé, mais il ne peut pas être exécuté correctement tant que l'objet n'existe pas.
Le module 'TROUVER_UPDATE_TR' dépend de l'objet manquant 'TROUVER_BLINDTEST_BLINDEUR_EXISTENCE_PROC'. Le module sera toutefois créé, mais il ne peut pas être exécuté correctement tant que l'objet n'existe pas.
Le module 'TROUVER_UPDATE_TR' dépend de l'objet manquant 'TROUVER_BLINDTEST_EXTRAIT_EXISTENCE_PROC'. Le module sera toutefois créé, mais il ne peut pas être exécuté correctement tant que l'objet n'existe pas.
Le module 'TROUVER_UPDATE_TR' dépend de l'objet manquant 'TROUVER_DOUBLON_PK_PROC'. Le module sera toutefois créé, mais il ne peut pas être exécuté correctement tant que l'objet n'existe pas.

Pour la facilité, je poste ici-joint un fichier txt (à ouvrir avec notepad++, par exemple, pour bénéficier de la numérotation des lignes) avec l'ensemble des instructions... De cette manière, c'est plus simple pour visualiser les lignes "fautives" !

Pour ma part, je pense qu'il y a une erreur à la ligne 194; vous dites :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CREATE TYPE joueur_blindtest_table_type AS TABLE

alors que ça devrait être :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CREATE TYPE blindeur_blindtest_table_type AS TABLE

Etes-vous d'accord avec cela ?

Merci d'avance si vous voyez ce qu'il y a à corriger !

Le fichier joint : ici

**Mike888** · 30/10/2019, 11h57

Après avoir corrigé :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CREATE TYPE joueur_blindtest_table_type AS TABLE

en cela :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CREATE TYPE blindeur_blindtest_table_type AS TABLE

je n'ai plus que ces erreurs-ci :

Msg 2111, Niveau 16, État 1, Procédure PARTICIPER_INSERT_TR, Ligne 2 [Ligne de départ du lot 700]
Impossible de créer le déclencheur 'PARTICIPER_INSERT_TR' sur vue 'PARTICIPER_V', car un déclencheur INSTEAD OF INSERT existe déjà sur cet objet.
Msg 2111, Niveau 16, État 1, Procédure PARTICIPER_UPDATE_TR, Ligne 2 [Ligne de départ du lot 872]
Impossible de créer le déclencheur 'PARTICIPER_UPDATE_TR' sur vue 'PARTICIPER_V', car un déclencheur INSTEAD OF UPDATE existe déjà sur cet objet.
Msg 2111, Niveau 16, État 1, Procédure COMPOSER_INSERT_TR, Ligne 2 [Ligne de départ du lot 1991]
Impossible de créer le déclencheur 'COMPOSER_INSERT_TR' sur vue 'COMPOSER_V', car un déclencheur INSTEAD OF INSERT existe déjà sur cet objet.
Msg 2111, Niveau 16, État 1, Procédure COMPOSER_UPDATE_TR, Ligne 2 [Ligne de départ du lot 2126]
Impossible de créer le déclencheur 'COMPOSER_UPDATE_TR' sur vue 'COMPOSER_V', car un déclencheur INSTEAD OF UPDATE existe déjà sur cet objet.
Le module 'TROUVER_INSERT_TR' dépend de l'objet manquant 'TROUVER_BLINDTEST_EXISTENCE_PROC'. Le module sera toutefois créé, mais il ne peut pas être exécuté correctement tant que l'objet n'existe pas.
Le module 'TROUVER_INSERT_TR' dépend de l'objet manquant 'TROUVER_BLINDEUR_EXISTENCE_PROC'. Le module sera toutefois créé, mais il ne peut pas être exécuté correctement tant que l'objet n'existe pas.
Le module 'TROUVER_INSERT_TR' dépend de l'objet manquant 'TROUVER_EXTRAIT_EXISTENCE_PROC'. Le module sera toutefois créé, mais il ne peut pas être exécuté correctement tant que l'objet n'existe pas.
Le module 'TROUVER_INSERT_TR' dépend de l'objet manquant 'TROUVER_BLINDEUR_EST_JOUEUR_PROC'. Le module sera toutefois créé, mais il ne peut pas être exécuté correctement tant que l'objet n'existe pas.
Le module 'TROUVER_INSERT_TR' dépend de l'objet manquant 'TROUVER_BLINDTEST_EXTRAIT_EXISTENCE_PROC'. Le module sera toutefois créé, mais il ne peut pas être exécuté correctement tant que l'objet n'existe pas.
Le module 'TROUVER_INSERT_TR' dépend de l'objet manquant 'TROUVER_BLINDTEST_BLINDEUR_EXISTENCE_PROC'. Le module sera toutefois créé, mais il ne peut pas être exécuté correctement tant que l'objet n'existe pas.
Le module 'TROUVER_INSERT_TR' dépend de l'objet manquant 'TROUVER_DOUBLON_PK_PROC'. Le module sera toutefois créé, mais il ne peut pas être exécuté correctement tant que l'objet n'existe pas.
Le module 'TROUVER_UPDATE_TR' dépend de l'objet manquant 'TROUVER_BLINDTEST_EXISTENCE_PROC'. Le module sera toutefois créé, mais il ne peut pas être exécuté correctement tant que l'objet n'existe pas.
Le module 'TROUVER_UPDATE_TR' dépend de l'objet manquant 'TROUVER_BLINDEUR_EXISTENCE_PROC'. Le module sera toutefois créé, mais il ne peut pas être exécuté correctement tant que l'objet n'existe pas.
Le module 'TROUVER_UPDATE_TR' dépend de l'objet manquant 'TROUVER_EXTRAIT_EXISTENCE_PROC'. Le module sera toutefois créé, mais il ne peut pas être exécuté correctement tant que l'objet n'existe pas.
Le module 'TROUVER_UPDATE_TR' dépend de l'objet manquant 'TROUVER_BLINDEUR_EST_JOUEUR_PROC'. Le module sera toutefois créé, mais il ne peut pas être exécuté correctement tant que l'objet n'existe pas.
Le module 'TROUVER_UPDATE_TR' dépend de l'objet manquant 'TROUVER_BLINDTEST_BLINDEUR_EXISTENCE_PROC'. Le module sera toutefois créé, mais il ne peut pas être exécuté correctement tant que l'objet n'existe pas.
Le module 'TROUVER_UPDATE_TR' dépend de l'objet manquant 'TROUVER_BLINDTEST_EXTRAIT_EXISTENCE_PROC'. Le module sera toutefois créé, mais il ne peut pas être exécuté correctement tant que l'objet n'existe pas.
Le module 'TROUVER_UPDATE_TR' dépend de l'objet manquant 'TROUVER_DOUBLON_PK_PROC'. Le module sera toutefois créé, mais il ne peut pas être exécuté correctement tant que l'objet n'existe pas.

**fsmrel** · 30/10/2019, 15h17

Bonjour Mike,

Bon, je vais regarder tout ça. A priori, dans votre code, il y a des mélanges de versions successives…

asap

**Mike888** · 30/10/2019, 15h35

Ah... Pourtant, j'ai vraiment fait attention... En fait, je pense qu'il ne reste plus que les instead of pour les vues.

**fsmrel** · 30/10/2019, 16h34

Bonsoir Mike,

Remplacer « CREATE TYPE joueur_blindtest_table_type » par la dernière version « CREATE TYPE blindeur_blindtest_table_type »
améliore effectivement les choses...

Envoyé par SQL Server

Msg 2111, Niveau 16, État 1, Procédure PARTICIPER_INSERT_TR, Ligne 2 [Ligne de départ du lot 700]
Impossible de créer le déclencheur 'PARTICIPER_INSERT_TR' sur vue 'PARTICIPER_V', car un déclencheur INSTEAD OF INSERT existe déjà sur cet objet.
Msg 2111, Niveau 16, État 1, Procédure PARTICIPER_UPDATE_TR, Ligne 2 [Ligne de départ du lot 872]
Impossible de créer le déclencheur 'PARTICIPER_UPDATE_TR' sur vue 'PARTICIPER_V', car un déclencheur INSTEAD OF UPDATE existe déjà sur cet objet.

Les lignes 248 à 619 décrivent la création du trigger obsolète ,PARTICIPER_TR (pour insert et update), lequel est toujours actif, ce qui interdit donc la création des triggers PARTICIPER_INSERT_TR et PARTICIPER_UPDATE_TR.

=> faire un drop du trigger PARTICIPER_TR et virer dans votre code les lignes en cause.

Envoyé par SQL Server

Msg 2111, Niveau 16, État 1, Procédure COMPOSER_INSERT_TR, Ligne 2 [Ligne de départ du lot 1991]
Impossible de créer le déclencheur 'COMPOSER_INSERT_TR' sur vue 'COMPOSER_V', car un déclencheur INSTEAD OF INSERT existe déjà sur cet objet.
Msg 2111, Niveau 16, État 1, Procédure COMPOSER_UPDATE_TR, Ligne 2 [Ligne de départ du lot 2126]
Impossible de créer le déclencheur 'COMPOSER_UPDATE_TR' sur vue 'COMPOSER_V', car un déclencheur INSTEAD OF UPDATE existe déjà sur cet objet.

Même principe, virer le trigger COMPOSER_TR et le code qui le définit (lignes 1666 à 1910).

Envoyé par SQL Server

Le module 'TROUVER_INSERT_TR' dépend de l'objet manquant 'TROUVER_BLINDTEST_EXISTENCE_PROC'. Le module sera toutefois créé, mais il ne peut pas être exécuté correctement tant que l'objet n'existe pas.

De fait, l’appel à la procédure TROUVER_BLINDTEST_EXISTENCE_PROC (ligne 2770) est effectué avant la création de cette procédure (ligne 3354) : la création de la procédure est donc à remonter en amont de l’appel...

Ceci vaut pour toutes les procédures, lesquelles doivent précéder les créations des triggers qui y font référence.

Après ces quelques aménagements, vous devriez y voir plus clair.

**Mike888** · 30/10/2019, 20h16

Génial ! Tout fonctionne !

Voici le diagramme de la DB :

Nom : Diagramme DB Admin_BT 1.png
Affichages : 294
Taille : 21,6 Ko

Est-ce bien comme vous l'avez conçu ?

D'autre part, puis-je maintenant ajouter des colonnes aux tables (par ex : ajouter "Points" à la table Blindeur) sans tout bouleverser ?

Encore un énorme merci pour tout le travail que vous avez effectué !!!

**fsmrel** · 31/10/2019, 01h04

Bonsoir Mike,

Votre diagramme de la BD est correct.

La modélisation n’étant pas le point fort de SQL Server, ce diagramme est moins précis que le MCD tel qu’on le trouve dans le post #5, mais c’est évidemment mieux que rien.

En tirant sur les traits des associations entre tables, en deux coups de cuiller à pot, vous pouvez améliorer, aérer la représentation, comme ici :

Nom : Mike888_mld_by_SqlServer.png
Affichages : 234
Taille : 68,2 Ko

Envoyé par Mike888

puis-je maintenant ajouter des colonnes aux tables (par ex : ajouter "Points" à la table Blindeur) sans tout bouleverser ?

Ajouter des colonnes est bien sûr possible, mais à condition que ce soit dans la bonne table. Par exemple, l’ajout de la colonne points dans la table BLINDEUR signifie que les points ne sont fonction que du blindeur, ce dont on peut douter : si par exemple les points ne dépendent en fait que des blindtests, alors c’est la table BLINDTEST qui doit être dotée de cette colonne. Pour connaître les points obtenus par le blindeur folace, il faut mettre en oeuvre une jointure des tables BLINDEUR, TROUVER, BLINDTEST, avec somme des points obtenus par folace dans ce contexte. Si les points dépendent non seulement des blindtests, mais aussi des extraits, alors c’est la table COMPOSER qui doit être dotée de colonne points, et il faut en passer par la jointure BLINDEUR, TROUVER, COMPOSER pour sommer les points gagnés par folace.

Ainsi, ajouter une colonne mérite réflexion, sinon au fil du temps on injectera dans la base de données des anomalies du genre : folace et jean on un nombre de point différent alors qu’ils ont trouvé exactement les mêmes réponses aux mêmes blindtests

Les erreurs se glissent partout dans les bases de données, mais il y a des limites, voyez par exemple le sort réservé à ce malheureux Nicolas Batum, c’est révoltant n'est-ce pas ?

**Mike888** · 01/11/2019, 16h48

Bonjour !

1) Alors, à la fin de chaque blindtest, les joueurs ont des points, selon les réponses qu'ils ont données. De là, on établit un classement pour chaque blindtest (à propos du classement, je n'ai pas encore d'idée pour le matérialiser).
Donc, oui, les points sont liés aux joueurs, aux blindtests, mais aussi aux extraits... Du coup, je ne sais pas trop dans quelle table les mettre

2) A noter aussi que les animateurs reçoivent un bonus par blindtest préparé et animé, une façon de les remercier pour le travail effectué. Mais je voudrais que ces points-là ne soient pas mélangés aux points cités au paragraphe 1 ci-dessus.

3) Je pense aussi dans mon code utiliser l'héritage. Joueur et animateur hériteraient de blindeur. Donc, ça nous fait 3 classes. Correct, non ?

4) Pour un blindtest, il y aurait comme colonnes supplémentaires : l'heure de début, la date, le lien vers le post du forum où le récapitulatif final et le classement sont. Et bien évidemment l'animateur du blindtest (mais ça, ça y est déjà

).

A plus tard !

**fsmrel** · 07/11/2019, 18h10

Bonsoir Mike,

Envoyé par Mike888

les points sont liés aux joueurs, aux blindtests, mais aussi aux extraits... Du coup, je ne sais pas trop dans quelle table les mettre

(A1) Si certains points ne dépendent que des joueurs (scénario « à la tète du client »), alors la table JOUEUR doit être dotée d’une colonne affectée aux points, appelons-la par exemple Jpoints.

Pour obtenir la liste des Jpoints des joueurs :

SELECT pseudo, Jpoints  as points
FROM   JOUEUR as x JOIN BLINDEUR as y ON x.joueurId = y.blindeurId
;

(A2) Si certains points ne dépendent que des blindtests, alors la table BLINDTEST doit être dotée d’une colonne affectée aux points, appelons-la par exemple Bpoints.

Pour obtenir la liste des Bpoints des joueurs :

SELECT pseudo, SUM(points) as points 
FROM (
      SELECT pseudo, COUNT(*) * z.Bpoints as points
      FROM   TROUVER as x JOIN BLINDEUR as y ON x.joueurId = y.blindeurId
        JOIN BLINDTEST as z ON x.blindtestId = z.blindtestId
      GROUP BY pseudo, z.Bpoints
) as t
GROUP BY pseudo
;

(A3) Si certains points ne dépendent que des paires {blindtest, extrait}, alors la table COMPOSER doit être dotée d’une colonne affectée aux points, appelons-la par exemple Cpoints.

Pour obtenir la liste des Cpoints des joueurs :

SELECT pseudo, SUM(points) as points 
FROM (
      SELECT pseudo, COUNT(*) * z.Cpoints  as points
      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
      GROUP BY pseudo, z.Cpoints
) as t
GROUP BY pseudo
;

Pour obtenir par exemple le total des Jpoints et des Cpoints des joueurs :

SELECT pseudo, SUM(points) as total 
FROM
    (
          SELECT pseudo, Jpoints  as points
          FROM   JOUEUR as x JOIN BLINDEUR as y ON x.joueurId = y.blindeurId
      UNION
          SELECT pseudo, SUM(points) as points 
          FROM (
                SELECT pseudo, COUNT(*) * z.Cpoints  as points
                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
          GROUP BY pseudo, z.Cpoints
    ) as t
    GROUP BY pseudo
) as w

GROUP BY pseudo
;

N.B. Si une table est affectée d’une colonne pour les points, les vues et les triggers appartenant à cette table seront à modifier en conséquence.

(A4) Cas de TROUVER
Si quel que soit le blindtest, le joueur et l’extrait, donner une bonne réponse (trouver) rapporte x points, alors on pourra définir une table spécifique, appelons-la par exemple P1, comportant une colonne Tpoints ; les points obtenus par chaque joueur ayant trouvé, sera le produit de la valeur fournie par la colonne Tpoints et du nombre de fois où le joueur aura trouvé. Exemple :

CREATE TABLE P1
(
    Tpoints INT NOT NULL PRIMARY KEY
)

INSERT INTO P1 VALUES (10) ;

SELECT pseudo, COUNT(*)*(SELECT Tpoints FROM P1) as points
FROM   TROUVER as x JOIN BLINDEUR as y ON x.joueurId = y.blindeurId
GROUP BY pseudo
;

Envoyé par Mike888

A noter aussi que les animateurs reçoivent un bonus par blindtest préparé et animé

(A5) Dans le même esprit que ci-dessus, si quel que soit le blindtest et l’animateur, animer rapporte x points, alors on pourra définir une table spécifique, appelons-la par exemple P2, comportant une colonne Apoints ; les points obtenus par chaque animateur, sera le produit de la valeur fournie par la colonne Apoints et du nombre de blindtests animés par le joueur. Exemple :

CREATE TABLE P2
(
    Apoints INT NOT NULL PRIMARY KEY
)

INSERT INTO P2 VALUES (50) ;

SELECT '' as Apoints, pseudo, COUNT(*)*(SELECT Apoints FROM P2) as points
FROM   BLINDTEST as x JOIN BLINDEUR as y ON x.animateurId = y.blindeurId
GROUP BY pseudo
;

Si ce qui précède ne convient pas, veuillez préciser de façon plus précise les règles relatives à la gestion des points.

Bon courage !

**Mike888** · 08/11/2019, 18h07

Bonjour à vous, grand maître du SGBD !

Pour les points, la règle de base est :

- 0.5 pts pour chaque artiste ou titre trouvé;
- 1.25 pts si le joueur trouve l'artiste et le titre d'un coup.

Mais le problème, c'est que il y a plein de cas particuliers (que je ne vais pas tous énumérer). Mais par ex, on peut accorder un bonus si l'extrait est très dur à trouver (0.5 pts en plus). Autre exemple : le joueur s'est aidé (Google, ...) et donc, ses points sont réduits de moitié...

Ce qui m'embête le plus, c'est ce que vous avez écrit :

N.B. Si une table est affectée d’une colonne pour les points, les vues et les triggers appartenant à cette table seront à modifier en conséquence.

Alors, que faire ? Quel serait le scénario qui correspondrait le mieux selon vous ?

**fsmrel** · 11/11/2019, 19h34

Bonsoir Mike,

Envoyé par Mike888

Pour les points, la règle de base est :

- 0.5 pts pour chaque artiste ou titre trouvé;
- 1.25 pts si le joueur trouve l'artiste et le titre d'un coup.

Ouch ! Le granule le plus fin de modélisation est l’extrait, on ne descend pas au niveau Artiste ou Titre...

Il faudrait donc revoir ainsi le MCD :

[TITRE]----1,N----()----1,1----[EXTRAIT]----1,1----()----1,N----[ARTISTE]

Puis mettre en oeuvre des associations connectant [TITRE] et [BLINDTEST] d’une part, [ARTISTE] et [BLINDTEST] d’autre part.

Pas triste pour quelque chose d’a priori anodin !

On peut de préférence faire un peu de cuisine : pour la table TROUVER, définir un attribut « quoi » pouvant prendre par exemple les valeurs suivantes :

1 : artiste seul ou titre seul (donc 0,5 point)
2 : artiste et titre en deux temps (donc 1 point)
3 : artiste et titre d’un coup (donc 1,25 point)

Cela vous convient-il ?

Structure correspondante de la table TROUVER :

TROUVER 
(
        joueurId         INT                NOT NULL
      , blindtestId      INT                NOT NULL
      , extraitId        INT                NOT NULL 
      , quoi             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)
    , CONSTRAINT TROUVER_QUOI CHECK (quoi IN (1, 2, 3))
) ;

Pour éviter de supprimer la table existante puis la recréer, on en modifie seulement la structure :

ALTER TABLE TROUVER 
ADD quoi INT NOT NULL ;
GO

ALTER TABLE TROUVER 
ADD CONSTRAINT TROUVER_QUOI CHECK (quoi IN (1, 2, 3)) ;

Envoyé par Mike888

on peut accorder un bonus si l'extrait est très dur à trouver (0.5 pts en plus)

Donc une colonne bonus dans la table EXTRAIT. Si on peut avoir d’autres motifs de points supplémentaires pour les extraits, on peut ajouter une colonne motif du bonus.

Envoyé par Mike888

le joueur s'est aidé (Google, ...) et donc, ses points sont réduits de moitié...

Donc a priori une colonne motif_de_bonus/malus dans la table TROUVER.

Gestion de blindtests; comment faire ?

Schéma

Discussions similaires

Partager

Partager