Capitaine, puisque tu préfères la solution sans modification du code SQL produit par Looping, d’accord, je me remets à l’étude des triggers (avec SQL Server pour commencer), à chaque jour suffit sa peine. 
Je reviens sur l’exemple proposé par Yves Tabourier, dans le post #1, dans lequel un professeur ne peut enseigner une matière à une classe que s’il est qualifié pour cela. Je reprends cet exemple parce qu’il est aussi présent dans l’ouvrage de Dominique Nanci et Bernard Espinasse, Ingénierie des Systèmes d’information : Merise, au chapitre 13, page 305 et qu’on y cause de triggers pour garantir la contrainte. 
Chez DVP qui a repris l’ouvrage : « III-C-3-ac. Contraintes d'inclusion de relations sur d'autres relations ».
Le MCD proposé dans l’ouvrage :
On y trouve donc un trigger pour garantir la contrainte d’inclusion :
1
2
3
4
5
6
7
8
9
10
11
12
13
| CREATE TRIGGER Inclusion_Enseigner_Qualifier
BEFORE INSERT ON Enseigner
ON EACH ROW
DECLARE
est_qualifié number;
BEGIN
SELECT COUNT(*) INTO est_qualifié FROM Qualifier
WHERE n°professeur = :new.n°professeur
AND n°matière = :new.n°matière;
IF est_qualifié = 0 THEN
raise_application_error (-20007, "Ce professeur n'est pas qualifié pour cette matière" ));
END IF;
END; |
So far so good, mais la contrainte peut malgré tout être violée ! En effet, que se passe-t-il quand le professeur Untel perd une ou plusieurs de ses qualifications ? Il n’y a aucun impact sur la table ENSEIGNER...
Moralité : la table QUALIFIE doit elle aussi avoir ses petits triggers...
Allez, je me fends d’un code pour remédier à cela, tout en sachant qu’il devra y avoir des triggers FOR UPDATE, mais comme je l’ai dit plus haut, à chaque jour suffit sa peine.
Je propose le code suivant (SQL Server)
CREATE TABLE MATIERE
(
NoMatiere INT NOT NULL
, NomMatiere VARCHAR(24) NOT NULL
, CONSTRAINT MATIERE_PK PRIMARY KEY(NoMatiere)
) ;
CREATE TABLE PROFESSEUR
(
NoProfesseur INT NOT NULL
, NomProfesseur VARCHAR(24) NOT NULL
, CONSTRAINT PROFESSEUR_PK PRIMARY KEY(NoProfesseur)
) ;
CREATE TABLE CLASSE
(
NoClasse INT NOT NULL
, NomClasse VARCHAR(24) NOT NULL
, CONSTRAINT CLASSE_PK PRIMARY KEY(NoClasse)
) ;
CREATE TABLE QUALIFIE
(
NoMatiere INT NOT NULL
, NoProfesseur INT NOT NULL
, CONSTRAINT QUALIFIE_PK PRIMARY KEY(NoMatiere, NoProfesseur)
, CONSTRAINT QUAL_MAT_FK FOREIGN KEY(NoMatiere)
REFERENCES MATIERE (NoMatiere)
ON DELETE CASCADE
, CONSTRAINT QUAL_PROF_FK FOREIGN KEY(NoProfesseur)
REFERENCES PROFESSEUR (NoProfesseur)
) ;
CREATE TABLE ENSEIGNER
(
NoMatiere INT NOT NULL
, NoProfesseur INT NOT NULL
, NoClasse INT NOT NULL
, CONSTRAINT ENSEGNER_PK PRIMARY KEY(NoMatiere, NoProfesseur, NoClasse)
, CONSTRAINT ENS_MAT_FK FOREIGN KEY(NoMatiere)
REFERENCES MATIERE (NoMatiere)
ON DELETE CASCADE
, CONSTRAINT ENS_PROF_FK FOREIGN KEY(NoProfesseur)
REFERENCES PROFESSEUR (NoProfesseur)
, CONSTRAINT ENS_CLASSE_FK FOREIGN KEY(NoClasse)
REFERENCES CLASSE (NoClasse)
) ;
INSERT INTO MATIERE VALUES
(1, 'français')
, (2, 'maths')
, (3, 'histoire')
, (4, 'géographie')
, (5, 'latin')
, (6, 'physique')
, (7, 'chimie')
, (8, 'philo')
, (9, 'anglais')
, (10, 'allemand')
, (11, 'informatique')
, (12, 'grec')
;
SELECT * FROM MATIERE ;
INSERT INTO PROFESSEUR VALUES
(1, 'Fernand')
, (2, 'Raoul')
, (3, 'Paul')
, (4, 'Antoine')
, (5, 'Mimile')
, (6, 'Jean')
, (7, 'Theo')
, (8, 'Fred')
, (9, 'Patrick')
;
SELECT * FROM PROFESSEUR ;
INSERT INTO CLASSE VALUES
(1, 'Mathelem')
, (2, 'Science ex')
, (3, 'Philo')
, (4, '1ere A')
, (5, '1ere B')
, (6, '1ere C')
, (7, '2nde A')
, (8, '2nde B')
, (9, '2nde C')
;
SELECT * FROM CLASSE ;
INSERT INTO QUALIFIE VALUES
(1, 2) -- français Raoul
, (1, 4) -- français Antoine
, (1, 5) -- français Mimile
, (3, 1) -- histoire Fernand
-- , (3, 6) -- histoire Jean
, (4, 1) -- géo Fernand
, (5, 2) -- latin Raoul
, (6, 7) -- physique Theo
, (7, 2) -- chimie Raoul
, (7, 7) -- chimie Theo
, (8, 1) -- philo Fernand
, (9, 6) -- anglais Jean
, (10, 6) -- allemand Jean
, (11, 8) -- informatique Fred
, (11, 9) -- informatique Patrick
, (12, 3) -- grec Paul
;
SELECT NomMatiere, NomProfesseur, '' as matprof
FROM QUALIFIE AS q
JOIN MATIERE AS m ON q.NoMatiere = m.NoMatiere
JOIN PROFESSEUR AS p ON q.NoProfesseur = p.NoProfesseur
;
CREATE TRIGGER INCLUSION_ENSEIGNER_TRIGGER_INSERT ON ENSEIGNER
AFTER INSERT
AS
DECLARE @Engueulade AS VARCHAR(512) ;
DECLARE @n as INT ;
DECLARE @nbInserted as INT ;
-- pour y voir clair
SELECT'' AS ENS_ISRT_trigger, *
FROM INSERTED
ORDER BY NoMatiere, NoProfesseur, NoClasse
SET @n =
(
SELECT COUNT(*)
FROM INSERTED AS e
WHERE NOT EXISTS
(
SELECT *
FROM QUALIFIE AS q
JOIN CLASSE AS c ON e.NoClasse = c.NoClasse
WHERE e.NoMatiere = q.NoMatiere
and e.NoProfesseur = q.NoProfesseur
)
)
;
SELECT @n AS nbViols
IF @n > 0
BEGIN
SELECT '' AS Les_delinquants, *
FROM INSERTED AS e
WHERE NOT EXISTS
(
SELECT *
FROM QUALIFIE AS q
JOIN CLASSE AS c ON e.NoClasse = c.NoClasse
WHERE e.NoMatiere = q.NoMatiere
AND e.NoProfesseur = q.NoProfesseur
)
SET @Engueulade = 'Insert into ENSEIGNER, Viol de la contrainte d''inclusion.'
RAISERROR (@Engueulade, 16,1) -- state = 16 pour bloquer
ROLLBACK
END;
CREATE TRIGGER INCLUSION_QUALIFIE_TRIGGER_DELETE ON QUALIFIE
AFTER DELETE
AS
DECLARE @Engueulade AS VARCHAR(512) ;
DECLARE @n as INT ;
SELECT *, '' AS deleted FROM DELETED
ORDER BY NoMatiere, NoProfesseur
SET @n =
(
SELECT COUNT(*)
FROM ENSEIGNER AS e
WHERE EXISTS
(
SELECT *
FROM DELETED AS d
WHERE d.NoMatiere = e.NoMatiere
and d.NoProfesseur = e.NoProfesseur
)
)
;
SELECT @n AS nDel ;
IF @n > 0
BEGIN
SELECT '' as Delinquant, *
FROM ENSEIGNER AS e
WHERE EXISTS
(
SELECT *
FROM DELETED AS d
WHERE d.NoMatiere = e.NoMatiere
and d.NoProfesseur = e.NoProfesseur
)
SET @Engueulade = 'Delete From QUALIFIE, viol de la contrainte d''inclusion.'
RAISERROR (@Engueulade, 16,1) -- state = 16 pour bloquer
ROLLBACK
END;
INSERT INTO ENSEIGNER VALUES
(1, 4, 6) -- français Antoine 1ere C
, (1, 4, 8) -- français Antoine 2nde B
, (1, 5, 4) -- français Mimile 1re A
, (1, 5, 8) -- français Mimile 2nde B
, (3, 1, 1) -- histoire Fernand mathelem
, (4, 1, 1) -- géo Fernand mathelem
, (4, 1, 3) --- géo Fernand philo
, (5, 2, 4) -- latin Raoul 1re A
, (6, 7, 4) -- physique Theo 1re A
, (7, 2, 1) -- chimie Raoul mathelem
, (7, 2, 3) - - chimie Raoul philo
, (7, 7, 4) -- chimie Theo 1re A
, (7, 7, 5) - - chimie Theo 1re B
, (9, 6, 3) -- anglais Jean philo
, (11, 8, 3) -- informatique Fred philo
, (11, 8, 2) -- informatique Fred science ex
, (11, 9, 2) -- informatique Patrick science ex
, (11, 9, 3) -- informatique Patrick philo
;
DELETE FROM QUALIFIE WHERE NoProfesseur = 1 AND NoMatiere = 4
Réaction du SGBD
Envoyé par
SQL Server
Msg 50000, Niveau 16, État 1, Procédure INCLUSION_QUALIFIE_TRIGGER_DELETE
Delete From QUALIFIE, viol de la contrainte d'inclusion.
Le constat de la culpabilité :
Delinquant NoMatiere NoProfesseur NoClasse
4 1 1
4 1 3
Comme annoncé, manquent les triggers FOR UPDATE.
Il est évident que le plus simple eut été de déclarer une étrangère dans le CREATE TABLE de la table ENSEIGNER (cf. post #1) :
1
2
3
4
5
6
7
8
9
10
11
| CREATE TABLE ENSEIGNER
(
NoMatiere INT
, NoProfesseur INT
, NoClasse INT
, CONSTRAINT ENSEGNER_PK PRIMARY KEY(NoMatiere, NoProfesseur, NoClasse)
, CONSTRAINT ENSEIGNER_QUALIFIE_FK FOREIGN KEY(NoMatiere, NoProfesseur)
REFERENCES QUALIFIE (NoMatiere, NoProfesseur),
, CONSTRAINT ENS_CLASSE_FK FOREIGN KEY(NoClasse)
RE(FERENCES CLASSE (NoClasse)
); |
Auquel cas exeunt les triggers…
Au sujet des contraintes d'inclusion
Répondre avec citation
!
INCLUSION
je garantis la contrainte d’inclusion au moyen d’un trigger...
Partager