Usar as tabelas inseridas e excluídas

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureBanco de dados SQL no Microsoft Fabric

As instruções de gatilho DML usam duas tabelas especiais: as tabelas excluídas e inseridas. O SQL Server cria e gerencia automaticamente essas tabelas. É possível usar essas tabelas temporárias, residentes na memória, para testar os efeitos de algumas modificações de dados e para definir critérios para ações de gatilhos DML. Você não pode modificar diretamente os dados nas tabelas nem realizar operações de linguagem de definição de dados (DDL) nas tabelas, como CREATE INDEX.

Reconhecimento das tabelas inseridas e excluídas

Nos gatilhos DML, as tabelas inseridas e excluídas são usadas principalmente para executar o seguinte:

• Estender a integridade referencial entre as tabelas.

• Inserir ou atualizar dados nas tabelas adjacentes da exibição.

• Testar quanto a erros e aplicar as ações com base no erro.

• Identificar a diferença entre o estado de uma tabela antes e depois da modificação dos dados e executar ações com base nessa diferença.

A tabela deletada armazena cópias das linhas afetadas na tabela de gatilhos antes de serem alteradas por uma DELETE instrução ou UPDATE (a tabela de gatilho é a tabela onde o gatilho DML roda). Durante a execução de uma DELETE instrução ou, UPDATE as linhas afetadas são primeiro copiadas da tabela de gatilho e transferidas para a tabela deletada.

A tabela inserida armazena cópias das linhas novas ou alteradas após uma INSERT instrução ou UPDATE . Durante a execução de uma INSERT instrução ou, UPDATE as linhas novas ou alteradas na tabela de gatilho são copiadas para a tabela inserida. As linhas na tabela inserida são cópias das linhas novas ou atualizadas na tabela de gatilho.

Uma transação de atualização é semelhante a uma operação de exclusão seguida de uma operação de inserção. Durante a execução de uma UPDATE afirmação, ocorre a seguinte sequência de eventos:

1. A linha original é copiada da tabela de gatilho para a tabela excluída.
2. A tabela de gatilhos é atualizada com os novos valores da UPDATE instrução.
3. A linha atualizada na tabela de gatilho é copiada para a tabela inserida.

Isso permite que você compare o conteúdo da linha antes da atualização (na tabela excluída) com os novos valores da linha após a atualização (na tabela inserida).

Quando você definir os critérios para o gatilho, use adequadamente as tabelas inseridas e excluídas para a ação que acionou o gatilho. Embora referenciar a tabela deletada ao testar um INSERT ou a tabela inserida ao testar a DELETE não cause erros, essas tabelas de teste de gatilho não contêm linhas nesses casos.

O SQL Server não permite referências de coluna text, ntextou image nas tabelas inseridas e excluídas para gatilhos AFTER. Entretanto, esses tipos de dados são incluídos somente para fins de compatibilidade com versões anteriores. O armazenamento preferencial para dados grandes é usar os tipos de dados varchar(max), nvarchar(max)e varbinary(max) . Os gatilhos AFTER e INSTEAD OF dão suporte a dados varchar(max), nvarchar(max)e varbinary(max) nas tabelas inseridas e excluídas. Para obter mais informações, consulte CREATE TRIGGER (Transact-SQL).

Exemplo: usar a tabela inserida em um gatilho para impor regras de negócio

Como as restrições CHECK só podem referenciar as colunas nas quais a restrição de nível de coluna ou de nível de tabela é definida, qualquer restrição de tabela cruzada (neste caso, as regras de negócio) deverá ser definida como gatilho.

O exemplo a seguir cria um gatilho DML. Esse gatilho realiza uma verificação para ter certeza de que a avaliação de crédito do fornecedor é satisfatória quando for efetuar uma tentativa para inserir uma nova ordem de compra na tabela PurchaseOrderHeader . Para obter a avaliação de crédito do fornecedor correspondente à ordem de compra que acaba de ser inserida, a tabela Vendor deve ser referenciada e associada a uma tabela inserida. Se a classificação de crédito for muito baixa, uma mensagem será exibida e a inserção não será executada.

USE AdventureWorks2022;
GO

IF OBJECT_ID('Purchasing.LowCredit', 'TR') IS NOT NULL
    DROP TRIGGER Purchasing.LowCredit;
GO

-- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table
-- when the credit rating of the specified vendor is set to 5 (below average).
CREATE TRIGGER Purchasing.LowCredit
ON Purchasing.PurchaseOrderHeader
AFTER INSERT
AS
    IF (ROWCOUNT_BIG() = 0)
    RETURN;
    IF EXISTS (SELECT 1
        FROM inserted AS i
        INNER JOIN Purchasing.Vendor AS v
            ON v.BusinessEntityID = i.VendorID
            WHERE v.CreditRating = 5)
BEGIN
    RAISERROR ('A vendor''s credit rating is too low to accept new purchase orders.', 16, 1);
    ROLLBACK;
    RETURN;
END
GO

-- This statement attempts to insert a row into the PurchaseOrderHeader table  
-- for a vendor that has a below average credit rating.  
-- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back.  

INSERT INTO Purchasing.PurchaseOrderHeader (RevisionNumber, Status, EmployeeID,
    VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight)
VALUES (2, 3, 261, 1652, 4, GETDATE(), GETDATE(), 44594.55, 3567.564, 1114.8638);
GO

Uso das tabelas inseridas e excluídas em gatilhos INSTEAD OF

As tabelas inseridas e excluídas passadas para os gatilhos INSTEAD OF definidos nas tabelas seguem as mesmas regras das tabelas inseridas e excluídas passadas para os gatilhos AFTER. O formato das tabelas inseridas e excluídas é o mesmo do formato da tabela na qual o gatilho INSTEAD OF está definido. Cada coluna das tabelas inseridas e excluídas é mapeada diretamente para uma coluna na tabela base.

As seguintes regras sobre quando uma INSERT instrução ou UPDATE que faz referência a uma tabela com um gatilho INSTEAD OF deve fornecer valores para colunas são as mesmas que se a tabela não tivesse um gatilho INSTEAD OF:

• Valores não podem ser especificados para colunas computadas ou colunas com tipo de dados timestamp .

• Valores não podem ser especificados para colunas com uma IDENTITY propriedade, a menos que IDENTITY_INSERT esteja LIGADO para essa tabela. Quando IDENTITY_INSERT está LIGADO, INSERT as instruções devem fornecer um valor.

• INSERT as instruções devem fornecer valores para todas as colunas NOT NULL que não possuem DEFAULT restrições.

• Para quaisquer colunas, exceto colunas computed, identidade ou timestamp , os valores são opcionais para qualquer coluna que permita nulos, ou qualquer coluna NOT NULL que tenha definição DEFAULT .

Quando uma INSERTinstrução , UPDATE, ou DELETE faz referência a uma visualização que tem um gatilho INSTEAD OF, o Mecanismo de Banco de Dados chama o gatilho em vez de tomar qualquer ação direta contra qualquer tabela. O gatilho deve usar as informações apresentadas nas tabelas inseridas e excluídas para construir as instruções necessárias para implementar a ação solicitada nas tabelas base, mesmo quando o formato das informações nas tabelas inseridas e excluídas construído para a exibição for diferente do formato dos dados nas tabelas base.

O formato das tabelas inseridas e excluídas passadas para o gatilho INSTEAD OF definido em uma exibição corresponde à lista de seleção da instrução SELECT definida para exibição. Por exemplo:

USE AdventureWorks2022;  
GO  
CREATE VIEW dbo.EmployeeNames (BusinessEntityID, LName, FName)  
AS  
SELECT e.BusinessEntityID, p.LastName, p.FirstName  
FROM HumanResources.Employee AS e   
JOIN Person.Person AS p  
ON e.BusinessEntityID = p.BusinessEntityID;  

O conjunto de resultados desta exibição tem três colunas: uma coluna int e duas colunas nvarchar . As tabelas inseridas e excluídas passadas para um gatilho INSTEAD OF definido em uma exibição têm também uma coluna int denominada BusinessEntityID, uma coluna nvarchar denominada LNamee uma coluna nvarchar denominada FName.

A lista de seleção de uma exibição pode também conter expressões que não mapeiam diretamente uma única coluna com base em tabelas. Algumas expressões de exibição, como um chamado de função ou de constante, podem não fazer referência a nenhuma coluna e podem ser ignoradas. Expressões complexas podem referenciar várias colunas, mesmo assim as colunas inseridas e excluídas têm apenas um valor para cada linha inserida. As mesmas questões se aplicam às expressões simples em uma exibição, caso elas façam referência a uma coluna computada que tenha uma expressão complexa. Um gatilho INSTEAD OF na exibição deve tratar desses tipos de expressões.

Considerações sobre o desempenho

Como as tabelas inseridas e de exclusão são virtuais e residentes na memória, propriedades como estatísticas ou índices não são disponibilizados. Embora algumas informações de cardinalidade sejam expostas por meio dessas tabelas, você deve ter cuidado ao considerar o número de linhas a serem armazenadas temporariamente lá. Inserir um grande número de linhas nessas tabelas e consultá-las ou juncioná-las com outras tabelas pode resultar em planos de consulta sub-ideais e execuções de consulta lentas. Lembre-se de projetar e testar cuidadosamente seu aplicativo para atender às suas necessidades de desempenho de consulta.

Conteúdo relacionado

• Gatilhos DML