Usar as tabelas inseridas e excluídas

Aplica-se a:SQL ServerBase de Dados SQL do AzureAzure SQL Managed InstanceBase de dados SQL no Microsoft Fabric

As instruções de gatilho DML usam duas tabelas especiais: as tabelas deleted e inserted. O SQL Server cria e gerencia automaticamente essas tabelas. Você pode usar essas tabelas temporárias residentes na memória para testar os efeitos de determinadas modificações de dados e definir condições para ações de gatilho DML. 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.

Compreender as tabelas inseridas e eliminadas

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

• Estenda a integridade referencial entre tabelas.

• Insira ou atualize dados em tabelas base subjacentes a uma vista.

• Teste se há erros e tome medidas com base no erro.

• Encontre a diferença entre o estado de uma tabela antes e depois de uma modificação de dados e execute ações com base nessa diferença.

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

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 trigger 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 por 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 de linha após a atualização (na tabela inserida).

Ao definir condições de disparo, utilize as tabelas de inserção e eliminação adequadamente para a ação que ativou o disparador. Embora a referência à tabela eliminada ao testar uma INSERT ou à tabela inserida ao testar a DELETE não cause erros, estas tabelas de teste de gatilho não contêm linhas nestes casos.

O SQL Server não permite referências a colunas de texto, ntextou imagem nas tabelas inseridas e eliminadas em gatilhos AFTER. No entanto, esses tipos de dados são incluídos apenas 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 suportam varchar(max), nvarchar(max)e varbinary(max) dados nas tabelas inseridas e excluídas. Para mais informações, vejaCREATE TRIGGER (Transact-SQL).

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

Como as restrições CHECK podem referir-se apenas às colunas nas quais a restrição de nível de coluna ou de tabela é definida, quaisquer restrições entre tabelas (neste caso, regras de negócio) devem ser definidas como triggers.

O exemplo a seguir cria um gatilho DML. Esse gatilho verifica se a classificação de crédito do fornecedor é boa quando é feita uma tentativa de inserir uma nova ordem de compra na tabela PurchaseOrderHeader. Para obter a classificação de crédito do fornecedor correspondente à ordem de compra que acabou de ser inserida, a tabela Vendor deve ser referenciada e unida à tabela inserida. Se a notação de crédito for demasiado baixa, é apresentada uma mensagem e a inserção não é 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

Use tabelas de inserções e exclusões em gatilhos INSTEAD OF

As tabelas de inserção e eliminação transmitidas para gatilhos INSTEAD OF definidos em tabelas seguem as mesmas regras que as tabelas de inserção e eliminação transmitidas para gatilhos AFTER. O formato das tabelas inseridas e excluídas é o mesmo que o formato da tabela na qual o gatilho INSTEAD OF é definido. Cada coluna nas 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 trigger INSTEAD OF deve fornecer valores para colunas são as mesmas que se a tabela não tivesse um trigger INSTEAD OF:

• Os valores não podem ser especificados para colunas calculadas ou colunas com o tipo de dados timestamp.

• Os 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 tenham DEFAULT restrições.

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

Quando uma INSERTinstrução , UPDATE, ou DELETE faz referência a uma vista que tem um trigger INSTEAD OF, o Database Engine chama o trigger 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 quaisquer 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 criadas para a exibição é diferente do formato dos dados nas tabelas base.

O formato das tabelas inseridas e eliminadas passadas para um disparador INSTEAD OF definido numa vista corresponde à lista de seleção da instrução SELECT definida para a vista. 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 para esta vista tem três colunas: uma coluna int e duas colunas nvarchar. As tabelas inseridas e eliminadas passadas para um gatilho INSTEAD OF definido na vista também têm uma coluna int chamada BusinessEntityID, uma coluna nvarchar chamada LName, e uma coluna nvarchar chamada FName.

A lista de seleção de uma vista também pode conter expressões que não correspondem diretamente a uma única coluna de uma tabela base. Algumas expressões de exibição, como uma constante ou invocação de função, podem não fazer referência a nenhuma coluna e podem ser ignoradas. Expressões complexas podem fazer referência a várias colunas, mas as tabelas inseridas e excluídas têm apenas um valor para cada linha inserida. Os mesmos problemas se aplicam a expressões simples em um modo de exibição se elas fizerem referência a uma coluna computada que tenha uma expressão complexa. Um gatilho INSTEAD OF no modo de exibição deve manipular esses tipos de expressões.

Considerações sobre desempenho

Como as tabelas inseridas e excluídas são tabelas virtuais residentes na memória, propriedades como estatísticas ou índices não estão disponíveis. Embora algumas informações de cardinalidade sejam expostas nessas 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 juntá-las a outras tabelas pode resultar em planos de consulta abaixo do ideal e execuções de consulta lentas. Certifique-se de projetar e testar cuidadosamente seu aplicativo para atender às suas necessidades de desempenho de consulta.

Conteúdo relacionado

• Gatilhos DML