Neutraliser trigger imbriqués (mais non récursifs)

**StringBuilder** · 18/02/2021, 16h39

Bonjour,

J'utilise Microsoft SQL Server 2014 (SP2-CU9) (KB4055557) - 12.0.5563.0 (X64) Dec 7 2017 01:00:06 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

J'ai des traitements effectués par des triggers, je et me trouve dans une situation où je souhaite que selon l'origine des modifications de certaines données, certains triggers se déclenchent ou non.

Pour faire "simple" (*sic*) j'ai trois tables A, B et C, dont j'historise certaines modifications dans une quatrième table LOG.

[code=sql]

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
 
/*
Pour rejouter le script
 
drop table LOG;
drop table C;
drop table B;
drop table A;
go
*/
 
/*
Statuts :
	1 - Créé
	2 - Gagné
	3 - Perdu
*/
 
create table A
(
     id int identity primary key,
     name char(10),
     statut tinyint
);
 
create table B
(
     id int identity primary key,
     name char(12),
     statut tinyint,
     a_id int references A(id)
);
 
create table C
(
     id int identity primary key,
     name char(14),
     statut tinyint,
     b_id int references B(id)
);
 
create table LOG
(
     id int identity primary key,
     a_id int not null references A(id),
	 a_old_statut tinyint not null,
	 a_new_statut tinyint not null,
     b_id int null references B(id), 
	 b_old_statut tinyint null,
	 b_new_statut tinyint null,
     c_id int null references C(id),
	 c_old_statut tinyint null,
	 c_new_statut tinyint null,
	 timestamp datetime2 not null default CURRENT_TIMESTAMP
);
go
 
set IDENTITY_INSERT A ON
insert into A (id, name, statut) values (1, 'Affaire 1', 1), (2, 'Affaire 2', 1), (3, 'Affaire 3', 1);
set IDENTITY_INSERT A OFF
 
set IDENTITY_INSERT B ON
insert into B (id, name, statut, a_id) values (1, 'Dossier A1D1', 1, 1), (2, 'Dossier A1D2', 1, 1), (3, 'Dossier A1D3', 1, 1),
                                              (4, 'Dossier A2D1', 1, 2), (5, 'Dossier A2D2', 1, 2), (6, 'Dossier A2D3', 1, 2),
                                              (7, 'Dossier A3D1', 1, 3), (8, 'Dossier A3D2', 1, 3), (9, 'Dossier A3D3', 1, 3)
set IDENTITY_INSERT B OFF
 
set IDENTITY_INSERT C ON
insert into C (id, name, statut, b_id) values (1, 'Etude A1D1E1', 1, 1), (2, 'Etude A1D1E2', 1, 1), (3, 'Etude A1D1E3', 1, 1), 
											  (4, 'Etude A1D2E1', 1, 1), (5, 'Etude A1D2E2', 1, 1), (6, 'Etude A1D2E3', 1, 1), 
											  (7, 'Etude A1D3E1', 1, 1), (8, 'Etude A1D3E2', 1, 1), (9, 'Etude A1D3E3', 1, 1),
                                              (10, 'Etude A2D1E1', 1, 2), (11, 'Etude A2D1E2', 1, 1), (12, 'Etude A2D1E3', 1, 1), 
											  (13, 'Etude A2D2E1', 1, 2), (14, 'Etude A2D2E2', 1, 1), (15, 'Etude A2D2E3', 1, 1), 
											  (16, 'Etude A2D3E1', 1, 2), (17, 'Etude A2D3E2', 1, 1), (18, 'Etude A2D3E3', 1, 1),
                                              (19, 'Etude A3D1E1', 1, 3), (20, 'Etude A3D1E2', 1, 1), (21, 'Etude A3D1E3', 1, 1), 
											  (22, 'Etude A3D2E1', 1, 3), (23, 'Etude A3D2E2', 1, 1), (24, 'Etude A3D2E3', 1, 1), 
											  (25, 'Etude A3D3E1', 1, 3), (26, 'Etude A3D3E2', 1, 1), (27, 'Etude A3D3E3', 1, 1)
set IDENTITY_INSERT C OFF
go

J'ai des règles de gestion (en réalité bien plus complexes) qui permettent de mettre à jour les statuts de A et B en fonction de C, en cascade.

Jusqu'à présent les seules modifications possibles de statut se faisaient dans C.

J'avais donc écrit un trigger de ce genre :

Code sql :

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
 
create trigger TRG_C on C after update
as
begin
	if (rowcount_big() > 0 and update(statut))
	begin
		create table #tmp_log (
			 a_id int not null,
			 a_old_statut tinyint not null,
			 a_new_statut tinyint null,
			 b_id int null, 
			 b_old_statut tinyint null,
			 b_new_statut tinyint null,
			 c_id int null,
			 c_old_statut tinyint null,
			 c_new_statut tinyint null
		);
 
		-- Historisation des statuts qu'ils étaient avant déclenchement du trigger
		insert into #tmp_log
		select a.id, a.statut, null, b.id, b.statut, null, i.id, d.statut, i.statut
		from inserted i
		inner join deleted d on d.id = i.id and d.statut <> i.statut
		inner join b on b.id = i.b_id
		inner join a on a.id = b.a_id;
 
		-- Mise à jour de B à partir du changement de statut de C
		update b
		set statut = z.statut
		from inserted i
		inner join b on b.id = i.b_id
		inner join (select b_id, max(statut) statut from c group by b_id) z on z.b_id = b.id;
 
		-- Mise à jour de A à partir du changement de statut de B en cascade à partir de C
		update a
		set statut = z.statut
		from inserted i 
		inner join b on b.id = i.b_id
		inner join a on a.id = b.a_id
		inner join (select a_id, max(statut) statut from b group by a_id) z on z.a_id = a.id;
 
		-- Mise à jour des nouveaux statuts de A et B dans la table temporaire
		update l
		set a_new_statut = a.statut, b_new_statut = b.statut
		from #tmp_log l
		inner join b on b.id = l.b_id
		inner join a on a.id = l.a_id;
 
		insert into log (a_id, a_old_statut, a_new_statut, b_id, b_old_statut, b_new_statut, c_id, c_old_statut, c_new_statut)
		select a_id, a_old_statut, a_new_statut, b_id, b_old_statut, b_new_statut, c_id, c_old_statut, c_new_statut
		from #tmp_log;
 
		drop table #tmp_log;
	end;
end;
go

Ce qui permettait d'avoir ça comme comportement :

Code sql :

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

1
2
3
4
5
6
7
8
 
update C set statut = 2 where id = 1;
update C set statut = 3 where id = 2;
update C set statut = 2 where b_id = 2;
update C set statut = 3 where id = 13;
update C set statut = 1 where statut > 1;
 
select * from log;

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
 
(1 ligne affectée)
 
(1 ligne affectée)
 
(1 ligne affectée)
 
(1 ligne affectée)
 
(1 ligne affectée)
 
(1 ligne affectée)
 
(1 ligne affectée)
 
(1 ligne affectée)
 
(1 ligne affectée)
 
(1 ligne affectée)
 
(1 ligne affectée)
 
(1 ligne affectée)
 
(3*lignes affectées)
 
(1 ligne affectée)
 
(1 ligne affectée)
 
(3*lignes affectées)
 
(3*lignes affectées)
 
(3*lignes affectées)
 
(1 ligne affectée)
 
(1 ligne affectée)
 
(1 ligne affectée)
 
(1 ligne affectée)
 
(1 ligne affectée)
 
(1 ligne affectée)
 
(5*lignes affectées)
 
(2*lignes affectées)
 
(1 ligne affectée)
 
(5*lignes affectées)
 
(5*lignes affectées)
 
(5*lignes affectées)
id          a_id        a_old_statut a_new_statut b_id        b_old_statut b_new_statut c_id        c_old_statut c_new_statut timestamp
----------- ----------- ------------ ------------ ----------- ------------ ------------ ----------- ------------ ------------ ---------------------------
1           1           1            2            1           1            2            1           1            2            2021-02-18 15:25:27.3200000
2           1           2            3            1           2            3            2           1            3            2021-02-18 15:25:27.3333333
3           1           3            3            2           1            2            16          1            2            2021-02-18 15:25:27.3566667
4           1           3            3            2           1            2            13          1            2            2021-02-18 15:25:27.3566667
5           1           3            3            2           1            2            10          1            2            2021-02-18 15:25:27.3566667
6           1           3            3            2           2            3            13          2            3            2021-02-18 15:25:27.3700000
7           1           3            1            2           3            1            16          2            1            2021-02-18 15:25:27.3900000
8           1           3            1            2           3            1            13          3            1            2021-02-18 15:25:27.3900000
9           1           3            1            2           3            1            10          2            1            2021-02-18 15:25:27.3900000
10          1           3            1            1           3            1            2           3            1            2021-02-18 15:25:27.3900000
11          1           3            1            1           3            1            1           2            1            2021-02-18 15:25:27.3900000
 
(11*lignes affectées)
 
 
Heure de fin*: 2021-02-18T15:25:27.4687477+01:00

Sauf que là, finalement, on doit pouvoir aussi mettre à jour directement B, sans passer par C.

Je dois donc écrire un trigger qui va :
- Passer toutes les lignes de C au même statut que B lorsque B change de statut
- Recalculer le statut de A en fonction du nouveau statut de B

Cependant, pour cette action :

Code sql :

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

1
2
 
update B set statut = 2 where id = 2;

Je ne souhaite pas avoir la liste des changements de statut de C dans LOG, mais uniquement une ligne dans LOG avec le changement de statut de B, et les statuts de B dans c_old_statut et c_new_statut et NULL dans c_id.

Code :

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

1
2
3
4
 
id          a_id        a_old_statut a_new_statut b_id        b_old_statut b_new_statut c_id        c_old_statut c_new_statut timestamp
----------- ----------- ------------ ------------ ----------- ------------ ------------ ----------- ------------ ------------ ---------------------------
12          1           1            2            2           1            2            NULL        1            2            2021-02-18 15:28:27.3333333

Seulement, si je crée un trigger dans B qui fait ça, il va déclencher des modifications dans C, qui vont loguer aussi les modifications de C, que je ne veux pas.
Inversement, le trigger actuel sur C met à jour B, mais je ne veux pas que dans ce cas je génère une ligne depuis le trigger sur B !

Comment faire (est-ce possible) au sein de mes triggers de dire "ne déclenche pas les triggers" (si possible avec explicitement le nom des triggers en question) pendant mes mises à jour ?

Je pourrais jouer avec des variables globales, vérifier l'existence de #tmp, etc. mais est-ce qu'il y a plus propre/fiable ?

Genre :

Code sql :

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

1
2
3
4
5
6
7
8
 
                disable trigger TRG_B on B;
		update b
		set statut = z.statut
		from inserted i
		inner join b on b.id = i.b_id
		inner join (select b_id, max(statut) statut from c group by b_id) z on z.b_id = b.id;
                enable trigger TRG_B on B;

Mais uniquement dans le périmètre de ma transaction : si quelqu'un modifie B en parallèle, je ne veux surtout pas empêcher le trigger de se déclencher !

**StringBuilder** · 18/02/2021, 17h38

Bon, d'après Stackoverflow, y'a pas trop de solution propre...

Vu que j'ai besoin d'une table temporaire pour travailler dans mon trigger, j'ai retenu la solution à base de vérification de la présence de la table temporaire...

Ca fait bien ce que je veux !

Code sql :

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
114
115
116
117
118
 
create trigger TRG_C on C after update
as
begin
	if (rowcount_big() > 0 and update(statut) and OBJECT_ID('tempdb..#tmp_log') is null)
	begin
		create table #tmp_log (
			 a_id int not null,
			 a_old_statut tinyint not null,
			 a_new_statut tinyint null,
			 b_id int null, 
			 b_old_statut tinyint null,
			 b_new_statut tinyint null,
			 c_id int null,
			 c_old_statut tinyint null,
			 c_new_statut tinyint null
		);
 
		-- Historisation des statuts qu'ils étaient avant déclenchement du trigger
		insert into #tmp_log
		select a.id, a.statut, null, b.id, b.statut, null, i.id, d.statut, i.statut
		from inserted i
		inner join deleted d on d.id = i.id and d.statut <> i.statut
		inner join b on b.id = i.b_id
		inner join a on a.id = b.a_id;
 
		-- Mise à jour de B à partir du changement de statut de C
		update b
		set statut = z.statut
		from inserted i
		inner join b on b.id = i.b_id
		inner join (select b_id, max(statut) statut from c group by b_id) z on z.b_id = b.id;
 
		-- Mise à jour de A à partir du changement de statut de B en cascade à partir de C
		update a
		set statut = z.statut
		from inserted i 
		inner join b on b.id = i.b_id
		inner join a on a.id = b.a_id
		inner join (select a_id, max(statut) statut from b group by a_id) z on z.a_id = a.id;
 
		-- Mise à jour des nouveaux statuts de A et B dans la table temporaire
		update l
		set a_new_statut = a.statut, b_new_statut = b.statut
		from #tmp_log l
		inner join b on b.id = l.b_id
		inner join a on a.id = l.a_id;
 
		insert into log (a_id, a_old_statut, a_new_statut, b_id, b_old_statut, b_new_statut, c_id, c_old_statut, c_new_statut)
		select a_id, a_old_statut, a_new_statut, b_id, b_old_statut, b_new_statut, c_id, c_old_statut, c_new_statut
		from #tmp_log;
 
		drop table #tmp_log;
	end;
end;
go
 
create trigger TRG_B on B after update
as
begin
	if (rowcount_big() > 0 and update(statut) and OBJECT_ID('tempdb..#tmp_log') is null)
	begin
		create table #tmp_log (
			 a_id int not null,
			 a_old_statut tinyint not null,
			 a_new_statut tinyint null,
			 b_id int null, 
			 b_old_statut tinyint null,
			 b_new_statut tinyint null,
			 c_id int null,
			 c_old_statut tinyint null,
			 c_new_statut tinyint null
		);
 
		-- Historisation des statuts qu'ils étaient avant déclenchement du trigger
		insert into #tmp_log
		select a.id, a.statut, null, i.id, d.statut, i.statut, null, d.statut, i.statut
		from inserted i
		inner join deleted d on d.id = i.id and d.statut <> i.statut
		inner join a on a.id = i.a_id;
 
		-- Mise à jour de C à partir du changement de statut de B
		update c
		set statut = i.statut
		from inserted i
		inner join c on c.b_id = i.id;
 
		-- Mise à jour de A à partir du changement de statut de B
		update a
		set statut = z.statut
		from inserted i 
		inner join a on a.id = i.a_id
		inner join (select a_id, max(statut) statut from b group by a_id) z on z.a_id = a.id;
 
		-- Mise à jour des nouveaux statuts de A dans la table temporaire
		update l
		set a_new_statut = a.statut
		from #tmp_log l
		inner join a on a.id = l.a_id;
 
		insert into log (a_id, a_old_statut, a_new_statut, b_id, b_old_statut, b_new_statut, c_id, c_old_statut, c_new_statut)
		select a_id, a_old_statut, a_new_statut, b_id, b_old_statut, b_new_statut, c_id, c_old_statut, c_new_statut
		from #tmp_log;
 
		drop table #tmp_log;
	end;
end;
go
 
update C set statut = 2 where id = 1;
update C set statut = 3 where id = 2;
update B set statut = 3 where id = 8;
update C set statut = 2 where b_id = 2;
update C set statut = 3 where id = 13;
update C set statut = 1 where statut > 1;
update B set statut = 2 where a_id = 3;
 
select * from log;

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
 
id          a_id        a_old_statut a_new_statut b_id        b_old_statut b_new_statut c_id        c_old_statut c_new_statut timestamp
----------- ----------- ------------ ------------ ----------- ------------ ------------ ----------- ------------ ------------ ---------------------------
1           1           1            2            1           1            2            1           1            2            2021-02-18 16:36:28.2100000
2           1           2            3            1           2            3            2           1            3            2021-02-18 16:36:28.2133333
3           3           1            3            8           1            3            NULL        1            3            2021-02-18 16:36:28.2366667
4           1           3            3            2           1            2            16          1            2            2021-02-18 16:36:28.2500000
5           1           3            3            2           1            2            13          1            2            2021-02-18 16:36:28.2500000
6           1           3            3            2           1            2            10          1            2            2021-02-18 16:36:28.2500000
7           1           3            3            2           2            3            13          2            3            2021-02-18 16:36:28.2500000
8           1           3            1            2           3            1            16          2            1            2021-02-18 16:36:28.2633333
9           1           3            1            2           3            1            13          3            1            2021-02-18 16:36:28.2633333
10          1           3            1            2           3            1            10          2            1            2021-02-18 16:36:28.2633333
11          1           3            1            1           3            1            2           3            1            2021-02-18 16:36:28.2633333
12          1           3            1            1           3            1            1           2            1            2021-02-18 16:36:28.2633333
13          3           3            2            9           1            2            NULL        1            2            2021-02-18 16:36:28.2733333
14          3           3            2            8           3            2            NULL        3            2            2021-02-18 16:36:28.2733333
15          3           3            2            7           1            2            NULL        1            2            2021-02-18 16:36:28.2733333
 
(15*lignes affectées)
 
 
Heure de fin*: 2021-02-18T16:36:28.3696346+01:00

Neutraliser trigger imbriqués (mais non récursifs) [2014]

Développement SQL Server

Discussions similaires

Partager

Partager