Comment supprimer un field qui référence un parent sans cascade delete

**randriano** · 06/11/2008, 15h22

Bonjour tout le monde!

En gros, j'ai la base suivante: 3 tables dont une référence les 2 autres (1 avec cascade delete et l'autre non)

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
create table RANDR.RIJA_ENTITY (
	"ID" varchar2(38),--equivalent UNIQUEIDENTIFIER
	"DONNEE" varchar2(50),
	CONSTRAINT "PK_RIJA_ENTITY" PRIMARY KEY(ID) 
/
create table RANDR.RIJA_WIRES(
	"ID" varchar2(38)  ,
	"DONNEE" varchar2(50),
	CONSTRAINT "PK_RIJA_WIRES" PRIMARY KEY(ID) 
/
create table RANDR.RIJA_LNK_WIRES (
	"ID" varchar2(38) ,
	"IDWIRE" varchar2(38) NOT NULL,
	"IDEQUIPMENT" varchar2(38) NOT NULL,
	CONSTRAINT "PK_RIJA_LNK_WIRES" PRIMARY KEY(ID) 
/
ALTER TABLE RANDR.RIJA_LNK_WIRES  ADD 	CONSTRAINT FK_666
 FOREIGN KEY (IDWire)	REFERENCES RANDR.RIJA_WIRES(ID)
/
ALTER TABLE RANDR.RIJA_LNK_WIRES ADD 	CONSTRAINT FK_777
 FOREIGN KEY (IDEquipment)	REFERENCES RANDR.RIJA_ENTITY(ID) ON DELETE CASCADE
/
insert into RANDR.RIJA_Entity(ID,donnee) values('{E8D441F7-6ACB-4CF3-B537-DD074C960193}','Entity1')
/
insert into RANDR.RIJA_Entity(ID,donnee) values('{5404F834-3338-4D08-90CA-04BDA66809FE}','Entity2')
/
insert into RANDR.RIJA_Wires(ID,donnee) values('{4D67A994-E42B-4B3C-BD06-B26CB4A11086}','WireA')
/
insert into RANDR.RIJA_Wires(ID,donnee) values('{3ABF4812-62ED-40DC-8ECE-A591E17B72AE}','WireB')
/
insert into RANDR.RIJA_LNK_Wires(ID,IDWire,IDEquipment) values('{FFBF4812-62ED-40DC-8ECE-A591E17B72AE}','{4D67A994-E42B-4B3C-BD06-B26CB4A11086}','{E8D441F7-6ACB-4CF3-B537-DD074C960193}')
/
insert into RANDR.RIJA_LNK_Wires(ID,IDWire,IDEquipment) values('{00BF4812-62ED-40DC-8ECE-A591E17B72AE}','{3ABF4812-62ED-40DC-8ECE-A591E17B72AE}','{5404F834-3338-4D08-90CA-04BDA66809FE}')
/
CREATE  OR REPLACE TRIGGER RANDR.A_D_RIJA_LNK_Wires
AFTER DELETE ON RANDR.RIJA_LNK_Wires
for each row
Begin
	DELETE FROM RIJA_Wires WHERE ID = :old.IDWire ;
end;

Mais

Code :

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

1
2
3
4
5
6
7
delete from RANDR.RIJA_Entity where donnee = 'Entity1'
1 row deleted.
 
delete from RANDR.RIJA_Wires where donnee = 'WireB'
*
ERROR at line 1:
ORA-02292: integrity constraint (RANDR.FK_666) violated - child record found

Je comprends que la DELETE n°2 a causé cette erreur car on supprime un field parent qui est référencé par un field fils
Mais je ne comprends pas la DELETE n°1 qui est à 100 % OK alors qu'il y a "delete from RANDR.RIJA_Wires where donnee = 'WireB'" exécuté par le trigger
J'avais eu peur qu'il y aura l'erreur mutating table mais il n'y en a pas!!

**DAB.cz** · 06/11/2008, 18h15

"delete from RANDR.RIJA_Wires where donnee = 'WireB'" est exécuté par le trigger? Où? Comment?
C'étais sûrement éxecuté par le client.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SQL> select * from rija_entity;
{E8D441F7-6ACB-4CF3-B537-DD074C960193} Entity1
{5404F834-3338-4D08-90CA-04BDA66809FE} Entity2
 
SQL> select * from rija_wires;
{4D67A994-E42B-4B3C-BD06-B26CB4A11086} WireA
{3ABF4812-62ED-40DC-8ECE-A591E17B72AE} WireB
 
SQL> select * from RIJA_LNK_WIRES;
{FFBF4812-62ED-40DC-8ECE-A591E17B72AE} {4D67A994
{00BF4812-62ED-40DC-8ECE-A591E17B72AE} {3ABF4812

Code :

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

1
2
3
4
SQL> DELETE FROM RIJA_Entity WHERE donnee = 'Entity1'
  2  ;
 
1 row deleted.

Code :

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

1
2
3
4
5
6
7
8
SQL> select * from rija_entity;
{5404F834-3338-4D08-90CA-04BDA66809FE} Entity2
 
SQL> select * from rija_wires;
{3ABF4812-62ED-40DC-8ECE-A591E17B72AE} WireB
 
SQL> select * from RIJA_LNK_WIRES;
{00BF4812-62ED-40DC-8ECE-A591E17B72AE} {3ABF4812-6

Ça marche bien je croix.

**randriano** · 07/11/2008, 07h05

Il y a un trigger AFTER DELETE sur RIJA_LNK_Wires qui contient un "delete from RANDR.RIJA_Wires where ID ..." mais cela ne cause aucun problème

**DAB.cz** · 07/11/2008, 07h45

Alors, pas de problème...

**randriano** · 07/11/2008, 09h36

Envoyé par DAB.cz

Alors, pas de problème...

Pas de problème mais je ne comprends pas pourquoi y a pas de problème pour ce trigger alors que si j'exécute ce DELETE directement (SQL Plus) j'ai l'erreur:

Code :

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

1
2
3
4
DELETE FROM RANDR.RIJA_Wires WHERE donnee = 'WireB'
*
ERROR at line 1:
ORA-02292: integrity constraint (RANDR.FK_666) violated - child record found

La base que j'ai montré au 1er post n'est qu'une partie du test, en fait, il y a encore une table de plus liée selon l'image:
Nom : TABLE RIJA WIRES.png
Affichages : 228
Taille : 16,4 Ko

Nom : TABLE RIJA WIRES.png
Affichages : 228
Taille : 16,4 Ko

Alors là il y a un problème

Code :

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

1
2
3
4
5
6
delete from RANDR.RIJA_Entity where donnee = 'Entity1'
                  *
ERROR at line 1:
ORA-04091: table RANDR.RIJA_WIRES is mutating, trigger/function may not see it
ORA-06512: at "RANDR.A_D_RIJA_LNK_WIRES", line 2
ORA-04088: error during execution of trigger 'RANDR.A_D_RIJA_LNK_WIRES'

Je remarque que cette structure a été inspirée d'une base MSSQL où elle n'a aucun soucis!

**DAB.cz** · 07/11/2008, 10h54

Envoyé par randriano

Pas de problème mais je ne comprends pas pourquoi y a pas de problème pour ce trigger alors que si j'exécute ce DELETE directement (SQL Plus) j'ai l'erreur:

Code :

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

1
2
3
4
DELETE FROM RANDR.RIJA_Wires WHERE donnee = 'WireB'
*
ERROR at line 1:
ORA-02292: integrity constraint (RANDR.FK_666) violated - child record found

Au première cas (DELETE FROM RANDR.RIJA_Entity WHERE donnee = 'Entity1') l'enregistrement référencé (RIJA_LNK_WIRES) a été déjà supprimé.

Ajutant cette ligne (WireA - Entity2):

Code :

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

1
2
3
4
5
INSERT INTO RIJA_LNK_Wires(ID,IDWire,IDEquipment) VALUES(
  '{00000000-0000-0000-0000-000000000000}',
  '{4D67A994-E42B-4B3C-BD06-B26CB4A11086}',
  '{5404F834-3338-4D08-90CA-04BDA66809FE}')
/

on peut avoir l'erreur:

Code :

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

1
2
3
4
DELETE FROM RIJA_Entity WHERE donnee = 'Entity1'
*
ERROR na řádku 1:
ORA-02292: integritní omezení (TEST.FK_666) porušeno - nalezen podřízený záznam

**DAB.cz** · 07/11/2008, 11h05

Envoyé par randriano

La base que j'ai montré au 1er post n'est qu'une partie du test, en fait, il y a encore une table de plus liée selon l'image:
Nom : TABLE RIJA WIRES.png
Affichages : 228
Taille : 16,4 Ko

Alors là il y a un problème

Code :

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

1
2
3
4
5
6
delete from RANDR.RIJA_Entity where donnee = 'Entity1'
                  *
ERROR at line 1:
ORA-04091: table RANDR.RIJA_WIRES is mutating, trigger/function may not see it
ORA-06512: at "RANDR.A_D_RIJA_LNK_WIRES", line 2
ORA-04088: error during execution of trigger 'RANDR.A_D_RIJA_LNK_WIRES'

Je remarque que cette structure a été inspirée d'une base MSSQL où elle n'a aucun soucis!

Maintenant, c'est un peu trop compliqué. Est-ce possible d'éviter les triggers?

Ask Tom - The Trouble with Triggers

**randriano** · 07/11/2008, 12h17

Envoyé par DAB.cz

Maintenant, c'est un peu trop compliqué. Est-ce possible d'éviter les triggers?

Et bien c'est ce que je souhaite, je suis à la recherche de solutions (sauf A_D_RIJA_LNK_Wires qui est utile pour respecter la spécification), je me pose même la question pourquoi le problème de la table en mutation n'apparaît qu'avec Oracle

J'ai utilisé des triggers dans le but de faire comme si tous ces FK sont tous en CASCADE DELETE mais si c'est le cas j'aurais plein de mutating table

Comment faire si je supprime dans RIJA_Entity, tous les champs liés (fils) seront supprimés jusqu'à la table la plus fille qui est RIJA_LNK_Wires?? Forcément les triggers after delete mais encore comment

**DAB.cz** · 07/11/2008, 13h04

Je vois deux solutions:
a/ écrire la procédure "delete_entity" qui va supprimer de RIJA_ENTITY, RIJA_WIRES, ... (à la place de commande "delete from")
b/ à déclencheur A_D_RIJA_LNK_Wires: déposer les enregistrements (RIJA_WIRES) dans la table temporaire et les supprimer par déclencheur au niveau "statement" (article)

**DAB.cz** · 07/11/2008, 13h15

tutoriel sur developpez.com

**randriano** · 07/11/2008, 15h31

J'ai utilisé une procédure pour supprimer à partir de RIJA_Wires

Code :

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

1
2
3
4
5
6
7
CREATE  OR REPLACE procedure rija_SupprWire (v_ID varchar2)
AS
BEGIN
 
  delete from RIJA_LNK_Wires where IDWire = v_ID;
 
  DELETE FROM EED_OBJ_Wires WHERE ID = v_ID;

Je regroupe dans une table les fields supprimés afin de les exploiter dans un trigger statement comme tu dis

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 table RANDR.RIJA_DELETED_LNK_WIRES (
	"ID" varchar2(38) ,
	"IDWIRE" varchar2(38) NOT NULL,
	"IDEQUIPMENT" varchar2(38) NOT NULL,
	CONSTRAINT "PK_RIJA_DELETED_LNK_WIRES" PRIMARY KEY(ID) 
/
CREATE  OR REPLACE TRIGGER RANDR.B_D_RIJA_LNK_Wires
BEFORE DELETE ON RANDR.RIJA_LNK_Wires
Begin
	DELETE FROM RIJA_DELETED_LNK_WIRES;
end ;
/
CREATE  OR REPLACE TRIGGER RANDR.A_D_ROW_RIJA_LNK_Wires
AFTER DELETE ON RANDR.RIJA_LNK_Wires
FOR EACH ROW
Begin
	INSERT INTO RIJA_DELETED_LNK_WIRES VALUES(:old.ID, :old.IDWire, :old.IDEquipment);
end ;
/
CREATE  OR REPLACE TRIGGER RANDR.A_D_RIJA_LNK_Wires
AFTER DELETE ON RANDR.RIJA_LNK_Wires
Begin
 
	DELETE FROM RIJA_Wires WHERE ID IN (SELECT IDWire FROM RIJA_DELETED_LNK_WIRES);
 
end ;

ET BEN, LE DELETE A PARTIR DE RIJA_Entity MARCHE AUSSI SANS SOUCIS

Merci DAB, vous êtes un sauveur!!! Mais je ne sais pas si c'est ça la table temporaire dont tu parles!

**DAB.cz** · 07/11/2008, 16h01

Code :

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

CREATE GLOBAL TEMPORARY TABLE RANDR.RIJA_DELETED_LNK_WIRES ...

**randriano** · 10/11/2008, 08h50

A propos de la global temporary table, je ne pense que ça marche ici car en fait, j'ai toujours besoin de cette table par seulement une fois ou pendant une session!!

**DAB.cz** · 10/11/2008, 17h33

Envoyé par randriano

A propos de la global temporary table, je ne pense que ça marche ici car en fait, j'ai toujours besoin de cette table par seulement une fois ou pendant une session!!

Code :

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

create global temporary table... on commit preserve|delete rows

doc

**randriano** · 11/11/2008, 09h28

pour le global temporary table!!!

Et je veux seulement remarquer une chose que j'ai vue avec SQL SERVER
- on a 2 tables: l'une parente et l'autre fille qui se référence par clé foreign
- SANS delete cascade (pour une certaine raison)
- pour éviter l'erreur d'intégrité référentielle
- il y a un trigger AFTER DELETE (FOR DELETE pour Mssql) qui contient une DELETE FROM TABLE_FILLE WHERE ... sur la table parente

J'ai effectué la même chose avec ORACLE mais le fait que c'est AFTER DELETE ne rend pas les choses possibles!
Pourquoi ça marche pour SQL Server?

**randriano** · 12/11/2008, 07h38

J'ai effectué la même chose avec ORACLE mais le fait que c'est AFTER DELETE ne rend pas les choses possibles!
Pourquoi ça marche pour SQL Server?

Ca marche pour SQL SERVER mais MsSql n'est pas ORacle !!!

Solution: faire toujours la suppression dans la table fille avant celle de la table référencée! Dans une procédure stockée par ex!

Comment supprimer un field qui référence un parent sans cascade delete

SQL Oracle

Discussions similaires

Partager

Partager