Expressões e colunas computadas em gatilhos INSTEAD OF
A lista de seleção de uma exibição pode conter outras expressões além das simples compostas de apenas um nome de coluna. Gatilhos INSTEAD OF nessas exibições devem ter lógica para determinar corretamente a partir de valores especificados em INSERT e UPDATE quais valores devem ser definidos em colunas na tabela base. Exemplos de tais expressões incluem:
Expressões de exibição que não mapeiam para nenhuma coluna em nenhuma tabela, como uma constante ou alguns tipos de funções.
Expressões de exibição que mapeiam para várias colunas, como expressões complexas formadas por cadeias de caracteres de concatenação de duas ou mais colunas.
Expressões de exibição que transformam o valor de uma coluna de tabela base única, como a referência a uma coluna em uma função.
Essas questões também se aplicam a colunas de exibição que são expressões simples que fazem referência a uma coluna computada em uma tabela base. A expressão que define a coluna computada pode ter o mesmo formulário que uma expressão mais complexa na lista de seleção de exibição.
Exibições podem conter expressões em sua lista de seleção que não mapeiam para nenhuma coluna da tabela base, por exemplo:
CREATE VIEW dbo.ExpressionView
AS
SELECT BusinessEntityID, JobTitle, GETDATE() AS TodaysDate
FROM AdventureWorks2008R2.HumanResources.Employee;
Embora a coluna TodaysDate não mapeie nenhuma coluna da tabela, o SQL Server deve construir uma coluna TodaysDate na tabela inserida para a qual ele passa um gatilho INSTEAD OF definido na ExpressionView. No entanto a coluna inserted.TodaysDate é anulável. e portanto uma instrução INSERT que esteja fazendo referência à ExpressionView não precisa fornecer um valor para essa coluna. Como a expressão não mapeia para uma coluna em uma tabela, o gatilho pode ignorar qualquer valor fornecido por INSERT nessa coluna.
A mesma abordagem deve ser aplicada a expressões de exibição simples que fazem referência a colunas computadas em tabelas base que também constroem um resultado que não é dependente de outras colunas, por exemplo:
CREATE TABLE dbo.ComputedExample
(
PrimaryKey int PRIMARY KEY,
ComputedCol AS SUSER_NAME()
);
Algumas expressões complexas mapeiam para várias colunas, por exemplo:
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;
A expressão CombinedName em ConcatView tem os valores concatenados dos valores FirstName e LastName. Se um gatilho INSTEAD OF INSERT estiver definido na ConcatView, você deverá ter uma convenção de como instruções INSERT fornecem um valor para a coluna CombinedName que permita que o gatilho determine qual parte da cadeia de caracteres deve ser colocada na coluna FirstName e qual parte deve ser colocada na coluna LastName. Se você escolher uma convenção de fazer com que instruções INSERT especifiquem o valor de CombinedName usando a convenção 'first_name;last_name', o seguinte gatilho poderá processar uma INSERT com êxito:
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;
Lógica semelhante é necessária para processar colunas de exibição que são expressões simples que fazem referência a colunas computadas que têm expressões complexas.
Algumas expressões de exibição podem transformar o valor de uma coluna de tabela base, por exemplo, executando uma operação matemática ou usando a coluna como um parâmetro para uma função. Nesse caso, a lógica no gatilho INSTEAD OF INSERT pode usar duas abordagens:
A convenção pode ser que todas as instruções INSERT forneçam o valor bruto a ser colocado na tabela base e a lógica do gatilho mova o valor da tabela inserida para a tabela base.
A convenção pode ser que todas as instruções INSERT forneçam o valor que esperam ter retornado por uma SELECT na exibição, em cujo caso a lógica do gatilho deve reverter a operação. Por exemplo:
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;
Em algumas expressões, como expressões complexas que usam operações matemáticas, como adição e subtração, os usuários podem não ser capazes de fornecer um valor que possa ser usado pelo gatilho para construir valores de maneira não ambígua para as colunas da tabela base de destino. Por exemplo, se uma lista de seleção de exibição contiver a expressão IntColA + IntColB AS AddedColumns, o que um valor de 10 em inserted.AddedColumns significa? 10 é o resultado de 3 + 7, 2 + 8 ou 5 + 5? Não há como saber a partir do valor de inserted.AddedColumns apenas quais valores devem ser colocados em IntColA e IntColB.
Nesses casos, o gatilho pode ser codificado para usar origens alternativas de informações para determinar os valores a serem definidos nas colunas da tabela base. Para exibições que têm gatilhos INSTEAD OF, a lista de seleção de exibições deve conter informações suficientes para construir valores para todas as colunas não nulas nas tabelas base modificadas pelo gatilho. Nem todos os dados devem vir diretamente da tabela inserida. Em alguns casos, os valores na tabela inserida podem ser valores de chaves que o gatilho usa para recuperar os dados relevantes de outras tabelas base.