Поделиться через


Выражения и вычисляемые столбцы в триггерах INSTEAD OF

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

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

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

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

CREATE VIEW ExpressionView
AS
SELECT *, GETDATE() AS TodaysDate
FROM AdventureWorks.HumanResources.Employee

Хотя столбец TodaysDate не ссылается ни на один из столбцов таблицы, SQL Server 2005 должен создать столбец TodaysDate в таблице inserted, которую он передает триггеру INSTEAD OF, определенному для ExpressionView. Однако столбец inserted.TodaysDate допускает значения NULL, поэтому инструкции INSERT, ссылающиеся на ExpressionView, могут не содержать значения этого столбца. Поскольку выражение не ссылается на столбец в таблице, триггер может игнорировать любые значения для столбца, предоставляемые в инструкции INSERT.

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

CREATE TABLE ComputedExample
   (
    PrimaryKey    int PRIMARY KEY,
    ComputedCol   AS SUSER_NAME()
   )

Некоторые сложные выражения могут ссылаться на несколько столбцов, например:

CREATE TABLE SampleTable
     (
      PriKey    int,
      FirstName nvarchar(20),
      LastName  nvarchar(30)
     )
GO
CREATE VIEW ConcatView
AS
SELECT PriKey, FirstName + ' ' + LastName AS CombinedName
FROM SampleTable

Выражение CombinedName в представлении ConcatView объединяет значения FirstName и LastName. Если для представления ConcatView определяется триггер INSTEAD OF INSERT, необходимо соглашение о способе передачи инструкциями INSERT значений столбца CombinedName, позволяющее триггеру определить, какую часть полученной строки необходимо поместить в столбец FirstName, а какую — в столбец LastName. При использовании соглашения о передаче инструкциями INSERT значений CombinedName, содержащих данные в виде 'first_name;last_name', следующий триггер сможет успешно отработать инструкцию INSERT:

CREATE TRIGGER InsteadSample on ConcatView
INSTEAD OF INSERT
AS
BEGIN

   INSERT INTO SampleTable
      SELECT PriKey,
         -- Pull out the first name string.
         SUBSTRING(
            CombinedName,
            1,
            (CHARINDEX(';', CombinedName) - 1)
            ),
         -- Pull out the last name string.
         SUBSTRING(
            CombinedName,
            (CHARINDEX(';', CombinedName) + 1),
            DATALENGTH(CombinedName) - (CHARINDEX(';', CombinedName) + 1)
            )
      FROM inserted
END

Подобная логика требуется и для обработки столбцов представления, являющихся простыми выражениями, но ссылающимися на вычисляемые столбцы со сложными выражениями

Некоторые выражения в представлениях могут изменять значения столбца базовой таблицы, например выполняя математическую операцию или используя значения столбца как параметр функции. В этом случае возможны два подхода к реализации логики триггера INSTEAD OF INSERT:

  • Соглашение о том, что все инструкции INSERT содержат необработанное значение, помещаемое в базовую таблицу, а триггер помещает это значение из таблицы inserted в базовую таблицу.

  • Соглашение о том, что все инструкции INSERT передают значение в таком виде, который может быть получен во время выполнения инструкции SELECT для представления, при этом логика триггера должна выполнить обратное преобразование. Например:

    CREATE TABLE BaseTable
      (
       PrimaryKey   int PRIMARY KEY,
       ColumnB      int,
       ColumnC      decimal(19,3)
      )
    
    CREATE VIEW SquareView AS
    SELECT PrimaryKey, ColumnB,
           -- Square the value of ColumnC
           SQUARE(ColumnC) AS SquareC
    FROM BaseTable
    
    CREATE TRIGGER SquareTrigger ON SquareView
    INSTEAD OF INSERT
    AS
    BEGIN
      INSERT INTO BaseTable
         SELECT PrimaryKey, ColumnB,
                 -- Perform logical inverse of function in view.
                 SQRT(SquareC)
         FROM inserted
    END
    

Для некоторых выражений, например для сложных выражений с использованием таких математических операций, как сложение и вычитание, предоставление данных, однозначно позволяющих триггеру определить значения соответствующих столбцов назначения в базовой таблице, может быть невозможным. Например, если список выборки представления содержит выражение IntColA + IntColB AS AddedColumns, что должно значить значение 10 в столбце inserted.AddedColumns? Является ли 10 результатом сложения 3 и 7, 2 и 8 или 5 и 5? По одному значению inserted.AddedColumns невозможно определить, какие значения нужно поместить в столбцы IntColA и IntColB.

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

См. также

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

Применение триггеров INSTEAD OF

Справка и поддержка

Получение помощи по SQL Server 2005