Usar as tabelas inseridas e excluídas

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada SQL do Azure

As instruções de gatilho DML usam duas tabelas especiais: as tabelas eliminadas e inseridas . 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 é possível modificar diretamente os dados nas tabelas ou executar operações DDL (linguagem de definição de dados) 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 gatilho, antes de serem alteradas por uma instrução DELETE ou UPDATE (a tabela de gatilho é a tabela na qual o gatilho DML é executado). Durante a execução de uma instrução DELETE ou UPDATE, as linhas afetadas são primeiro copiadas da tabela de gatilho e transferidas para a tabela excluída.

A tabela inserida armazena cópias das linhas novas ou alteradas após uma instrução INSERT ou UPDATE. Durante a execução de uma instrução INSERT 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 por uma operação de inserção. Durante a execução de uma instrução UPDATE, 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 gatilho é atualizada com os novos valores da instrução UPDATE.
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 gatilho, use as tabelas inseridas e excluídas apropriadamente para a ação que disparou o gatilho. Embora a referência à tabela excluída ao testar um INSERT ou a tabela inserida ao testar um 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 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 obter mais informações, consulte CREATE TRIGGER (Transact-SQL).

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

Como as restrições CHECK podem fazer referência apenas às colunas nas quais a restrição de nível de coluna ou de tabela é definida, quaisquer restrições de tabela cruzada (neste caso, regras de negócios) devem ser definidas como gatilhos.

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   
           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 TRANSACTION;  
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 inseridas e eliminadas passadas para gatilhos INSTEAD OF definidos nas tabelas seguem as mesmas regras que as tabelas inseridas e eliminadas passadas 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 instrução INSERT ou UPDATE referenciando uma tabela com um gatilho INSTEAD OF deve fornecer valores para colunas são as mesmas como se a tabela não tivesse um gatilho INSTEAD OF:

• Os valores não podem ser especificados para colunas calculadas ou colunas com tipo de dados de carimbo de data/hora .

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

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

• Para qualquer coluna, exceto as colunas computadas, de identidade ou de carimbo de data/hora , os valores são opcionais para qualquer coluna que permita nulos ou qualquer coluna NOT NULL que tenha uma definição DEFAULT.

Quando uma instrução INSERT, UPDATE ou DELETE faz referência a uma vista que tem um trigger INSTEAD OF, o Motor de Base de Dados aciona o trigger em vez de tomar qualquer ação direta sobre a 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 excluídas passadas para um gatilho INSTEAD OF definido na exibição também têm uma coluna int chamada , uma coluna nvarchar chamada e uma coluna nvarchar chamada .

A lista de seleção de um modo de exibição também pode conter expressões que não correspondem diretamente a uma única coluna 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.

Próximos passos

Para obter mais informações, consulte a visão geral do DML Triggers.