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…
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…
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 ;
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
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 ;
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 !
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 !
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 :
Est-ce que cet ordre est bon ?
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
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 :
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" !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 ma part, je pense qu'il y a une erreur à la ligne 194; vous dites :
alors que ça devrait être :
Code : Sélectionner tout - Visualiser dans une fenêtre à part CREATE TYPE joueur_blindtest_table_type AS TABLE
Etes-vous d'accord avec cela ?
Code : Sélectionner tout - Visualiser dans une fenêtre à part CREATE TYPE blindeur_blindtest_table_type AS TABLE
Merci d'avance si vous voyez ce qu'il y a à corriger !
Le fichier joint : ici
Après avoir corrigé :
en cela :
Code : Sélectionner tout - Visualiser dans une fenêtre à part CREATE TYPE joueur_blindtest_table_type AS TABLE
je n'ai plus que ces erreurs-ci :
Code : Sélectionner tout - Visualiser dans une fenêtre à part CREATE TYPE blindeur_blindtest_table_type AS TABLE
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.
Bonjour Mike,
Bon, je vais regarder tout ça. A priori, dans votre code, il y a des mélanges de versions successives…
asap
Ah... Pourtant, j'ai vraiment fait attention... En fait, je pense qu'il ne reste plus que les instead of pour les vues.
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...
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.Envoyé par SQL Server
=> faire un drop du trigger PARTICIPER_TR et virer dans votre code les lignes en cause.
Même principe, virer le trigger COMPOSER_TR et le code qui le définit (lignes 1666 à 1910).Envoyé par SQL Server
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...Envoyé par SQL Server
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.
Génial ! Tout fonctionne !
Voici le diagramme de la DB :
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é !!!
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 :
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 ?
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 !
Bonsoir Mike,
(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 :
Pour obtenir par exemple le total des Jpoints et 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 ;
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 ;
(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 !
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 ?
Bonsoir Mike,
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 :
Pour éviter de supprimer la table existante puis la recréer, on en modifie seulement la structure :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)) ) ;
ALTER TABLE TROUVER ADD quoi INT NOT NULL ; GO ALTER TABLE TROUVER ADD CONSTRAINT TROUVER_QUOI CHECK (quoi IN (1, 2, 3)) ;
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.
Donc a priori une colonne motif_de_bonus/malus dans la table TROUVER.
Vous avez un bloqueur de publicités installé.
Le Club Developpez.com n'affiche que des publicités IT, discrètes et non intrusives.
Afin que nous puissions continuer à vous fournir gratuitement du contenu de qualité, merci de nous soutenir en désactivant votre bloqueur de publicités sur Developpez.com.
Partager