Risque de l'utilisation de cascade delete

Version imprimable

14/03/2008, 08h58
Lucas Panny

Risque de l'utilisation de cascade delete

Bonjour,

J'utilise une base avec des tables liées où "on delete cascade" est activé ! Ces tables possèdent alors toutes des triggers évènement after delete.
Le soucis c'est alors lors de la suppression d'un enregistrement de parent table, le trigger de chaque table (after delete) s'exécute au cours de la suppression en cascade causant "ORA-00036: maximum number of recursive SQL levels (50) exceeded" sur l'exécution de la table la plus enfant !!
Mon inquiétude se place surtout sur le fait que :

DELETE FROM most_parent_table WHERE Name = v_Value;

La condition WHERE n'est pas satisfait donc rien à supprimer, le trigger de most_parent_table ne s'exécute mais les triggers des tables enfants s'exécutent, je pense que le delete cascade s'effectue quand même non ? (jsuis sous Oracle 9i)

Question de plus :
Oracle Sql Developer permet-il d'exécuter pas à pas une procédure et un trigger ??
14/03/2008, 09h54
farenheiit

Citation:

Question de plus :
Oracle Sql Developer permet-il d'exécuter pas à pas une procédure et un trigger ??

Sql Developer je ne sais pas mais avec TOAD oui.
14/03/2008, 09h56
farenheiit

Il consiste en quoi ton trigger after delete?

Ci-dessous un exemple qui montre qq cas

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
set echo on
 
drop table journal
 
drop table journal succeeded.
drop table t1 cascade constraints
 
drop table t1 succeeded.
drop table t2
 
drop table t2 succeeded.
create table journal (trig varchar2(30), val number)
 
create table succeeded.
create table t1 (a number primary key)
 
create table succeeded.
create table t2 (a number references t1(a) on delete cascade)
 
create table succeeded.
insert into t1 values (1)
 
1 rows inserted
insert into t1 values (2)
 
1 rows inserted
insert into t2 values (1)
 
1 rows inserted
insert into t2 values (1)
 
1 rows inserted
create trigger trig1
after delete
on t1
begin
  insert into journal values('trig1', null);
end;
 
trigger trig1 Compiled.
create trigger trig2
after delete
on t2
begin
  insert into journal values('trig2', null);
end;
 
trigger trig2 Compiled.
create trigger trig3
after delete
on t2
for each row
begin
  insert into journal values('trig3', :old.a);
end;
 
trigger trig3 Compiled.
delete t1 where a = 10
 
0 rows deleted
select * from journal
 
TRIG                           VAL                    
------------------------------ ---------------------- 
trig2                                                 
trig1                                                 
 
2 rows selected
 
rollback
 
rollback succeeded.
delete t1 where a = 2
 
1 rows deleted
select * from journal
 
TRIG                           VAL                    
------------------------------ ---------------------- 
trig2                                                 
trig1                                                 
 
2 rows selected
 
rollback
 
rollback succeeded.
delete t1 where a = 1
 
1 rows deleted
select * from journal
 
TRIG                           VAL                    
------------------------------ ---------------------- 
trig3                          1                      
trig3                          1                      
trig2                                                 
trig1                                                 
 
4 rows selected

On voit bien que le trigger sur la table enfant s'exécute même si aucune ligne n'a été touchée dans cette table à partir du moment où c'est un trigger d'instruction (statement trigger). Bien évidemment, les triggers ligne (row triggers) ne sont pas exécutés dans ce cas.

14/03/2008, 10h44
Michel SALAIS

Citation:

Envoyé par Lucas Panny

Question de plus :
Oracle Sql Developer permet-il d'exécuter pas à pas une procédure et un trigger ??

Oui SQL Developer permet de le faire
14/03/2008, 13h07
Lucas Panny
Je résume
les relations entre tables sont créées ainsi avec DELETE CASCADE
Code:

1 2 ALTER TABLE MYSCHEMA.Powers ADD CONSTRAINT FK_Employee_Id FOREIGN KEY (IDC)REFERENCES MYSCHEMA.Employees(ID) ON DELETE CASCADE
On fait un delete sur une ligne d'une table parent, la ligne n'existe même pas mais le cascade s'effectue quand même comme le cas que Michel SALAIS a exposé mais il s'agit de DELETE mais pas de DROP TABLE.

La cause de l'erreur de récursivité c'est parce que le trigger after delete d'une table enfant lointaine contient un DELETE FROM de la table dont elle fait référence par foreign key !!!
Je vous ferai un petit script de la création de ma base si c'est pas clair !!

En outre, merci pour les conseils sur TOAD et SQL DEVELOPER
14/03/2008, 14h57
Lucas Panny

Comment éviter donc cette erreur de récursivité due au delete cascade ?
27/08/2008, 10h07
Lucas Panny

Comment éviter donc cette erreur de récursivité due au delete cascade ?
27/08/2008, 10h26
skuatamad

Salut,

Re regarde l'exemple de Michel Salais, tu vois bien que trig3 ne s'exécute que lorsqu'il y a suppression d'une ligne qui existe dans t2.
D'où la conclusion :

Citation:

Envoyé par Michel SALAIS

On voit bien que le trigger sur la table enfant s'exécute même si aucune ligne n'a été touchée dans cette table à partir du moment où c'est un trigger d'instruction (statement trigger). Bien évidemment, les triggers ligne (row triggers) ne sont pas exécutés dans ce cas.

Donc je pense qu'il te faut des triggers for each row, non?
09/09/2008, 16h10
Lucas Panny

Citation:

Donc je pense qu'il te faut des triggers for each row, non?

Qui confirme ??
09/09/2008, 20h08
skuatamad

Citation:

Envoyé par Lucas Panny

Qui confirme ??

Qui confirme ça pour moi :

Citation:

Envoyé par Lucas Panny

Comment éviter donc cette erreur de récursivité due au delete cascade ?

Mais j'ai peut être mal compris ton problème.

Citation:

Envoyé par Lucas Panny

Je vous ferai un petit script de la création de ma base si c'est pas clair !!

Pour moi ça semble nécessaire pour mieux comprendre, mais peut être pas pour d'autres.
11/09/2008, 08h35
Lucas Panny

Citation:

Pour moi ça semble nécessaire pour mieux comprendre, mais peut être pas pour d'autres.

En fait, c'était même pas nécessaire!

En analysant bien le code de SALAIS, j'ai tout compris quand le cascade delete se déclenche pour un trigger instruction et un trigger for each row!!!
17/09/2008, 08h41
Lucas Panny

Pour en finir, les triggers statement je les évite car il se déclenche même s'il y a aucune ligne à supprimer lors d'un DELETE. Faut savoir les utiliser!!!
02/10/2008, 15h59
Lucas Panny

Citation:

Envoyé par Lucas Panny

Pour en finir, les triggers statement je les évite car il se déclenche même s'il y a aucune ligne à supprimer lors d'un DELETE. Faut savoir les utiliser!!!

Pourquoi Oracle a choisi cette option que les triggers statement se déclenchent quand même ?
Je suis utilisateur SQL Server et je me demande pourquoi la même DB de même structure (foreign keys et triggers), il y a erreur de "mutating table" ou "recursive sql" sous Oracle alors que c'est impéc sur SQL server? N'y a-t-il jamais d'erreur lié au Cascade Delete sur Mssql?

De plus, il y a cet article sur la table en mutation http://sgbd.developpez.com/oracle/ora-04091/ qui ne semble pas marcher!!! :aie: