Bonsoir Mike,
Avant de foncer tête baissée, il y a quelques précautions à prendre. Ainsi il n’est pas bon de faire figurer dans les requêtes les valeurs de clés primaires générées par le SGBD quand le paramètre IDENTITY est dans le coup, car comme je l’ai précisé dans le post #9, ce paramètre peut être source de déboires.
Dans ce même post, reprenons cette partie du jeu d’essai :
---------------------------
--- Quelques blindeurs
---------------------------
INSERT INTO BLINDEUR (blindeurId, pseudo) VALUES
(1, 'fernand'), (2, 'raoul'), (3, 'paul'), (4,'mado')
, (11, 'folace'), (12, 'jean'), (13, 'antoine'), (14, 'patricia')
;
---------------------------
--- Quelques animateurs
---------------------------
INSERT INTO ANIMATEUR (animateurId) VALUES
(1), (2), (3), (4)
;
Si on utilise le paramètre IDENTITY pour la table BLINDEUR :
CREATE TABLE BLINDEUR
(
blindeurId INT IDENTITY NOT NULL
, pseudo VARCHAR(16) NOT NULL
, CONSTRAINT BLINDEUR_PK PRIMARY KEY (blindeurId)
, CONSTRAINT BLINDEUR_AK UNIQUE (pseudo)
) ;
---------------------------
--- Quelques blindeurs
---------------------------
INSERT INTO BLINDEUR (blindeurId, pseudo) VALUES
('fernand'), ('raoul'), ('paul'), ('mado')
, ('folace'), ('jean'), ('antoine'), ('patricia')
;
Afin d’éviter cela, pour créer les animateurs il est raisonnable de coder :
INSERT INTO ANIMATEUR (animateurId)
SELECT blindeurId
FROM BLINDEUR
WHERE pseudo IN ('fernand', 'raoul', 'paul', 'mado')
;
INSERT INTO ANIMATEUR (animateurId)
SELECT blindeurId
FROM BLINDEUR
WHERE pseudo = 'fernand'
;
INSERT INTO ANIMATEUR (animateurId)
SELECT blindeurId
FROM BLINDEUR
WHERE pseudo = 'raoul'
;
Etc.
Pour savoir qui sont les animateurs :
SELECT pseudo AS Animateur
FROM ANIMATEUR AS x
JOIN BLINDEUR AS y on x.animateurId = y.blindeurId
;
Animateur
---------
fernand
raoul
paul
mado
INSERT INTO JOUEUR (joueurId)
SELECT blindeurId
FROM BLINDEUR
WHERE pseudo IN ('fernand', 'mado', 'folace', 'jean', 'antoine', 'patricia')
;
SELECT pseudo AS Joueur
FROM JOUEUR AS x
JOIN BLINDEUR AS y on x.joueurId = y.blindeurId
;
CREATE TABLE BLINDTEST
(
blindtestId INT IDENTITY NOT NULL
, blindtestCode VARCHAR(8) NOT NULL
, animateurId INT NOT NULL
, CONSTRAINT BLINDTEST_PK PRIMARY KEY (blindtestId)
, CONSTRAINT BLINDTEST_AK UNIQUE (blindtestCode)
, CONSTRAINT BLINDTEST_ANIMATEUR_FK FOREIGN KEY (animateurId)
REFERENCES ANIMATEUR (animateurId)
) ;
Affectons les animateurs aux blindtests (on peut by-passer la table ANIMATEUR car par transitivité un blindtest détermine un blindeur). fernand anime le blindtest 'blindt01' et mado anime le blindtest 'blindt02' :
INSERT INTO BLINDTEST (blindtestCode, animateurId)
SELECT 'blindt01', blindeurId
FROM BLINDEUR
WHERE pseudo = 'fernand'
;
INSERT INTO BLINDTEST (blindtestCode, animateurId)
SELECT 'blindt02', blindeurId
FROM BLINDEUR
WHERE pseudo = 'mado'
SELECT '' as blindtest, blindtestCode, pseudo
FROM BLINDTEST AS x
JOIN BLINDEUR AS y ON x.animateurId = y.blindeurId
;
blindtest blindtestCode pseudo
--------- ------------- ------
blindt01 fernand
blindt02 mado
Qu’à cela ne tienne, changeons le code de la vue :
CREATE VIEW PARTICIPER_V (pseudo, blindtestCode)
AS
SELECT pseudo, blindtestCode
FROM PARTICIPER as x
JOIN BLINDEUR as y ON x.joueurId = y.blindeurId ------ pour avoir le pseudo
JOIN BLINDTEST as z ON x.blindtestId = z.blindtestId ------ pour avoir le blindtestCode
;
Le trigger PARTICIPER_TR est évidemment impacté, puisqu’il s’attend jusqu’ici à lire une table temporaire INSERTED ayant pour en-tête celui qu’avait la vue avant son changement, à savoir {joueurId, blindtestId}, alors que désormais la table INSERTED a pour en-tête {pseudo, blindtestCode}. Une façon d’aménager le trigger est la suivante :
(1) On déclare une variable de type table et d’en-tête {joueurId, blindtestId}, nommons-la @inserted :
DECLARE @inserted TABLE
(
joueurId INT
, blindtestId INT
) ;
INSERT INTO @inserted (joueurId, blindtestId)
SELECT DISTINCT y.blindeurId, z.blindtestId
FROM INSERTED AS x
JOIN BLINDEUR AS y ON x.pseudo = y.pseudo
JOIN BLINDTEST AS z ON x.blindtestCode = z.blindtestCode
pseudo blindtestCode
------ -------------
folace blindt01
folace blindt02
jean blindt01
joueurId blindtestId
-------- -----------
317 9
317 10
318 9
(3) Si l’on n’a pas constaté de viol de la contrainte d’exclusion, on met à jour la table PARTICIPER, non plus à partir de la table INSERTED mais à partir de la table @inserted :
INSERT INTO PARTICIPER (joueurId, blindtestId)
SELECT joueurId, blindtestId
FROM @inserted
;
DROP TRIGGER IF EXISTS PARTICIPER_TR ;
GO
CREATE TRIGGER PARTICIPER_TR ON PARTICIPER_V INSTEAD OF INSERT, UPDATE
AS
DECLARE @Engueulade AS VARCHAR(512) ;
DECLARE @n as INT ;
DECLARE @inserted TABLE
(
joueurId INT
, blindtestId INT
) ;
---- pour debug ---- select '' as INSERTED, * from INSERTED
INSERT INTO @inserted (joueurId, blindtestId)
SELECT DISTINCT y.blindeurId, z.blindtestId
FROM INSERTED AS x
JOIN BLINDEUR AS y ON x.pseudo = y.pseudo
JOIN BLINDTEST AS z ON x.blindtestCode = z.blindtestCode
;
---- pour debug ---- select '' as '@inserted', * from @inserted
SET @N =
(SELECT COUNT(*)
FROM @inserted AS x
JOIN BLINDTEST AS y
ON x.blindtestId = y.blindtestId
AND x.joueurId = y.animateurId)
---- pour debug ---- select @n as '@n'
----------------------------------------------------
-- Si on constate une tentative de viol de la
-- contrainte d'exclusion, on rouspète
-- et on s'en va.
----------------------------------------------------
IF @n > 0
BEGIN
SET @Engueulade = 'Un blinder ne peut pas participer au blindtest qu''il anime !'
SELECT @Engueulade AS Engueulons --, * FROM INSERTED
RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
---- RAISERROR (@Engueulade, 16,1) -- state = 16 pour bloquer
RETURN
END
----------------------------------------------------
-- On n'a pas constaté d'anomalie :
-- on met la table PARTICIPER à jour.
----------------------------------------------------
INSERT INTO PARTICIPER (joueurId, blindtestId)
SELECT joueurId, blindtestId
FROM @inserted
;
GO
INSERT INTO PARTICIPER_V (pseudo, blindtestCode) VALUES
('folace', 'blindt01')
, ('folace', 'blindt02')
, ('jean', 'blindt01')
SELECT '' as participer, blindtestCode, pseudo
from PARTICIPER_V
;
participer blindtestCode pseudo
---------- ------------- ------
blindt01 folace
blindt02 folace
blindt01 jean
-- Le blindeur fernand anime le blindtest blindt01
-- et ne pourra donc y participer, le trigger veillant au grain
INSERT INTO PARTICIPER_V (pseudo, blindtestCode) VALUES
('fernand', 'blindt01')
;
Un blinder ne peut pas participer au blindtest qu'il anime !
-- Le blindeur mado a le droit de participer
-- au blindtest blindt01.
-- Par contre, mado anime le blindtest blindt02
-- et ne pourra donc y participer, le trigger veillant au grain.
INSERT INTO PARTICIPER_V (pseudo, blindtestCode) VALUES
('mado', 'blindt01')
;
UPDATE PARTICIPER_V
SET blindtestCode = 'blindt02' WHERE pseudo = 'mado'
;
Maintenant, le trigger PARTICIPER_TR est-il la panacée pour garantir la contrainte d’exclusion ? Par exemple, que se passe-t-il si par mise à jour de la table BLINDTEST, le joueur du blindtest t1 devient animateur de ce blindtest à la place de son animateur actuel ?
Je fais une pause et reviendrai pour traiter des extraits et tout ce qui s’en suit.
Envoyé par
Mike888
Je vais essayer d'employer LINQ pour manipuler la DB depuis C#.
Désolé, mais d’une part on sort du domaine de la modélisation des données et d’autre part je ne connais ni C# ni LINQ (quand je programmais, c’était en assembleur...)
N.B.
Dans le post #9, j’ai corrigé une erreur de rédaction et remplacé "inclusion" par "exclusion".
Envoyé par
Mike888
Hors-sujet :
Vous jouez d'un instrument ?
Oui, guitare classique ♪ ♫.
Gestion de blindtests; comment faire ?
Répondre avec citation
(y'a surement de bons tutos sur le net).
Partager