Эта статья была опубликована на сайте www.optim.ru
Журнал Технология клиент/сервер №3, 2000

Триггеры INSTEAD OF

В SQL Server 2000 появился новый вид триггеров – триггер INSTEAD OF. Триггер INSTEAD OF выполняется вместо какого-либо действия (INSERT, UPDATE, DELETE). Такие триггеры могут быть определены и для view, что позволяет изменить логику обновления view или делать обновляемыми не обновляемые view. По сути, такой триггер позволяет выполнить некий скрипт Transact SQL вместо физического изменения базовых таблиц. Вы можете вручную изменять данные таблиц БД, реализуя логику любой сложности. Отсутствие такой функциональности ставилось в укор Microsoft такими оппонентами, как Oracle.

Microsoft ввел еще один вид триггера – AFTER, но он ничем не отличается от триггеров, которые применялись в предыдущих версиях этой РСУБД, и по умолчанию используется именно он. Единственной альтернативой AFTER является INSTEAD OF. Microsoft так и не ввел аналога BEFORE-триггерам. А жаль, ведь все ограничения проверяются до запуска триггеров, и в случае их нарушения триггеры просто не запускаются. Кстати, это утверждение верно и для INSTEAD OF-триггеров. Это не дает вмешаться в логику записи изменений до проверки декларативных ограничений.

Если разобраться по существу, то AFTER вообще не соответствует действительности, так как у SQL Server в триггере доступны виртуальные таблицы inserted и deleted. С их помощью в триггере можно установить, какое состояние было в БД до выполнения действия, запустившего триггер и после, а такие триггеры соответствуют понятию ВОВРЕМЯ (On). По уму, Microsoft надо было просто добавить возможность выполнять триггер до проверки декларативных ограничений или после. Тогда надобность в BEFORE-триггерах вообще отпала бы, а пока есть два решения:

• Снятие декларативных ограничений и реализация всех проверок в триггерах.

• Перенести бизнес-логику и сложную логику данных в сервер приложений.

Примеры триггеров INSTEAD OF приведены в Листингах 3 и 4.

Листинг 3  
INSTEAD OF - триггер (instead)

CREATE TRIGGER dbo.trg region ins ON Region
INSTEAD OF INSERT
AS
BEGIN
-- Process records that require an update
UPDATE Region SET
Region.RegionDescription - inserted.RegionDescription
FROM Region INNER JOIN inserted
ON Region.RegionID - inserted.RegionID
-- Process records that require an insert
INSERT INTO Region
SELECT inserted.*
FROM inserted LEFT JOIN Region
ON inserted.RegionID - Region.RegionID
WHERE Region.RegionID IS NULL
END
GO
CREATE TRIGGER dbo.trg territories ins ON Territories
INSTEAD OF INSERT
AS
BEGIN
-- Process records that require an update
UPDATE Territories SET
Territories.TerritoryDescription - inserted.TerritoryDescription,
Territories.RegionID - inserted.RegionID
FROM Territories INNER JOIN inserted
ON Territories.TerritoryID - inserted.TerritoryID
-- Process records that require an insert
INSERT INTO Territories
SELECT inserted.*
FROM inserted LEFT JOIN Territories
ON inserted.TerritoryID - Territories.TerritoryID
WHERE Territories.TerritoryID IS NULL
END
GO
 

Листинг 4  
INSTEAD OF - триггер (update)

CREATE TRIGGER dbo.trg employeename update ON EmployeeName
INSTEAD OF UPDATE
AS
BEGIN
UPDATE Employees SET
FirstName - SUBSTRING(inserted.EmployeeName, 1,
(CHARINDEX(' '. inserted.Employeename) - 1)),
LastName - SUBSTRING(inserted.EmployeeName,
(CHARINDEX(' ', inserted.EmployeeName) + 1),
DATALENGTH(inserted.EmployeeName))
FROM Employees INNER JOIN inserted
ON Employees.EmployeeId - inserted.EmployeeId
END
GO

Каскадная ссылочная целостность

При задании декларативной ссылочной целостности появилась возможность добавлять выражения ON UPDATE CASCADE и ON DELETE CASCADE, что приводит к каскадному изменению связанных таблиц. Опытным разработчикам БД это вряд ли что даст, поскольку инструменты типа ERWin давно позволяют реализовать подобную функциональность с помощью триггеров, причем более гибко, чем то, что можно сделать декларативным способом. Однако все, что объявлено декларативно, автоматически становится метаданными, доступными другим приложениям. Это выгодно отличает такой способ от эмуляции функциональности с помощью триггеров.

Проще всего показать действие этой новой возможности на примере. Рассмотрим определения внешних ключей в таблице Order Details к таблицам Orders и Products БД Northwind:

ALTER TABLE dbo.[Order Details] ADD
CONSTRAINT FK_Order_Details_Orders FOREIGN KEY
(OrderID) REFERENCES dbo.Orders(OrderID),
CONSTRAINT FK_Order_Details_Products FOREIGN KEY
(ProductID) REFERENCES dbo.Products (ProductID)

Это определение предотвращает операции над Orders и Products, приводящие к появлению «потерянных» записей в таблице Order Details заказов. В новом стиле это можно записать с помощью нового ограничения NO ACTION, и наш пример будет выглядеть следующим образом:

ALTER TABLE dbo.[Order Details] ADD
CONSTRAINT FK_Order_Details_Orders FOREIGN KEY
(OrderID) REFERENCES dbo.Orders(OrderID)
ON UPDATE NO ACTION
ON DELETE NO ACTION,
CONSTRAINT FK_Order_Details_Products FOREIGN KEY
(ProductID) REFERENCES dbo.Products (ProductID)
ON UPDATE NO ACTION
ON DELETE NO ACTION

Но с точки зрения здравого смысла такое ограничение в данных условиях не слишком удачно. Если изменить код продукта, может потребоваться изменить этот код во всех заказах. Лучше всего это делать каскадно для всех связанных записей. Аналогично, если заказ удален, нужно удалить и соответствующие ему записи в Order Details. Теперь это просто:

ALTER TABLE dbo.[Order Details] ADD
CONSTRAINT FK_Order_Details_Orders FOREIGN KEY
(OrderID) REFERENCES dbo.Orders(OrderID)
ON UPDATE NO ACTION
ON DELETE CASCADE,
CONSTRAINT FK_Order_Details_Products FOREIGN KEY
(ProductID) REFERENCES dbo.Products (ProductID)
ON UPDATE CASCADE
ON DELETE NO ACTION

Расширения порядка сортировки

При установке SQL Server 7.0 было необходимо указать кодовую страницу по умолчанию и порядок сортировки. Таким образом, все базы данных были связаны с определенной кодовой страницей и порядком сортировки. В SQL Server 2000 этот подход расширен за счет введения понятия collation — коллекции из трех свойств: порядка сортировки для Unicode-данных, порядка сортировки для не-Unicode-данных и кодовой страницы, используемой для хранения не-Unicode-строк.

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

Для русских разработчиков это приводит только к лишним проблемам (например, при экспорте структуры с помощью SQL Server Enterprise Manager), но грамотное назначение collation по умолчанию сводит проблемы к минимуму. Можно рекомендовать перед установкой SQL Server удостовериться, что в системе выбрана русская локаль. Тогда русский язык автоматически будет выбран языком SQL Server 2000 по умолчанию. Для поддержки же многоязычности лучше и проще использовать Unicode, тогда зависимость от collations будет минимальна.