Trigger et PL/SQL Oracle 10

[cybernoid]

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;

[dgi77]

J'ai survolé ton post...
Au premier abord, je suis d'accord avec tes INSERT (via SQL ou PL/SQL), par contre je ne comprends pas le but de ces triggers et je ne vois pas de toute façon l'utilité ni même la possibilité d'utiliser des triggers pour faire les mises à jour en base que tu dois faire.
En général, on utilise un trigger pour déclencher lors de la mise à jour d'une table, la mise à jour d'autre tables ayant des dépendances fonctionnelles avec la première. Mais là ce n'est pas ton cas : les mises à jour qu'on te demande n'impliquent qu'une seule table à chaque fois... me semble-t-il...

[7gyY9w1ZY6ySRgPeaefZ]

Je suis du même avis que dgi77

Utiliser un trigger pour une bête mise à jour ponctuelle sur une table, ça me semble aller à l'encontre de la notion de trigger.

[cybernoid]

Je suis d'accord avec vous, mais c'est une exigence du devoir, alors je cherche quelque chose d'intéressant à faire avec ces triggers sans être hors sujet !

Merci de vos réponses !

[chaya]

l'intitulé du sujet est obligatoirement celui que tu cites?

sinon tu peux te faire un sujet avec par exemple: un accès a des ressources qui declenche un update du nom de l'user demandant la ressource?

une table produit par exemple, et pour chaque consultation de produit tu met a jour un champ : Derniere_consultation_par

non ?

[cybernoid]

bonjour !

Eh oui l'intitulé est obligatoirement celui du devoir !

J'ai posté des corrections et des ajouts, je ne dois pas etre loin de la version finale. Il convient de désactiver un trigger avant d'en lancer un autre sinon cela devient vite ingérable ! (cas Elève Duracuire)
Mais j'ai eu beau retourner le sujet dans tous les sens, je n'ai rien trouvé de plus pertinent à faire, j'ai donc suivi l'énoncé !
a + et encore merci pour vos avis !

[nenex73]

T'es reconnu FP.

Je partage l'avis de tous ceux qui pensent qu'il n'y a aucun intérêt à utiliser des triggers dans ce devoir (c'est même abberant).

La seule utilisation que j'ai trouvé c'est d'affecter la clé unique avec la séquence dans le trigger.

Signé AR - Le vendeur de jus d'orange frelaté