Bonjour,

J'ai attaqué mon devoir, si quelqu'un peut vérifier, je pense que mes triggers sont hors sujets

---------------------------------------------------------------
On considère une base de données relationnelles concernant une école

ELEVE (numE, nomE, pnomE, #numC)
PROFESSEUR (numP, nomP, pnomP)
CLASSE (numC, salleC)
ENSEIGNEMENT (#numC, #numP, nbh)

numE : numéro d'élève (unique, de type compteur)
nomE, pnomE : nom et prénom d'élève
numP : numéro de professeur (unique, de type compteur)
nomP, pnomP : nom et prénom de professeur
numC : code de classe (unique)
salleC : salle de classe
nbh : nombre d'heures annuelles

Un élève n'appartient qu'à une seule classe. Une classe possède sa propre et unique salle.

On désire effectuer un certain nombre d'opérations de mise à jour de la base :

a) Ajouter un nouvel élève dans la classe numC = "CM2".
b) Faire passer l'élève DURACUIRE Léon de la classe "CM1" à la classe "CM2"
c) Ajouter un nouveau professeur DUPONT Georges qui effectuera annuellement 120h en "CM1" et 240h en "CM2".

1) On souhaite employer des triggers pour effectuer ces mises à jour. Quelles sont vos propositions ?
2) On souhaite employer PL/SQL pour ces opérations de mises à jour. Quelles sont vos solutions ?

-------------------------------------------------
Mes réponses

Création des tables relationnelles


CREATE TABLE CLASSE
(
numC VARCHAR(3) PRIMARY KEY,
salleC VARCHAR(50)
)
;

CREATE TABLE PROFESSEUR
(
numP INT PRIMARY KEY,
nomP VARCHAR(50),
pnomP VARCHAR(50)
)
;

CREATE TABLE ELEVE
(
numE INT PRIMARY KEY,
nomE VARCHAR(50),
pnomE VARCHAR(50),
numC VARCHAR(3),
FOREIGN KEY (numC) REFERENCES CLASSE (numC)
)
;

CREATE TABLE ENSEIGNEMENT
(
numC VARCHAR(3),
numP INT,
nbh REAL,
PRIMARY KEY (numC, numP),
FOREIGN KEY (numC) REFERENCES CLASSE (numC),
FOREIGN KEY (numP) REFERENCES PROFESSEUR (numP)
)
;


A) Séquences SQL

On peut utiliser les séquences pour gérer les numéros uniques des élèves et professeurs.


CREATE SEQUENCE SEQ_ELEVE
START WITH 1
MAXVALUE 999
MINVALUE 1;

CREATE SEQUENCE SEQ_PROF
START WITH 1
MAXVALUE 999
MINVALUE 1;

On initialise des valeurs dans la table CLASSE

INSERT INTO CLASSE VALUES (‘CM1’,’SALLE CM1’);
INSERT INTO CLASSE VALUES (‘CM2’,’SALLE CM2’);

a) Insertion de l'élève DURAND Marc en CM2 :

INSERT INTO ELEVE VALUES (SEQ_ELEVE.NEXTVAL,'DURAND','MARC','CM2');

b) Passage de l’élève DURACUIRE Léon du CM1 au CM2 :

On initialise l’élève dans la table ELEVE avec sa classe de base (CM1)

INSERT INTO ELEVE VALUES (SEQ_ELEVE.NEXTVAL,'DURACUIRE’,'LEON','CM1');

UPDATE ELEVE SET numC='CM2' WHERE nomE='DURACUIRE' AND pnomE='LEON' AND numC='CM1';

c) Ajout du professeur Dupont Georges, 120h de CM1 et 240h de CM2

INSERT INTO PROFESSEUR VALUES (SEQ_PROF.NEXTVAL,'DUPONT','GEORGES');
INSERT INTO ENSEIGNEMENT VALUES ('CM1',SEQ_PROF.CURRVAL,120);
INSERT INTO ENSEIGNEMENT VALUES ('CM2',SEQ_PROF.CURRVAL,240);

1) Triggers

a)
CREATE OR REPLACE TRIGGER ins_eleve_cm2
BEFORE INSERT
ON ELEVE
REFERENCING news AS NEW old AS OLD
FOR EACH ROW
DECLARE
BEGIN
:new.numC:='CM2';
END;

Puis on execute la commande d’insertion de l’élève Durand
INSERT INTO ELEVE VALUES (SEQ_ELEVE.NEXTVAL,'DURAND','MARC','CM2');


b)

On désactive le trigger ins_eleve_cm2 sinon Duracuire sera automatiquement en CM2 !

ALTER TRIGGER ins_eleve_cm2 DISABLE;
INSERT INTO ELEVE VALUES (SEQ_ELEVE.NEXTVAL,'DURACUIRE’,'LEON','CM1');
CREATE OR REPLACE TRIGGER maj_eleve_cm2
BEFORE UPDATE
ON ELEVE
REFERENCING new AS NEW old AS OLD
FOR EACH ROW
DECLARE
BEGIN
:new.numC:='CM2';
END;
ALTER TRIGGER ins_eleve_cm2 ENABLE ;

Attention car ces triggers vont se déclencher quand on voudra faire une modification quelconque sur un élève... A utiliser à des fonctions de tests uniquement.

c)
CREATE OR REPLACE TRIGGER maj_professeur
AFTER INSERT
ON PROFESSEUR
REFERENCING new AS NEW old AS OLD
FOR EACH ROW
DECLARE
BEGIN
INSERT INTO ENSEIGNEMENT VALUES ('CM1',:new.numP,120);
INSERT INTO ENSEIGNEMENT VALUES ('CM2',:new.numP,240);
END;

2) PL/SQL

a)

CREATE OR REPLACE PROCEDURE proc_insert_eleve (nom_eleve IN VARCHAR2,pnom_eleve IN VARCHAR2) IS
BEGIN
INSERT INTO ELEVE VALUES (SEQ_ELEVE.NEXTVAL,nom_eleve,pnom_eleve,'CM2');
END;

Ce qui donne lors de l’execution de la commande

BEGIN proc_insert_eleve (‘DURAND’,’MARC‘);
END;

b)

CREATE OR REPLACE PROCEDURE proc_maj_eleve (nom_eleve IN VARCHAR2,pnom_eleve IN VARCHAR2) IS
BEGIN
UPDATE ELEVE SET numC='CM2' WHERE nomE=nom_eleve AND pnomE=pnom_eleve AND numC='CM1';
END;

Ce qui donne lors de l’ execution de la commande

BEGIN proc_maj_eleve (‘DURACUIRE’,’LEON‘);
END;

c)

CREATE OR REPLACE PROCEDURE proc_insert_professeur (nom_prof IN VARCHAR2,pnom_prof IN VARCHAR2) IS
BEGIN
INSERT INTO PROFESSEUR VALUES (SEQ_PROF.NEXTVAL,nom_prof,pnom_prof);
INSERT INTO ENSEIGNEMENT VALUES ('CM1',SEQ_PROF.CURRVAL,120);
INSERT INTO ENSEIGNEMENT VALUES ('CM2',SEQ_PROF.CURRVAL,240);
END;

Ce qui donne lors de l’ execution de la commande

BEGIN proc_insert_professeur (‘DUPONT’,’GEORGES‘);
END;