Gatilhos INSTEAD OF INSERT

Gatilhos INSTEAD OF INSERT podem ser definidos em uma exibição ou tabela para substituir a ação padrão da instrução INSERT. Geralmente, o gatilho INSTEAD OF INSERT é definido em uma exibição para inserir dados em uma ou mais tabelas base.

As colunas na lista de seleção de exibição podem ser anuláveis ou não anuláveis. Se uma coluna de exibição não permitir nulos, uma instrução INSERT deve fornecer valores para a coluna. Colunas de exibição permitem nulos se a expressão que define a coluna de exibição incluir itens como os seguintes:

  • Referências a qualquer coluna de tabela base que permite nulos

  • Operadores aritméticos

  • Referências a funções

  • CASE ou COALESCE com uma subexpressão anulável

  • NULLIF

Você pode usar a propriedade AllowsNull informada pela função COLUMNPROPERTY para determinar se uma coluna de exibição permite nulos. O procedimento armazenado sp_help também informa quais colunas de exibição permitem nulos.

Uma instrução INSERT que referencie uma exibição que tenha um gatilho INSTEAD OF INSERT deve fornecer valores para cada coluna de exibição que não permitir nulos. Isso inclui colunas de exibição que referenciam colunas na tabela base para as quais não é possível especificar valores de entrada, como:

  • Colunas computadas na tabela base.

  • Colunas de identidade na tabela base para as quais IDENTITY INSERT é OFF.

  • Colunas de tabela base com o tipo de dados timestamp.

Se o gatilho de exibição INSTEAD OF INSERT gerar um INSERT contra a tabela base usando os dados na tabela inserida, ele deve ignorar os valores para esses tipos de colunas ao não incluir as colunas na lista de seleção da instrução INSERT. A instrução INSERT pode gerar valores fictícios para esses tipos de colunas.

Por exemplo, embora uma instrução INSERT deva especificar um valor para uma coluna de exibição que mapeie para uma coluna de identidade ou coluna computada na tabela base, ela pode fornecer um valor de espaço reservado. O gatilho INSTEAD OF pode ignorar o valor fornecido quando formar a instrução INSERT que insere os valores na tabela base.

As instruções seguintes criam uma tabela, uma exibição e um gatilho que ilustram o processo:

CREATE TABLE BaseTable
  (ID     int PRIMARY KEY IDENTITY(1,1),
   Color          nvarchar(10) NOT NULL,
   Material       nvarchar(10) NOT NULL,
   ComputedCol AS (Color + Material)
  );
GO

--Create a view that contains all columns from the base table.
CREATE VIEW InsteadView
AS SELECT ID, Color, Material, ComputedCol
FROM BaseTable;
GO

--Create an INSTEAD OF INSERT trigger on the view.
CREATE TRIGGER InsteadTrigger on InsteadView
INSTEAD OF INSERT
AS
BEGIN
  --Build an INSERT statement ignoring inserted.ID and 
  --inserted.ComputedCol.
  INSERT INTO BaseTable
       SELECT Color, Material
       FROM inserted
END;
GO

Uma instrução INSERT que referencia diretamente a BaseTable não pode fornecer um valor para as colunas ID e ComputedCol. Por exemplo:

--A correct INSERT statement that skips the ID and ComputedCol columns.
INSERT INTO BaseTable (Color, Material)
       VALUES (N'Red', N'Cloth');

--View the results of the INSERT statement.
SELECT ID, Color, Material, ComputedCol
FROM BaseTable;

--An incorrect statement that tries to supply a value for the 
--ID and ComputedCol columns.
INSERT INTO BaseTable
       VALUES (2, N'Green', N'Wood', N'GreenWood');

Porém, uma instrução INSERT que referencie a InsteadView deve fornecer um valor para ID e ComputedCol:

--A correct INSERT statement supplying dummy values for the 
--PrimaryKey and ComputedCol columns.
INSERT INTO InsteadView (ID, Color, Material, ComputedCol)
       VALUES (999, N'Blue', N'Plastic', N'XXXXXX')
--View the results of the INSERT statement.
SELECT ID, Color, Material, ComputedCol
FROM InsteadView;

A tabela inserida passada ao InsteadTrigger é compilada com uma coluna não anulável ID e ComputedCol; portanto, a instrução INSERT que está referenciando a exibição deve fornecer um valor para essas colunas. Os valores 999 e N'XXXXXX' são passados para InsteadTrigger, mas a instrução INSERT no gatilho não seleciona inserted.ID nem inserted.ComputedCol; portanto, os valores são ignorados. A linha inserida em BaseTable tem 2 em ID e N'BluePlastic' em ComputedCol.

Os valores contidos na tabela inserida para as colunas computada, identidade e timestamp são diferentes para gatilhos INSTEAD OF INSERT especificados em tabelas em comparação a gatilhos INSTEAD OF especificados em exibições.

Coluna de tabela base

Valor em uma tabela inserida em qualquer gatilho INSERT em uma tabela

Valor em tabela inserida em um gatilho INSTEAD OF INSERT em uma exibição

For uma coluna computada.

Expressão computada

Valor especificado pelo usuário ou NULL

Tiver uma propriedade IDENTITY.

0 se IDENTITY_INSERT for OFF, valor especificado se IDENTITY_INSERT for ON

Valor especificado pelo usuário ou NULL

Tiver um tipo de dados timestamp.

Zeros binários se a coluna não permitir nulos, NULL se a coluna permitir nulos

Valor especificado pelo usuário ou NULL

Uma instrução INSERT que referencie diretamente uma tabela base não tem de fornecer valores para uma coluna NOT NULL que também tenha uma definição DEFAULT. Se a instrução INSERT não fornecer um valor, o valor padrão será usado. Porém, se uma coluna NOT NULL com uma definição DEFAULT é referenciada por uma expressão simples em uma exibição que tenha um gatilho INSTEAD OF INSERT, qualquer instrução INSERT que referencie a exibição deve fornecer um valor para a coluna. Esse valor é exigido para compilar a tabela inserida passada ao gatilho. Uma convenção é necessária para um valor que sinalize ao gatilho que o valor padrão deve ser usado. A melhor convenção é que a instrução INSERT forneça o valor padrão.

A tabela excluída em um gatilho INSTEAD OF INSERT sempre está vazia.