Usando as tabelas inseridas e excluídas

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

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 aplicar ações com base nessa diferença.

A tabela excluída armazena cópias das linhas afetadas durante as instruções DELETE e UPDATE. Durante a execução da instrução DELETE ou UPDATE, as linhas são excluídas da tabela de gatilhos e transferidas para a tabela excluída. A tabela excluída e a tabela de gatilhos geralmente não têm nenhuma linha em comum.

A tabela inserida armazena cópias das linhas afetadas durante as instruções INSERT e UPDATE. Durante uma transação de inserção ou de atualização, novas linhas são adicionadas à tabela inserida e à tabela de gatilho. As linhas na tabela inserida são cópias das novas linhas na tabela de gatilhos.

Uma transação de atualização é semelhante à operação de exclusão seguida por uma operação de inserção, as linhas antigas são copiadas primeiro na tabela excluída e, em seguida, as novas linhas são copiadas na tabela de gatilhos e 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 referenciando a tabela excluída quando testar INSERT ou a tabela inserida quando testar DELETE não cause qualquer erro, essas tabelas de teste de gatilhos não contêm nenhuma linha nesses casos.

Observação
Se as ações dos gatilhos dependem do número de linhas que uma modificação de dados efetua, use os testes (como uma verificação de @@ROWCOUNT) para modificações de dados de multilinhas (uma INSERT, DELETE, ou UPDATE com base em uma instrução SELECT), e aplique as ações adequadas.

O SQL Server 2008 não permite referências de coluna de text, ntext ou image nas tabelas inseridas e excluídas para gatilhos AFTER. Porém, esses tipos de dados são incluídos somente para a finalidade de compatibilidade com versões anteriores. É preferível armazenar dados grandes usando os tipos de dados varchar(max), nvarchar(max) e varbinary(max). Os gatilhos AFTER e INSTEAD OF oferecem suporte aos dados varchar(max), nvarchar(max) e varbinary(max) nas tabelas inseridas e excluídas. Para obter mais informações, consulte CREATE TRIGGER (Transact-SQL).

Um exemplo do uso de 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 avaliação de crédito for muito baixa, uma mensagem será exibida e a inserção não será realizada.

Observação
Para exibir exemplos de gatilhos DML AFTER que atualizam várias linhas, consulte Considerações multilinha para gatilhos DML.

USE AdventureWorks2008R2;
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 EXISTS (SELECT *
           FROM Purchasing.PurchaseOrderHeader p 
           JOIN inserted AS i 
           ON p.PurchaseOrderID = i.PurchaseOrderID 
           JOIN Purchasing.Vendor AS v 
           ON v.BusinessEntityID = p.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