Bonjour tout le monde,

J'aurais besoin d'aide avec ma base de donnée. Je travaille avec Oracle SQL Developer et je rencontre un problème avec deux de mes tables.

Il s'agit d'une base de donnée pour un zoo. J'ai une table "ESPECE" et une autre "INDIVIDU" qui renferme les informations des individus d'une espèce.

Je dois créer un TRIGGER qui met à jour le nombre d'individu dans la table ESPECE lorsque j'ajoute un individu dans la table INDIVIDU. Pour ce qui est d'ajouter, ça va, je n'ai pas mis le script du trigger, mais par contre quand il s'agit d'enlever un individu, le script fonctionne aussi, mais cause un problème lorsque je veux supprimer une espèce de la table ESPECE. En effet, ça me met le message suivant:

"ORA-04091: la table INDIVIDU est en mutation ; le déclencheur ou la fonction ne peut la voir"

CREATE TABLE ESPECE (
CodeEspece CHAR(12) PRIMARY KEY,
NomEspece VARCHAR2(50) NOT NULL UNIQUE,
Nombre NUMBER DEFAULT 0,
CodeZone CHAR(12),
CodeLotissement CHAR(12),
CONSTRAINT Nb CHECK(Nombre>=0),
CONSTRAINT FK_CoZ_CL FOREIGN KEY (CodeZone,CodeLotissement)
REFERENCES LOTISSEMENT(CodeZone,CodeLotissement)
ON DELETE SET NULL
);

CREATE TABLE INDIVIDU (
CodeIndividu CHAR(12) PRIMARY KEY,
NomIndividu VARCHAR2(50) NOT NULL,
CodeEspece CHAR(12),
Sang VARCHAR2(10) NOT NULL,
DateNaissance DATE,
DateDeces DATE,
Pere CHAR(12),
Mere CHAR(12),
CONSTRAINT FK_ES FOREIGN KEY (CodeEspece)
REFERENCES ESPECE(CodeEspece)
ON DELETE CASCADE,
CONSTRAINT FK_P FOREIGN KEY (Pere)
REFERENCES INDIVIDU(CodeIndividu),
CONSTRAINT FK_M FOREIGN KEY (Mere)
REFERENCES INDIVIDU(CodeIndividu)
);

create or replace TRIGGER Nb_Individu_Moins
after DELETE ON INDIVIDU
FOR EACH ROW
Begin
Update ESPECE
SET NOMBRE = NOMBRE - 1
WHERE CodeEspece = :Old.CodeEspece;
End;

/*Exemple d'insertion*/

INSERT INTO ESPECE (CodeEspece,NomEspece,Nombre,CodeZone,CodeLotissement)
VALUES (3739,'Laxodonta',0,123456,123456);

INSERT INTO INDIVIDU (CodeIndividu,NomIndividu,CodeEspece,Sang,DateNaissance)
VALUES (1000001,'Babar',3739,'O','01-10-15');

DELETE FROM ESPECE
WHERE CodeEspece=3739;

Je sais qu'en fait, vu qu'il y a ON DELETE CASCADE pour la Clé étrangère d'INDIVIDU, il veut supprimer les individus convernés, mais en même cela déclenche le TRIGGER étant donné qu'on essaye de supprimer les individu. C'est un cercle vicieux. Du moins c'est mon interprétation, dites-moi si je me trompe.

Est-ce que quelqu'un saurait comment contourner le problème?

Merci d'avance

Bonjour,

Tu as bien un conflit entre ta contrainte de clé étrangère ON DELETE CASCADE sur la table ESPECE et ton TRIGGER AFTER DELETE sur ta table INDIVIDU ... Et il n'y a pas moyen de contourner cela ...

Personnellement je retirerai la contrainte ON DELETE CASCADE pour empêcher la suppression d'une espèce tant qu'elle a des individus ...

Si tu y réfléchis bien elle n'a pas de raison d'être logiquement ... Cela paraît "fort" de supprimer une espèce qui a des individus non ?

Si tu as créé une espèce par erreur et que tu as ajouté également par erreur des individus dedans (c'est déjà une situation bien tordue), alors il te faudra gérer d'abord le transfert des individus de cette espèce vers une autre puis supprimer l'espèce erronée ...

Bonjour,

Désolé pour cette réponse tardive. Merci Benzouye pour ton aide! Il faudrait quand même que je puisse enlever les individus d'une espèce quand je supprime une espèce, parce qu'imagine qu'on doive transférer tous les individus d'une même espèce dans un autre zoo, ou bien qu'une maladie étrange ne frappe que cette espèce en particulier (je regarde trop de film je pense...), bref, il faudrait que je sois capable de le faire. 

Je n'ai pas réglé mon problème encore, mais si jamais je trouve, je vous le ferai savoir!

LudovicMalet a écrit:

imagine qu'on doive transférer tous les individus d'une même espèce dans un autre zoo

Dans ce cas, tu ne supprimes pas l'espèce, mais seulement les individus ... Tu pourras avoir de nouveau des individus de cette espèce, donc de nouveau avoir besoin de l'espèce ...

LudovicMalet a écrit:

une maladie étrange ne frappe que cette espèce en particulier

Même remarque ... Tu supprimes les individus, pas l'espèce ...

Rien n'est gênant dans le fait de conserver une espèce en base si elle n'a pas d'individu ...

Dans tous les cas, la suppression d'une espèce passera au préalable par la suppression de tous ces individus ...

Imaginons que tu veuilles vraiment supprimer une espèce et tous ses individus, une façon un peu "brutale" mais simple serait :

DELETE FROM individu
WHERE id_espece = 1;

DELETE FROM espece
WHERE id_espece = 1;