Конструирование триггеров INSTEAD OF
Главное преимущество триггеров INSTEAD OF в том, что они позволяют поддерживать обновления для таких представлений, которые обновлять невозможно. Чтобы представление на основе нескольких базовых таблиц поддерживало вставку, обновление и удаление данных в нескольких таблицах, следует использовать триггер INSTEAD OF. Другое преимущество триггера INSTEAD OF состоит в том, что он обеспечивает логику кода, при которой можно отвергать одни части пакета и принимать другие.
Триггер INSTEAD OF может выполнять следующие действия:
пропускать части пакета;
не обрабатывать часть пакета и сохранять строки с неполадками в журнале;
выполнять альтернативное действие в случае ошибки.
.gif "Примечание")
ПримечаниеТриггеры INSTEAD OF DELETE и INSTEAD OF UPDATE нельзя использовать в таблицах с внешним ключом, определенным с помощью цепочки действий DELETE или UPDATE.
Кодирование этой логики в составе триггера INSTEAD OF позволяет всем приложениям, которые работают с данными, не реализовывать эту логику повторно.
Пример
В приведенной ниже последовательности инструкций языка Transact-SQL триггер INSTEAD OF обновляет две базовых таблицы из представления. Кроме того, показаны следующие подходы к обработке ошибок:
Вставка повторяющихся данных в таблицу Person игнорируется, вставляемые данные записываются в таблицу PersonDuplicates.
Вставка одинаковых значений в таблицу EmployeeTable заменяется на инструкцию UPDATE, которая возвращает текущие данные в таблицу EmployeeTable без формирования нарушения повторяющихся ключей.
Инструкции языка Transact-SQL создают две базовые таблицы, представление, таблицу для записи ошибок, а также триггер INSTEAD OF в представлении. В следующих таблицах раздельно хранятся сведения о сотрудниках и бизнес-сведения. Эти таблицы являются базовыми для представления.
CREATE TABLE Person
(
SSN char(11) PRIMARY KEY,
Name nvarchar(100),
Address nvarchar(100),
Birthdate datetime
)
CREATE TABLE EmployeeTable
(
EmployeeID int PRIMARY KEY,
SSN char(11) UNIQUE,
Department nvarchar(10),
Salary money,
CONSTRAINT FKEmpPer FOREIGN KEY (SSN)
REFERENCES Person (SSN)
)
В следующем представлении показаны все данные о сотруднике из двух таблиц.
CREATE VIEW Employee AS
SELECT P.SSN as SSN, Name, Address,
Birthdate, EmployeeID, Department, Salary
FROM Person P, EmployeeTable E
WHERE P.SSN = E.SSN
Можно записывать попытки вставить строки с одинаковыми номерами социального страхования. В таблицу PersonDuplicates записываются вставленные значения, имя пользователя, который выполнил вставку, а также время вставки.
CREATE TABLE PersonDuplicates
(
SSN char(11),
Name nvarchar(100),
Address nvarchar(100),
Birthdate datetime,
InsertSNAME nchar(100),
WhenInserted datetime
)
Триггер INSTEAD OF вставляет строки в несколько базовых таблиц из одного представления. Попытки вставить строки с одинаковыми номерами социального страхования записываются в таблицу PersonDuplicates. Одинаковые строки в таблице EmployeeTable заменяются инструкциями обновления.
CREATE TRIGGER IO_Trig_INS_Employee ON Employee
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
-- Check for duplicate Person. If there is no duplicate, do an insert.
IF (NOT EXISTS (SELECT P.SSN
FROM Person P, inserted I
WHERE P.SSN = I.SSN))
INSERT INTO Person
SELECT SSN,Name,Address,Birthdate
FROM inserted
ELSE
-- Log an attempt to insert duplicate Person row in PersonDuplicates table.
INSERT INTO PersonDuplicates
SELECT SSN,Name,Address,Birthdate,SUSER_SNAME(),GETDATE()
FROM inserted
-- Check for duplicate Employee. If no there is duplicate, do an INSERT.
IF (NOT EXISTS (SELECT E.SSN
FROM EmployeeTable E, inserted
WHERE E.SSN = inserted.SSN))
INSERT INTO EmployeeTable
SELECT EmployeeID,SSN, Department, Salary
FROM inserted
ELSE
--If there is a duplicate, change to UPDATE so that there will not
--be a duplicate key violation error.
UPDATE EmployeeTable
SET EmployeeID = I.EmployeeID,
Department = I.Department,
Salary = I.Salary
FROM EmployeeTable E, inserted I
WHERE E.SSN = I.SSN
END