Une table d'historisation en utilisant un trigger et un curseur

bonjour à tous

je dispose d'une table que je souhaite historiser dans une autre table

supposant qu'on a table 1 avec les colonnes suivantes :

1- Id
2-Date_heure
3-Utilisateur

avec date_heure est la date heure system et utilisateur est l'utilisateur ayant effectué une modification sur la table1

je souhaite utiliser un curseur qui stocke l'enregistrement en le renvoie à une table d'historisation.

ma question alors est comment on declare un curseur dans un trigger qui se declenche pour chaque ligne ????

deuxieme question :

apres le transfert des données on souhaite mettre à jour les 2 colonnes date_heure et Utilisateur dans la table1 sachant qu'une operation de mise à jour est interdite dans un trigger dependant de cette table ???

merci :)

Salut,
Je ne pense pas qu'il y ait besoin d'utiliser un curseur, tout doit pouvoir se faire depuis le trigger...

Voici en passant un petit exemple d'audit que nous avons sur une de nos Table.

Le principe est que non seulement nous voulons savoir si une table a été modifiée, mais de quelle façon (INSERT, UPDATE, DELETE) et aussi garder les infos qui ont été modifiées en gardant les infos "Avant" et "Après" à l'aide des attribut :Old et :New du trigger.

la Table source des changement :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE TABLE HRM_PERSON_JOB
(
  HRM_PERSON_JOB_ID  NUMBER(12)             NOT NULL,
  HRM_JOB_ID         NUMBER(12)             NOT NULL,
  HRM_IN_OUT_ID      NUMBER(12)             NOT NULL,
  HRM_PERSON_ID      NUMBER(12),
  PEJ_FROM           DATE                   NOT NULL,
  PEJ_TO             DATE,
  PEJ_AFFECT_RATE    NUMBER(16,5)           NOT NULL,
  PEJ_MAIN_JOB       NUMBER(1),
  TAUX_ACTUEL        VARCHAR2(100 BYTE),
  TAUX_LIEU_TRAVAIL  VARCHAR2(100 BYTE),
  UNITE              VARCHAR2(100 BYTE),
  ENTITE             VARCHAR2(100 BYTE),
  LIEU_TRAVAIL       VARCHAR2(100 BYTE),
  DIVISION           VARCHAR2(100 BYTE)
);

la table d'audit, qui se découpe en 3 "types" de colonnes...
les colonne OP qui renseignent sur l'Opération (transaction) auditée
les colonnes OLD qui contiennent les data "Avant" (colonnes identiques à la table source)
les colonnes NEW qui contiennent les data "Après" (colonnes identiques à la table source)

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
CREATE TABLE HRM_PERSON_JOB_AUDIT
(
  OP_ID                  NUMBER,
  OP_DATE                DATE                   NOT NULL,
  OP_USER                VARCHAR2(20 BYTE)      NOT NULL,
  OP_TYPE                VARCHAR2(10 BYTE)      NOT NULL,
  OLD_HRM_PERSON_JOB_ID  NUMBER(12),
  OLD_HRM_JOB_ID         NUMBER(12),
  OLD_HRM_IN_OUT_ID      NUMBER(12),
  OLD_HRM_PERSON_ID      NUMBER(12),
  OLD_PEJ_FROM           DATE,
  OLD_PEJ_TO             DATE,
  OLD_PEJ_AFFECT_RATE    NUMBER(16,5),
  OLD_PEJ_MAIN_JOB       NUMBER(1),
  OLD_TAUX_ACTUEL        VARCHAR2(100 BYTE),
  OLD_TAUX_LIEU_TRAVAIL  VARCHAR2(100 BYTE),
  OLD_UNITE              VARCHAR2(100 BYTE),
  OLD_ENTITE             VARCHAR2(100 BYTE),
  OLD_LIEU_TRAVAIL       VARCHAR2(100 BYTE),
  OLD_DIVISION           VARCHAR2(100 BYTE),
  NEW_HRM_PERSON_JOB_ID  NUMBER(12),
  NEW_HRM_JOB_ID         NUMBER(12),
  NEW_HRM_IN_OUT_ID      NUMBER(12),
  NEW_HRM_PERSON_ID      NUMBER(12),
  NEW_PEJ_FROM           DATE,
  NEW_PEJ_TO             DATE,
  NEW_PEJ_AFFECT_RATE    NUMBER(16,5),
  NEW_PEJ_MAIN_JOB       NUMBER(1),
  NEW_TAUX_ACTUEL        VARCHAR2(100 BYTE),
  NEW_TAUX_LIEU_TRAVAIL  VARCHAR2(100 BYTE),
  NEW_UNITE              VARCHAR2(100 BYTE),
  NEW_ENTITE             VARCHAR2(100 BYTE),
  NEW_LIEU_TRAVAIL       VARCHAR2(100 BYTE),
  NEW_DIVISION           VARCHAR2(100 BYTE)
);

puis enfin le Trigger qui va inscrire les données dans la table d'audit :

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
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
CREATE OR REPLACE TRIGGER TRG_HRM_PERSON_JOB_AUDIT
BEFORE DELETE OR INSERT OR UPDATE
ON HRM_PERSON_JOB
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
BEGIN
 
    If (Inserting)
    Then
      INSERT INTO hrm_person_job_audit
            ( op_id
            , op_date
            , op_user
            , op_type
            , old_hrm_person_job_id
            , old_hrm_job_id
            , old_hrm_in_out_id
            , old_hrm_person_id
            , old_pej_from
            , old_pej_to
            , old_pej_affect_rate
            , old_pej_main_job
            , old_taux_actuel
            , old_taux_lieu_travail
            , old_unite
            , old_entite
            , old_lieu_travail
            , old_division
            , new_hrm_person_job_id
            , new_hrm_job_id
            , new_hrm_in_out_id
            , new_hrm_person_id
            , new_pej_from
            , new_pej_to
            , new_pej_affect_rate
            , new_pej_main_job
            , new_taux_actuel
            , new_taux_lieu_travail
            , new_unite
            , new_entite
            , new_lieu_travail
            , new_division
            )
     VALUES ( HRM_Audit_Seq.NextVal 
            , sysdate
            , user
            , 'INSERT'
            , null
            , null
            , null
            , null
            , null
            , null
            , null
            , null
            , null
            , null
            , null
            , null
            , null
            , null
            , :new.hrm_person_job_id
            , :new.hrm_job_id
            , :new.hrm_in_out_id
            , :new.hrm_person_id
            , :new.pej_from
            , :new.pej_to
            , :new.pej_affect_rate
            , :new.pej_main_job
            , :new.taux_actuel
            , :new.taux_lieu_travail
            , :new.unite
            , :new.entite
            , :new.lieu_travail
            , :new.division
            ) ;
    Elsif (Updating)
    Then
      INSERT INTO hrm_person_job_audit
            ( op_id
            , op_date
            , op_user
            , op_type
            , old_hrm_person_job_id
            , old_hrm_job_id
            , old_hrm_in_out_id
            , old_hrm_person_id
            , old_pej_from
            , old_pej_to
            , old_pej_affect_rate
            , old_pej_main_job
            , old_taux_actuel
            , old_taux_lieu_travail
            , old_unite
            , old_entite
            , old_lieu_travail
            , old_division
            , new_hrm_person_job_id
            , new_hrm_job_id
            , new_hrm_in_out_id
            , new_hrm_person_id
            , new_pej_from
            , new_pej_to
            , new_pej_affect_rate
            , new_pej_main_job
            , new_taux_actuel
            , new_taux_lieu_travail
            , new_unite
            , new_entite
            , new_lieu_travail
            , new_division
            )
     VALUES ( HRM_Audit_Seq.NextVal 
            , sysdate
            , user
            , 'UPDATE'
            , :old.hrm_person_job_id
            , :old.hrm_job_id
            , :old.hrm_in_out_id
            , :old.hrm_person_id
            , :old.pej_from
            , :old.pej_to
            , :old.pej_affect_rate
            , :old.pej_main_job
            , :old.taux_actuel
            , :old.taux_lieu_travail
            , :old.unite
            , :old.entite
            , :old.lieu_travail
            , :old.division
            , :new.hrm_person_job_id
            , :new.hrm_job_id
            , :new.hrm_in_out_id
            , :new.hrm_person_id
            , :new.pej_from
            , :new.pej_to
            , :new.pej_affect_rate
            , :new.pej_main_job
            , :new.taux_actuel
            , :new.taux_lieu_travail
            , :new.unite
            , :new.entite
            , :new.lieu_travail
            , :new.division
            ) ;
    Elsif (Deleting)
    Then
      INSERT INTO hrm_person_job_audit
            ( op_id
            , op_date
            , op_user
            , op_type
            , old_hrm_person_job_id
            , old_hrm_job_id
            , old_hrm_in_out_id
            , old_hrm_person_id
            , old_pej_from
            , old_pej_to
            , old_pej_affect_rate
            , old_pej_main_job
            , old_taux_actuel
            , old_taux_lieu_travail
            , old_unite
            , old_entite
            , old_lieu_travail
            , old_division
            , new_hrm_person_job_id
            , new_hrm_job_id
            , new_hrm_in_out_id
            , new_hrm_person_id
            , new_pej_from
            , new_pej_to
            , new_pej_affect_rate
            , new_pej_main_job
            , new_taux_actuel
            , new_taux_lieu_travail
            , new_unite
            , new_entite
            , new_lieu_travail
            , new_division
            )
     VALUES ( HRM_Audit_Seq.NextVal 
            , sysdate
            , user
            , 'DELETE'
            , :old.hrm_person_job_id
            , :old.hrm_job_id
            , :old.hrm_in_out_id
            , :old.hrm_person_id
            , :old.pej_from
            , :old.pej_to
            , :old.pej_affect_rate
            , :old.pej_main_job
            , :old.taux_actuel
            , :old.taux_lieu_travail
            , :old.unite
            , :old.entite
            , :old.lieu_travail
            , :old.division
            , null
            , null
            , null
            , null
            , null
            , null
            , null
            , null
            , null
            , null
            , null
            , null
            , null
            , null
            ) ;
    End If ;
 
END TRG_HRM_PERSON_JOB_AUDIT;
/

désolé, c'est un peu long, mais c'est complet. Et en analysant le code tu devrais trouver toutes les réponses dont tu as besoin !!

un vue matérialisé ce serait pas plus simple ?

:)

Merci bien pour votre reponse :D

sinon j'ai une specification d'inserer tout l'enregistrement de table1 dans la table 2 , ta raison j'ai pas besoin d'un curseur j'ai declaré un enregistrement et ça marche bienn ;)

sinon le probleme qui reste est le suivant :

comme je vous ai dit la table 1 est composé de 3 champs :

1-Id
2-Date_heure
3-Utilisateur

normalement apres une modification je dois mettre à jour les 2 champs Date_heure (timestamp) et Utilisateur (user) mais le probleme c'est qu'en trigger qui depend de la table TABLE 1 on ne peut pas effectuer un UPDATE sur cette table :(

merciii pour votre aide :D

sinon fait le AVANT update et met à jour les champs
Code:

1 2 3 4 5 BEFORE UPDATE --lancement de l'historisation' :new.date_heure := sysdate; :new.utilisateur := user;

:)

ça marche trééééé biennnn je vous remercie bq :D :D

:)

Re Salut :oops:

apparement y a encore un probleme :

c'est au niveau de la date ajouté dans la table d'historisation

normalement j'ai fait une insertion :old.date_heure de la table de base

mais la table d'historisation prend la date mais apres la mise à jour :?

voici mon code :

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
 
 
create or replace trigger Histo
  before update or delete  on emp  
  for each row
declare 
--   Enregistrement de type table histo 
  Rec histo%rowtype;
--  var_date emp.date_heure%type;
begin
if updating then
--Appel de la procedure insertion_histo
    insertion_histo(:old.ref,:old.nom,:old.fonction,:old.sal,:old.comm,:old.utilisateur,:new.date_heure,:old.code_mvt);
    :new.date_heure:=CURRENT_TIMESTAMP;
    :new.utilisateur:=user;
    :new.code_mvt:='m';
end if;
if deleting then
 
   rec.ref:=:old.ref;
   rec.nom:=:old.nom;
   rec.fonction:=:old.fonction;
   rec.sal:=:old.sal;
   rec.comm:=:old.comm;
   rec.utilisateur:=:old.utilisateur;
   rec.date_heure:=:old.date_heure;
   rec.code_mvt:='s';
--Appel de la procedure insertion_histo
        insertion_histo(rec.ref,rec.nom,rec.fonction,rec.sal,rec.comm,rec.utilisateur,rec.date_heure,rec.code_mvt);
 
end if;
 
end Histo;

ps : insertion histo est une procedure effectuant l'insertion dans la table historisation

merci bien ;)

euuuuuh et pourquoi tu passes en paramètre de ta fonction :new.date_heure sit tu veux stocker :old.date_heure alors ? :koi:

Citation:

Envoyé par Fred_D

un vue matérialisé ce serait pas plus simple ?

C'est clair que c'est plus simple, le seul défaut c'est que ça ne réponds pas du tout au besoin! (ça répond meme exactement au besoin inverse :aie: )