Discussion :

[Monstros Velu]

Bonjour,


J'ai 2 tables (évidement, en vrai, j'ai plus de tables et c'est plus compliqué que ça) :

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
 
CREATE TABLE dbo.A
(
	IdA int NOT NULL IDENTITY PRIMARY KEY,
	Lbl varchar(50) NOT NULL UNIQUE,
	ValidFrom datetime2(7) GENERATED ALWAYS AS ROW START NOT NULL,
	ValidTo datetime2(7) GENERATED ALWAYS AS ROW END NOT NULL,
	PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo])
)
WITH
(
 SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.A_TT )
)
GO
 
CREATE TABLE dbo.B
(
	IdA int NOT NULL PRIMARY KEY FOREIGN KEY REFERENCES A(IdA),
	IdReference int NOT NULL,
	ValidFrom datetime2(7) GENERATED ALWAYS AS ROW START NOT NULL,
	ValidTo datetime2(7) GENERATED ALWAYS AS ROW END NOT NULL,
	PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo])
)
WITH
(
 SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.B_TT )
)
GO

Pour gérer les données de l'application, j'ai un type table qui sert comme paramètre

Code :

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

1
2
3
4
5
6
7
 
CREATE TYPE UDT_MyType AS TABLE(
	IdA int NOT NULL,
	IdReference int NULL,
	Lbl varchar(50)
)
GO

pour la procédure stockée permet au client d'insérer/mettre à jour/supprimer les données, en 1 seul appel

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
 
 
CREATE OR ALTER PROC TR_B_Merge_DML
@data UDT_MyType READONLY
AS
SET XACT_ABORT, NOCOUNT ON
BEGIN TRY	
BEGIN TRANSACTION
 
-- Contrôles sur @data
 
-- stockage temporaire modifiable
	SELECT * INTO #data FROM @data
 
	DECLARE @resultA AS TABLE(IdA int, Lbl varchar(50))
 
-- merge dans A
	MERGE INTO dbo.A AS b
	USING (SELECT IdA, Lbl FROM #data) AS src
		ON src.IdA = src.IdA
	WHEN MATCHED AND EXISTS (SELECT src.IdA EXCEPT SELECT b.IdA) THEN
		UPDATE SET IdA = src.IdA
	WHEN NOT MATCHED THEN
		INSERT(IdA, Lbl) VALUES(src.IdA, src.Lbl)
	WHEN NOT MATCHED BY SOURCE THEN
		DELETE
	OUTPUT Inserted.IdA, inserted.LbL INTO @resultA;
 
-- récupération des nouveaux IdA
	UPDATE a
	SET IdA = r.IdA
	FROM #data AS a
		INNER JOIN @resultA r
			ON a.Lbl = r.Lbl
	WHERE a.IdA IS NULL
 
--utilisation de #a pour merge les infos dans B	
-- ...	je vous passe ce détail	
 
COMMIT TRANSACTION
END TRY
BEGIN CATCH
 
	IF (XACT_STATE()) = -1  
    BEGIN
        ROLLBACK TRANSACTION;  
    END; 
 
	THROW;
 
END CATCH;
GO

Voici le problème :
La foreign key B(IdA) qui référence A(IdA) empêche la suppression de lignes dans A.

J'ai pensé au delete en cascade, mais toutes mes tables sont historisées (temporal tables), je ne peux donc pas l'utiliser (SQL Server 2016).
Du coup, je vois 2 possibilités :

1. Trigger : Je peux mettre un trigger sur A qui fera la suppression. Si le nombre de ligne supprimé n'est pas trop grand, c'est jouable.
   De plus, comme je supprime les données, ça ne peut pas être un "after delete", donc un "instead delete". Donc j'empêche la suppression des données pour les supprimer ensuite ? Pas joli tout ça...
2. Merge différencié : Je fais un premier merge pour insert/update sur A. Puis je merge dans B. Puis je supprime dans A. Si le nombre de lignes dans A n'est pas trop grand, je peux me permettre 2 passes sur la table.

Je n'ai pas d'idée de quelle est la meilleure solution. Ni s'il en existe un autre.

Pouvez-vous me donner votre avis, s'il-vous-plait ?

[Monstros Velu]

Je crois que j'ai partiellement résolu mon dilemme :
"Impossible de créer un déclencheur sur une table temporelle avec version gérée par le système «*maDB.dbo.A*»."