Venons-en aux associations entre les joueurs, les blindtests et les extraits, c’est-à-dire à la table TROUVER. On a trois triggers appartenant à la vue TROUVER_V, à savoir TROUVER_INSERT_TR, TROUVER_UPDATE_TR, TROUVER_DELETE_TR.
les triggers TROUVER_INSERT_TR et TROUVER_UPDATE_TR font appel aux procédures suivantes :
— INSERTED_VIDE_PROC (déjà décrite, cf. posts #52 et #53)
— TROUVER_BLINDTEST_EXISTENCE_PROC (s’assurer que le blindtest proposé existe bien)
— TROUVER_BLINDEUR_EXISTENCE_PROC (s’assurer que le blindeur proposé existe bien)
— TROUVER_BLINDEUR_EST_JOUEUR_PROC (s’assurer que le blindeur est bien un joueur)
— TROUVER_EXTRAIT_EXISTENCE_PROC (s’assurer que l’extrait proposé existe bien)
— TROUVER_BLINDTEST_BLINDEUR_EXISTENCE_PROC (s’assurer que le joueur supposé trouver participe au blindtest)
— TROUVER_DOUBLON_PK_PROC (s’assurer qu’il n’y aura pas de tentative de clé primaire en double)
Voici le code des 3 triggers, les procédures auxquelles ils s’adressent feront l’objet du post suivant.
(1) Le trigger dédié aux inserts via la vue TROUVER_V :
--------------------------------------------------------
-- inserts dans la table TROUVER, via la vue TROUVER_V
---------------------------------------------------------
DROP TRIGGER IF EXISTS TROUVER_INSERT_TR ;
GO
CREATE TRIGGER TROUVER_INSERT_TR ON TROUVER_V INSTEAD OF INSERT
AS
DECLARE @tableCible as VARCHAR(48) = 'TROUVER'
DECLARE @theTrigger as VARCHAR(64) = 'Trigger ' + OBJECT_NAME(@@PROCID)
DECLARE @typeOperation as VARCHAR(48) = 'insert'
DECLARE @tableComposer as VARCHAR(48) = 'COMPOSER'
DECLARE @tableParticiper as VARCHAR(48) = 'PARTICIPER'
DECLARE @pseudoConnu as CHAR(1) = 'Y'
DECLARE @pseudoParticipant as CHAR(1) = 'Y'
DECLARE @blindtestConnu as CHAR(1) = 'Y'
DECLARE @blindtestPseudoParticipant as CHAR(1) = 'Y'
DECLARE @extraitConnu as CHAR(1) = 'Y'
DECLARE @blindtestExtraitParticipant as CHAR(1) = 'Y'
DECLARE @blindeurEstJoueur as CHAR(1) = 'Y'
DECLARE @integritePKrespectee as CHAR(1) = 'Y'
DECLARE @insertedNonVide as CHAR(1) = 'Y'
----------------------------------------------------------------------------
-- La table @inserted est du type blindeur_blindtest_extrait_table_type :
-- ceci permet de passer les données des tables aux procédures.
-- Rappel du type blindeur_blindtest_extrait_table_type :
-- (blinderId, blindtestId, extraitId, pseudo, Numero, artiste, titre).
-- Pour sa part, la table @deleted est vide, elle ne sert
-- qu'en update.
-----------------------------------------------------------------------------
DECLARE @inserted AS blindeur_blindtest_extrait_table_type ;
DECLARE @deleted AS blindeur_blindtest_extrait_table_type ; -- table vide
---------------------------------------------
-- Projection de @inserted sur le blindtest
--------------------------------------------
DECLARE @insertedBlindtest AS blindtest_table_type ;
-----------------------------------------------------
-- c'est parti !
-----------------------------------------------------
---- pour debug ---- select '' as INSERTED, * from INSERTED
----------------------------------------------------------
-- On alimente les tables utilisées par les procédures
-- chargées d'effectuer les différents contrôles.
----------------------------------------------------------
INSERT INTO @inserted (blindeurId, blindtestId, extraitId
, pseudo, Numero, artiste, titre)
SELECT COALESCE(blindeurId, -1)
, COALESCE(blindtestId, -1)
, COALESCE(extraitId, -1)
, x.pseudo, x.Numero, x.artiste, x.titre
FROM INSERTED as x
LEFT JOIN BLINDEUR as y ON x.pseudo = y.pseudo
LEFT JOIN BLINDTEST as z ON x.Numero = z.Numero
LEFT JOIN EXTRAIT as t ON x.artiste = t.artiste
AND x.titre = t.titre
;
----------------------------------------------
-- pour test de vacuité de INSERTED
----------------------------------------------
INSERT INTO @insertedBlindtest (blindtestId, Numero)
SELECT blindtestId, Numero
FROM @inserted
;
---------------------------------------------
-- Si INSERTED est vide, on dégage
---------------------------------------------
EXEC INSERTED_VIDE_PROC
@theTrigger
, @tableCible
, @typeOperation
, @insertedBlindtest
, @insertedNonVide OUTPUT
IF @insertedNonVide = 'N'
RETURN
------------------------------------------------------------
-- on vérifie les valeurs fournies dans INSERTED :
-- les joueurs doivent exister en tant que blindeurs,
-- les numeros des blindtests doivent exister,
-- les paires (artiste, titre) des extraits doivent exister.
-------------------------------------------------------------
----------------------------------------
-- les numéros des blindtests doivent
-- exister
----------------------------------------
EXEC TROUVER_BLINDTEST_EXISTENCE_PROC
@theTrigger
, @tableCible
, @typeOperation
, @inserted
, @blindtestConnu OUTPUT
----------------------------------------
-- les pseudos des joueurs doivent
-- exister
----------------------------------------
EXEC TROUVER_BLINDEUR_EXISTENCE_PROC
@theTrigger
, @tableCible
, @typeOperation
, @inserted
, @pseudoConnu OUTPUT
---------------------------------------------------
-- si un extrait est candidat au
-- remplacement d'un autre extrait,
-- il doit être connu dans la table EXTRAIT
---------------------------------------------------
EXEC TROUVER_EXTRAIT_EXISTENCE_PROC
@theTrigger
, @tableCible
, @typeOperation
, @inserted
, @extraitConnu OUTPUT
----------------------------------------------------------------
-- recherche des blindeurs connus qui disent participer à des
-- blintests mais en fait qui ne participent pas
----------------------------------------------------------------
IF @pseudoConnu = 'Y' AND @blindtestConnu = 'Y'
BEGIN
EXEC TROUVER_BLINDEUR_EST_JOUEUR_PROC
@theTrigger
, @tableCible
, @typeOperation
, @tableParticiper
, @inserted
, @blindeurEstJoueur OUTPUT
END
----------------------------------------------------------------
-- recherche des paires blindtest/extrait inexistantes
-- dans la table de référence COMPOSER
----------------------------------------------------------------
IF @blindtestConnu = 'Y' AND @extraitConnu = 'Y'
BEGIN
EXEC TROUVER_BLINDTEST_EXTRAIT_EXISTENCE_PROC
@theTrigger
, @tableCible
, @typeOperation
, @tableComposer
, @inserted
, @blindtestExtraitParticipant OUTPUT
END
----------------------------------------------------------------
-- recherche des paires blindtest/pseudo inexistantes
-- dans la table de référence PARTICIPER
----------------------------------------------------------------
IF @blindtestConnu = 'Y' AND @pseudoConnu = 'Y'
BEGIN
EXEC TROUVER_BLINDTEST_BLINDEUR_EXISTENCE_PROC
@theTrigger
, @tableCible
, @typeOperation
, @tableParticiper
, @inserted
, @blindtestPseudoParticipant OUTPUT
END
--------------------------------------------------------------------------
-- Détection des tentatives de clé primaire en double :
-- Si INSERTED contient une ligne {pseudo, Numero, artiste, titre}
-- déjà présente dans la table TROUVER, on évitera
-- de mettre celle-ci à jour...
--------------------------------------------------------------------------
IF @pseudoConnu = 'Y' AND @pseudoParticipant = 'Y'
AND @blindeurEstJoueur = 'Y'
AND @blindtestConnu = 'Y' AND @extraitConnu = 'Y'
AND @blindtestExtraitParticipant = 'Y'
AND @blindtestPseudoParticipant = 'Y'
BEGIN
EXEC TROUVER_DOUBLON_PK_PROC
@theTrigger
, @tableCible
, @typeOperation
, @inserted
, @deleted
, @integritePKrespectee OUTPUT
END
------------------------------------------------------
-- Mise à jour de la table TROUVER.
-- Si on a détecté des erreurs, on ne fait rien.
------------------------------------------------------
IF @pseudoConnu = 'Y' AND @pseudoParticipant = 'Y'
AND @blindeurEstJoueur = 'Y'
AND @blindtestConnu = 'Y' AND @extraitConnu = 'Y'
AND @blindtestExtraitParticipant = 'Y'
AND @blindtestPseudoParticipant = 'Y'
AND @integritePKrespectee = 'Y'
BEGIN
INSERT INTO TROUVER (joueurId, blindtestId, extraitId)
SELECT blindeurId, blindtestId, extraitId
FROM @inserted
;
END
GO
(2) Le trigger dédié aux updates via la vue TROUVER_V :
DROP TRIGGER IF EXISTS TROUVER_UPDATE_TR ;
GO
CREATE TRIGGER TROUVER_UPDATE_TR ON TROUVER_V INSTEAD OF UPDATE
AS
DECLARE @tableCible as VARCHAR(48) = 'TROUVER'
DECLARE @theTrigger as VARCHAR(64) = 'Trigger ' + OBJECT_NAME(@@PROCID)
DECLARE @typeOperation as VARCHAR(48) = 'update'
DECLARE @tableComposer as VARCHAR(48) = 'COMPOSER'
DECLARE @tableParticiper as VARCHAR(48) = 'PARTICIPER'
DECLARE @EngueuladeInit AS VARCHAR(128) = @theTrigger
+ 'Update rejeté de '
+ @tableCible
+ ' - ' ;
DECLARE @Engueulade AS VARCHAR(512) ;
DECLARE @nbDeleted as INT ;
DECLARE @pseudoConnu as CHAR(1) = 'Y'
DECLARE @pseudoParticipant as CHAR(1) = 'Y'
DECLARE @blindeurEstJoueur as CHAR(1) = 'Y'
DECLARE @blindtestConnu as CHAR(1) = 'Y'
DECLARE @blindtestPseudoParticipant as CHAR(1) = 'Y'
DECLARE @extraitConnu as CHAR(1) = 'Y'
DECLARE @integritePKrespectee as CHAR(1) = 'Y'
DECLARE @blindtestExtraitParticipant as CHAR(1) = 'Y'
DECLARE @insertedNonVide as CHAR(1) = 'Y'
----------------------------------------------------------------------------
-- Les tables @inserted et @deleted sont du type
-- blindeur_blindtest_extrait_table_type
-- Ceci permet de passer les données des tables aux procédures.
-- Rappel du type blindeur_blindtest_extrait_table_type :
-- (blinderId, blindtestId, extraitId, pseudo, Numero, artiste, titre).
-----------------------------------------------------------------------------
DECLARE @inserted AS blindeur_blindtest_extrait_table_type ;
DECLARE @deleted AS blindeur_blindtest_extrait_table_type ; -- table vide
---------------------------------------------
-- Projection de @inserted sur le blindtest
--------------------------------------------
DECLARE @insertedBlindtest AS blindtest_table_type ;
-----------------------------------------------------
-- c'est parti !
-----------------------------------------------------
---- pour debug ---- select '' as INSERTED, * from INSERTED
---- pour debug ---- select '' as DELETED, * from DELETED
----------------------------------------------------------
-- On alimente les tables utilisées par les procédures
-- chargées d'effectuer les différents contrôles.
----------------------------------------------------------
INSERT INTO @inserted (blindeurId, blindtestId, extraitId
, pseudo, Numero, artiste, titre)
SELECT COALESCE(blindeurId, -1)
, COALESCE(blindtestId, -1)
, COALESCE(extraitId, -1)
, x.pseudo, x.Numero, x.artiste, x.titre
FROM INSERTED as x
LEFT JOIN BLINDEUR as y ON x.pseudo = y.pseudo
LEFT JOIN BLINDTEST as z ON x.Numero = z.Numero
LEFT JOIN EXTRAIT as t ON x.artiste = t.artiste
AND x.titre = t.titre
;
INSERT INTO @deleted (blindeurId, blindtestId, extraitId
, pseudo, Numero, artiste, titre)
SELECT COALESCE(blindeurId, -1)
, COALESCE(blindtestId, -1)
, COALESCE(extraitId, -1)
, x.pseudo, x.Numero, x.artiste, x.titre
FROM DELETED as x
LEFT JOIN BLINDEUR as y ON x.pseudo = y.pseudo
LEFT JOIN BLINDTEST as z ON x.Numero = z.Numero
LEFT JOIN EXTRAIT as t ON x.artiste = t.artiste
AND x.titre = t.titre
;
SET @nbdeleted = (SELECT COUNT(*) FROM @deleted)
----------------------------------------------
-- pour test de vacuité de INSERTED
----------------------------------------------
INSERT INTO @insertedBlindtest (blindtestId, Numero)
SELECT blindtestId, Numero
FROM @inserted
;
---------------------------------------------
-- Si INSERTED est vide, on dégage
---------------------------------------------
EXEC INSERTED_VIDE_PROC
@theTrigger
, @tableCible
, @typeOperation
, @insertedBlindtest
, @insertedNonVide OUTPUT
IF @insertedNonVide = 'N'
RETURN
------------------------------------------------------------
-- on vérifie les valeurs fournies dans INSERTED :
-- les joueurs doivent exister en tant que blindeurs,
-- les numeros des blindtests doivent exister,
-- les paires (artiste, titre) des extraits doivent exister.
-------------------------------------------------------------
----------------------------------------
-- les numéros des blindtests doivent
-- exister
----------------------------------------
EXEC TROUVER_BLINDTEST_EXISTENCE_PROC
@theTrigger
, @tableCible
, @typeOperation
, @inserted
, @blindtestConnu OUTPUT
----------------------------------------
-- les pseudos des joueurs doivent
-- exister
----------------------------------------
EXEC TROUVER_BLINDEUR_EXISTENCE_PROC
@theTrigger
, @tableCible
, @typeOperation
, @inserted
, @pseudoConnu OUTPUT
---------------------------------------------------
-- si un extrait est candidat au
-- remplacement d'un autre extrait,
-- il doit être connu dans la table EXTRAIT
---------------------------------------------------
EXEC TROUVER_EXTRAIT_EXISTENCE_PROC
@theTrigger
, @tableCible
, @typeOperation
, @inserted
, @extraitConnu OUTPUT
----------------------------------------------------------------
-- recherche des blindeurs connus qui disent participer à des
-- blintests mais en fait qui ne participent pas
----------------------------------------------------------------
IF @pseudoConnu = 'Y' AND @blindtestConnu = 'Y'
BEGIN
EXEC TROUVER_BLINDEUR_EST_JOUEUR_PROC
@theTrigger
, @tableCible
, @typeOperation
, @tableParticiper
, @inserted
, @blindeurEstJoueur OUTPUT
END
----------------------------------------------------------------
-- recherche des paires blindtest/pseudo inexistantes
-- dans la table de référence PARTICIPER
----------------------------------------------------------------
IF @blindtestConnu = 'Y' AND @pseudoConnu = 'Y'
AND @blindeurEstJoueur = 'Y'
BEGIN
EXEC TROUVER_BLINDTEST_BLINDEUR_EXISTENCE_PROC
@theTrigger
, @tableCible
, @typeOperation
, @tableParticiper
, @inserted
, @blindtestPseudoParticipant OUTPUT
END
----------------------------------------------------------------
-- recherche des paires blindtest/extrait inexistantes
-- dans la table de référence COMPOSER
----------------------------------------------------------------
IF @blindtestConnu = 'Y' AND @extraitConnu = 'Y'
BEGIN
EXEC TROUVER_BLINDTEST_EXTRAIT_EXISTENCE_PROC
@theTrigger
, @tableCible
, @typeOperation
, @tableComposer
, @inserted
, @blindtestExtraitParticipant OUTPUT
END
--------------------------------------------------------------------------
-- Détection des tentatives de clé primaire en double.
--------------------------------------------------------------------------
IF @pseudoConnu = 'Y' AND @pseudoParticipant = 'Y'
AND @blindeurEstJoueur = 'Y'
AND @blindtestConnu = 'Y' AND @extraitConnu = 'Y'
AND @blindtestExtraitParticipant = 'Y'
AND @blindtestPseudoParticipant = 'Y'
BEGIN
EXEC TROUVER_DOUBLON_PK_PROC
@theTrigger
, @tableCible
, @typeOperation
, @inserted
, @deleted
, @integritePKrespectee OUTPUT
END
-----------------------------------------------------
-- Mise à jour de la table TROUVER.
-- Si on a détecté des erreurs, on ne fait rien.
------------------------------------------------------
IF @pseudoConnu = 'Y' AND @pseudoParticipant = 'Y'
AND @blindeurEstJoueur = 'Y'
AND @blindtestConnu = 'Y' AND @extraitConnu = 'Y'
AND @blindtestExtraitParticipant = 'Y'
AND @blindtestPseudoParticipant = 'Y'
AND @integritePKrespectee = 'Y'
BEGIN
---------------------------------------------
-- Si l'attribut pseudo est touché par
-- l'update, mais pas les attributs
-- Numero, artiste et titre,
-- alors on se limite à l'update de pseudo.
---------------------------------------------
IF UPDATE (pseudo) AND NOT UPDATE (Numero)
AND NOT UPDATE (artiste) AND NOT UPDATE (titre)
BEGIN
;
WITH Restriction as
(SELECT joueurId, blindtestId, extraitId
FROM TROUVER
WHERE joueurId IN (SELECT DISTINCT blindeurId FROM @deleted)
AND blindtestId IN (SELECT DISTINCT blindtestId FROM @deleted)
AND extraitId IN (SELECT DISTINCT extraitId FROM @deleted))
UPDATE Restriction
SET joueurId = x.blindeurId
FROM Restriction as y
JOIN @inserted as x ON x.blindtestId = y.blindtestId
AND x.extraitId = y.extraitId
;
RETURN
END
---------------------------------------------
-- Si l'attribut Numero est touché par
-- l'update, mais pas les attributs
-- pseudo, artiste et titre,
-- alors on se limite à l'update de Numero.
---------------------------------------------
IF UPDATE (Numero) AND NOT UPDATE (pseudo)
AND NOT UPDATE (artiste) AND NOT UPDATE (titre)
BEGIN
;
WITH Restriction as
(SELECT joueurId, blindtestId, extraitId
FROM TROUVER
WHERE joueurId IN (SELECT DISTINCT blindeurId FROM @deleted)
AND blindtestId IN (SELECT DISTINCT blindtestId FROM @deleted)
AND extraitId IN (SELECT DISTINCT extraitId FROM @deleted))
UPDATE Restriction
SET blindtestId = x.blindtestId
FROM Restriction as y
JOIN @inserted as x ON x.blindeurId = y.joueurId
AND x.extraitId = y.extraitId
;
RETURN
END
---------------------------------------------
-- Si l'attribut artiste ou l'attribut titre
-- sont touchés par l'update, mais ni
-- l'attribut Numero ni l'attribut pseudo,
-- alors on se limite à l'update de artiste
-- et titre.
---------------------------------------------
IF NOT UPDATE (pseudo) AND NOT UPDATE (Numero) AND (UPDATE (artiste) OR UPDATE (titre))
BEGIN
;
WITH Restriction as
(SELECT joueurId, blindtestId, extraitId
FROM TROUVER
WHERE joueurId IN (SELECT DISTINCT blindeurId FROM @deleted)
AND blindtestId IN (SELECT DISTINCT blindtestId FROM @deleted)
AND extraitId IN (SELECT DISTINCT extraitId FROM @deleted))
UPDATE Restriction
SET extraitId = x.extraitId
FROM Restriction as y
JOIN @inserted as x ON x.blindeurId = y.joueurId
AND x.blindtestId = y.blindtestId
;
RETURN
END
--------------------------------------------
-- Dans les cas non traités ci-dessus,
-- on effectue un update classque, sous
-- réserve qu'il soit déterministe
-- (une seule ligne à updater).
--------------------------------------------
IF @nbDeleted < 2
BEGIN
UPDATE TROUVER
SET
joueurId =
(SELECT DISTINCT blindeurId
FROM @inserted)
, blindtestId =
(SELECT DISTINCT blindtestId
FROM @inserted)
, extraitId =
(SELECT DISTINCT extraitId
FROM @inserted)
WHERE joueurId IN (SELECT DISTINCT blindeurId FROM @deleted)
AND blindtestId IN (SELECT DISTINCT blindtestId FROM @deleted)
AND extraitId IN (SELECT DISTINCT extraitId FROM @deleted)
END
ELSE
BEGIN
SET @Engueulade = @EngueuladeInit
+ 'Update affectant '
+ 'simultanément plus d''une ligne. '
+ CHAR(13)
+ 'La table ' + @tableCible
+ ' ne sera donc pas mise à jour.'
SELECT @Engueulade AS ' ' ;
SELECT ' ' as 'échec donc, hélas ! (deleted)'
, pseudo as 'pseudo (à remplacer)'
, numero as 'numero (à remplacer)'
, artiste as 'artiste (à remplacer)'
, titre as 'titre (à remplacer)'
FROM DELETED
SELECT ' ' as 'échec donc, hélas ! (inserted)'
, pseudo as 'pseudo (remplaçant)'
, Numero as 'numero (remplaçant)'
, artiste as 'artiste (remplaçant)'
, titre as 'titre (remplaçant)'
FROM INSERTED
RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
---- RAISERROR (@Engueulade, 16,1) -- state = 16 pour bloquer
RETURN
END
END
GO
(3) Le trigger dédié aux deletes via la vue TROUVER_V :
---------------------------------------
-- Contrôle des delete, table TROUVER
----------------------------------------
DROP TRIGGER IF EXISTS TROUVER_DELETE_TR ;
GO
CREATE TRIGGER TROUVER_DELETE_TR ON TROUVER_V INSTEAD OF DELETE
AS
DECLARE @tableCible as VARCHAR(48) = 'TROUVER'
DECLARE @theTrigger as VARCHAR(64) = @tableCible + '_DELETE_TR - '
DECLARE @EngueuladeInit AS VARCHAR(128) = @theTrigger + 'delete dans ' + @tableCible + ' - ';
DECLARE @Engueulade AS VARCHAR(512) ;
DECLARE @nbDeleted as INT ;
-----------------------------------------------------------
-- tables temporaires pour les clés primaires
-----------------------------------------------------------
DECLARE @deleted as TABLE
(
blindeurId INT
, blindtestId INT
, extraitId INT
) ;
-----------------------------------------------------
-- c'est parti !
-----------------------------------------------------
---- pour debug ---- select '' as 'DELETED (on delete)', * from DELETED
---------------------------------------------
-- Si DELETED est vide, on dégage
---------------------------------------------
SET @nbdeleted = (SELECT COUNT(*) FROM DELETED) ;
IF @nbdeleted = 0
BEGIN
SET @Engueulade = @theTrigger + 'Le programme reçoit une sélection vide (DELETED), '
+ 'la table ' + @tableCible
+ ' ne sera donc pas mise à jour.'
SELECT @Engueulade as '@Engueulade'
RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
---- RAISERROR (@Engueulade, 16,1) -- state = 16 pour bloquer
RETURN
END
------------------------------------------------------
-- de DELETED vers @deleted :
-- Traduction des clés alternatives (naturelles) en
-- clés primaires (artificielles)
------------------------------------------------------
INSERT INTO @deleted (blindeurId, blindtestId, extraitId)
SELECT DISTINCT y.blindeurId, z.blindtestId, t.extraitId
FROM DELETED AS x
JOIN BLINDEUR AS y ON x.pseudo = y.pseudo
JOIN BLINDTEST AS z ON x.Numero = z.Numero
JOIN EXTRAIT AS t ON x.artiste = t.artiste
AND x.titre = t.titre
;
------------------------------------------------------
-- Mise à jour de la table TROUVER.
------------------------------------------------------
DELETE FROM TROUVER
WHERE joueurId IN (SELECT blindeurId FROM @deleted)
AND blindtestId IN (SELECT blindtestId FROM @deleted)
AND extraitId IN (SELECT extraitId FROM @deleted)
;
GO
Partager