Конструирование триггеров INSTEAD OF

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

Триггер INSTEAD OF может выполнять следующие действия:

  • пропускать части пакета;

  • не обрабатывать часть пакета и сохранять строки с неполадками в журнале;

  • выполнять альтернативное действие в случае ошибки.

    ПримечаниеПримечание

    Триггеры 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

См. также

Основные понятия