Trigger et PL/SQL Oracle 10
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;
