Обробка транзакцій консолідації

Збережені процедури ідеально підходять для роботи з денормалізованнимі консолідованими даними Типовим прикладом такого завдання є складська система, яка записує всі операції в спеціальну таблицю, обчислює залишки, а потім записує отримані значення в головну складську таблицю з метою підвищення продуктивності

Щоб захистити цілісність такої складської таблиці, за допомогою тригерів повинні підтримуватися наступні правила

■ Складська таблиця не повинна оновлюватися ніяким процесом, крім тригерів таблиці складських операцій Будь-яка спроба безпосереднього запису в складську таблицю повинна негайно відбиватися в таблиці операцій як коригування вручну

■ При вставці будь-якого запису в таблицю операцій повинні негайно коригуватися залишки в складської таблиці

■ Таблиця складських операцій не повинна допускати оновлення існуючих рядків

Навчальну базу даних OBXKites можна розглядати як приклад спрощеної складської системи Щоб продемонструвати обробку транзакцій консолідації, реалізуємо описані вище правила за допомогою наступних тригерів

З метою тестування ми спочатку створимо приклад складської позиції за допомогою наступного сценарію:

USE OBXKites

DECLARE

@ProdID Uniqueidentifier,

@LocationID Uniqueidentifier SELECT @ProdID = ProductID FROM dboProduct WHERE Code = 1001 SELECT @LocationID= LocationID FROM dboLocation WHERE LocationCode = CH

INSERT dboInventory (ProductID, InventoryCode, LocationID)

VALUES (OProdlD,Al, @LocationID)

SELECT ProductCode, InventoryCode, QuantityOnHand FROM dboInventory JOIN dboProduct

ON InventoryProductID = ProductProductID

Отримаємо наступний результат:

Code InventoryCode QuantityOnHand

1001               A1    0

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

Тригер складської операції використовує підсумкову функцію для підрахунку поточних залишків для таблиці Inventory Як тільки в таблицю InventoryTransaction вставляється яка-або рядок, цей тригер оновлює таблицю Inventory Обєднання між таблицею образів Inserted і Inventory дозволяє триггеру обслуговувати багаторядкові вставки:

CREATE TRIGGER InvTrans_Aggregate ON dboInventoryTransaction AFTER Insert

AS

UPDATE dboInventory SET QuantityOnHand

= InventoryQuantityOnHand + InsertedValue FROM dboInventory JOIN Inserted

ON InventoryInventorylD = InsertedInventorylD

Return

Наступний пакет тестує тригер InvTrans_Aggregate, вставляючи складську операцію і оглядаючи результати в обох розглянутих таблицях:

INSERT InventoryTransaction (InventoryID, Value)

SELECT InventorylD, 5 FROM dboInventory WHERE InventoryCode = Al

INSERT InventoryTransaction (InventoryID, Value)

SELECT InventorylD, -3 FROM dboInventory WHERE InventoryCode = 1Al1

INSERT InventoryTransaction (InventoryID, Value)

SELECT InventorylD, 7 FROM dboInventory WHERE InventoryCode = 1Al1

Наступний запит витягує дані з таблиці InventoryTransaction:

SELECT InventoryCode, Value

FROM dboInventoryTransaction JOIN dboInventory

ON InventoryInventorylD

= InventorytransactionInventorylD

Отримаємо наступний результат:

InventoryCode Value

Al        5

Al        -3

Al        7

Тригер InvTrans_Aggregate мав сформувати коректне значення залишків у таблиці Inventory Наступний запит допоможе нам перевірити, чи все він зробив правильно:

SELECT ProductCode, InventoryCode, QuantityOnHand FROM dboInventory JOIN dboProduct

ON InventoryProductID = ProductProductID

Отримаємо наступний результат:

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 = DeletedQuantityOnHand FROM Deleted

JOIN dboInventory

ON InventoryInventorylD = DeletedInventorylD INSERT InventoryTransaction (Value, InventorylD)

SELECT

InsertedQuantityOnHand – InventoryQuantityOnHand,

InventoryInventorylD FROM dboInventory JOIN Inserted

ON InventoryInventorylD = InsertedInventorylD

END

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

– Тестування тригера Update dboInventory

SET QuantityOnHand = 10 Where InventoryCode = Al

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

SELECT InventoryCode, Value

FROM dboInventoryTransaction

JOIN dboInventory

ON InventoryInventorylD

= InventorytransactionInventorylD

Як ми бачимо, ручне збільшення кількості товару на одиницю було успішно записано: InventoryCode Value

А1 5

А1 -3

А1 7

А1 1

А тепер подивимося, скорегував Чи тригер InvTrans_Aggregate значення залишків у таблиці Inventory:

SELECT ProductCode, InventoryCode, QuantityOnHand FROM dboInventory JOIN dboProduct

ON InventoryProductID = ProductProductID

Отримаємо наступний результат:

Code InventoryCode QuantityOnHand

1001&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp A1      10

Джерело: Нільсен, Пол Microsoft SQL Server 2005 Біблія користувача : Пер з англ – М: ООО ІД Вільямс , 2008 – 1232 с : Ил – Парал тит англ

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*