Usar as tabelas inseridas e excluídas
Instruções de gatilho DML usam duas tabelas especiais: a tabela deleted e as tabelas inserted. 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 executar operações DDL (linguagem de definição de dados) nas tabelas, como CREATE INDEX.
Em gatilhos DML, as tabelas inserted e deleted são principalmente usadas 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 deleted armazena cópias das linhas afetadas durante as instruções DELETE e UPDATE. Durante a execução de uma instrução DELETE ou UPDATE, as linhas são excluídas da tabela de gatilhos e transferidas para a tabela deleted. A tabela deleted e a tabela de gatilhos geralmente não têm linhas em comum.
A tabela inserted armazena cópias das linhas afetadas durante as instruções INSERT e UPDATE. Durante uma transação de inserção ou atualização, novas linhas são adicionadas à tabela inserted e à tabela de gatilhos. As linhas na tabela inserted são cópias das novas linhas na tabela de gatilhos.
Uma transação de atualização é similar a uma operação de exclusão seguida por uma operação de inserção; as linhas antigas são copiadas primeiro para a tabela deleted. Em seguida, as novas linhas são copiadas para a tabela de gatilhos e para a tabela inserted.
Quando você definir condições de gatilho, use as tabelas inserted e deleted de forma apropriada para a ação que disparou o gatilho. Embora o fato de se fazer referência à tabela deleted ao testar INSERT ou à tabela inserted ao testar DELETE não cause erros, essas tabelas de teste de gatilho não contêm linhas nesses casos.
.gif "Observação") Observação |
|---|
Se as ações dos gatilhos dependem do número de linhas que uma modificação de dados afeta, use os testes (como um exame de @@ROWCOUNT) para modificações de dados em várias linhas (uma instrução INSERT, DELETE ou UPDATE com base em uma instrução SELECT) e execute as ações apropriadas. |
O SQL Server 2012 não permite referências de coluna de text, ntext ou image nas tabelas inserted e deleted para gatilhos AFTER. Entretanto, esses tipos de dados são incluídos somente para fins 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 dão suporte a dados varchar(max), nvarchar(max) e varbinary(max) nas tabelas inserted e deleted. 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 acabou de ser inserida, a tabela Vendor deve ser referenciada e unida a uma tabela inserted. Se a classificação de crédito for muito baixa, uma mensagem será exibida e a inserção não será executada. Observe que este exemplo não permite modificações de dados de multilinha. Para obter mais informações, consulte Crie gatilhos DML para tratar várias linhas de dados.
USE AdventureWorks2012;
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
Usando as tabelas inseridas e excluídas em gatilhos INSTEAD OF
As tabelas inserted e deleted passadas para gatilhos INSTEAD OF definidos nas tabelas seguem as mesmas regras que as tabelas inserted e deleted passadas para gatilhos AFTER. O formato das tabelas inserted e deleted é o mesmo que o formato da tabela em que o gatilho INSTEAD OF foi definido. Cada coluna nas tabelas inserted e deleted mapeia diretamente para uma coluna na tabela base.
As regras a seguir, relativas a quando uma instrução INSERT ou UPDATE, que faz referência a 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:
Valores não podem ser especificados para colunas computadas ou colunas com tipo de dados timestamp.
Valores não podem ser especificados com uma propriedade IDENTITY, a menos que IDENTITY_INSERT seja ON para aquela tabela. Quando IDENTITY_INSERT for 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 computada, identidade ou colunas timestamp, 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 exibição que tenha 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 inserted e deleted para criar instruções necessárias à implementação da ação solicitada nas tabelas base, mesmo quando o formato das informações nas tabelas inserted e deleted criadas para a exibição for diferente do formato dos dados nas tabelas base.
O formato das tabelas inserted e deleted passadas para um gatilho INSTEAD OF definido em uma exibição corresponde à lista de seleção da instrução SELECT definida para a exibição. Por exemplo:
USE AdventureWorks2012;
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 inserted e deleted passadas para um gatilho INSTEAD OF definido na exibição também têm uma coluna int nomeada BusinessEntityID, uma coluna nvarchar nomeada LName e uma coluna nvarchar nomeada 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 fazer referência a várias colunas, ainda que as tabelas inserted e deleted só tenham um único 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.