[Oracle 9i] Déclencheur sur table objet

Version imprimable

26/01/2006, 12h10
Process Linux

[Oracle 9i] Déclencheur sur table objet

bonjours,
voilà , j'ai deux tables objet employé ( Employe_o ) et département (Dept_o) du type employe_t et dept_t.

Dans le type dep_t , j'ai comme champs une collection de référence d'employé ( nested table ).

maintenant , je veux créer un déclencheur sur la table employ_o , pour inserer la nouveau employé dans cette collection ( pour mettre à jour les informations du département). Mais je n'arrive pas à le créer , est ce qu'il y a qq un qui peut m'envoyer la syntaxe de création de ce déclencheur.

merci

La description du probleme etant assez dense, je ne suis pas sur de repondre a la question a 100% ...

Script

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
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
 
SET AUTOTRACE OFF
PROMPT ===================================================================
PROMPT Clean-up
PROMPT ===================================================================
DROP TABLE dept
/
DROP TYPE dept_type
/
DROP TABLE emp
/
DROP TYPE list_emp_ref_type 
/
DROP TYPE emp_type 
/
PROMPT ===================================================================
PROMPT Type and Tables DDL
PROMPT ===================================================================
CREATE TYPE emp_type          AS OBJECT
(empno        INTEGER
,ename        VARCHAR2(30))
/
CREATE TYPE list_emp_ref_type AS TABLE OF REF emp_type
/ 
CREATE TABLE emp OF emp_type
/
CREATE TYPE dept_type         AS OBJECT
(deptno       INTEGER
,list_emp_ref list_emp_ref_type)
/
CREATE TABLE dept OF dept_type
NESTED TABLE list_emp_ref STORE AS dept_emp
/
PROMPT ===================================================================
PROMPT Package 
PROMPT ===================================================================
CREATE OR REPLACE PACKAGE emp_pkg AS
g_empno                INTEGER;
END;
/
PROMPT ===================================================================
PROMPT Trigger (Row)
PROMPT ===================================================================
CREATE OR REPLACE TRIGGER emp_iur1
AFTER INSERT ON emp
FOR EACH ROW
BEGIN                                                                                                                           
emp_pkg.g_empno  := :NEW.empno;
END;
/
PROMPT ===================================================================
PROMPT Trigger (Table)
PROMPT ===================================================================
CREATE OR REPLACE TRIGGER emp_iu1
AFTER INSERT ON emp
REFERENCING NEW AS new_emp
BEGIN                                                                                                                           
FOR i IN (SELECT *
          FROM   dept) LOOP
 
  INSERT INTO TABLE(SELECT list_emp_ref 
                    FROM   dept
                    WHERE  deptno = i.deptno)
  SELECT REF(p)
  FROM   emp   p
  WHERE  p.empno = emp_pkg.g_empno;
 
END LOOP;
END;
/
PROMPT ===================================================================
PROMPT Insert Testcase (3 dept, 2 empno)
PROMPT ===================================================================
INSERT INTO dept (deptno,list_emp_ref) VALUES (10,list_emp_ref_type());
INSERT INTO dept (deptno,list_emp_ref) VALUES (20,list_emp_ref_type());
INSERT INTO dept (deptno,list_emp_ref) VALUES (30,list_emp_ref_type());
INSERT INTO emp  (empno,ename) VALUES (1000,'Scott');
INSERT INTO emp  (empno,ename) VALUES (2000,'Allen');
PROMPT ===================================================================
PROMPT Verification (employees references par le departement 10)
PROMPT ===================================================================
SELECT DEREF(COLUMN_VALUE).ename
FROM   THE(SELECT list_emp_ref 
           FROM   dept
           WHERE  deptno = 10)
;

Resultat:

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
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
 
===================================================================
Clean-up
===================================================================
 
Table dropped.
 
 
Type dropped.
 
 
Table dropped.
 
 
Type dropped.
 
 
Type dropped.
 
===================================================================
Type and Tables DDL
===================================================================
 
Type created.
 
 
Type created.
 
 
Table created.
 
 
Type created.
 
 
Table created.
 
===================================================================
Package
===================================================================
 
Package created.
 
===================================================================
Trigger (Row)
===================================================================
 
Trigger created.
 
===================================================================
Trigger (Table)
===================================================================
 
Trigger created.
 
===================================================================
Insert Testcase (3 dept, 2 empno)
===================================================================
 
1 row created.
 
 
1 row created.
 
 
1 row created.
 
 
1 row created.
 
 
1 row created.
 
===================================================================
Verification (employees references par le departement 10)
===================================================================
 
DEREF(COLUMN_VALUE).ENAME
------------------------------
Scott
Allen

Teste sur Oracle Database 10g Enterprise Edition Release 10.1.0.4.0

27/01/2006, 07h27
SheikYerbouti

c'est nickel :ccool:

bravo , :bravo: , c'est une solution qui marche trés bien. J'aimerai bien creuser une solution que j'ai commencé , je ne sais pas si ca peut marcher ou pas voici le corps du déclencheur que j'ai fais

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
CREATE OR REPLACE TRIGGER TRIG_EMPL
 AFTER 
 INSERT
 ON EMPLOYE_O
 REFERENCING NEW AS NEW_EMP
 FOR EACH ROW 
begin
insert into TABLE
( 
select d.listemployes from dept_o d
where d.deptno = deref(:NEW_EMP.refDept).deptno
) l
values (null);
 
end;
/

quand je met null dans values , ca marche , mais normalement je dois mettre le REF du nouveau employé inseré , qui lui connait la référence de son département voici la desc de mes types

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
 
/* ====================================
   === Création des types Forward  ====
   ====================================
*/
CREATE OR REPLACE TYPE dept_t
/
CREATE OR REPLACE type employe_t
/
 
/* ===================================
   === Création des types      =======
   ===================================
   */
CREATE OR REPLACE 
type listemployes_t as table of REF employe_t
/
 
CREATE OR REPLACE 
TYPE dept_t as object (
DEPTNO number(4) ,
DNAME varchar2(30) ,
LOC varchar2(30) ,
listEmployes listEmployes_t  
)
/
 
 
CREATE OR REPLACE 
type employe_t as object (
EMPNO NUMBER(4),
ENAME varchar2(15),
PRENOMS tabPrenoms_t,
JOB Varchar2(20),
SAL number(7,2),
CV CLOB,
DATE_NAISS date,
DATE_EMB date,
refDept REF dept_t
)
/

La reference de l'object en cours d'insertion n'est pas possible depuis l'interieur du trigger. C'est le bug494604. Il faut passer par une operation SQL pour determiner cette REFerence, chose qui n'est pas possible a moins de declencher une erreur mutating, d'ou le contournement en utilisant une batterie de triggers et un package qui tient lieu de cache temporaire.

Si on a maintenant une recursivite DEPT <-> EMP dans la gestion des references, ca peut donner ceci:

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
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
 
PROMPT ===================================================================
PROMPT Clean-up
PROMPT ===================================================================
DROP TABLE dept
/
DROP TYPE dept_type FORCE
/
DROP TABLE emp
/
DROP TYPE list_emp_ref_type 
/
DROP TYPE emp_type 
/
PROMPT ===================================================================
PROMPT Type and Tables DDL
PROMPT ===================================================================
CREATE TYPE emp_type          AS OBJECT
(empno        INTEGER
,ename        VARCHAR2(30))
/
CREATE TYPE list_emp_ref_type AS TABLE OF REF emp_type
/ 
CREATE TABLE emp OF emp_type
/
CREATE TYPE dept_type         AS OBJECT
(deptno       INTEGER
,list_emp_ref list_emp_ref_type)
/
ALTER TYPE emp_type
ADD ATTRIBUTE
(dept_ref     REF dept_type) CASCADE
/
CREATE TABLE dept OF dept_type
NESTED TABLE list_emp_ref STORE AS dept_emp
/
PROMPT ===================================================================
PROMPT Package 
PROMPT ===================================================================
CREATE OR REPLACE PACKAGE emp_pkg AS
TYPE t_array_no        IS TABLE OF INTEGER       INDEX BY BINARY_INTEGER;
TYPE t_array_ref       IS TABLE OF REF dept_type INDEX BY BINARY_INTEGER;
g_empno_null           t_array_no;        
g_empno                t_array_no;        
g_dept_ref_null        t_array_ref;
g_dept_ref             t_array_ref;
END;
/
 
PROMPT ===================================================================
PROMPT Trigger (BI)
PROMPT ===================================================================
CREATE OR REPLACE TRIGGER emp_bi1
BEFORE INSERT ON emp
FOR EACH ROW
BEGIN                                                                                                                           
emp_pkg.g_empno    := emp_pkg.g_empno_null;
emp_pkg.g_dept_ref := emp_pkg.g_dept_ref_null;
END;
/
PROMPT ===================================================================
PROMPT Trigger (ARI)
PROMPT ===================================================================
CREATE OR REPLACE TRIGGER emp_ari1
AFTER INSERT ON emp
FOR EACH ROW
BEGIN                                                                                                                           
emp_pkg.g_empno   (emp_pkg.g_empno.COUNT+1)     := :NEW.empno;
emp_pkg.g_dept_ref(emp_pkg.g_dept_ref.COUNT+1)  := :NEW.dept_ref;
END;
/
PROMPT ===================================================================
PROMPT Trigger (Table)
PROMPT ===================================================================
CREATE OR REPLACE TRIGGER emp_ai1
AFTER INSERT ON emp
BEGIN                                                                                                                           
FOR i IN 1..emp_pkg.g_empno.COUNT LOOP
  INSERT INTO TABLE(SELECT list_emp_ref 
                    FROM   dept
                    WHERE  deptno = DEREF(emp_pkg.g_dept_ref(i)).deptno)
  SELECT REF(p)
  FROM   emp   p
  WHERE  p.empno = emp_pkg.g_empno(i);
END LOOP;  
END;
/
PROMPT ===================================================================
PROMPT Insert Testcase (3 dept, 2 empno)
PROMPT ===================================================================
SET SERVEROUTPUT ON SIZE 1000000
INSERT INTO dept (deptno,list_emp_ref) VALUES (10,list_emp_ref_type());
INSERT INTO dept (deptno,list_emp_ref) VALUES (20,list_emp_ref_type());
INSERT INTO dept (deptno,list_emp_ref) VALUES (30,list_emp_ref_type());
INSERT INTO emp  (empno,ename,dept_ref) SELECT 1000,'Scott',REF(d)
                                        FROM   dept d
                                        WHERE  d.deptno = 10;
INSERT INTO emp  (empno,ename,dept_ref) SELECT 2000,'Allen',REF(d)
                                        FROM   dept d
                                        WHERE  d.deptno = 20;
PROMPT ===================================================================
PROMPT Verification (employees references par le departement 10) (Scott)
PROMPT ===================================================================
SELECT DEREF(COLUMN_VALUE).ename
FROM   THE(SELECT list_emp_ref 
           FROM   dept
           WHERE  deptno = 10)
;
PROMPT ===================================================================
PROMPT Verification (employees references par le departement 20) (Allen)
PROMPT ===================================================================
SELECT DEREF(COLUMN_VALUE).ename
FROM   THE(SELECT list_emp_ref 
           FROM   dept
           WHERE  deptno = 20)
;

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
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
 
===================================================================
Clean-up
===================================================================
 
Table dropped.
 
 
Type dropped.
 
 
Table dropped.
 
 
Type dropped.
 
 
Type dropped.
 
===================================================================
Type and Tables DDL
===================================================================
 
Type created.
 
 
Type created.
 
 
Table created.
 
 
Type created.
 
 
Type altered.
 
 
Table created.
 
===================================================================
Package
===================================================================
 
Package created.
 
===================================================================
Trigger (BI)
===================================================================
 
Trigger created.
 
===================================================================
Trigger (ARI)
===================================================================
 
Trigger created.
 
===================================================================
Trigger (Table)
===================================================================
 
Trigger created.
 
===================================================================
Insert Testcase (3 dept, 2 empno)
===================================================================
 
1 row created.
 
1 row created.
 
1 row created.
 
1 row created.
 
1 row created.
 
===================================================================
Verification (employees references par le departement 10) (Scott)
===================================================================
 
DEREF(COLUMN_VALUE).ENAME
------------------------------
Scott
 
===================================================================
Verification (employees references par le departement 20) (Allen)
===================================================================
 
DEREF(COLUMN_VALUE).ENAME
------------------------------
Allen
SQL>

28/01/2006, 09h53
Process Linux

merci beaucoup plabrevo, votre réponse est trés claire, et répond exactement à ma question 10000000000 merci :D developpez.com