Выражения и вычисляемые столбцы в триггерах INSTEAD OF
Список выборки представления может содержать не только простые выражения, состоящие лишь из имени столбца. Для таких представлений триггеры INSTEAD OF должны содержать логику, позволяющую определить, какие значения должны устанавливаться в столбцах базовой таблицы при заданных значениях, указанных в инструкциях INSERT или UPDATE. Ниже приводятся примеры таких выражений.
Представления, которые не ссылаются на какие-либо столбцы в таблицах, например константы или некоторые виды функций.
Представления, ссылающиеся на многие столбцы, например сложные выражения, объединяющие строки из двух и более столбцов.
Представления, преобразующие значение единственного столбца базовой таблицы, например, ссылающиеся на столбец из функции.
Эти вопросы также относятся к столбцам представления, являющимися простыми выражениями, но ссылающимися на вычисляемый столбец базовой таблицы. Выражение, определяющее вычисляемый столбец, может иметь ту же форму, что и у более сложного выражения из списка выборок представления.
Представления могут содержать в списках выборки выражения, не ссылающиеся ни на один из столбцов базовой таблицы, например:
CREATE VIEW dbo.ExpressionView
AS
SELECT BusinessEntityID, JobTitle, GETDATE() AS TodaysDate
FROM AdventureWorks2008R2.HumanResources.Employee;
Хотя столбец TodaysDate не ссылается ни на один из столбцов таблицы, SQL Server должен создать столбец TodaysDate в таблице inserted, которую он передает триггеру INSTEAD OF, определенному для ExpressionView. Однако столбец inserted.TodaysDate допускает значения NULL, поэтому инструкции INSERT, ссылающиеся на ExpressionView, могут не содержать значения этого столбца. Поскольку выражение не ссылается на столбец в таблице, триггер может игнорировать любые значения для столбца, предоставляемые в инструкции INSERT.
Тот же подход рекомендуется применять к простым выражениям-представлениям, ссылающимся на вычисляемые столбцы в базовой таблице и создающим результат, не зависящий от других столбцов, например:
CREATE TABLE dbo.ComputedExample
(
PrimaryKey int PRIMARY KEY,
ComputedCol AS SUSER_NAME()
);
Некоторые сложные выражения могут ссылаться на несколько столбцов, например:
CREATE TABLE dbo.SampleTable
(
PriKey int,
FirstName nvarchar(20),
LastName nvarchar(30)
);
GO
CREATE VIEW dbo.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 dbo.ConcatView
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO dbo.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 dbo.BaseTable ( PrimaryKey int PRIMARY KEY, ColumnB int, ColumnC decimal(19,3) ); CREATE VIEW dbo.SquareView AS SELECT PrimaryKey, ColumnB, -- Square the value of ColumnC SQUARE(ColumnC) AS SquareC FROM BaseTable; CREATE TRIGGER SquareTrigger ON dbo.SquareView INSTEAD OF INSERT AS BEGIN INSERT INTO dbo.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 могут использоваться как значения ключа, используемые триггером для получений необходимых данных из других базовых таблиц.