Trigger sql "complexe"

Version imprimable

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:

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:

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:

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.

14/11/2012, 13h21
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 +
14/11/2012, 13h51
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.
14/11/2012, 13h54
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...
14/11/2012, 15h30
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 ?
14/11/2012, 15h39
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
14/11/2012, 16h02
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 :oops:

La partie sur lequel il faut se focaliser est
Code:

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 ?

Oulaaaa mais je me suis compliqué pour rien !!
Code:

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:

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 ? :/ !

Salut,

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

Code:

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:

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;

14/11/2012, 19h16
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 :).
14/11/2012, 20h32
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,