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)
;
Pour créer des animateurs, on a repris les valeurs affectées par nos soins à la colonne blindeurId de la table BLINDEUR : 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)
) ;
Alors le code de création des blindeurs se simplifie :
---------------------------
--- Quelques blindeurs
---------------------------
INSERT INTO BLINDEUR (blindeurId, pseudo) VALUES
('fernand'), ('raoul'), ('paul'), ('mado')
, ('folace'), ('jean'), ('antoine'), ('patricia')
;
So far, so good. Mais on peut se demander quelles valeurs ont été affectées cette fois-ci à blindeurId par SQL Server, il va falloir effectuer des recherches.
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')
;
Ou à l’unité :
INSERT INTO ANIMATEUR (animateurId)
SELECT blindeurId
FROM BLINDEUR
WHERE pseudo = 'fernand'
;
INSERT INTO ANIMATEUR (animateurId)
SELECT blindeurId
FROM BLINDEUR
WHERE pseudo = 'raoul'
;
Etc.
Ainsi on n’a nul besoin de la connaissance des valeurs de la clé primaire, calculées par le SGBD, elles restent sous le capot et ne nous intéressent pas. En réalité, l’objectif est de ne se servir que des propriétés naturelles des objets, celles dont on a la maîtrise, et d’ignorer les propriétés artificielles calculées uniquement par le SGBD. Ainsi est-on amené à doubler chaque clé primaire de type IDENTITY d’une clé alternative telle que {pseudo} dans le cas de la table BLINDEUR : grâce à cette clé alternative, on a pu insérer sans problème dans la table ANIMATEUR. De façon générale les valeurs des clés primaires peuvent se retrouver en tant que valeurs de clés étrangères dans les autres tables (intégrité référentielle oblige), mais en aucun cas les valeurs des clés alternatives, elles n’existent que comme points d’entrée dans le système, d’accès au données, comme illustré dans l’exemple des blindeurs et des animateurs : les valeurs 'fernand', 'raoul', 'paul', 'mado' ne sont présentes que dans la table BLINDEUR, ce qui ne nous empêche pas, comme on vient de le voir, de nous en servir pour créer les animateurs.
Pour savoir qui sont les animateurs :
SELECT pseudo AS Animateur
FROM ANIMATEUR AS x
JOIN BLINDEUR AS y on x.animateurId = y.blindeurId
;
Réponse du SGBD :
Animateur
---------
fernand
raoul
paul
mado
Passons aux joueurs. La technique est la même :
INSERT INTO JOUEUR (joueurId)
SELECT blindeurId
FROM BLINDEUR
WHERE pseudo IN ('fernand', 'mado', 'folace', 'jean', 'antoine', 'patricia')
;
Pour savoir qui sont les joueurs :
SELECT pseudo AS Joueur
FROM JOUEUR AS x
JOIN BLINDEUR AS y on x.joueurId = y.blindeurId
;
La table BLINDTEST n’ayant pas de clé alternative que nous maîtrisions, on lui en attribue une, disons {blindtestCode} :
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'
Pour voir le résultat :
SELECT '' as blindtest, blindtestCode, pseudo
FROM BLINDTEST AS x
JOIN BLINDEUR AS y ON x.animateurId = y.blindeurId
;
Réponse du SGBD :
blindtest blindtestCode pseudo
--------- ------------- ------
blindt01 fernand
blindt02 mado
Le cas des participations des joueurs est intéressant. En effet, la table PARTICIPER est dotée de la vue PARTICIPER_V laquelle a pour attributs joueurId, blindtestId, mais ceux-ci sont artificiels, et on voudrait les remplacer par leurs homologues naturels : pseudo et blindtestCode...
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
;
La vue permet évidemment d’accéder à la table PARTICIPER, c’est sa finalité 1re. Mais il faut avoir accès le pseudo du joueur, d’où le JOIN avec la table BLINDEUR, et il faut avoir accès au blindtestCode, d’où la jointure avec la table BLINDTEST.
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
) ;
(2) On insère dans @inserted les valeurs de joueurId, blindtestId en fonction des valeurs de pseudo et blindtestCode fournies par le SGBD dans 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
Contenu de INSERTED, transmis au trigger par le SGBD :
pseudo blindtestCode
------ -------------
folace blindt01
folace blindt02
jean blindt01
A titre de curiosité, contenu de @inserted après INSERT :
joueurId blindtestId
-------- -----------
317 9
317 10
318 9
Mais les valeurs affichées nous indiffèrent complètement, puisque seules les valeurs des propriétés naturelles nous intéressent, d’autant que les valeurs artificielles affichées peuvent parfaitement changer dans le temps...
(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
;
Le code du trigger :
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
Insérons quelques participations (avec des valeurs « naturelles » c’est quand même plus simple !) :
INSERT INTO PARTICIPER_V (pseudo, blindtestCode) VALUES
('folace', 'blindt01')
, ('folace', 'blindt02')
, ('jean', 'blindt01')
Interrogeons le SGBD quant au résultat :
SELECT '' as participer, blindtestCode, pseudo
from PARTICIPER_V
;
Ce qu’il en dit :
participer blindtestCode pseudo
---------- ------------- ------
blindt01 folace
blindt02 folace
blindt01 jean
Une tentative de viol, contrée par le trigger :
-- 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')
;
Au résultat :
Un blinder ne peut pas participer au blindtest qu'il anime !
Une autre tentative de viol, par UPDATE cette fois-ci :
-- 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'
;
Attention ! Le trigger est à compléter, car en cas d’update et s’il n’y a pas de viol, ça n’est pas un INSERT qu’l faut exécuter pour la table PARTICIPER mais un UPDATE. On fera ça plus tard.
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 ♪ ♫.
Partager