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

Trigger on insert ne fonctionne pas

PL/SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager