Discussion :

[kitsune]

Bonjour,

Je débute en ce qui concerne les trigger sous Oracle et j'ai beau m'acharner mais celui que j'essaye de faire ne marche pas...

Je travaille sur 2 tables : Emp et Dept (Employés et Départements)

Dans la table Emp j'ai les employés avec leur métier (JOB) et leur numéro de département (DEPTNO).
Dans la table Dept j'ai les départements (DEPTNO) et le nombre de métier par département (NbMetierDep).

Pour mettre à jour la colonne NbMetierDep j'ai créer une fonction :

Code :

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

1
2
3
4
5
6
7
8
 
CREATE OR REPLACE FUNCTION Departement (dept NUMBER) RETURN NUMBER IS
metier NUMBER;
BEGIN
	SELECT COUNT(DISTINCT JOB) INTO metier FROM emp WHERE DEPTNO = dept;
	RETURN metier;
END;
/

Pour mon trigger voici ma 1ere version (qui ne marche pas) :

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
 
CREATE OR REPLACE TRIGGER majNbMetierDept
BEFORE INSERT OR DELETE OR UPDATE OF JOB ON Emp
	FOR EACH ROW
 
BEGIN
    CASE
	WHEN INSERTING THEN
		UPDATE dept SET NbMetierDep = Departement(:NEW.deptno)
		WHERE deptno = :NEW.deptno;
	WHEN DELETING THEN
		UPDATE dept SET NbMetierDep = Departement(:OLD.deptno)
		WHERE deptno = :OLD.deptno;
	WHEN UPDATING THEN
		UPDATE 	dept SET NbMetierDep = Departement(:NEW.deptno)
		WHERE deptno = :NEW.deptno;
		UPDATE dept SET NbMetierDep = Departement(:OLD.deptno)
		WHERE deptno = :OLD.deptno;
   END CASE;
END;
/

Et la 2 eme version qui ne marche pas plus...

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
 
CREATE OR REPLACE TRIGGER majNbMetierDept
BEFORE INSERT OR DELETE OR UPDATE OF JOB ON Emp
	FOR EACH ROW
DECLARE
 
CURSOR c1 IS SELECT * from dept;
	C1_enr NUMBER;
BEGIN
C1_enr := 0;
    CASE
	WHEN INSERTING THEN
		FOR c1_enr IN C1 LOOP
			UPDATE dept SET NbMetierDep = Departement(:NEW.deptno)
			WHERE deptno = :NEW.deptno;
		END LOOP;
	WHEN DELETING THEN
		FOR c1_enr IN C1 LOOP
			UPDATE dept SET NbMetierDep = Departement(:OLD.deptno)
			WHERE deptno = :OLD.deptno;
		END LOOP;
	WHEN UPDATING THEN
		FOR c1_enr IN C1 LOOP
			UPDATE 	dept SET NbMetierDep = Departement(:NEW.deptno)
			WHERE deptno = :NEW.deptno;
			UPDATE dept SET NbMetierDep = Departement(:OLD.deptno)
			WHERE deptno = :OLD.deptno;
		END LOOP;
   END CASE;
END;
/

En fait quand j'ajoute une ligne à Emp cela modifie bien mon nombre de metiers dans Dept mais quand je veux supprimer ou mettre à jour, il me dit que mon trigger ne vas pas voir le changement.

Si quelqu'un a la patience de s'y pencher, merci d'avance !

[/quote]

[LeoAnderson]

Si vous nous expliquiez plutôt et en français ce que vous souhaitez faire ? ;-) (le code, on s'en moque un peu, ce qui est important, c'est vos besoins !)

Après, si vous rencontrez des erreurs, le mieux est de nous donner le code et le libellé exact de l'erreur !

[Cereal123]

Bonjour,

Votre trigger est déclaré "BEFORE", ce qui veut dire effectivement que le calcul va être effectué avant que la ligne ne soit ajoutée/modifiée/supprimée. Il vaudrait mieux le déclarer "AFTER"...
C.

[CD]

Le problème vient en effet de l'utilisation de l'ancienne valeur dans le delete, ou de la nouvelle valeur sur l'update.

Dans ce cas, il possède 2 versions de la table, une avant le déclenchement du trigger, et une pendant le déclenchement du trigger lorsque l'on fait la mise à jour (update, delete ou insert).

Pour pallier cela, le plus simple serait d'utiliser un trigger AFTER comme indiqué précédemment. Il aura par contre l'inconvénient de faire parcourir toute la table Emp à chaque mise à jour pour mettre à jour dept :

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
42
43
44
45
46
47
48
49
50
51
52
53
54
  1  create or replace trigger majdelete
  2  after delete or insert or update of job on emp
  3  begin
  4  update dept set NbMetierDep = Departement(deptno);
  5* end;
SQL> /
 
DÚclencheur crÚÚ.
 
SQL> insert into emp values(0203, 'Toto', 'Forumiste', 7782, sysdate, 100, null,
 10);
 
1 ligne crÚÚe.
 
SQL> select * from dept;
 
    DEPTNO DNAME          LOC           NBMETIERDEP
---------- -------------- ------------- -----------
        10 ACCOUNTING     NEW YORK                4
        20 RESEARCH       DALLAS                  3
        30 SALES          CHICAGO                 3
        40 OPERATIONS     BOSTON                  0
 
SQL> delete from emp where empno=0203;
 
1 ligne supprimÚe.
 
SQL> select * from dept;
 
    DEPTNO DNAME          LOC           NBMETIERDEP
---------- -------------- ------------- -----------
        10 ACCOUNTING     NEW YORK                3
        20 RESEARCH       DALLAS                  3
        30 SALES          CHICAGO                 3
        40 OPERATIONS     BOSTON                  0
 
SQL> insert into emp values(0203, 'Toto', 'Forumiste', 7782, sysdate, 100, null,
 10);
 
1 ligne crÚÚe.
 
SQL> update emp set (job, deptno)=(select 'Forumeux', 20 from dual) where empno=
0203;
 
1 ligne mise Ó jour.
 
SQL> select * from dept;
 
    DEPTNO DNAME          LOC           NBMETIERDEP
---------- -------------- ------------- -----------
        10 ACCOUNTING     NEW YORK                3
        20 RESEARCH       DALLAS                  4
        30 SALES          CHICAGO                 3
        40 OPERATIONS     BOSTON                  0

[kitsune]

je n'ai pas acces à Oracle actuellement pour tester meme si ça me semble bien marcher là

C'est beaucoup plus simple que je ne le pensais...
En tout cas merci beaucoup pour votre aide !