Usar as tabelas inseridas e excluídasUse the inserted and deleted Tables

APLICA-SE A: simSQL Server simBanco de Dados SQL do Azure nãoAzure Synapse Analytics (SQL DW) nãoParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

As instruções de gatilho DML usam duas tabelas especiais: a tabela excluída e as tabelas inseridas.DML trigger statements use two special tables: the deleted table and the inserted tables. SQL ServerSQL Server cria e gerencia automaticamente essas tabelas.automatically creates and manages these tables. É 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.You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for DML trigger actions. 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.You cannot directly modify the data in the tables or perform data definition language (DDL) operations on the tables, such as CREATE INDEX.

Nos gatilhos DML, as tabelas inseridas e excluídas são usadas principalmente para executar o seguinte:In DML triggers, the inserted and deleted tables are primarily used to perform the following:

  • Estender a integridade referencial entre as tabelas.Extend referential integrity between tables.

  • Inserir ou atualizar dados nas tabelas adjacentes da exibição.Insert or update data in base tables underlying a view.

  • Testar quanto a erros e aplicar as ações com base no erro.Test for errors and take action based on the error.

  • 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.Find the difference between the state of a table before and after a data modification and take actions based on that difference.

A tabela excluída armazena cópias das linhas afetadas durante as instruções DELETE e UPDATE.The deleted table stores copies of the affected rows during DELETE and UPDATE statements. 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.During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table. A tabela excluída e a tabela de gatilhos geralmente não têm nenhuma linha em comum.The deleted table and the trigger table ordinarily have no rows in common.

A tabela inserida armazena cópias das linhas afetadas durante as instruções INSERT e UPDATE.The inserted table stores copies of the affected rows during INSERT and UPDATE statements. Durante uma transação de inserção ou de atualização, novas linhas são adicionadas à tabela inserida e à tabela de gatilho.During an insert or update transaction, new rows are added to both the inserted table and the trigger table. As linhas na tabela inserida são cópias das novas linhas na tabela de gatilhos.The rows in the inserted table are copies of the new rows in the trigger table.

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.An update transaction is similar to a delete operation followed by an insert operation; the old rows are copied to the deleted table first, and then the new rows are copied to the trigger table and to the inserted table.

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.When you set trigger conditions, use the inserted and deleted tables appropriately for the action that fired the trigger. 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.Although referencing the deleted table when testing an INSERT or the inserted table when testing a DELETE does not cause any errors, these trigger test tables do not contain any rows in these cases.

Observação

Se as ações de 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.If trigger actions depend on the number of rows a data modification effects, use tests (such as an examination of @@ROWCOUNT) for multirow data modifications (an INSERT, DELETE, or UPDATE based on a SELECT statement), and take appropriate actions.

SQL ServerSQL Server não permite referências de coluna text, ntextou image nas tabelas inseridas e excluídas para gatilhos AFTER.does not allow for text, ntext, or image column references in the inserted and deleted tables for AFTER triggers. Entretanto, esses tipos de dados são incluídos somente para fins de compatibilidade com versões anteriores.However, these data types are included for backward compatibility purposes only. O armazenamento preferencial para dados grandes é usar os tipos de dados varchar(max) , nvarchar(max) e varbinary(max) .The preferred storage for large data is to use the varchar(max), nvarchar(max), and varbinary(max) data types. Os gatilhos AFTER e INSTEAD OF dão suporte a dados varchar(max) , nvarchar(max) e varbinary(max) nas tabelas inseridas e excluídas.Both AFTER and INSTEAD OF triggers support varchar(max), nvarchar(max), and varbinary(max) data in the inserted and deleted tables. Para obter mais informações, veja CREATE TRIGGER (Transact-SQL).For more information, see CREATE TRIGGER (Transact-SQL).

Um exemplo do uso de tabela inserida em um gatilho para impor regras de negócioAn Example of Using the inserted Table in a Trigger to Enforce Business Rules

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.Because CHECK constraints can reference only the columns on which the column-level or table-level constraint is defined, any cross-table constraints (in this case, business rules) must be defined as triggers.

O exemplo a seguir cria um gatilho DML.The following example creates a DML trigger. 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 .This trigger checks to make sure the credit rating for the vendor is good when an attempt is made to insert a new purchase order into the PurchaseOrderHeader table. 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.To obtain the credit rating of the vendor corresponding to the purchase order that was just inserted, the Vendor table must be referenced and joined with the inserted table. Se a classificação de crédito for muito baixa, uma mensagem será exibida e a inserção não será executada.If the credit rating is too low, a message is displayed and the insertion does not execute. Observe que este exemplo não permite modificações de dados de multilinha.Note that this example does not allow for multirow data modifications. Para obter mais informações, veja Criar gatilhos DML para tratar várias linhas de dados.For more information, see Create DML Triggers to Handle Multiple Rows of Data.

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 OFUsing the inserted and deleted Tables in INSTEAD OF Triggers

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.The inserted and deleted tables passed to INSTEAD OF triggers defined on tables follow the same rules as the inserted and deleted tables passed to AFTER triggers. O formato das tabelas inseridas e excluídas é o mesmo do formato da tabela na qual o gatilho INSTEAD OF está definido.The format of the inserted and deleted tables is the same as the format of the table on which the INSTEAD OF trigger is defined. Cada coluna das tabelas inseridas e excluídas é mapeada diretamente para uma coluna na tabela base.Each column in the inserted and deleted tables maps directly to a column in the base table.

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:The following rules regarding when an INSERT or UPDATE statement referencing a table with an INSTEAD OF trigger must supply values for columns are the same as if the table did not have an INSTEAD OF trigger:

  • Valores não podem ser especificados para colunas computadas ou colunas com tipo de dados timestamp .Values cannot be specified for computed columns or columns with a timestamp data type.

  • Valores não podem ser especificados com uma propriedade IDENTITY, a menos que IDENTITY_INSERT seja ON para aquela tabela.Values cannot be specified for columns with an IDENTITY property, unless IDENTITY_INSERT is ON for that table. Quando IDENTITY_INSERT for ON, as instruções INSERT devem fornecer um valor.When IDENTITY_INSERT is ON, INSERT statements must supply a value.

  • As instruções INSERT devem fornecer valores para todas as colunas NOT NULL que não têm restrições DEFAULT.INSERT statements must supply values for all NOT NULL columns that do not have DEFAULT constraints.

  • 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.For any columns except computed, identity, or timestamp columns, values are optional for any column that allows nulls, or any NOT NULL column that has a DEFAULT definition.

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 DadosDatabase Engine chama o gatilho em vez de tomar qualquer ação direta contra qualquer tabela.When an INSERT, UPDATE, or DELETE statement references a view that has an INSTEAD OF trigger, the Mecanismo de Banco de DadosDatabase Engine calls the trigger instead of taking any direct action against any table. 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.The trigger must use the information presented in the inserted and deleted tables to build any statements required to implement the requested action in the base tables, even when the format of the information in the inserted and deleted tables built for the view is different from the format of the data in the base tables.

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.The format of the inserted and deleted tables passed to an INSTEAD OF trigger defined on a view matches the select list of the SELECT statement defined for the view. Por exemplo:For example:

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 .The result set for this view has three columns: an int column and two nvarchar columns. 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.The inserted and deleted tables passed to an INSTEAD OF trigger defined on the view also have an int column named BusinessEntityID, an nvarchar column named LName, and an nvarchar column named 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.The select list of a view can also contain expressions that do not directly map to a single base-table column. 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.Some view expressions, such as a constant or function invocation, may not reference any columns and can be ignored. 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.Complex expressions can reference multiple columns, yet the inserted and deleted tables have only one value for each inserted row. 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.The same issues apply to simple expressions in a view if they reference a computed column that has a complex expression. Um gatilho INSTEAD OF na exibição deve tratar desses tipos de expressões.An INSTEAD OF trigger on the view must handle these types of expressions.