Слияние и триггер - Нарушение первичного ключа

Question

У меня есть эти таблицы

CREATE TABLE [Test](
    [Id] [int] NOT NULL,
    [Value] [int] NOT NULL,
    [Id_Test_2] [int] NOT NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]



CREATE TABLE [Test_2](
    [Id_Test_2] [int] NOT NULL,
    [Value_Test_2] [int] NOT NULL,
 CONSTRAINT [PK_Test_2] PRIMARY KEY CLUSTERED 
(
    [Id_Test_2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

На тестовом столе у ​​меня есть этот триггер обновления:

CREATE TRIGGER [dbo].[Test_TriggerUpdate]
ON [dbo].[Test]

FOR UPDATE 

NOT FOR REPLICATION

AS
BEGIN

MERGE Test_2 AS Target

    USING 
        ( SELECT    D.Id_Test_2 ,
                    ( COALESCE(D.Value, 0) * 2 ) AS Value 
          FROM      Deleted D                    
        ) AS Source
    ON ( Target.Id_Test_2 = Source.Id_Test_2       )
    WHEN MATCHED 
        THEN 
            UPDATE
          SET               TARGET.Value_Test_2 = ( TARGET.Value_Test_2 - Source.Value )
    WHEN NOT MATCHED BY TARGET 
        THEN
        INSERT  (
                      Id_Test_2 ,
                      Value_Test_2

                    )
          VALUES    ( Source.Id_Test_2 ,                      
                      (Source.[Value]*(-1))
                    );



  MERGE Test_2 AS Target
    USING 
        ( SELECT    I.Id_Test_2 ,
                    ( COALESCE(I.Value, 0)
                      * 2 ) AS Value
          FROM      INSERTED I
        ) AS Source
    ON ( Target.Id_Test_2 = Source.Id_Test_2
       )
    WHEN MATCHED 
        THEN 
            UPDATE
          SET               TARGET.Value_Test_2 = ( TARGET.Value_Test_2    + Source.Value )
    WHEN NOT MATCHED BY TARGET 
        THEN
        INSERT  (
                      Id_Test_2 ,
                      Value_Test_2

                    )
          VALUES    ( Source.Id_Test_2 ,
                      Source.[Value]
                    );

END

таблица test_2 пуста и test имеет эту запись

Id  Value   Id_Test_2
1   10  1
2   20  1
3   30  2

когда я запускаю это обновление

UPDATE Test  SET VALUE= 50

у меня такая ошибка

Сообщение 2627, Уровень 14, Состояние 1, Процедура Test_TriggerUpdate, Строка 12 Нарушение ПЕРВИЧНЫЙ КЛЮЧ 'PK_Test_2'. Невозможно вставить дубликат ключа со значением (1) в "Test_2".

Возможно, это происходит, когда операция слияния вызывается с несколькими строками и вместо вызова перед INSERT и следующей операцией UPDATE она запускает два INSERT для записи 1 и 2. Что можно сделать?

Answer 1

Простой способ решить эту проблему - использовать инструкцию group by для ключа и функцию агрегирования значений для суммирования следующим образом:

ALTER TRIGGER [dbo].[Test_TriggerUpdate]
ON [dbo].[Test]

FOR UPDATE 

NOT FOR REPLICATION

AS
BEGIN

  MERGE Test_2 AS Target
    USING 
        ( SELECT    D.Id_Test_2 ,
                    sum(( COALESCE(D.Value, 0) * 2 )) AS Value
          FROM      Deleted D      
          GROUP BY  Id_Test_2
        ) AS Source
    ON ( Target.Id_Test_2 = Source.Id_Test_2 )
    WHEN MATCHED 
        THEN 
        UPDATE
          SET TARGET.Value_Test_2 = ( TARGET.Value_Test_2 - Source.Value )
    WHEN NOT MATCHED BY TARGET 
        THEN
        INSERT  ( Id_Test_2 ,
                  Value_Test_2
                )
        VALUES  ( Source.Id_Test_2 ,
                  (Source.[Value]*(-1))
                );

  MERGE Test_2 AS Target
    USING 
        ( SELECT    I.Id_Test_2 ,
                    sum(( COALESCE(I.Value, 0) * 2 )) AS Value
          FROM      INSERTED I
          GROUP BY  Id_Test_2  
        ) AS Source
    ON ( Target.Id_Test_2 = Source.Id_Test_2 )
    WHEN MATCHED 
        THEN 
        UPDATE
          SET TARGET.Value_Test_2 = ( TARGET.Value_Test_2 + Source.Value )
    WHEN NOT MATCHED BY TARGET 
        THEN
        INSERT  ( Id_Test_2 ,
                  Value_Test_2
                )
        VALUES  ( Source.Id_Test_2 ,
                  Source.[Value]
                );

END