Создание триггеров DML для обработки нескольких строк данных
Область применения:
SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure
При написании кода для триггера DML следует учитывать, что инициирующая триггер отдельная инструкция может влиять на несколько строк, а не на одну строку данных. Такой режим работы является стандартным для триггеров UPDATE и DELETE, поскольку эти инструкции зачастую касаются нескольких строк, Это поведение менее характерно для триггеров INSERT, поскольку базовая инструкция INSERT добавляет только одну строку. Тем не менее, так как допускается запуск триггера инструкцией INSERT INTO (table_name) SELECT, вставка множества строк может привести к вызову одного триггера.
Учет особенностей работы с несколькими строками особенно важен в случаях, когда функция триггера DML автоматически пересчитывает сводные значения из одной таблицы и помещает результаты в другую таблицу для временных итогов.
Примечание.
Не рекомендуется использовать курсоры в триггерах, поскольку они потенциально могут приводить к снижению производительности. Чтобы создать триггер, влияющий на несколько строк, вместо курсоров следует использовать логику, основанную на наборах строк.
Примеры
Триггеры DML в нижеприведенных примерах предназначены для хранения промежуточных итогов по столбцу в другой таблице образца базы данных AdventureWorks2022 .
А. Хранение накопительного итога для добавления одной строки
Первая версия триггера DML подходит для вставки одной строки данных, которая загружается в таблицу PurchaseOrderDetail . Инструкция INSERT активирует триггер DML, и новая строка добавляется в таблицу inserted на время выполнения триггера. Инструкция UPDATE считывает значение столбца LineTotal по строке и добавляет его к текущему значению в столбце SubTotal таблицы PurchaseOrderHeader . Предложение WHERE служит для проверки того, что обновленная строка в таблице PurchaseOrderDetail соответствует столбцу PurchaseOrderID строки в таблице inserted .
-- Trigger is valid for single-row inserts.
USE AdventureWorks2022;
GO
CREATE TRIGGER NewPODetail
ON Purchasing.PurchaseOrderDetail
AFTER INSERT AS
UPDATE PurchaseOrderHeader
SET SubTotal = SubTotal + LineTotal
FROM inserted
WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID ;
B. Сохранение промежуточных итогов при вставке нескольких строк или одной строки
При вставке нескольких строк триггер DML в примере A может не работать правильно; выражение справа от выражения присваивания в инструкции UPDATE (SubTotal + LineTotal) должно быть только одним значением, а не списком значений. Следовательно, триггер должен извлечь значение из любой отдельной строки таблицы inserted и добавить его к текущему значению SubTotal таблицы PurchaseOrderHeader для конкретного значения PurchaseOrderID . Эта операция может не иметь ожидаемого эффекта, если отдельное значение PurchaseOrderID встречается несколько раз в таблице inserted .
Для правильного обновления таблицы PurchaseOrderHeader триггер должен учитывать возможность наличия нескольких строк в таблице inserted. Этого можно добиться с помощью функции SUM , которая вычисляет итог LineTotal по группе строк таблицы inserted для каждого значения PurchaseOrderID. Функция SUM включена в коррелированный вложенный запрос (инструкцию SELECT в круглых скобках). Этот вложенный запрос возвращает одно значение для каждого значения PurchaseOrderID таблицы inserted , которое соответствует или коррелирует со значением PurchaseOrderID таблицы PurchaseOrderHeader .
-- Trigger is valid for multirow and single-row inserts.
USE AdventureWorks2022;
GO
CREATE TRIGGER NewPODetail2
ON Purchasing.PurchaseOrderDetail
AFTER INSERT AS
UPDATE Purchasing.PurchaseOrderHeader
SET SubTotal = SubTotal +
(SELECT SUM(LineTotal)
FROM inserted
WHERE PurchaseOrderHeader.PurchaseOrderID
= inserted.PurchaseOrderID)
WHERE PurchaseOrderHeader.PurchaseOrderID IN
(SELECT PurchaseOrderID FROM inserted);
Этот триггер также правильно выполняется при вставке однорядных данных, при этом сумма значений в столбце LineTotal равна значению одной строки. Однако при использовании этого триггера коррелированные вложенные запросы и IN оператор, используемый в WHERE предложении, требуют дополнительной обработки из SQL Server. Это необязательно для вставки одной строки.
В. Сохранение промежуточных итогов в зависимости от типа вставки
В триггер можно вносить изменения для подбора способа, оптимального при работе с несколькими строками. Например, в логике триггера можно использовать функцию @@ROWCOUNT, чтобы различать вставку одного или нескольких строк.
-- Trigger valid for multirow and single row inserts
-- and optimal for single row inserts.
USE AdventureWorks2022;
GO
CREATE TRIGGER NewPODetail3
ON Purchasing.PurchaseOrderDetail
FOR INSERT AS
IF @@ROWCOUNT = 1
BEGIN
UPDATE Purchasing.PurchaseOrderHeader
SET SubTotal = SubTotal + LineTotal
FROM inserted
WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID
END
ELSE
BEGIN
UPDATE Purchasing.PurchaseOrderHeader
SET SubTotal = SubTotal +
(SELECT SUM(LineTotal)
FROM inserted
WHERE PurchaseOrderHeader.PurchaseOrderID
= inserted.PurchaseOrderID)
WHERE PurchaseOrderHeader.PurchaseOrderID IN
(SELECT PurchaseOrderID FROM inserted)
END;