Discussion :

[Thymii]

Bonjour à tous,

Voila plusieurs jours que je travaille sur une question de Travaux pratiques de SQL sans trouver la réponse à ma question et ce malgré de très très nombreux tests. Je deviens totalement fou, je suis persuadé de passer non loin de la solution mais je rencontre toujours la même erreur.

Voila rapidement la chose :
J'ai ces trois tables-là

Employee(EmpNo, Name, Job, Mgr, Hiredate, Sal , Comm, DeptNo);
SalaryGrade(Grade, LoSal, HiSal);
Department(DeptNo, DName, Loc, NBEmployé, NBMétier);

La question est la suivante : Créer un déclencheur qui, pour chaque mise à jour (insertion ou suppression ou modification) de l’affectation d’un employé, incrémente ou décrémente le nombre d’employés par département.

J'utilise SQL Developer de Oracle

Je n'ai volontairement pas précisé la structure de création de mes tables car je pense pas que le problème vienne de la et surtout cela va surcharger mon message et démotiver les éventuelles réponses .

Pour ce faire, voila la solution que j'ai apportée :

1er ) je crée une fonction qui prend en paramètre DeptNo et qui me retourne le nombre d'employés pour ce dernier

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
CREATE OR REPLACE FUNCTION fonction4 (NoDépt IN Employee.DeptNo%TYPE) RETURN NUMBER IS
CURSOR C1 IS SELECT *
FROM Employee ;
Nb NUMBER(3):=0;
BEGIN
   FOR C1_enr IN C1 LOOP
       IF C1_enr.DeptNo = NoDépt THEN
          Nb := Nb + 1 ;
       END IF;
   END LOOP ;
RETURN Nb;
END fonction4 ;

2) ensuite je crée une procédure faisant appel à la fonction ci-dessus, procédure qui me sera utile dans le trigger

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
CREATE OR REPLACE PROCEDURE NBEmploye AS
CURSOR c IS  
SELECT * 
FROM Department;
Nb NUMBER;
BEGIN
    FOR Cur IN c LOOP 
        Nb := fonction4(Cur.DeptNo);
        UPDATE Department
        SET NBEmployé = Nb  
        WHERE DeptNo = Cur.DeptNo;
    END LOOP;
END;

Puis je crée le trigger se déclenchant lors d'une mise à jour sur la table employé, ce qui va lancer la procédure qui elle-même appelle la fonction qui va s'occuper de mettre à jour le champ NBEmployé.

Code :

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

1
2
3
4
5
6
7
 
CREATE OR REPLACE TRIGGER Incremente
AFTER INSERT OR DELETE OR UPDATE OF EmpNo ON Employee
FOR EACH ROW
BEGIN
    NBEmploye() ;
END ;

Tout se passe bien mais quand je fais un test du style
DELETE FROM Employee WHERE EmpNo = 'un numéro d'employé de ma base', eh bien en sortie j'ai une jolie erreur me disant "La table EMPLOYEE est en mutation ; le déclencheur ou la fonction ne peut la voir"

Je comprends cette erreur mais je n'ai pas réussi à la contourner. A savoir que j'ai cherché pleiiiiins de méthodes, et que je vous donne celle qui me semble être la plus propre et surtout la plus juste. Celle que je rendrais si je ne trouve pas de solution à mon problème.

Avez-vous des suggestions ? Je suis preneur, merci d'avance !
Bonne journée.

Edit : En espérant que cette reformulation sera bonne, je vous souhaite une bonne journée.

[SQLpro]

Commencez par respecter la charte de postage. Impossible de vous aider sans cela !

A lire : http://www.developpez.net/forums/a69...gage-sql-lire/

A +

[Thymii]

Ok, modifications effectuées, il me semble remplir plus ou moins les 7 critères demandés.

En espérant que vous m'aiderez à trouver, je vous souhaite à toutes et à tous une excellente journée.

[skuatamad]

Ne pas faire de curseur, inutile et cause de la table en mutation, directement gérer le +1 -1 dans l'update.
Regarde Vérifier le nombre de places disponibles avant insertion

Mais dans un contexte hors scolaire la réponse serait : ne pas faire de trigger mais juste une requête, et si besoin créer une vue...

[Thymii]

Bonjour et merci de ta réponse,
si je saisi bien tu me parles du curseur de la procédure que je dois enlever, c'est bien ça ? Le curseur de la fonction "fonction4" je le laisse ?

[skuatamad]

Non aucun curseur, d'une manière plus générale pas de requête sur la table employee faisant l'objet du trigger
Regarde également les explications sur le tuto Table en mutation

[Thymii]

Oulala je viens de lire les deux liens que tu m'as conseillé de lire et je t'avoue que j'ai beaucoup de mal à saisir comment adapter les différentes solutions à mon problème et comment modifier mon code.
Je sais pas si c'est pas un peu trop "osé" de te demander ça mais est-il possible que tu me dise précisément que faire ou m'écrire une ébauche de code sur lequel je pourrais me baser pour tenter quelques choses ?

Tout ça m'a l'air bien compliqué pour mon niveau PL/SQL

[mnitu]

La partie sur lequel il faut se focaliser est

Code :

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

1
2
3
4
5
6
7
8
9
10
 
CREATE TRIGGER trg_inscription
before INSERT ON s_dating_eleve_session
FOR each row
begin
    UPDATE s_dating_session
       SET encour_inscrit = encour_inscrit + 1
     WHERE id_session = :new.id_session;
end;
/

Votre trigger doit être crée sur la table employee (s_dating_eleve_session). Dans votre cas la colonne à mettre à jour est NBEmployé (encour_inscrit) dans la table Department (s_dating_session). La clé de cette table est DeptNo (id_session).
Maintenant c’est plus clair ?

[Thymii]

Oulaaaa mais je me suis compliqué pour rien !!

Code :

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

1
2
3
4
5
6
7
8
9
 
CREATE TRIGGER test
before INSERT OR DELETE OR UPDATE ON employee
FOR each row
begin
    UPDATE department
       SET NBEmployé = NBEmployé + 1
     WHERE DeptNo = :new.DeptNo;
end;

Avec ça ça marche apparement pour un Insert, c'est à dire que mon champ NBEmployé s'incrémente bien de un pour le département concerné. Par contre si je fais ça

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
 
CREATE TRIGGER test
before INSERT OR DELETE OR UPDATE ON employee
FOR each row
begin
   IF INSERTING THEN
       UPDATE department
       SET NBEmployé = NBEmployé + 1
       WHERE DeptNo = :new.DeptNo;
   ELSIF DELETING THEN
       UPDATE department
       SET NBEmployé = NBEmployé - 1
       WHERE DeptNo = :new.DeptNo;
   END IF;
end;

l'incrémentation lors d'une insertion marche par contre la décrémentation lors du delete ne marche jamais, pourquoi ? :/ !

[amir.]

Salut,

J'étais en train de coder la solution à ton problème et tu as trouvé tout seul

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
18
19
20
21
22
23
24
25
26
27
 
CREATE OR REPLACE TRIGGER upd_cnt_in_dpt
 AFTER INSERT OR UPDATE OR DELETE ON employee
FOR EACH ROW
BEGIN
  IF INSERTING THEN
    -- nouvelle personne, j'ajoute 1
    UPDATE department d
       SET d.NBEmployé = COALESCE(d.NBEmployé, 0) + 1
     WHERE d.DeptNo = :new.DeptNo;
  ELSIF UPDATING THEN 
    -- update d'une personne existance, j'ajoute 1 dans le nouveau dpt
    UPDATE department d
       SET d.NBEmployé = COALESCE(d.NBEmployé, 0) + 1
     WHERE d.DeptNo = :new.DeptNo;
    -- update d'une personne existance, je supprime 1 dans l'ancien dpt
    UPDATE department d
       SET d.NBEmployé = d.NBEmployé - 1
     WHERE d.DeptNo = :old.DeptNo;
  ELSIF DELETING THEN
    -- suppression d'une personne existance, je supprime 1 dans l'ancien dpt
    UPDATE department d
       SET d.NBEmployé = d.NBEmployé - 1
     WHERE d.DeptNo = :old.DeptNo;
  END IF;
END;
/

Le problème de ton trigger c'est que lorsque tu supprimes, la variable :new.deptno n'est pas définie. Dans un delete tu dois utiliser OLD.

Pour ceux qui veulent tester, voici le code de création des tables.

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
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
 
CREATE TABLE Employee
(
  EmpNo NUMBER(15), 
  name VARCHAR2(100), 
  job VARCHAR2(100), 
  mgr NUMBER(15), 
  hiredate DATE, 
  Sal NUMBER(15, 5), 
  comm VARCHAR2(100),  --?
  DeptNo NUMBER(15)
);
 
 
CREATE TABLE SalaryGrade
(
  Grade VARCHAR2(100), 
  LoSal NUMBER(15, 5), 
  HiSal NUMBER(15, 5)
);
 
CREATE TABLE Department
(
  DeptNo NUMBER(15), 
  DName VARCHAR2(100), 
  Loc VARCHAR2(100), 
  NBEmployé NUMBER(15), -- mauvaise idée d'utiliser des accents.
  NBMétier NUMBER(15)
);
 
 INSERT INTO department(deptno, dname, loc) 
  SELECT TRUNC(dbms_random.value(1, 10000)), 
         dbms_random.string('a', dbms_random.value(1, 100)), 
         dbms_random.string('a', dbms_random.value(1, 100))
    FROM dual
  CONNECT BY LEVEL <= 10;
 
UPDATE department 
   SET NBEmployé = 0, NBMétier = 0;
 
COMMIT;

[Thymii]

Ah oui quel imbécile, ça fait 3 jours que je manipule des OLD et des NEW et je trouve encore le moyens de me tromper ^^ !
Merci beaucoup de ton aide en tout cas, je ne sais pas pourquoi je suis parti aussi loin dans mon code. En plus j'avais codé une solution un peu similaire à celle ci mais j'ai tellement changé que je ne sais même plus comment j'en suis arrivé à une solution aussi compliqué.

Merci beaucoup pour votre/ton aide .

[amir.]

Avec plaisir Thymii

Tu sais, je voulais aussi te dire que:
1- C'est une mauvaise idée de mettre des accents dans le noms de ses attributs/tables/index/vues/... Moi à ta place, j'utiliserai juste des caractères alphabétique sans accents, diacritiques... [A-Z] et des chiffres [0-9]. Le reste, crois moi, c'est source de problèmes.
2- Ton trigger à toi est un BEFORE trigger alors que dans ma solution j'utilise un AFTER trigger. Au niveau du résultat, c'est clair que dans ce cas-ci ca ne pose aucun souci. Mais, en pratique, on utilise un BEFORE TRIGGER souvent pour faire des validations sur les données, voire pour les modifier. Dans un BEFORE TRIGGER , tu peux modifier ton ":new". Alors que dans un AFTER trigger, tu ne peux pas. C'est pour ca, que dans le cas de traitements à opérer "après", on utilise un AFTER trigger. Je ne sais pas si je suis clair mais prenons un petit exemple. Si, tu dois tronquer un chiffre introduit par l'utilisateur à la dizaine la plus proche, tu créeras un trigger BEFORE UPDATE et tu mettra dedans un code comme :new.value = TRUNC(:new.value, -1). Par contre, si tu dois faire une moyenne, une somme des valeurs introduite par l'utilisateur, tu créeras un AFTER TRIGGER.

Allez, maintenant que tu as compris ton erreur, je suis sûr que tu ne la referas pas

Bonne soirée à toi,