Странное поведение триггера

Question

У меня есть триггер (SQL 2008 R2), который выполняет одну простую операцию, но результаты не логичны. Вот обзор:

Текстовый файл подается в пакет служб SSIS с ОДНОЙ строкой (одна запись), которая загружает его в таблицу "ORDERS_IN_PROCESS". Режим доступа к данным установлен на "Таблица или представление" в "Назначении OLE DB", чтобы разрешить срабатывание триггеров.

Вот моя таблица ЗАКАЗОВ:

OrderID        ItemNo
---------    ---------
9813           1
9813           2
9813           3
9817           1

Итак, SSIS выполняется, и ORDERS_IN_PROCESS получает одну вставленную запись, которая является OrderID 9813

Триггер срабатывает:

INSERT INTO ORDERS_ARCHIVE SELECT * FROM ORDERS WHERE OrderID=INSERTED.OrderID

Довольно просто пока...

Результаты, которые я получаю в моей таблице ORDERS_ARCHIVE (идентична разметке ORDERS)

OrderId       ItemNo
---------   ----------
9813          3

Где остальные 2 позиции?

Обратите внимание: он вставил только последнюю строку, прочитанную из таблицы ORDERS, в ORDERS_ARCHIVE.

Мне нужны все 3 из них в ORDERS_ARCHIVE.

Почему это происходит?

Я полагаю, что это как-то связано с тем, как SSIS обрабатывает его с помощью "OLE DB Destination", потому что если я вставляю запись в RLFL вручную, триггер делает именно то, что должен, и вставляет все 3 записи из BACK.

Вы можете утверждать, что триггеры срабатывают один раз за партию, и я согласен, но в этом случае у меня есть партия только ОДНОЙ записи.

Я думаю о sp, но я бы не стал добавлять еще один уровень сложности для чего-то такого тривиального, якобы.

Есть идеи?

Благодарю.

Answer 1

Я не думаю, что это как-то связано с SSIS, но с вашим триггером. Вместо чем IN что у вас там, попробуйте использовать JOIN в вашем запросе:

INSERT INTO ORDERS_ARCHIVE 
SELECT O.* 
FROM ORDERS O
INNER JOIN INSERTED I
     ON O.ORderID = I.OrderID

Answer 2

Я согласен с Ламарком, ошибочно написав преднамеренное, с оценкой вашего триггера неверно.

Логика, которую вы указали для триггера, не компилируется. Предложение WHERE недопустимо. Я предполагаю, что, как и Ламак, вы собирались присоединиться на основе OrderID.

create table dbo.ORDERS_ARCHIVE
(
    OrderID int
,   ItemNo int
)
GO
create table dbo.ORDERS
(
    OrderID int
,   ItemNo int
)
GO
create trigger 
    trUpdate
ON
    dbo.ORDERS
    AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    -- This doesn't work
    -- Msg 4104, Level 16, State 1, Procedure trUpdate, Line 12
    -- The multi-part identifier "INSERTED.OrderID" could not be bound.
    --INSERT INTO dbo.ORDERS_ARCHIVE 
    --SELECT * 
    --FROM ORDERS 
    --WHERE OrderID=INSERTED.OrderID;

    -- I think you meant
    INSERT INTO dbo.ORDERS_ARCHIVE 
    SELECT * 
    FROM ORDERS 
    WHERE OrderID=(SELECT INSERTED.OrderID FROM INSERTED);
 END
 GO

Затем я сгенерировал простой пакет служб SSIS, у меня есть источник данных, который предоставляет 4 указанные вами строки и пишет в dbo.ORDERS. Я запустил пакет 2 раза, и каждая из них заняла 4 строки в таблице ORDERS_ARCHIVE. 3 ряда по 9813, по 1 по 9817 за партию.

Я получаю правильное количество строк там, поэтому я считаю, что триггер срабатывает правильно. Вместо этого происходит неправильная логика. Поскольку OrderID не является уникальным в таблице ORDERS, ядро ​​базы данных выберет первую строку, которая соответствует критериям поиска. Так уж получилось, что он выбирает одну и ту же строку (ItemNo = 1) каждый раз, но нет гарантии заказа без ORDER BY пункт, это просто случайный или артефакт того, как Двигатель выбирает, но никакого поведения, на которое я бы рассчитывал, оставаясь неизменным.

Как вы это исправите?

Исправьте курок. Присоединение к вставленной виртуальной таблице только по OrderID приводит к тому, что несколько строк удовлетворяют условию.

create trigger 
    trUpdate
ON
    dbo.ORDERS
    AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    -- This trigger will add all the rows from the ORDERS table
    -- that match what was just inserted based on OrderID and ItemNo
    INSERT INTO dbo.ORDERS_ARCHIVE 
    SELECT O.* 
    FROM dbo.ORDERS O
    INNER JOIN INSERTED I
         ON O.OrderID = I.OrderID
         AND O.ItemNo = I.ItemNo;
END

Теперь, когда я запускаю ETL, я вижу 4 строки в ORDERS_ARCHIVE с правильным ItemNo ценности.