Trigger on insert ne fonctionne pas

**Stessy** · 17/01/2008, 14h28

Bonjour à tous,

désolé pour le titre mais je n'ai jamais été très fort dans ce domaine.

Voivi tout d'abord les DDL de mes 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
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
219
220
221
222
223
224
225
226
227
228
229
230
 
REM START  PERSONNES
 
  CREATE TABLE ."PERSONNES" 
   (	"PERSONNE_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"NOM" VARCHAR2(100 BYTE) NOT NULL ENABLE, 
	"PRENOM" VARCHAR2(100 BYTE) NOT NULL ENABLE, 
	"ADRESSE" VARCHAR2(200 BYTE), 
	"CODE_POSTAL" VARCHAR2(10 BYTE), 
	"VILLE" VARCHAR2(200 BYTE), 
	"TELEPHONE" VARCHAR2(20 BYTE), 
	"GSM" VARCHAR2(15 BYTE), 
	"EMAIL" VARCHAR2(150 BYTE), 
	"DATE_NAISSANCE" DATE, 
	"NUMERO_MEMBRE" NUMBER(10,0) DEFAULT -1, 
	"TYPE_PERSONNE" VARCHAR2(10 BYTE), 
	"CARTE_IDENTITE" VARCHAR2(20 BYTE), 
	 CONSTRAINT "PERSONNES_PK" PRIMARY KEY ("PERSONNE_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;
 
REM END  PERSONNES
 
REM START  MUTATING_TRIGGER_ON_PERSONNE
 
  CREATE OR REPLACE TRIGGER "MUTATING_TRIGGER_ON_PERSONNE" 
  after insert on personnes
  for each row
  begin
    MUTATING_PACKAGE.personne_id := :new.personne_id;
  end;
 
 
/
ALTER TRIGGER "MUTATING_TRIGGER_ON_PERSONNE" ENABLE;
 
REM END  MUTATING_TRIGGER_ON_PERSONNE
 
REM START  PERSONNES_TRIGGER_ON_INSERT
 
  CREATE OR REPLACE TRIGGER "PERSONNES_TRIGGER_ON_INSERT" 
AFTER
insert on "PERSONNES"
begin
update personnes set numero_membre =-1 where personne_id = mutating_package.personne_id;
end;
 
/
ALTER TRIGGER "PERSONNES_TRIGGER_ON_INSERT" ENABLE;
 
REM END  PERSONNES_TRIGGER_ON_INSERT
 
REM START  PERSONNES_TRIGGER_ON_DELETE
 
  CREATE OR REPLACE TRIGGER "PERSONNES_TRIGGER_ON_DELETE" 
AFTER
delete on "PERSONNES"
 
 
declare
cursor cur is select personne_id from personnes where numero_membre > 0 order by numero_membre;
 
v_personne_id number;
v_numero_membre number :=1;
v_counter number :=0;
v_query varchar2(4000);
 
 
begin
 
for rec_personne in cur loop
v_personne_id := rec_personne.personne_id;
update personnes set numero_membre = v_numero_membre where personne_id = v_personne_id;
v_numero_membre := v_numero_membre + 1;
end loop;
 
 
end;
 
/
ALTER TRIGGER "PERSONNES_TRIGGER_ON_DELETE" ENABLE;
 
REM END  PERSONNES_TRIGGER_ON_DELETE
/
 
REM START COTISATIONS
 
  CREATE TABLE "COTISATIONS" 
   (	"ANNEE_COTISATION" VARCHAR2(4 BYTE) NOT NULL ENABLE, 
	"MONTANT_COTISATION" NUMBER(10,2) NOT NULL ENABLE, 
	 CONSTRAINT "COTISATIONS_PK" PRIMARY KEY ("ANNEE_COTISATION")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;
 
REM END COTISATIONS
/
 
REM START PERSONNES_COTISATIONS
 
  CREATE TABLE "PERSONNES_COTISATIONS" 
   (	"PERSONNE_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"PAYE" NUMBER(1,0) NOT NULL ENABLE, 
	"ANNEE_COTISATION" VARCHAR2(4 BYTE) NOT NULL ENABLE, 
	"PERSONNES_COTISATIONS_ID" NUMBER, 
	"DATE_PAIEMENT" DATE, 
	 CONSTRAINT "PERSONNES_COTISATIONS_PER_FK1" FOREIGN KEY ("PERSONNE_ID")
	  REFERENCES "STANDARD_NAAST"."PERSONNES" ("PERSONNE_ID") ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;
 
REM END PERSONNES_COTISATIONS
 
REM START INSERT_SEQ
 
  CREATE OR REPLACE TRIGGER "INSERT_SEQ" 
BEFORE INSERT
ON PERSONNES_COTISATIONS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
    NEW_SEQUENCE_NUMBER NUMBER;
BEGIN
    SELECT PERSONNES_COTISATIONS_SEQ.NEXTVAL
    INTO NEW_SEQUENCE_NUMBER
    FROM DUAL;
    :NEW.PERSONNES_COTISATIONS_ID := NEW_SEQUENCE_NUMBER;
END;
/
ALTER TRIGGER "INSERT_SEQ" ENABLE;
 
REM END STANDARD_NAAST INSERT_SEQ
 
REM START STANDARD_NAAST TRIGGER_INSERT_ON_COTISATIONS
 
  CREATE OR REPLACE TRIGGER "STANDARD_NAAST"."TRIGGER_INSERT_ON_COTISATIONS" 
 AFTER INSERT ON PERSONNES_COTISATIONS 
 
declare
v_personne_id number;
v_numero_membre number;
v_numero_membre_actual number;
 
BEGIN
 
  select personne_id into v_personne_id from personnes_cotisations where personnes_cotisations_id = create_personnes_cotisations.personnes_cotisations_id ;
 
  select numero_membre into v_numero_membre_actual from personnes where personne_id = create_personnes_cotisations.personnes_cotisations_id ;
 
  if v_numero_membre_actual = -1 then  
 
  select max(numero_membre) + 1 into v_numero_membre from personnes;
 
  update personnes set numero_membre = v_numero_membre where personne_id = create_personnes_cotisations.personnes_cotisations_id;
 
 
 
  end if;
 
 
END;
 
 
/
ALTER TRIGGER "TRIGGER_INSERT_ON_COTISATIONS" ENABLE;
 
REM END TRIGGER_INSERT_ON_COTISATIONS
 
REM START FIRST_TRIGGER_PERS_COT
 
  CREATE OR REPLACE TRIGGER "FIRST_TRIGGER_PERS_COT" 
  after insert on personnes_cotisations
  for each row
  begin
    CREATE_PERSONNES_COTISATIONS.personnes_cotisations_id := :new.personnes_cotisations_id;
  end;
/
ALTER TRIGGER "FIRST_TRIGGER_PERS_COT" ENABLE;
 
REM END FIRST_TRIGGER_PERS_COT
 
 
 
insert into personnes (PERSONNE_ID, NOM, PRENOM, NUMERO_MEMBRE) values (252,'DANIEL','Martine',1);
insert into personnes (PERSONNE_ID, NOM, PRENOM, NUMERO_MEMBRE) values (253,'BOTTEMANNE','Richard',2);
insert into personnes (PERSONNE_ID, NOM, PRENOM, NUMERO_MEMBRE) values (254,'SLUYS ','Léon',3);
insert into personnes (PERSONNE_ID, NOM, PRENOM, NUMERO_MEMBRE) values (255,'POLART','Pierre',4);
insert into personnes (PERSONNE_ID, NOM, PRENOM, NUMERO_MEMBRE) values (256,'PERREMAN','Michel',5);
insert into personnes (PERSONNE_ID, NOM, PRENOM, NUMERO_MEMBRE) values (257,'EHRLER ','Chantal',6);
insert into personnes (PERSONNE_ID, NOM, PRENOM, NUMERO_MEMBRE) values (258,'LETOT','Henri',7);
insert into personnes (PERSONNE_ID, NOM, PRENOM, NUMERO_MEMBRE) values (259,'MAQUESTIAU ','Jean-Pierre',8);
insert into personnes (PERSONNE_ID, NOM, PRENOM, NUMERO_MEMBRE) values (260,'SABIAU ','Irène',9);
 
insert into cotisations (ANNEE_COTISATION, MONTANT_COTISATION) values (2008,13);
insert into cotisations (ANNEE_COTISATION, MONTANT_COTISATION) values (2007,12.5);
 
 
insert into personnes (PERSONNE_ID, PAYE, ANNEE_COTISATION, PERSONNES_COTISATIONS_ID,DATE_PAIEMENT) values (252,1,2007,205,sysdate);
insert into personnes (PERSONNE_ID, PAYE, ANNEE_COTISATION, PERSONNES_COTISATIONS_ID,DATE_PAIEMENT) values (253,1,2007,206,sysdate);
insert into personnes (PERSONNE_ID, PAYE, ANNEE_COTISATION, PERSONNES_COTISATIONS_ID,DATE_PAIEMENT) values (254,1,2007,207,sysdate);
insert into personnes (PERSONNE_ID, PAYE, ANNEE_COTISATION, PERSONNES_COTISATIONS_ID,DATE_PAIEMENT) values (255,1,2007,208,sysdate);
insert into personnes (PERSONNE_ID, PAYE, ANNEE_COTISATION, PERSONNES_COTISATIONS_ID,DATE_PAIEMENT) values (256,1,2007,209,sysdate);
insert into personnes (PERSONNE_ID, PAYE, ANNEE_COTISATION, PERSONNES_COTISATIONS_ID,DATE_PAIEMENT) values (257,1,2007,210,sysdate);
insert into personnes (PERSONNE_ID, PAYE, ANNEE_COTISATION, PERSONNES_COTISATIONS_ID,DATE_PAIEMENT) values (258,1,2007,211,sysdate);
insert into personnes (PERSONNE_ID, PAYE, ANNEE_COTISATION, PERSONNES_COTISATIONS_ID,DATE_PAIEMENT) values (259,1,2007,212,sysdate);
insert into personnes (PERSONNE_ID, PAYE, ANNEE_COTISATION, PERSONNES_COTISATIONS_ID,DATE_PAIEMENT) values (260,1,2007,213,sysdate);
 
commit;
 
create or replace PACKAGE create_personnes_cotisations AS
  personnes_cotisations_id personnes_cotisations.personnes_cotisations_id%TYPE;
 
  END;
 
create or replace PACKAGE mutating_package AS
  personne_id personnes.personne_id%TYPE;  
  END;

Le trigger permettant de mettre à jour numero_membre dans la table personnes ne met pas à jour la colonne et je n'ai aucun retour d'erreur.

Je suis certain que c'est une bêtise mais je ne trouve pas le problème.

Si vous avez une solution je suis preneur.

D'avance merci pour vos réponses.

**orafrance** · 17/01/2008, 15h40

évidemment, tu ne mets pas à jour une colonne mais tu affectes la valeur insérée dans personnes_cotisations_id à la variable CREATE_PERSONNES_COTISATIONS.personnes_cotisations_id, variable qu'apparemment tu n'utilises nul par dans ta session

**Stessy** · 17/01/2008, 15h50

Envoyé par orafrance

évidemment, tu ne mets pas à jour une colonne mais tu affectes la valeur insérée dans personnes_cotisations_id à la variable CREATE_PERSONNES_COTISATIONS.personnes_cotisations_id, variable qu'apparemment tu n'utilises nul par dans ta session

J'ai 2 triggers

Un trigger 'FIRST_TRIGGER_PERS_COT' qui attribue le :new.ma_colonne à une variable globale dans le package

Et un trigger 'TRIGGER_INSERT_ON_COTISATIONS' qui utilise la variable globale pour updater une colonne dans une autre table.

Donc j'utilise bien la variable CREATE_PERSONNES_COTISATIONS.personnes_cotisations_id dans le deuxième trigger.

package

Code :

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

1
2
3
4
CREATE OR REPLACE PACKAGE create_personnes_cotisations AS
  personnes_cotisations_id personnes_cotisations.personnes_cotisations_id%TYPE;
 
  END;

Trigger 1

Code :

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

1
2
3
4
5
6
CREATE OR REPLACE TRIGGER "FIRST_TRIGGER_PERS_COT" 
  after INSERT ON personnes_cotisations
  FOR each row
  begin
    CREATE_PERSONNES_COTISATIONS.personnes_cotisations_id := :new.personnes_cotisations_id;
  end;

Trigger 2

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
CREATE OR REPLACE TRIGGER "STANDARD_NAAST"."TRIGGER_INSERT_ON_COTISATIONS" 
 AFTER INSERT ON PERSONNES_COTISATIONS 
 
declare
v_personne_id number;
v_numero_membre number;
v_numero_membre_actual number;
 
BEGIN
 
  SELECT personne_id INTO v_personne_id FROM personnes_cotisations WHERE personnes_cotisations_id = create_personnes_cotisations.personnes_cotisations_id ;
 
  SELECT numero_membre INTO v_numero_membre_actual FROM personnes WHERE personne_id = create_personnes_cotisations.personnes_cotisations_id ;
 
  IF v_numero_membre_actual = -1 then  
 
  SELECT max(numero_membre) + 1 INTO v_numero_membre FROM personnes;
 
  UPDATE personnes SET numero_membre = v_numero_membre WHERE personne_id = create_personnes_cotisations.personnes_cotisations_id;
 
 
 
  end IF;
 
 
END;

Voilà

Merci beaucoup pour ton intervention.

**orafrance** · 17/01/2008, 15h55

2 AFTER INSERT sur la même table ??? Et pourquoi donc un tel capilotractage ?

Ce serait plus simple de nous expliquer ce que tout cela est sensé faire

**Stessy** · 17/01/2008, 16h18

Envoyé par orafrance

2 AFTER INSERT sur la même table ??? Et pourquoi donc un tel capilotractage ?

Ce serait plus simple de nous expliquer ce que tout cela est sensé faire

Je suis obligé d'utiliser deux after insert car j'avais un problème de mutating table.

Donc j'utilise in 'after insert of each row' pour avoir la valeur de :new.ma_colonne dans la variable globale du package

Et ensuite 'after insert' pour attribuer la nouvelle valeur dans la colonne de l'autre table.

D'un autre coté je viens de mettre le premier trigger 'after insert of each row' sur disabled car j'avais déjà un trigger 'before insert' qui attribuait déjà un nouvel ID à partir d'une séquence mais le problème reste le même.

Voilà en gros ce que je veux faire:

2 tables

personnes
personnes_cotisations

j'ajoute une nouvelle personne dans la table personnes
par défaut la colonne numero_membre a une valeur -1 car cette personne n'est pas encore un membre
Ensuite j'ajoute une nouvelle cotisation dans la table personnes_cotisations basée sur la nouvelle personne ajoutée

Une fois que cette cotisation est ajoutée, la personne devient un membre, donc le numero_membre doit avoir la valeur

Code :

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

 select max(numero_membre) + 1 from personnes

C'est ce que j'essaye de faire via un trigger mais cela ne marche pas.
La valeur n'est pas modifiée alors que le trigger ne me retourne aucune erreur.

Voilà

Merci beaucoup

**orafrance** · 17/01/2008, 17h12

Pourquoi pas simplement quelque chose de ce style :

Code :

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

1
2
3
4
5
6
7
8
CREATE OR REPLACE TRIGGER "FIRST_TRIGGER_PERS_COT" 
  after INSERT ON personnes_cotisations
  FOR each row
  begin
UPDATE personnes 
SET numero_membre = (SELECT max(numero_membre) + 1 FROM personnes) 
WHERE personne_id = :new.personnes_cotisations_id;
  end;

sachant qu'il est préférable d'utiliser une séquence plutôt que de chercher MAX+1

**mnitu** · 17/01/2008, 22h52

Désolé pour la suite mais c'est tellement mauvais.

Je ne sais pas si c'est pour une application réelle ou simplement pour apprendre aux étudiants le méandre et pièges des triggers (personnellement j'utilise très rarement les triggers) mais j'ai l'impression qu'il y a beaucoup de confusion dans l'ensemble du code et non pas une simple bêtise.

Pour affecter la valeur -1 à la zone numéro de membre dans la création de la personne nul besoin d'écrire 2 triggers et un package. Le trigger en insert suffit amplement. En plus faire update dans un trigger insert c'est chercher des ennuis.
Pour avoir une problème de mutation dans les triggers il faut il me semble un insert/update etc qui se fait pour plus d'une enregistrement, de type insert into select from ... Dans ce cas le trigger after insert on each row se déclanche bien N fois (each row). Donc ça va être très difficile de se débrouiller avec une variable de type sclaire dans le package; il faut un tableau!
La renumérotation des membres dans le cas d'une suppression d'une personne qui est fait dans le trigger on delete n'a rien avoir avec le relationnel. On peut dire que faire l'update dans la boucle la où un simple update de toute la table suffit c'est loin d'être optimale
Faire Select Max dans une table sans ajouter le Nvl c'est un délit d'optimisme

Je pense que tu devrait faire d'abord le ménage dans le code avant d'avancer. Pour l'instant je n'ai pas le temps de diagnostiquer le problème mais si le constat est que la mise à jour ne se fait pas et qu'il n'y a pas de message d'erreur alors le Test IF v_numero_membre_actual = -1 then devrait retourner faux. Mais pourquoi ça reste à analyser.

Un bon exemple de contournement du problème de la table mutante se trouve ici (malheureusement comme je le déjà signalé les exemples de ce site sur ce problème ne se sent pas très bien non plus, pour dire ainsi).

**Stessy** · 18/01/2008, 09h22

Envoyé par mnitu

Désolé pour la suite mais c'est tellement mauvais.

Je ne sais pas si c'est pour une application réelle ou simplement pour apprendre aux étudiants le méandre et pièges des triggers (personnellement j'utilise très rarement les triggers) mais j'ai l'impression qu'il y a beaucoup de confusion dans l'ensemble du code et non pas une simple bêtise.

Pour affecter la valeur -1 à la zone numéro de membre dans la création de la personne nul besoin d'écrire 2 triggers et un package. Le trigger en insert suffit amplement. En plus faire update dans un trigger insert c'est chercher des ennuis.
Pour avoir une problème de mutation dans les triggers il faut il me semble un insert/update etc qui se fait pour plus d'une enregistrement, de type insert into select from ... Dans ce cas le trigger after insert on each row se déclanche bien N fois (each row). Donc ça va être très difficile de se débrouiller avec une variable de type sclaire dans le package; il faut un tableau!
La renumérotation des membres dans le cas d'une suppression d'une personne qui est fait dans le trigger on delete n'a rien avoir avec le relationnel. On peut dire que faire l'update dans la boucle la où un simple update de toute la table suffit c'est loin d'être optimale
Faire Select Max dans une table sans ajouter le Nvl c'est un délit d'optimisme

Je pense que tu devrait faire d'abord le ménage dans le code avant d'avancer. Pour l'instant je n'ai pas le temps de diagnostiquer le problème mais si le constat est que la mise à jour ne se fait pas et qu'il n'y a pas de message d'erreur alors le Test IF v_numero_membre_actual = -1 then devrait retourner faux. Mais pourquoi ça reste à analyser.

Un bon exemple de contournement du problème de la table mutante se trouve ici (malheureusement comme je le déjà signalé les exemples de ce site sur ce problème ne se sent pas très bien non plus, pour dire ainsi).

J'utilise APEX comme environnement de développement.

Et je peux prouver que j'ai un problème de table mutante si j'execute un trigger simplement sur un after insert.

Maintenant je ne sais pas comment travaille APEX derrière pour insérer de nouvelles données.

Je viens à l'instant de faire un test en utilisant uniquement un seul trigger 'after insert of each row' et j'ai un problème de table mutante.

Maintenant si tu dis que tous les documents sur les problèmes de table mutante sont mauvais, ce serait bien que tu argumentes et montres un exemple de best practices pour résoudre le problème.

Voilà

Merci

**Stessy** · 18/01/2008, 09h30

Envoyé par orafrance

Pourquoi pas simplement quelque chose de ce style :

Code :

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

1
2
3
4
5
6
7
8
CREATE OR REPLACE TRIGGER "FIRST_TRIGGER_PERS_COT" 
  after INSERT ON personnes_cotisations
  FOR each row
  begin
UPDATE personnes 
SET numero_membre = (SELECT max(numero_membre) + 1 FROM personnes) 
WHERE personne_id = :new.personnes_cotisations_id;
  end;

sachant qu'il est préférable d'utiliser une séquence plutôt que de chercher MAX+1

Salut orafrance,

je suis obligé de d'utiliser le max + 1 car les numéros doivent doivent se suivre, donc pas de séquence.

je viens de faire le test avec ton trigger et comme prévu j'ai un problème de table mutante.

Voilà

Merci

**orafrance** · 18/01/2008, 09h33

Envoyé par Stessy

je suis obligé de d'utiliser le max + 1 car les numéros doivent doivent se suivre, donc pas de séquence.

Ca pourrait être pas mal d'avoir une table avec une colonne et une ligne qui contient le MAX

Envoyé par Stessy

je viens de faire le test avec ton trigger et comme prévu j'ai un problème de table mutante.

tu peux envoyer quelques INSERT pour alimenter les 2 tables et les tests que tu as réalisé STP ?

**Stessy** · 18/01/2008, 10h08

Envoyé par orafrance

Ca pourrait être pas mal d'avoir une table avec une colonne et une ligne qui contient le MAX

tu peux envoyer quelques INSERT pour alimenter les 2 tables et les tests que tu as réalisé STP ?

Création des trois tables avec insertion de données sans 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
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
REM START  PERSONNES
 
  CREATE TABLE ."PERSONNES"
   (    "PERSONNE_ID" NUMBER(10,0) NOT NULL ENABLE,
        "NOM" VARCHAR2(100 BYTE) NOT NULL ENABLE,
        "PRENOM" VARCHAR2(100 BYTE) NOT NULL ENABLE,
        "ADRESSE" VARCHAR2(200 BYTE),
        "CODE_POSTAL" VARCHAR2(10 BYTE),
        "VILLE" VARCHAR2(200 BYTE),
        "TELEPHONE" VARCHAR2(20 BYTE),
        "GSM" VARCHAR2(15 BYTE),
        "EMAIL" VARCHAR2(150 BYTE),
        "DATE_NAISSANCE" DATE,
        "NUMERO_MEMBRE" NUMBER(10,0) DEFAULT -1,
        "TYPE_PERSONNE" VARCHAR2(10 BYTE),
        "CARTE_IDENTITE" VARCHAR2(20 BYTE),
         CONSTRAINT "PERSONNES_PK" PRIMARY KEY ("PERSONNE_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;
 
REM END  PERSONNES
 
 
REM START COTISATIONS
 
  CREATE TABLE "COTISATIONS"
   (    "ANNEE_COTISATION" VARCHAR2(4 BYTE) NOT NULL ENABLE,
        "MONTANT_COTISATION" NUMBER(10,2) NOT NULL ENABLE,
         CONSTRAINT "COTISATIONS_PK" PRIMARY KEY ("ANNEE_COTISATION")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;
 
REM END COTISATIONS
/
 
REM START PERSONNES_COTISATIONS
 
  CREATE TABLE "PERSONNES_COTISATIONS"
   (    "PERSONNE_ID" NUMBER(10,0) NOT NULL ENABLE,
        "PAYE" NUMBER(1,0) NOT NULL ENABLE,
        "ANNEE_COTISATION" VARCHAR2(4 BYTE) NOT NULL ENABLE,
        "PERSONNES_COTISATIONS_ID" NUMBER,
        "DATE_PAIEMENT" DATE,
         CONSTRAINT "PERSONNES_COTISATIONS_PER_FK1" FOREIGN KEY ("PERSONNE_ID")
          REFERENCES "STANDARD_NAAST"."PERSONNES" ("PERSONNE_ID") ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;
 
REM END PERSONNES_COTISATIONS
 
INSERT INTO personnes (PERSONNE_ID, NOM, PRENOM, NUMERO_MEMBRE) VALUES (252,'DANIEL','Martine',1);
INSERT INTO personnes (PERSONNE_ID, NOM, PRENOM, NUMERO_MEMBRE) VALUES (253,'BOTTEMANNE','Richard',2);
INSERT INTO personnes (PERSONNE_ID, NOM, PRENOM, NUMERO_MEMBRE) VALUES (254,'SLUYS ','Léon',3);
INSERT INTO personnes (PERSONNE_ID, NOM, PRENOM, NUMERO_MEMBRE) VALUES (255,'POLART','Pierre',4);
INSERT INTO personnes (PERSONNE_ID, NOM, PRENOM, NUMERO_MEMBRE) VALUES (256,'PERREMAN','Michel',5);
INSERT INTO personnes (PERSONNE_ID, NOM, PRENOM, NUMERO_MEMBRE) VALUES (257,'EHRLER ','Chantal',6);
INSERT INTO personnes (PERSONNE_ID, NOM, PRENOM, NUMERO_MEMBRE) VALUES (258,'LETOT','Henri',7);
INSERT INTO personnes (PERSONNE_ID, NOM, PRENOM, NUMERO_MEMBRE) VALUES (259,'MAQUESTIAU ','Jean-Pierre',8);
INSERT INTO personnes (PERSONNE_ID, NOM, PRENOM, NUMERO_MEMBRE) VALUES (260,'SABIAU ','Irène',9);
 
INSERT INTO cotisations (ANNEE_COTISATION, MONTANT_COTISATION) VALUES (2008,13);
INSERT INTO cotisations (ANNEE_COTISATION, MONTANT_COTISATION) VALUES (2007,12.5);
 
 
INSERT INTO personnes_cotisations (PERSONNE_ID, PAYE, ANNEE_COTISATION, PERSONNES_COTISATIONS_ID,DATE_PAIEMENT) VALUES (252,1,2007,205,sysdate);
INSERT INTO personnes_cotisations (PERSONNE_ID, PAYE, ANNEE_COTISATION, PERSONNES_COTISATIONS_ID,DATE_PAIEMENT) VALUES (253,1,2007,206,sysdate);
INSERT INTO personnes_cotisations (PERSONNE_ID, PAYE, ANNEE_COTISATION, PERSONNES_COTISATIONS_ID,DATE_PAIEMENT) VALUES (254,1,2007,207,sysdate);
INSERT INTO personnes_cotisations (PERSONNE_ID, PAYE, ANNEE_COTISATION, PERSONNES_COTISATIONS_ID,DATE_PAIEMENT) VALUES (255,1,2007,208,sysdate);
INSERT INTO personnes_cotisations (PERSONNE_ID, PAYE, ANNEE_COTISATION, PERSONNES_COTISATIONS_ID,DATE_PAIEMENT) VALUES (256,1,2007,209,sysdate);
INSERT INTO personnes_cotisations (PERSONNE_ID, PAYE, ANNEE_COTISATION, PERSONNES_COTISATIONS_ID,DATE_PAIEMENT) VALUES (257,1,2007,210,sysdate);
INSERT INTOpersonnes_cotisations (PERSONNE_ID, PAYE, ANNEE_COTISATION, PERSONNES_COTISATIONS_ID,DATE_PAIEMENT) VALUES (258,1,2007,211,sysdate);
INSERT INTO personnes_cotisations (PERSONNE_ID, PAYE, ANNEE_COTISATION, PERSONNES_COTISATIONS_ID,DATE_PAIEMENT) VALUES (259,1,2007,212,sysdate);
INSERT INTO personnes_cotisations (PERSONNE_ID, PAYE, ANNEE_COTISATION, PERSONNES_COTISATIONS_ID,DATE_PAIEMENT) VALUES (260,1,2007,213,sysdate);
 
commit;

Les différents triggers et packages

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
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
 
--package pour mettre dans une variable globale le personnes_cotisation_id
create or replace PACKAGE create_personnes_cotisations AS
  personnes_cotisations_id personnes_cotisations.personnes_cotisations_id%TYPE;
 
  END;
/
 
--package pour mettre dans une variable globale le personne_id
create or replace PACKAGE mutating_package AS
  personne_id personnes.personne_id%TYPE;  
  END;
/
 
 
create or replace TRIGGER 
"FIRST_TRIGGER_ON_PERS_COT" 
  after INSERT ON personnes_cotisations
  FOR each row
 
 
 
declare
v_personne_id number;
v_numero_membre number;
v_numero_membre_actual number;
 
  begin
 
  select personne_id into v_personne_id from personnes_cotisations where personnes_cotisations_id = :new.personnes_cotisations_id; 
  select numero_membre into v_numero_membre_actual from personnes where personne_id = :new.personnes_cotisations_id;
 
  if v_numero_membre_actual = -1 then    
 
UPDATE personnes 
SET numero_membre = (SELECT max(numero_membre) + 1 FROM personnes) 
WHERE personne_id = :new.personnes_cotisations_id;
 else
  update personnes set numero_membre = -10 where personne_id = create_personnes_cotisations.personnes_cotisations_id;
 
  end if;
  end;
/
 
create or replace TRIGGER 
"STANDARD_NAAST"."PERSONNE_BEFORE_INSERT" 
BEFORE INSERT
ON PERSONNES_COTISATIONS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
    NEW_SEQUENCE_NUMBER NUMBER;
BEGIN
    SELECT PERSONNES_COTISATIONS_SEQ.NEXTVAL
    INTO NEW_SEQUENCE_NUMBER
    FROM DUAL;
    :NEW.PERSONNES_COTISATIONS_ID := NEW_SEQUENCE_NUMBER;
    create_personnes_cotisations.personnes_cotisations_id := :NEW.PERSONNES_COTISATIONS_ID;
END;
/
 
create or replace TRIGGER 
"PERSONNES_FIRST_TRIG_INSERT"
  after insert on personnes
  for each row
 
begin
    MUTATING_PACKAGE.personne_id := :new.personne_id;    
  end;
 
 
/
create or replace TRIGGER 
"PERSONNES_SECOND_TRIG_INSERT"
AFTER
insert on "PERSONNES" 
begin
update personnes set numero_membre =-1 where personne_id = mutating_package.personne_id;
end;
 
/
 
create or replace TRIGGER 
"PERSONNES_SECOND_TRIG_INSERT"
AFTER
insert on "PERSONNES" 
begin
update personnes set numero_membre =-1 where personne_id = mutating_package.personne_id;
end;

Voici ensuite une ligne pour insérer une personne

Code :

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

INSERT INTO personnes (PERSONNE_ID, NOM, PRENOM, NUMERO_MEMBRE) VALUES (300,'TEST','TEST',-1);

et une ligne pour insérer une cotisation

Code :

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

INSERT INTO personnes_cotisations (PERSONNE_ID, PAYE, ANNEE_COTISATION,DATE_PAIEMENT) VALUES (300,1,2007,sysdate);

Voilà

Merci beaucoup pour ton aide

**orafrance** · 18/01/2008, 10h35

le mutating table vient de :

Code :

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

SELECT personne_id INTO v_personne_id FROM personnes_cotisations WHERE personnes_cotisations_id = :new.personnes_cotisations_id;

dans le trigger FIRST_TRIGGER_ON_PERS_COT

cette ligne est inutile puisque tu utilises :new.personne_id plutôt que faire un SELECT

Par contre tu as un no_data_found parce que tu utilises personne_id = :new.personnes_cotisations_id; alors que personnes_cotisations_id n'est pas renseignée dans l'insert... ce serait pas :new.personne_id plutôt ?

essaye :

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
CREATE OR REPLACE TRIGGER 
FIRST_TRIGGER_ON_PERS_COT 
  after INSERT ON personnes_cotisations
  FOR each row
declare
v_personne_id number;
v_numero_membre number;
v_numero_membre_actual number;
 
  begin
 
WHERE personnes_cotisations_id = :new.personnes_cotisations_id; 
  BEGIN
  SELECT numero_membre INTO v_numero_membre_actual FROM personnes WHERE personne_id = :new.personne_id;
  exception when no_data_found then raise_application_error(-20001,'pas de personne');
  END;
 
  IF v_numero_membre_actual = -1 then    
 
UPDATE personnes 
SET numero_membre = (SELECT max(numero_membre) + 1 FROM personnes) 
WHERE personne_id = :new.personnes_cotisations_id;
 else
  UPDATE personnes SET numero_membre = -10 WHERE personne_id = create_personnes_cotisations.personnes_cotisations_id;
 
  end IF;
  end;
/

**phili_b** · 18/01/2008, 10h47

et as tu essayé, pour tester, de faire ton trigger sans le max mais avec constante élévée bidon pour voir si ça vient du max le souci ?

sinon je pensais à truc du style ci-dessous mais c'est un peu tiré par les cheveux et je ne sais pas si les DDL sont acceptés dans les triggers, bien que je fais de l'oracle depuis un moment.

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
[..]
v_diff number;
BEGIN 
[..]
       SELECT -(ma_sequence.currval -max(numero_membre))-1  INTO v_diff              
       FROM personnes;
 
       IF v_diff <>0 THEN
              DBMS_UTILITY.EXEC_DDL_STATEMENT ('ALTER SEQUENCE seq INCREMENT BY '||v_diff);--v_diff aura de grande chance d'être négatif
              SELECT ma_sequence.NEXTVAL  FROM dual;
              DBMS_UTILITY.EXEC_DDL_STATEMENT ('ALTER SEQUENCE seq INCREMENT BY 1');
       END IF;
 
       UPDATE personnes 
       SET numero_membre =ma_sequence.nextval 
       WHERE personne_id = :new.personnes_cotisations_id;
[..]

**orafrance** · 18/01/2008, 10h47

Et au lieu du MAX :

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
55
56
57
58
create table next_personne_id (personne_id number);
-- je mets 260 parce que c'est le max de ton exemple
insert into next_personne_id values (260);
commit;
 
CREATE OR REPLACE TRIGGER 
FIRST_TRIGGER_ON_PERS_COT 
  after INSERT ON personnes_cotisations
  FOR each row
declare
v_personne_id number;
v_numero_membre number;
v_numero_membre_actual number;
 
  begin
 
  --SELECT personne_id INTO v_personne_id FROM personnes_cotisations WHERE personnes_cotisations_id = :new.personnes_cotisations_id; 
  BEGIN
  SELECT numero_membre INTO v_numero_membre_actual FROM personnes WHERE personne_id = :new.personne_id;
  exception when no_data_found then raise_application_error(-20001,'pas de personne');
  END;
 
  IF v_numero_membre_actual = -1 then    
 
UPDATE personnes 
SET numero_membre = (SELECT max(numero_membre) + 1 FROM personnes) 
WHERE personne_id = :new.personnes_cotisations_id;
 else
  UPDATE personnes SET numero_membre = -10 WHERE personne_id = (select personne_id from next_personne_id);
 
  end IF;
  end;
/
 
CREATE OR REPLACE TRIGGER 
PERSONNE_BEFORE_INSERT 
BEFORE INSERT
ON PERSONNES_COTISATIONS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
    NEW_SEQUENCE_NUMBER NUMBER;
BEGIN
    SELECT PERSONNES_COTISATIONS_SEQ.NEXTVAL
    INTO :NEW.PERSONNES_COTISATIONS_ID
    FROM DUAL;
END;
/
 
CREATE OR REPLACE TRIGGER 
PERSONNE_AFTER_UPDATE
AFTER UPDATE OF personne_id 
ON PERSONNES
FOR EACH ROW
BEGIN
    update next_personne_id set personne_id = :new.personne_id + 1;
END;
/

et enfin, tes triggers sur insert de la table personne sont inutiles puisque tu as un DEFAULT de -1

**Stessy** · 18/01/2008, 10h55

Envoyé par orafrance

le mutating table vient de :

Code :

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

SELECT personne_id INTO v_personne_id FROM personnes_cotisations WHERE personnes_cotisations_id = :new.personnes_cotisations_id;

dans le trigger FIRST_TRIGGER_ON_PERS_COT

cette ligne est inutile puisque tu utilises :new.personne_id plutôt que faire un SELECT

Par contre tu as un no_data_found parce que tu utilises personne_id = :new.personnes_cotisations_id; alors que personnes_cotisations_id n'est pas renseignée dans l'insert... ce serait pas :new.personne_id plutôt ?

essaye :

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
CREATE OR REPLACE TRIGGER 
FIRST_TRIGGER_ON_PERS_COT 
  after INSERT ON personnes_cotisations
  FOR each row
declare
v_personne_id number;
v_numero_membre number;
v_numero_membre_actual number;
 
  begin
 
WHERE personnes_cotisations_id = :new.personnes_cotisations_id; 
  BEGIN
  SELECT numero_membre INTO v_numero_membre_actual FROM personnes WHERE personne_id = :new.personne_id;
  exception when no_data_found then raise_application_error(-20001,'pas de personne');
  END;
 
  IF v_numero_membre_actual = -1 then    
 
UPDATE personnes 
SET numero_membre = (SELECT max(numero_membre) + 1 FROM personnes) 
WHERE personne_id = :new.personnes_cotisations_id;
 else
  UPDATE personnes SET numero_membre = -10 WHERE personne_id = create_personnes_cotisations.personnes_cotisations_id;
 
  end IF;
  end;
/

Salut et merci pour ta réponse.

Je crois qu'il y a une petite confusion.

Si tu insères deux personnes.

la première aura le personne_id = 1000 et numero_membre = -1
la deuxième aura le personne_id = 1001 et numero_membre = -1

si après, tu souhaites ajouter une cotisation à personne_id = 1000 après avoir ajouté les deux personnes, si je lis bien ton trigger c'est personne_id = 1001 qui aura le nouveau numéro_membre et non le personne_id=1000 alors que c'est à lui que je souhaite attribuer le nouveau numéro.

J'espère que tu me suis bien là

Donc c'est pour ça que je dois récupérer le personne_id dans la table personnes_cotisations avant de pouvoir attribuer le nouveau numéro_membre à personne_id = 1000

J'espère que c'est clair ce que dis

voilà

merci beaucoup

**orafrance** · 18/01/2008, 11h02

ha oui, j'avais pas vu... bah il faut adapter mais ça doit pas être loin du compte

faudrait que tu donnes les résultats attendus à la fin des inserts en faisant un SELECT des colonnes pertinentes de toutes les tables.

**orafrance** · 18/01/2008, 11h13

de toute façon ça :

Code :

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

SELECT numero_membre INTO v_numero_membre_actual FROM personnes WHERE personne_id = :new.personnes_cotisations_id;

ça ne marche pas, personnes_cotisations_id n'étant pas renseigné dans l'INSERT

Faut débugger mais là c'est plus notre boulot

**mnitu** · 18/01/2008, 13h35

Envoyé par Stessy

J'utilise APEX comme environnement de développement.

Et je peux prouver que j'ai un problème de table mutante si j'execute un trigger simplement sur un after insert.

Maintenant je ne sais pas comment travaille APEX derrière pour insérer de nouvelles données.

Je viens à l'instant de faire un test en utilisant uniquement un seul trigger 'after insert of each row' et j'ai un problème de table mutante.

Maintenant si tu dis que tous les documents sur les problèmes de table mutante sont mauvais, ce serait bien que tu argumentes et montres un exemple de best practices pour résoudre le problème.

Voilà

Merci

Ce n’est pas APEX qui écrit les triggers mais bien c’est toi.

Concernant la critique des exemples je l'ai déjà fait sur ce forum il va falloir rechercher. Le lien qui montre les bons exemples je l’ai fourni il suffit de cliquer.

J’ai oublié à signaler que le trigger qui utilise le Select Max … est conçu pour une application mono - utilisateur parce que comme il manque le verrouillage de la table sur laquelle se calcule le Max rien ne pouvait empêcher que deux utilisateurs différentes obtiens la même valeur. Ce seulement une question de type "à quel moment cela va arriver".

Si ma remarque concernant l’utilisation du Nvl n’a pas été très claire alors il faut savoir que Select Max … from Table Into Variable Where False renvoie NULL et que NULL + 1 = NULL.

Bon courage et si j'arrive à me trouver le temps ce WE je vais debuger le code.

**Michel SALAIS** · 18/01/2008, 20h51

Envoyé par Stessy

Salut orafrance,

je suis obligé de d'utiliser le max + 1 car les numéros doivent doivent se suivre, donc pas de séquence.

je viens de faire le test avec ton trigger et comme prévu j'ai un problème de table mutante.

Voilà

Merci

Mais le code que j'ai vu ici n'est pas correct si plusieurs utilisateurs sont censés insérer des données d'une façon concurrente et la proposition d'une table avec une seule colonne et une seule ligne pour tenir la valeur max est meilleure. D'un autre côté, y a-t-il des suppressions de lignes?

-- Edition
Désolé, j'ai réagi avant de regarder jusqu'au bout et j'ai donc formulé la même remarque que mnitu

Trigger on insert ne fonctionne pas

PL/SQL Oracle

Discussions similaires

Partager

Partager