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

Version imprimable

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:

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:

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!!

"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:

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:

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

Code:

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.

07/11/2008, 07h05
randriano

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
07/11/2008, 07h45
DAB.cz

Alors, pas de problème...

1 pièce(s) jointe(s)

Citation:

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:

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:
Pièce jointe 37857

Alors là il y a un problème
Code:

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!

Citation:
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:

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:

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:

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

Citation:
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:
Pièce jointe 37857

Alors là il y a un problème
Code:

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

07/11/2008, 12h17
randriano

Citation:

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
07/11/2008, 13h04
DAB.cz

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)
07/11/2008, 13h15
DAB.cz

tutoriel sur developpez.com

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

Code:

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:

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!

07/11/2008, 16h01
DAB.cz

Code:

CREATE GLOBAL TEMPORARY TABLE RANDR.RIJA_DELETED_LNK_WIRES ...
10/11/2008, 08h50
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!!
10/11/2008, 17h33
DAB.cz

Citation:

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:

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

doc
11/11/2008, 09h28
randriano

:king: 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?
12/11/2008, 07h38
randriano

Citation:

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!