Discussion :

[Almenor]

Bonjour,

J'ai les tables Commune, Quartier et Logement. Dans la table Quartier j'ai un champ nbrlogement.

A chaque fois que j'insère un nouveau logement j'aimerai incrémenter nbrlogement de 1 afin de savoir le nombre de logemements par Commune.

J'ai essayé de plusieurs façons sans succès. Voici le derniers truc que j'ai essayé :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
CREATE OR REPLACE TRIGGER Maj_nbrlogement AFTER INSERT ON Logement
FOR EACH ROW
BEGIN
   UPDATE  Commune 
   SET nbrlogement = nbrlogement+1 FROM Commune,Quartier,Logement 
   WHERE Commune.idlogment=Quartier.idcom AND Quartier.idquartier=Logement.idquart;
END ;
/

Merci d'avance pour votre aide

[seabs]

Bonjour,

A chaque fois que j'insère un nouveau logement j'aimerai incrémenter nbrlogement de 1 afin de savoir le nombre de logemements par Commune.

Me paraît compliquée ton approche. Dans le cas d'une suppression, tu vas gérer la réduction comment ?

Si j'avais à traiter ce problème, je ferai une VUE pour calculer le nombre de logement par commune. Tu peux utiliser COUNT ou SUM suivant la méthode de définition de ta table LOGEMENT

A titre d'exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT COUNT(*) AS NbLog FROM LOGEMENT WHERE QUARTIER_ID = :pQuartId

Bon tout cela est parfaire avec tes éléments, mais voilà l'idée.

Bon courage

[estofilo]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
CREATE OR REPLACE TRIGGER Maj_nbrlogement AFTER INSERT ON Logement
FOR EACH ROW
BEGIN
   UPDATE  Commune 
   SET nbrlogement = nbrlogement+1 FROM Commune,Quartier,Logement 
   WHERE Commune.idlogment=Quartier.idcom AND Quartier.idquartier=Logement.idquart;
END ;
/

Le SGBD est Oracle apparemment.

Le problème apparent est que le trigger n'utilise pas la variable-ligne NEW pour restreindre au logement concerné par l'INSERT. Tel que c'est écrit là, c'est complètement indépendant du logement inséré, donc ça ferait +1 pour tous les logements de la base, à chaque INSERT.

D'autre part la jointure Commune.idlogment=Quartier.idcom ressemble à une erreur d'après les noms des colonnes.

[CinePhil]

Le nombre de logement étant facilement calculable, il est inutile de le stocker. C'est une redondance indirecte de données qui peut entraîner des erreurs.

[Almenor]

Bonjour,

Merci pour vos réponses !

Je comprends tout à fait ce que vous voulez dire mais le prof demande ce trigger et je m'arrache le peu peu de cheveux qu'il me reste encore !!!

@estofilo : tout à fait, une erreur c'est glisée dans mes clé

il s'agit bien de Commune.idcommune=Quartier.idcom et non pas de Commune.idlogment=Quartier.idcom

J'ai beau essayer de toute les manière possible, oracle me renvoie à chaque fois une erreur !

[Almenor]

Bon, j'ai résolu le problème autrement, plutôt que d'incrémenter (ou décrémenter) à chaque insertion (ou suppression) je compte le nombre de logement dans la commune à chaque insertion (ou suppression). Ca risque d'être lourd lorsqu'il y aura beaucoup de logement mais je n'ai pas trouvé mieux :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
CREATE OR REPLACE TRIGGER Maj_nbrlogement AFTER INSERT OR DELETE ON Logement
BEGIN
   UPDATE  Commune C
   SET nbrlogement = (SELECT COUNT (idlogement) FROM Logement L,Quartier Q WHERE L.idquart=Q.idquartier AND Q.idcom=C.idcommune) ;
END ;
/

Petite question qui n'est pas en rapport mais ça m'évite d'ouvrir un nouveau topic :

lorsque l'on a une table de jointure avec juste deux foreign key, doit ont spécifier la primary key ? Avec ou sans ça tourne nickel chez moi !

Voici les deux méthode :

sans pk

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
CREATE TABLE Signataire 
  ( 
     idpersonne INTEGER NOT NULL, 
     numcontrat INTEGER NOT NULL, 
     CONSTRAINT fk_Sig_idpers_Pers FOREIGN KEY (idpersonne) REFERENCES Personne(id) ON DELETE CASCADE, 
     CONSTRAINT fk_Sig_numcont_Contrat FOREIGN KEY (numcontrat) REFERENCES Contrat(numero) ON DELETE CASCADE 
  );

avec pk

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
CREATE TABLE Signataire 
  ( 
     idpersonne INTEGER NOT NULL, 
     numcontrat INTEGER NOT NULL,
     CONSTRAINT pk_Signataire PRIMARY KEY (idpersonne,numcontrat),
     CONSTRAINT fk_Sig_idpers_Pers FOREIGN KEY (idpersonne) REFERENCES Personne(id) ON DELETE CASCADE, 
     CONSTRAINT fk_Sig_numcont_Contrat FOREIGN KEY (numcontrat) REFERENCES Contrat(numero) ON DELETE CASCADE 
  );

[CinePhil]

Toute table doit avoir une clé primaire. Dans le cas d'une table associative, c'est une clé primaire composée de clés étrangères.

[Almenor]

Merci !

Il me semblait bien en effet que toute table doit avoir une clé primaire, mais j'ai eu un doute du fait que le script fonctionnait correctement même sans

[CinePhil]

Ça fonctionne car rien n'oblige à déclarer la clé primaire à la création de la table, tout comme les clés étrangères.

[estofilo]

Bon, j'ai résolu le problème autrement, plutôt que d'incrémenter (ou décrémenter) à chaque insertion (ou suppression) je compte le nombre de logement dans la commune à chaque insertion (ou suppression).

Sauf que la requête montrée ne met pas à jour une commune mais TOUTES les communes à chaque fois. C'est le genre de chose qu'il ne faut pas faire dans un trigger.
Surtout qu'il n'y aurait pas de difficulté à filtrer sur :new.id_quart ou :old.id_quart pour se limiter l'update à la commune unique contenant le quartier unique contenant le logement inséré ou supprimé.

[Almenor]

J'ai une erreur, lors de l'insertion d'un logement, que je ne comprends pas si j'essaye de ne faire la Màj que pour les lignes concernées :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
CREATE OR REPLACE TRIGGER Maj_nbrlogement AFTER INSERT OR DELETE ON Logement
FOR EACH ROW
BEGIN
   UPDATE  Commune C
   SET nbrlogement = (SELECT COUNT (idlogement) FROM Logement L,Quartier Q WHERE :NEW.idquart=Q.idquartier AND Q.idcom=C.idcommune) ;
END ;
/

ORA-04091: la table TEST_ORACLE.LOGEMENT est en mutation ; le déclencheur ou la fonction ne peut la voir ORA-06512: à "TEST_ORACLE.MAJ_NBRLOGEMENT", ligne 2 ORA-04088: erreur lors d'exécution du déclencheur 'TEST_ORACLE.MAJ_NBRLOGEMENT'

[Scriuiw]

La méthode n'est pas bonne.

Comme l'a dit Estofilo, ton UPDATE met à jour tous les enregistrements de ta table ce qui n'est pas le comportement voulu (Valorisation d'une ligne et mise à NULL de toutes les autres).
De plus tu essaies de lire la table lié au déclencheur et Oracle ne peut pas te permettre cette opération.

Revois ton problème autrement en utilisant par exemple une vue ou alors gère toi-même la mise à jour du compteur

[estofilo]

Ce que dit l'erreur, c'est qu'il ne faut pas que le trigger utilise la table logement puisque cette table est en train d'être modifiée.

Mais de toute façon, cet UPDATE ferait (s'il passait) la mise à jour de toutes les communes avec la même valeur, ce qui serait faux.

Pour reprendre le pb dans sa forme initiale, la solution la plus simple et qui ne pose pas ce problème est du style:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
CREATE OR REPLACE TRIGGER Maj_nbrlogement AFTER INSERT ON Logement
FOR EACH ROW
BEGIN
   UPDATE  Commune 
   SET nbrlogement = nbrlogement+1
   WHERE idcommune=(SELECT idcom FROM Quartier WHERE idquartier=:NEW.idquart);
END ;
 
 
CREATE OR REPLACE TRIGGER Maj_nbrlogement AFTER DELETE ON Logement
FOR EACH ROW
BEGIN
   UPDATE  Commune
   SET nbrlogement = nbrlogement-1
   WHERE idcommune=(SELECT idcom FROM Quartier WHERE idquartier=:OLD.idquart);
END ;

[Scriuiw]

Mais de toute façon, cet UPDATE ferait (s'il passait) la mise à jour de toutes les communes avec la même valeur, ce qui serait faux.

Je dirais plutôt que seul l'enregistrement répondant aux critères de sélection (WHERE :NEW.idquart=Q.idquartier AND Q.idcom=C.idcommune) sera mis à jour avec une valeur tandis que les autres égaleront NULL

Sinon oui ta solution semble la bonne mais n'empêche que les triggers.... le jour ou t'as 1 million de lignes à insérer/mettre à jour/supprimer ...

[Almenor]

EDIT * apprendre a vérifier si il y a des réponses avant de poster *

Merci

J'était bien parti sur une incrémentation au départ, mais je n'arrivait à faire fonctionner ce trigger car je ne voyait pas comment faire ce passage :

WHERE idcommune=(SELECT idcom FROM Quartier WHERE idquartier=:NEW.idquart);

[estofilo]

Je dirais plutôt que seul l'enregistrement répondant aux critères de sélection (WHERE :NEW.idquart=Q.idquartier AND Q.idcom=C.idcommune) sera mis à jour avec une valeur tandis que les autres égaleront NULL

Effectivement la référence à C.idcommune fait que c'est un update corrélé, donc pas mono-valeur contrairement à ce que je disais. Je pense que ça mettrait 0 plutôt que null à cause du COUNT() pour les communes hors critère mais dans les deux cas ça reste faux par rapport au besoin.

Sinon le principe du compteur mis à jour en trigger est discutable mais le contexte ici est que c'est une demande d'un prof.

[pachot]

Bonjour,

Sinon le principe du compteur mis à jour en trigger est discutable mais le contexte ici est que c'est une demande d'un prof.

Est-ce que tu as la possibilité de dire au prof que c'est de toute façon une très mauvaise idée de faire celà avec des triggers

Que se passe-t-il si un autre utilisateur, en même temps est en train de modifier la table Quartier, en affectant par exemple le quartier à une autre commune ?

Que se passe-t-il si 2 utilisateurs veulent saisir en même temps un logement, chacun étant dans la même commune ?

Si tu veux faire ça par trigger il faudrait avoir des triggers sur toutes les tables, pour gérer tous les cas de insert/delete/update, et il faudrait vérouiller toutes les tables concernées (ce qui serait une catastrophe en multi-utilisateur).

Il n'y a qu'une vue qui peut donner une valeur cohérente pour nbrlogement.

Si ton prof te donne sa solution, tu peux la poster ici et je te montrerais comment rendre la base complètement inconsistente en 2 ou 3 requêtes

Cordialement,
Franck.

[Almenor]

Ce que dit l'erreur, c'est qu'il ne faut pas que le trigger utilise la table logement puisque cette table est en train d'être modifiée.

Mais de toute façon, cet UPDATE ferait (s'il passait) la mise à jour de toutes les communes avec la même valeur, ce qui serait faux.

Pour reprendre le pb dans sa forme initiale, la solution la plus simple et qui ne pose pas ce problème est du style:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
CREATE OR REPLACE TRIGGER Maj_nbrlogement AFTER INSERT ON Logement
FOR EACH ROW
BEGIN
   UPDATE  Commune 
   SET nbrlogement = nbrlogement+1
   WHERE idcommune=(SELECT idcom FROM Quartier WHERE idquartier=:NEW.idquart);
END ;
 
 
CREATE OR REPLACE TRIGGER Maj_nbrlogement AFTER DELETE ON Logement
FOR EACH ROW
BEGIN
   UPDATE  Commune
   SET nbrlogement = nbrlogement-1
   WHERE idcommune=(SELECT idcom FROM Quartier WHERE idquartier=:OLD.idquart);
END ;

Bon ben j'ai essayé ça ne fonctionne pas

Aucune erreur, tout roule mais les champs nbrlogement reste à zero

[Waldar]

Attention les deux triggers ici ont le même nom, le second va donc écraser le premier.

[mnitu]

Vous avez un example ici: On Rollups, Merges, and Moves