Учет требований, связанных с совместным использованием нескольких таблиц

Преимущество ограничений CHECK заключается в том, что эти программные средства являются быстродействующими и эффективными, но, к сожалению, с их помощью нельзя реализовать все необходимые действия по обеспечению целостности данньгх. По-видимому, наиболее существенный недостаток ограничений CHECK обнаруживается после того, как возникает необходимость выполнить проверку данных с охватом нескольких таблиц.

В качестве иллюстрации рассмотрим таблицы Products и SalesOrderDetail базы данньгх AdventureWorks, а также связанную с ними таблицу SpecialOf f erProduct. Связь между этими таблицами показана на рис. 13.2.

Product (Production)

ProductID Name

SpecielOfferProduct (Sales)

SpecialOfferlD <J ProductID

rowguid

Modif iedDate

SalesOrderDetail (Sales)

9 SalesOrderlD

? SalesOrderDetaillD

CarrierTrachingNumber OrderQty ProductID 5pecialOfferID UnitPrice

UnitPriceDiscount Unelotal

ModlfiedDate ~

ProductNumber MakeFlag FinishedGoodFlag Color

SafetyStockLevel

ReorderPoint

StandardCost

ListPrice

SiseUNtMeasureCode

WeightUritMeasureCode

Weight

DaysToMdnufacture

ProductLine

Class

Style

ProductSubcategorylD

ProductHodellD

5ell5tartDate

SeUEndDate

DiscontinuedDdte

rowguid

ModifiedDate

InformationFlag

Puc. 13.2. Связь между таблицами Products, SalesOrderDetail иSpecialOfferProduct

Следует отметить, что с помощью обычных декларативных средств поддержки ссылочной целостности можно исключить возможность вставки каких-либо позиций заказа в таблицу SalesOrderDetail, если отсутствуют соответствующие значения идентификаторов товаров, ProductID, в таблице Products (доступ к информации об идентификаторах товаров предоставляется через таблицу SpecialOf ferProduct).

Однако для решения сформулированной выше задачи требуется нечто большее, чем обычные средства.

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

Указанную задачу невозможно решить с помощью ограничения CHECK, поскольку о том, что поставка товара прекращена, можно узнать только с помощью отдельной таблицы (таблицы Products), на основании данных которой должно контролироваться ограничение (распространяющееся на таблицу SalesOrderDetail). Но такую задачу несложно решить с помощью триггера. Проверим полученные результаты.

USE AdventureWorks GO

CREATE TRIGGER OrderDetailNotDiscontinued ON Sales.SalesOrderDetail AFTER INSERT, UPDATE

IF EXISTS (

SELECT True

FROM Inserted i

JOIN Production.Product p

ON i.ProductID = p.ProductID vmERE p.DiscontinuedDate IS NOT NULL

BEGIN

RAISERROR(Order Item is discontinued. Transaction Failed.16,1) ROLLBACK TRAN

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

SELECT ProductID, Name FROM Production.Product WHERE DiscontinuedDate IS NOT NULL ProductID Name

(0 row(s) affected)

Далее, можно выбрать эту строку и попытаться внести в нее изменения для проверки работы триггера:

UPDATE Production.Product

SET DiscontinuedDate = GETDATE()

WHERE ProductID = 680

После завершения этого этапа подготовки можно перейти к проверке работы триггера, поэтому приступим к следующему этапу и введем данные о товарной позиции, которые нарушают указанное ограничение целостности. Для этого достаточно воспользоваться данными о заказе, которые уже существуют в таблице SalesOrderHeader, чтобы нам не приходилось применять сложную процедуру подготовки полного заказа:

INSERT Sales.SalesOrderDetail

(SalesOrderlD, OrderQty, ProductID, SpecialOfferlD, UnitPrice, UnitPriceDiscount) VALUES

(43660, 5, 680, 1, 1431, 0)

В результате попытка ввода недопустимых данных будет отвергнута, как и следовало ожидать:

Msg 50000, Level 16, State 1, Procedure OrderDetailNotDiscontinued, Line 14 Order Item is discontinued. Transaction Failed. Msg 3609, Level 16, State 1, Line 1

The transaction ended in the trigger. The batch has been aborted.

Необходимо также учитывать, что можно было бы также при желании создать определяемое пользователем сообщение об ошибке, которое активизировалось бы вместо произвольного сообщения, используемого в команде RAISERROR.

Применение триггеров для проверки дельты обновления

Иногда интерес представляет не то, каким было или стало значение в результате обновления, а то, насколько изменилось это значение. Безусловно, подобную информацию невозможно найти в каком-либо одном столбце или таблице, но величину изменения можно вычислить с помощью триггера, воспользовавшись таблицами Inserted и Deleted.

Информация о том, что происходит в результате обновления, может применяться во многих цел51х, в частности для обеспечения защиты данных. Например, предположим, что для контроля над модификацией данных необходимо отслеживать любые изменения в информации о товарных запасах, независимо от того, кто был инициатором этих изменений (это позволяет, например, отличить непосредственно внесенные в таблицы корректировки данных о товарных запасах от изменений, которые возникают после оформления прихода или расхода товаров).

Для реализации такого замысла необходимо создать таблицу аудита и обеспечить применение таблиц Inserted и Deleted:

CREATE TABLE Production.InventoryAudit (

TransactionID int IDENTITY PRIMARY KEY,

ProductID int NOT NULL

REFERENCES Production.Product(ProductID), NetAdjustment smallint NOT NULL,

ModifiedDate datetime DEFAULT(CURRENT TIMESTAMP)

CREATE TRIGGER ProductAudit

ON Production.ProductInventory FOR INSERT, UPDATE, DELETE

INSERT INTO Production.InventoryAudit (ProductID, NetAdjustment)

SELECT COALESCE(i.ProductID, d.ProductID),

ISNULL(i.Quantity, 0) - ISNULL(d.Quantity, 0) AS NetAdjustment FROM Inserted i FULL JOIN Deleted d

ON i.ProductID = d.ProductID AND i.LocationID = d.LocationID WHERE ISNULL(i.Quantity, 0) - ISNULL(d.Quantity, 0) != 0