Discussion :

[Kropernic]

Hello à tous,

J'ai une procédure qui fait un merge (d'une table sur base d'un tvp passé en paramètre) suivi d'un delete. Le souci est que j'ai également un index unique filtré sur cette table et du coup, dans certains cas, le merge génère une erreur vis-à-vis de cette index unique alors qu'en fait, la situation serait corrigée par le delete.

Vu que j'ai besoin de cet index pour assuré la cohérence des données au cas où un p'tit malin irait joué avec des requêtes ad-hoc dans la db, je cherche un moyen de concilier ces deux instructions (merge et delete) en une seule (j'imagine un merge).

J'ai bien vu qu'on pouvait mettre une clause delete dans le merge mais je n'ai pas trouvé comment faire sans supprimer de la table tout ce qui n'est pas dans le tvp...

Voici donc le code de la procédure (que j'ai édulcoré des passages qui ne concernent pas cette table) (j'ai remis le code complet car je me suis rendu compte qu'une solution possible avec la version édulcorée aurait été de mettre le delete avant le merge mais alors, il pourrait y avoir une violation de clef étrangère pour la table S_PROMO.T_PRODUCT_LIST_DEMO_PLD (à moins de mettre on delete cascade mais bon...)) :

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
ALTER PROCEDURE [S_PROMO].[UP_PROMO_SAVE_DEMO]
    @PRM_ID                INT,
    @DETAILS_DEMO        DETAILS_DEMO READONLY,
    @BA_ID                TINYINT,
    @PRODUCTS            PRODUCT_LIST READONLY,
    @PCT_ID                TINYINT
AS
BEGIN
    SET NOCOUNT ON;
 
    SET XACT_ABORT ON;
 
    BEGIN TRAN
 
    DECLARE @T_DTD AS TABLE(DTD_ID INT NOT NULL);
 
    MERGE S_PROMO.T_DETAIL_DEMO_DTD AS TARGET
    USING (    SELECT  CONT_ID, DTD_PERCENT, DTD_DESC, DTD_NOTE
            FROM    @DETAILS_DEMO ) AS SOURCE(CONT_ID, DTD_PERCENT, DTD_DESC, DTD_NOTE)
        ON    TARGET.PRM_ID = @PRM_ID
        AND    TARGET.CONT_ID = SOURCE.CONT_ID
        AND TARGET.DTD_PERCENT = SOURCE.DTD_PERCENT
    WHEN MATCHED THEN
        UPDATE    SET    DTD_PERCENT = SOURCE.DTD_PERCENT,
                    DTD_DESC    = SOURCE.DTD_DESC,
                    DTD_NOTE    = SOURCE.DTD_NOTE,
                    PCT_ID        = @PCT_ID
    WHEN NOT MATCHED THEN    
        INSERT(PRM_ID, CONT_ID, DTD_PERCENT, DTD_DESC, DTD_NOTE, BA_ID, PCT_ID)
        VALUES(@PRM_ID, SOURCE.CONT_ID, SOURCE.DTD_PERCENT, SOURCE.DTD_DESC, SOURCE.DTD_NOTE, @BA_ID, @PCT_ID)
        OUTPUT INSERTED.DTD_ID INTO @T_DTD;
 
    MERGE S_PROMO.T_PRODUCT_LIST_DEMO_PLD AS TARGET
    USING (    SELECT    DTD.DTD_ID,
                    P.PLD_BARCODE,
                    P.PLD_INCLUSIVE,
                    P.PLD_COLOR,
                    P.PLD_SIZE,
                    P.PLD_DESC,
                    P.PLD_PRICE
            FROM    @PRODUCTS P
                        INNER JOIN S_PROMO.T_DETAIL_DEMO_DTD DTD
                            ON    DTD.PRM_ID = @PRM_ID
                            AND    DTD.CONT_ID = P.CONT_ID
                            AND DTD.DTD_PERCENT = P.DTD_PERCENT) AS SOURCE(DTD_ID, PLD_BARCODE, PLD_INCLUSIVE, PLD_COLOR, PLD_SIZE, PLD_DESC, PLD_PRICE)
        ON    TARGET.DTD_ID = SOURCE.DTD_ID
        AND TARGET.PLD_BARCODE = SOURCE.PLD_BARCODE
    WHEN MATCHED THEN
        UPDATE    SET    PLD_INCLUSIVE = SOURCE.PLD_INCLUSIVE,
                    PLD_COLOR = SOURCE.PLD_COLOR,
                    PLD_SIZE = SOURCE.PLD_SIZE,
                    PLD_DESC = SOURCE.PLD_DESC,
                    PLD_PRICE = SOURCE.PLD_PRICE
    WHEN NOT MATCHED THEN
        INSERT(DTD_ID, PLD_BARCODE, PLD_INCLUSIVE, PLD_COLOR, PLD_SIZE, PLD_DESC, PLD_PRICE)
        VALUES(SOURCE.DTD_ID, SOURCE.PLD_BARCODE, SOURCE.PLD_INCLUSIVE, SOURCE.PLD_COLOR, SOURCE.PLD_SIZE, SOURCE.PLD_DESC, SOURCE.PLD_PRICE);
 
    DELETE FROM S_PROMO.T_PRODUCT_LIST_DEMO_PLD
    FROM    S_PROMO.T_PRODUCT_LIST_DEMO_PLD PLD
                INNER JOIN S_PROMO.T_DETAIL_DEMO_DTD DTD
                    ON    PLD.DTD_ID = DTD.DTD_ID
                    AND DTD.PRM_ID = @PRM_ID
    WHERE    PLD.DTD_ID IN (SELECT DTD_ID FROM @T_DTD)
        AND NOT EXISTS (SELECT    1
                        FROM    @PRODUCTS P
                        WHERE    P.CONT_ID = DTD.CONT_ID
                            AND    P.DTD_PERCENT = DTD.DTD_PERCENT
                            AND P.PLD_BARCODE = PLD.PLD_BARCODE);
 
    DELETE FROM S_PROMO.T_DETAIL_DEMO_DTD
    FROM    S_PROMO.T_DETAIL_DEMO_DTD DTD
                INNER JOIN @DETAILS_DEMO DTL
                    ON    DTD.CONT_ID = DTL.CONT_ID
                    AND DTD.PRM_ID = @PRM_ID
    WHERE    NOT EXISTS (SELECT    1
                        FROM    @DETAILS_DEMO D
                        WHERE    D.CONT_ID = DTD.CONT_ID
                            AND D.DTD_PERCENT = DTD.DTD_PERCENT);
 
    COMMIT TRAN;

Le ddl de la table en question :

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
CREATE TABLE [S_PROMO].[T_DETAIL_DEMO_DTD](
    [DTD_ID] [int] IDENTITY(-2147483648,1) NOT NULL,
    [PRM_ID] [int] NOT NULL,
    [CONT_ID] [int] NOT NULL,
    [DTD_PERCENT] [decimal](4, 2) NOT NULL,
    [DTD_DESC] [varchar](200) NULL,
    [DTD_NOTE] [varchar](200) NULL,
    [BA_ID] [tinyint] NOT NULL,
    [DTD_ENCODED] [bit] NOT NULL,
    [PCT_ID] [tinyint] NULL,
    [DTD_CREATED_ON] [datetime] NOT NULL,
    [DTD_CREATED_BY] [varchar](100) NOT NULL,
    [DTD_MODIFIED_ON] [datetime] NULL,
    [DTD_MODIFIED_BY] [varchar](100) NULL,
 CONSTRAINT [PK_T_DETAIL_DEMO_DTD] PRIMARY KEY CLUSTERED 
(
    [DTD_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [AK_T_DETAIL_DEMO_DTD] UNIQUE NONCLUSTERED 
(
    [PRM_ID] ASC,
    [CONT_ID] ASC,
    [DTD_PERCENT] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO
 
ALTER TABLE [S_PROMO].[T_DETAIL_DEMO_DTD] ADD  CONSTRAINT [DF_T_DETAIL_DEMO_DTD_DTD_ENCODED]  DEFAULT ((0)) FOR [DTD_ENCODED]
GO
 
ALTER TABLE [S_PROMO].[T_DETAIL_DEMO_DTD] ADD  CONSTRAINT [DF_T_DETAIL_DEMO_DTD_PCT_ID]  DEFAULT ((0)) FOR [PCT_ID]
GO
 
ALTER TABLE [S_PROMO].[T_DETAIL_DEMO_DTD] ADD  CONSTRAINT [DF__T_DETAIL___DTD_C__40E5634A]  DEFAULT (getdate()) FOR [DTD_CREATED_ON]
GO
 
ALTER TABLE [S_PROMO].[T_DETAIL_DEMO_DTD] ADD  CONSTRAINT [DF__T_DETAIL___DTD_C__41D98783]  DEFAULT (suser_sname()) FOR [DTD_CREATED_BY]
GO
 
ALTER TABLE [S_PROMO].[T_DETAIL_DEMO_DTD]  WITH CHECK ADD  CONSTRAINT [FK_DTD_TO_BA] FOREIGN KEY([BA_ID])
REFERENCES [dbo].[T_BUREAU_ACHAT_BA] ([BA_ID])
GO
 
ALTER TABLE [S_PROMO].[T_DETAIL_DEMO_DTD] CHECK CONSTRAINT [FK_DTD_TO_BA]
GO
 
ALTER TABLE [S_PROMO].[T_DETAIL_DEMO_DTD]  WITH CHECK ADD  CONSTRAINT [FK_DTD_TO_CONT_ID] FOREIGN KEY([CONT_ID])
REFERENCES [S_CONTRAT].[T_CONTRAT_DEMO_CONT] ([CONT_ID])
GO
 
ALTER TABLE [S_PROMO].[T_DETAIL_DEMO_DTD] CHECK CONSTRAINT [FK_DTD_TO_CONT_ID]
GO
 
ALTER TABLE [S_PROMO].[T_DETAIL_DEMO_DTD]  WITH CHECK ADD  CONSTRAINT [FK_DTD_TO_PCT] FOREIGN KEY([PCT_ID])
REFERENCES [S_PROMO].[T_PERCENT_TYPE_PCT] ([PCT_ID])
GO
 
ALTER TABLE [S_PROMO].[T_DETAIL_DEMO_DTD] CHECK CONSTRAINT [FK_DTD_TO_PCT]
GO
 
ALTER TABLE [S_PROMO].[T_DETAIL_DEMO_DTD]  WITH CHECK ADD  CONSTRAINT [FK_DTD_TO_PRM] FOREIGN KEY([PRM_ID])
REFERENCES [S_PROMO].[T_PROMO_PRM] ([PRM_ID])
GO
 
ALTER TABLE [S_PROMO].[T_DETAIL_DEMO_DTD] CHECK CONSTRAINT [FK_DTD_TO_PRM]
GO

Le ddl de l'index unique filtré :

Code :

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

1
2
3
4
5
6
7
8
CREATE UNIQUE NONCLUSTERED INDEX [UQ_PRM_ID_CONT_ID] ON [S_PROMO].[T_DETAIL_DEMO_DTD]
(
    [PRM_ID] ASC,
    [CONT_ID] ASC
)
WHERE ([PCT_ID]=(0))
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Et pour être sûr d'être complet, le ddl du tvp :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TYPE [S_PROMO].[DETAILS_DEMO] AS TABLE(
    [CONT_ID] [int] NOT NULL,
    [DTD_PERCENT] [decimal](4, 2) NOT NULL,
    [DTD_DESC] [varchar](50) NULL,
    [DTD_NOTE] [varchar](50) NULL,
    PRIMARY KEY CLUSTERED 
(
    [CONT_ID] ASC,
    [DTD_PERCENT] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO

[Kropernic]

J'ai opté pour le déplacement de l'instruction DELETE en premier avec ajout de la clause ON DELETE CASCADE sur la contrainte d'intégrité référentielle.

Cependant, je laisse ouvert car je voudrais savoir s'il y a moyen de réécrire le merge pour y intégrer le delete et donc se passer de la cascade... (même si ça fait joli dans le paysage et que ça impressionnera sûrement mon boss)