Главная » Microsoft SQL Server, Базы данных » Обработка транзакций консолидации

0

Хранимые процедуры идеально подходят для работы с денормализованными консолидированными данными. Типичным примером такой задачи является складская система, которая записывает все операции в специальную таблицу, вычисляет остатки, а затем записывает полученные значения в главную складскую таблицу с целью повышения производительности.

Чтобы защитить целостность такой складской таблицы, с помощью триггеров должны поддерживаться следующие правила.

?               Складская таблица не должна обновляться никаким процессом, кроме триггеров таблицы складских операций. Любая попытка непосредственной записи в складскую таблицу должна немедленно отражаться в таблице операций как корректировка вручную.

?               При вставке любой записи в таблицу операций должны немедленно корректироваться остатки в складской таблице.

?               Таблица складских операций не должна допускать обновления существующих строк.

Учебную базу данных OBXKites можно рассматривать как пример упрощенной складской системы. Чтобы продемонстрировать обработку транзакций консолидации, реализуем описанные выше правила с помощью следующих триггеров.

С целью тестирования мы вначале создадим пример складской позиции с помощью следующего сценария:

USE OBXKites

DECLARE

@ProdID Uniqueidentifier,

@LocationID Uniqueidentifier SELECT @ProdID = ProductID FROM dbo.Product WHERE Code = 1001 SELECT @LocationID= LocationID FROM dbo.Location WHERE LocationCode = ‘CH’

INSERT dbo.Inventory (ProductID, InventoryCode, LocationID)

VALUES (OProdlD,’Al’, @LocationID)

SELECT Product.Code, InventoryCode, QuantityOnHand FROM dbo.Inventory JOIN dbo.Product

ON Inventory.ProductID = Product.ProductID

Получим следующий результат:

Code InventoryCode QuantityOnHand

1001               A1    0

Триггер таблицы складских операций

Триггер складской операции использует итоговую функцию для подсчета текущих остатков для таблицы Inventory. Как только в таблицу InventoryTransaction вставляется какая- либо строка, этот триггер обновляет таблицу Inventory. Объединение между таблицей образов Inserted и Inventory позволяет триггеру обслуживать многострочные вставки:

CREATE TRIGGER InvTrans_Aggregate ON dbo.InventoryTransaction AFTER Insert

AS

UPDATE dbo.Inventory SET QuantityOnHand

= Inventory.QuantityOnHand + Inserted.Value FROM dbo.Inventory JOIN Inserted

ON Inventory.InventorylD = Inserted.InventorylD

Return

Следующий пакет тестирует триггер InvTrans_Aggregate, вставляя складскую операцию и обозревая результаты в обеих рассматриваемых таблицах:

INSERT InventoryTransaction (InventoryID, Value)

SELECT InventorylD, 5 FROM dbo.Inventory WHERE InventoryCode = ‘Al’

INSERT InventoryTransaction (InventoryID, Value)

SELECT InventorylD, -3 FROM dbo.Inventory WHERE InventoryCode = 1Al1

INSERT InventoryTransaction (InventoryID, Value)

SELECT InventorylD, 7 FROM dbo.Inventory WHERE InventoryCode = 1Al1

Следующий запрос извлекает данные из таблицы InventoryTransaction:

SELECT InventoryCode, Value

FROM dbo.InventoryTransaction JOIN dbo.Inventory

ON Inventory.InventorylD

= Inventorytransaction.InventorylD

Получим следующий результат:

InventoryCode Value

Al        5

Al        -3

Al        7

Триггер InvTrans_Aggregate должен был сформировать корректное значение остатков в таблице Inventory. Следующий запрос поможет нам проверить, все ли он сделал правильно:

SELECT Product.Code, InventoryCode, QuantityOnHand FROM dbo.Inventory JOIN dbo.Product

ON Inventory.ProductID = Product.ProductID

Получим следующий результат:

Code InventoryCode QuantityOnHand

1001               Al     9

Триггер складской таблицы

С количественными значениями таблицы Inventory, в принципе, нельзя работать напрямую. Любая корректировка количества товаров на складе должна проходить через таблицу складских операций. В то же время некоторым пользователям может понадобиться вручную внести коррективы в складские остатки. Лучшим выходом из данной ситуации является использование серверного программного кода для формирования корректной операции, независимо от метода, использованного пользователем. Таким образом, триггер складской таблицы должен перенаправлять непосредственные обновления таблицы Inventory в таблицу InventoryTransaction, в то же время позволяя триггеру InvTrans_Aggregate обновлять таблицу Inventory.

Естественно, лучше, чтобы триггер мог обслуживать многострочные обновления. Главной целью является отмена исходной инструкции UPDATE при одновременном сохранении достаточного количества данных для вставки операций в таблицу InventoryTransact ion.

Откат такой операции UPDATE невозможен, поскольку это сотрет данные в таблицах образов Inserted и Deleted, равно как и во временных таблицах, созданных в триггере. В то же время значения не могут быть сохранены в локальных переменных, так как такая техника не позволит обслуживать многострочные операции.

Решение состоит в том, чтобы переписать еще не обновленные значения из таблицы Deleted назад в таблицу Inventory. Тогда разницу между полями Quant it yOnHand таблиц Deleted и Inventory можно записать в таблицу InventoryTransaction как отдельную ручную операцию.

Данный триггер выполняется только в том случае, когда обновляется значение поля Quant it yOnHand и триггер вызывается пользовательской инструкцией DML. Если же данное поле обновляется ранее рассмотренным триггером InvTrans_Agregate, то уровень вложенности (т.е. результат функции NestLevel ()) будет больше единицы. Ниже приведен текст триггерного решения складской задачи со стороны таблицы Inventory.

CREATE TRIGGER Inventory_Aggregate ON Inventory AFTER UPDATE AS

– Перенаправление прямых обновлений

If Trigger_NestLevel() = 1 AND Update(QuantityOnHand)

BEGIN

UPDATE Inventory

SET QuantityOnHand = Deleted.QuantityOnHand FROM Deleted

JOIN dbo.Inventory

ON Inventory.InventorylD = Deleted.InventorylD INSERT InventoryTransaction (Value, InventorylD)

SELECT

Inserted.QuantityOnHand – Inventory.QuantityOnHand,

Inventory.InventorylD FROM dbo.Inventory JOIN Inserted

ON Inventory.InventorylD = Inserted.InventorylD

END

Чтобы продемонстрировать работу триггера, следующая инструкция UPDATE попытается изменить складские остатки с 9 на 10. Новый триггер Inventory_Aggregate перехватит эту инструкцию и восстановит значение остатков 9, в то же время создавая новую операцию +1 в таблице InventoryTransaction. (Если таблица операций имеет столбцы типа операции и комментарии, то можно пометить эту операцию как ручную.) После этого триггер InvTrans_Aggregate таблицы InventoryTransaction увидит инструкцию INSERT и скорректирует поле Quant ityOnHand таблицы Inventory в значение 10.

– Тестирование триггера Update dbo.Inventory

SET QuantityOnHand = 10 Where InventoryCode = ‘Al’

Сформировав ручную операцию, с помощью следующего запроса проверим содержимое таблицы InventoryTransaction:

SELECT InventoryCode, Value

FROM dbo.InventoryTransaction

JOIN dbo.Inventory

ON Inventory.InventorylD

= Inventorytransaction.InventorylD

Как мы видим, ручное увеличение количества товара на единицу было успешно записано: InventoryCode Value

А1    5

А1   -3

А1    7

А1    1

А теперь посмотрим, скорректировал ли триггер InvTrans_Aggregate значение остатков в таблице Inventory:

SELECT Product.Code, InventoryCode, QuantityOnHand FROM dbo.Inventory JOIN dbo.Product

ON Inventory.ProductID = Product.ProductID

Получим следующий результат:

Code InventoryCode QuantityOnHand

1001                   A1      10

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии