Discussion :

[Sergejack]

J'ai mis au point une méthode assez sympa (imo) pour gérer le casse tête que peut être les suppression en cascade (récursion).

Inspiré par l'Orienté Object, je voulais avoir quelque chose de flexible du genre "la table Y est liée* à la table X, le code (procedures, triggers, ...) concernant X ne doit pas être impacté par Y et pourtant à la suppression de lignes de X, une action automatique est nécessaire sur Y".

* "liée" signifiant que Y a une ou plusieurs Foreign Key vers X

La façon la plus facile de rajouter individuellement et indépendamment des automatismes à la suppression d'une ligne, est le TRIGGER AFTER.

ex:

Code :

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

1
2
3
4
 
CREATE TRIGGER trigger01 ON X AFTER DELETE ...
CREATE TRIGGER trigger02 ON X AFTER DELETE ...
CREATE TRIGGER trigger03 ON X AFTER DELETE ...

Mais le problème est que les contraintes révérencielles ne peuvent pas être traités après suppression de la table parent (même dans un TRIGGER AFTER).

Donc, le TRIGGER AFTER directement sur la table parent, ça ne fonctionne pas.

Pour pallier à cela, il faut commencer par créer une nouvelle table X_ID qui contienne une copie de tous les identifiants de X.
Ensuite, il faut créer les TRIGGER AFTER sur cette table.
Et finalement, il faut créer un TRIGGER INSTEAD sur la table X, où l'on ne fait qu'une seule chose avant de supprimer les lignes de X : supprimer les lignes correspondantes de X_ID.

L'agencement des différentes opérations lors d'une supresion dans X devient alors.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
	DELETE FROM X WHERE ...
	=> déclenchement du trigger INSTEAD OF sur X
		DELETE X_ID FROM X_ID INNER JOIN deleted ...
		=> déclenchement des triggers AFTER sur X_ID
			* trigger01
			* trigger02
			* trigger03
		<= fin des triggers
 
		DELETE X FROM X INNER JOIN deleted 
	<= fin du trigger

Voici un exemple complet de code

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
 
	create table tTest20140128 (
		id int not null primary key
	)
	go
 
	create table tTest20140128_Child01 (
		id int not null primary key references tTest20140128(id) on delete no action
	)
	go
 
	create table tTest20140128_Child02 (
		id int not null primary key references tTest20140128(id) on delete no action
	)
	go
 
	insert tTest20140128 values (1), (2), (3)
	insert tTest20140128_Child01 values (1), (2), (3)
	insert tTest20140128_Child02 values (1), (2), (3)
	go
 
	delete from tTest20140128 -- Error
	go
 
	-- table for holding copies of ids
	create table tTest20140128_ID (
		id int not null primary key references tTest20140128(id) on delete no action
	)
	go
 
	insert tTest20140128_ID
	select id from tTest20140128
	go
 
	-- trigger that keeps tTest20140128_ID up to date for new ids
	create trigger tTest20140128_AFINS
	on tTest20140128
	after insert
	as
	begin
		insert tTest20140128_ID
		select id from inserted
	end
	go
 
	-- Instead of delete (keeps tTest20140128_ID up to date for deleted ids)
	create trigger tTest20140128_IODEL
	on tTest20140128
	instead of delete
	as
	begin
		delete ID
		from deleted AS D
		inner join tTest20140128_ID AS ID ON (
			ID.id = D.id
		)
 
		delete from T
		from deleted AS D
		inner join tTest20140128 AS T on (
			T.id = D.id
		)
	end
	go
 
	-- Sorta "attching listeners to event" 
	-- tTest20140128_Child01
	create trigger tTest20140128_tTest20140128_Child01
	on tTest20140128_ID
	after delete
	as
	begin
		delete T
		from deleted as D
		inner join tTest20140128_Child01 AS T on (
			T.id = D.id
		)
	end
	go
 
	-- tTest20140128_Child02
	create trigger tTest20140128_tTest20140128_Child02
	on tTest20140128_ID
	after delete
	as
	begin
		delete T
		from deleted as D
		inner join tTest20140128_Child02 AS T on (
			T.id = D.id
		)
	end
	go
 
	-- New tests
	insert tTest20140128 values (4), (5), (6)
	insert tTest20140128_Child01 values (4), (5), (6)
	insert tTest20140128_Child02 values (4), (5), (6)
	go
 
	select COUNT (*) as [COUNT after insert] from tTest20140128
	go
 
	delete from tTest20140128 -- No Error
	go
 
	select COUNT (*) as [COUNT" after delete] from tTest20140128
	go
 
	drop table tTest20140128_ID
	drop table tTest20140128_Child02
	drop table tTest20140128_Child01
	drop table tTest20140128

[aieeeuuuuu]

Bonjour et merci de partager votre code,

Je me pose quelques questions :

Quel est l’intérêt par rapport à un ON DELETE CASCADE.?

Par ailleurs, je ne comprend pas l’intérêt de la table X_ID. Pourquoi ne pas supprimer dans la/les tables fille directement dans le declencheur "instead of" de la table mère ?

Merci

[Sergejack]

ON DELETE CASCADE n'est pas toujours accepté par SQL Server dont les mécanismes de préventions de récursion (cycles) est hyper sensible.

Quant à l'intérêt de ne pas tout faire dans le trigger instead of, c'est de conserver l'indépendance du code.

Sinon, si tu as 20 tables liés à X, l'instead of "fourre-tout" sera une vraie foire.
Et si ton collègue Gérard Menrézon en rajoute une 21-ième il pourrait triturer ton trigger jusqu'à licenciement.

Alors que si les 20 tables ont chacun juste un trigger AFTER sur X_ID, c'est plus lisible et maintenable.
Ensuite si Gérard Menrézon en rajoute une 21-ième, il ne touchera pas à ton instead of, ni à tes 20 triggers AFTER.

Bref, l'intérêt est d'isoler et de conserver la spécialisation tes opérations ainsi que de les figer dans le temps.