Question pour remplacer le delete cascade
Bonjour,
J'ai 2 tables (évidement, en vrai, j'ai plus de tables et c'est plus compliqué que ça) :
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
|
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:
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:
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 :
- 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... - 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 ?
