[oracle 9i] Un trigger qui fait planter oracle !

[isoman]

Salut,
j'ai essayé de supprimer un trigger dont je n'avais plus besoin mais bizarrement ce n'est pas faisable. A chaque fois que j'essaye de le modifier/désactiver/supprimer la requête se lance mais ça boucle indéfiniment et al fin je suis obligé de quitter (sql+ ou toad)
Que faire ?
Merci.

[7gyY9w1ZY6ySRgPeaefZ]

1- quel est ce trigger ? Code...
2- comment le supprimes tu ?
3- pourquoi tu parles d'une requête dans la suppression d'un trigger ?

[isoman]

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
CREATE OR REPLACE TRIGGER TRIG
AFTER INSERT
ON JOJO.VILLE_FOURNISSEUR
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
nbre_code_postal ville_fournisseur.CODEPOSTAL%type;
nbre_num_rue ville_fournisseur.NUM_RUE%type;
 
begin
 
SELECT count(*) INTO nbre_code_postal FROM ville WHERE codepostal=:new.codepostal
	   AND nomville=:new.nomville;
 
IF (nbre_code_postal=0)
then
	INSERT INTO ville VALUES (:new.codepostal,:new.nomville);
end IF;
 
SELECT count(*) INTO nbre_num_rue FROM adresse WHERE num_rue=:new.num_rue AND
	   adresse=:new.adresse;
IF (nbre_num_rue=0)
then
	 INSERT INTO adresse VALUES (:new.num_rue,:new.adresse);
	 INSERT INTO ville_adresse VALUES (:new.codepostal,:new.nomville,:new.num_rue,:new.adresse);
end IF;
 
end;
/

j'ai essayé de le supprimer/désactiver/modifier avec toad (via l'interface graphique et ligne de commande) et sql+ (drop trigger trig) mais ça coince toujours lors de l'execution.
3>je parle de la requête drop trigger trig;

[Yorglaa]

Salut,
nous dire "ça coince" ne nous aide pas beaucoup...
tu as quel message d'erreur ?

[orafrance]

Déja tu peux simplifier :

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
CREATE OR REPLACE TRIGGER TRIG
AFTER INSERT
ON JOJO.VILLE_FOURNISSEUR
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
nbre_code_postal ville_fournisseur.CODEPOSTAL%type;
nbre_num_rue ville_fournisseur.NUM_RUE%type;
 
begin
 
INSERT INTO ville (
SELECT :new.codepostal,:new.nomville
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM ville WHERE codepostal=:new.codepostal
	   AND nomville=:new.nomville)
);
 
SELECT count(*) INTO nbre_num_rue FROM adresse WHERE num_rue=:new.num_rue AND
	   adresse=:new.adresse;
 
INSERT INTO adresse (
SELECT :new.num_rue,:new.adresse
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM adresse WHERE num_rue=:new.num_rue AND adresse=:new.adresse)
);
 
 
IF SQL%ROWCOUNT =1 THEN
INSERT INTO ville_adresse VALUES (:new.codepostal,:new.nomville,:new.num_rue,:new.adresse);
end IF;
 
end;
/

L'idéal étant d'avoir un index unique sur VILLE(codepostal,nomville) et ADRESSE(num_rue,adresse) et gérer les exceptions :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE OR REPLACE TRIGGER TRIG
AFTER INSERT
ON JOJO.VILLE_FOURNISSEUR
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
begin
 
BEGIN
  INSERT INTO ville VALUES (:new.codepostal,:new.nomville);
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN NULL; -- ignore l'erreur de clé unique
END;
 
BEGIN
  INSERT INTO adresse VALUES (:new.num_rue,:new.adresse);
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN NULL; -- ignore l'erreur de clé unique
END;
 
IF SQL%ROWCOUNT =1 THEN
  INSERT INTO ville_adresse VALUES (:new.codepostal,:new.nomville,:new.num_rue,:new.adresse);
end IF;
 
end;
/

PS : Ca c'est faut :

Code :

1
2
nbre_code_postal ville_fournisseur.CODEPOSTAL%type;
nbre_num_rue ville_fournisseur.NUM_RUE%type;

les count sont des NUMBER, si jamais tu changes le type de tes colonnes tu risques d'avoir des soucis

[mnitu]

Envoyé par isoman

...
j'ai essayé de le supprimer/désactiver/modifier avec toad (via l'interface graphique et ligne de commande) et sql+ (drop trigger trig) mais ça coince toujours lors de l'execution.

Est-ce que par "ça coince toujours lors de l'execution" tu veut dire que la commande n'abouti jamais sans avoir une erreur ? Comme s’il y avait un lock ?

PS: Mise en forme

[laurentschneider]

c'est bien possible qu'il y ait un lock...

session 1

Code :

1
2
3
4
5
6
7
8
9
10
SQL> CREATE TABLE t (x number);
 
TABLE created.
 
SQL> CREATE TRIGGER tr after UPDATE ON t begin sys.dbms_lock.sleep(2000); end;
  2  /
 
TRIGGER created.
 
SQL> UPDATE t SET x=0;

session 2

Code :

1
2
3
4
5
6
7
8
9
10
11
12
SQL> DROP TRIGGER tr;
DROP TRIGGER tr
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
 
SQL> SELECT mode_held,LOCK_TYPE,BLOCKING_OTHERS FROM dba_locks 
WHERE lock_id1=(SELECT object_id FROM dba_objects WHERE object_name='T');
 
MODE_HELD            LOCK_TYPE                  BLOCKING_OTHERS
-------------------- -------------------------- ------------------
Row-X (SX)           DML                        NOT Blocking

PS: le titre est un peu léger, ce n'est pas Oracle qui plante, c'est ton Toad qui attend...

[isoman]

ben comme je débute je ne sais pas ce que donne un lock.Mais quand j'essaye de le dropper (via toad ou l'invite de commande) ça tourne indéfiniment et le trigger est toujours la !

[laurentschneider]

commence par sélectionner les sessions. Avec la requete que je t'ai donnée cherche s'il y a un lock sur VILLE_FOURNISSEUR et identifie la session...

[isoman]

c'est bizarre car je n'utilise qu'une seule session. Je vais essayer pour voir ce que ça donne.

[laurentschneider]

toad démarre toujours des sessions en parallèles, ferme toad et tente avec sqlplus, regarde avec select sid,program,username from v$session where username!='SYS' qui est connecté