cláusula OUTPUT (Transact-SQL)

Aplica-se a: simSQL Server (todas as versões compatíveis) SimBanco de Dados SQL do Azure

Retorna informações ou expressões baseadas em cada linha afetada por uma instrução INSERT, UPDATE, DELETE ou MERGE. Esses resultados podem ser retornados ao aplicativo de processamento para uso em mensagens de confirmação, arquivamentos e outros requisitos similares de aplicativo. Os resultados também podem ser inseridos em uma tabela ou variável de tabela. Além disso, você pode capturar os resultados de uma cláusula OUTPUT em uma instrução INSERT, UPDATE, DELETE ou MERGE aninhada e inserir esses resultados em uma tabela ou exibição de destino.

Observação

Uma instrução UPDATE, INSERT ou DELETE que tem uma cláusula OUTPUT retornará linhas ao cliente mesmo que a instrução encontre erros e seja revertida. O resultado não deverá ser usado se ocorrer algum erro quando você executar a instrução.

Usado em:

DELETE

INSERT

UPDATE

MERGE

Ícone de link do tópico Convenções da sintaxe Transact-SQL

Sintaxe

<OUTPUT_CLAUSE> ::=  
{  
    [ OUTPUT <dml_select_list> INTO { @table_variable | output_table } [ ( column_list ) ] ]  
    [ OUTPUT <dml_select_list> ]  
}  
<dml_select_list> ::=  
{ <column_name> | scalar_expression } [ [AS] column_alias_identifier ]  
    [ ,...n ]  
  
<column_name> ::=  
{ DELETED | INSERTED | from_table_name } . { * | column_name }  
    | $action

Observação

Para ver a sintaxe do Transact-SQL para o SQL Server 2014 e versões anteriores, confira a Documentação das versões anteriores.

Argumentos

@table_variable
Especifica uma variável table na qual as linhas retornadas são inseridas, em vez de serem retornadas ao chamador. @table_variable precisa ser declarada antes da instrução INSERT, UPDATE, DELETE ou MERGE.

Se column_list não for especificada, a variável table precisará ter o mesmo número de colunas que o conjunto de resultados de OUTPUT. As colunas de identidade e as colunas computadas são exceções, que devem ser ignoradas. Se column_list for especificada, as colunas omitidas precisarão permitir valores nulos ou ter valores padrão atribuídos.

Para obter mais informações sobre as variáveis table, confira table (Transact-SQL).

output_table
Especifica uma tabela na qual as linhas retornadas são inseridas, em vez de serem retornadas ao chamador. output_table pode ser uma tabela temporária.

Se column_list não for especificada, a tabela precisará ter o mesmo número de colunas que o conjunto de resultados de OUTPUT. As colunas de identidade e as colunas computadas são exceções. Elas devem ser ignoradas. Se column_list for especificada, as colunas omitidas precisarão permitir valores nulos ou ter valores padrão atribuídos.

output_table não pode:

  • Ter gatilhos habilitados definidos.

  • Participar de uma restrição FOREIGN KEY de nenhuma forma.

  • Ter restrições CHECK ou regras habilitadas.

column_list
É uma lista opcional de nomes de coluna na tabela de destino da cláusula INTO. Ela é semelhante à lista de colunas permitida na instrução INSERT.

scalar_expression
É qualquer combinação de símbolos e operadores que avalia um mesmo valor. As funções de agregação não são permitidas na scalar_expression.

Qualquer referência a colunas na tabela que está sendo modificada deve estar qualificada com o prefixo INSERTED ou DELETED.

column_alias_identifier
É um nome alternativo usado como referência ao nome de coluna.

DELETED
É um prefixo de coluna que especifica o valor excluído pela operação de atualização ou exclusão. Colunas prefixadas com DELETED refletem o valor antes de a instrução UPDATE, DELETE ou MERGE ser concluída.

DELETED não pode ser usado com a cláusula OUTPUT na instrução INSERT.

INSERTED
É um prefixo de coluna que especifica o valor adicionado pela operação de inserção ou atualização. Colunas prefixadas com INSERTED refletem o valor depois da conclusão da instrução UPDATE, INSERT ou MERGE, mas antes da execução dos gatilhos.

INSERTED não pode ser usado com a cláusula OUTPUT na instrução DELETE.

from_table_name
É um prefixo de coluna que especifica uma tabela incluída na cláusula FROM de uma instrução DELETE, UPDATE ou MERGE utilizada para especificar as linhas a serem atualizadas ou excluídas.

Se a tabela que está sendo modificada também estiver especificada na cláusula FROM, toda a referência a colunas nessa tabela também deverá estar qualificada com o prefixo INSERTED ou DELETED.

*
Especifica que todas as colunas afetadas pela ação de exclusão, inserção ou atualização serão retornadas na ordem em que aparecem na tabela.

Por exemplo, OUTPUT DELETED.* na instrução DELETE a seguir retorna todas as colunas excluídas da tabela ShoppingCartItem:

DELETE Sales.ShoppingCartItem
    OUTPUT DELETED.*;

column_name
É uma referência de coluna explícita. Toda referência à tabela que está sendo modificada deve ser corretamente qualificada pelo prefixo INSERTED ou DELETED, conforme apropriado, por exemplo: INSERTED . column_name.

$action
Está disponível apenas para a instrução MERGE. Especifica uma coluna do tipo nvarchar(10) na cláusula OUTPUT em uma instrução MERGE que retorna um entre três valores para cada linha: 'INSERT', 'UPDATE' ou 'DELETE', de acordo com a ação que foi executada nessa linha.

Comentários

A cláusula OUTPUT <dml_select_list> e a cláusula OUTPUT <dml_select_list> INTO { @ table_variable | output_table } pode ser definida em uma instrução INSERT, UPDATE, DELETE ou MERGE.

Observação

Salvo indicação em contrário, as referências à cláusula OUTPUT se referem tanto à cláusula OUTPUT, quanto à cláusula OUTPUT INTO.

A cláusula OUTPUT pode ser útil para recuperar o valor de identidade ou colunas computadas depois de uma operação INSERT ou UPDATE.

Quando uma coluna computada é incluída na <dml_select_list>, a coluna correspondente na tabela de saída ou na variável de tabela não é uma coluna computada. Os valores na nova coluna são aqueles que foram computados no momento em que a instrução foi executada.

Não há nenhuma garantia de que a ordem na qual as alterações são aplicadas à tabela e a ordem na qual as linhas são inseridas na tabela de saída ou na variável de tabela correspondam.

Se forem modificados parâmetros ou variáveis como parte de uma instrução UPDATE, a cláusula OUTPUT sempre retornará o valor do parâmetro ou a variável como era antes de a instrução ser executada, e não o valor modificado.

Você pode usar OUTPUT com uma instrução UPDATE ou DELETE posicionada em um cursor que use a sintaxe WHERE CURRENT OF.

Não há suporte para a cláusula OUTPUT nas seguintes instruções:

  • Instruções DML que façam referência a exibições particionadas locais, exibições particionadas distribuídas ou tabelas remotas.

  • Instruções INSERT contendo uma instrução EXECUTE.

  • Não são permitidos predicados de texto completo na cláusula OUTPUT quando o nível de compatibilidade de banco de dados é definido como 100.

  • A cláusula OUTPUT INTO não pode ser usada para inserção em uma exibição ou função de conjunto de linhas.

  • Não é possível criar uma função definida pelo usuário caso ela contenha uma cláusula OUTPUT INTO que tenha uma tabela como seu destino.

Para impedir um comportamento não determinista, a cláusula OUTPUT não pode conter as referências a seguir:

  • Subconsultas ou funções definidas pelo usuário que executam acesso a dados pelo usuário ou sistema ou que assumem que executam tal acesso. Supõe-se que as funções definidas pelo usuário executam acesso a dados quando não são associadas a esquema.

  • Uma coluna de uma função com valor de tabela embutida ou exibição quando essa coluna é definida por um dos seguintes métodos:

    • Uma subconsulta.

    • Uma função definida pelo usuário que executa acesso a dados de usuário ou de sistema ou que supostamente executa tal acesso.

    • Uma coluna computada que contém uma função definida pelo usuário e que executa acesso a dados de usuário ou de sistema em sua definição.

    Quando SQL Server detectar tal coluna na cláusula OUTPUT, ocorrerá o erro 4186.

Inserindo dados retornados de uma cláusula OUTPUT em uma tabela

Ao capturar os resultados de uma cláusula OUTPUT em uma instrução INSERT, UPDATE, DELETE ou MERGE aninhada e inserir esses resultados em uma tabela ou exibição de destino, lembre-se do seguinte:

  • Toda a operação é atômica. As instruções INSERT interna e DML aninhada que contêm a cláusula OUTPUT cláusula são executadas ou falham inteiramente.

  • As seguintes restrições aplicam-se ao destino da instrução INSERT exterior:

    • O destino não pode ser uma tabela remota, exibição ou expressão de tabela comum.

    • O destino não pode ter uma restrição FOREIGN KEY nem ser referenciado por uma restrição FOREIGN KEY.

    • Não podem ser definidos gatilhos no destino.

    • O gatilho não pode participar de replicação de mesclagem ou de assinaturas atualizáveis para replicação transacional.

  • As seguintes restrições aplicam-se à instrução DML aninhada:

    • O destino não pode ser uma tabela remota ou exibição particionada.

    • A origem em si não pode conter uma cláusula <dml_table_source>.

  • A cláusula OUTPUT INTO não é compatível com instruções INSERT que contêm uma cláusula <dml_table_source>.

  • @@ROWCOUNT retorna as linhas inseridas apenas pela instrução INSERT externa.

  • @@IDENTITY, SCOPE_IDENTITY e IDENT_CURRENT retornam valores de identidade gerados apenas pela instrução DML aninhada, e não os valores gerados pela instrução INSERT externa.

  • As notificações de consulta tratam a instrução como uma única entidade, e o tipo de qualquer mensagem criada será o tipo DML aninhado, mesmo que alteração significativa seja proveniente da própria instrução INSERT.

  • Na cláusula <dml_table_source>, as cláusulas SELECT e WHERE não podem conter subconsultas, funções de agregação, funções de classificação, predicados de texto completo, funções definidas pelo usuário que executam acesso a dados nem a função TEXTPTR.

Paralelismo

Uma cláusula OUTPUT que retorna resultados para o cliente sempre usará um plano serial.

No contexto de um banco de dados definido com o nível de compatibilidade 130 ou superior, se uma operação INSERT...SELECT usar uma dica WITH (TABLOCK) para a instrução SELECT e também usar OUTPUT…INTO para inserir em uma tabela temporária ou de usuário, a tabela de destino da instrução INSERT…SELECT será qualificada para o paralelismo, dependendo do custo da subárvore. A tabela de destino referenciada na cláusula OUTPUT INTO não será qualificada para o paralelismo.

Gatilhos

Colunas retornadas de OUTPUT refletem os dados da forma em que se encontram após a conclusão da instrução UPDATE, INSERT ou MERGE, mas antes da execução dos gatilhos.

No caso dos gatilhos INSTEAD OF, os resultados retornados são gerados como se INSERT, UPDATE ou DELETE tivesse ocorrido de fato, mesmo que nenhuma modificação aconteça como resultado da operação do gatilho. Se uma instrução que inclui uma cláusula OUTPUT for usada dentro do corpo de um disparador, devem ser usados aliases de tabela para fazer referência às tabelas inseridas e excluídas pelo disparador, para evitar referências duplicadas a colunas com as tabelas INSERTED e DELETED associadas à OUTPUT.

Se a cláusula OUTPUT for especificada sem especificação da palavra-chave INTO, o destino da operação de DML não poderá ter um gatilho habilitado definido para a ação DML fornecida. Por exemplo, se a cláusula OUTPUT estiver definida em uma instrução UPDATE, a tabela de destino não poderá ter nenhum gatilho UPDATE habilitado.

Se a opção sp_configure disallow results from triggers estiver definida, uma cláusula OUTPUT sem cláusula INTO fará com que a instrução falhe quando ela for invocada a partir de um disparador.

Tipos de dados

A cláusula OUTPUT é compatível com os tipos de dados de objeto grande: nvarchar(max) , varchar(max) , varbinary(max) , text, ntext, image e xml. Quando você usar a cláusula .WRITE na instrução UPDATE para modificar uma coluna nvarchar(max) , varchar(max) ou varbinary(max) , as imagens completas de antes e depois dos valores serão retornadas se forem referenciadas. A função TEXTPTR( ) não pode fazer parte de uma expressão em uma coluna text, ntext ou image na cláusula OUTPUT.

Filas

Você pode usar OUTPUT em aplicativos que usam tabelas como filas ou para manter conjuntos de resultados intermediários. Ou seja, o aplicativo está somando ou removendo linhas constantemente da tabela. O exemplo a seguir usa a cláusula OUTPUT em uma instrução DELETE para retornar a linha excluída para o aplicativo de chamada.

USE AdventureWorks2012;
GO

DELETE TOP(1) dbo.DatabaseLog WITH (READPAST)  
OUTPUT deleted.*  
WHERE DatabaseLogID = 7;  
GO

Este exemplo remove uma linha de uma tabela usada como fila e retorna os valores excluídos para o aplicativo de processamento em uma única ação. Outras semânticas também podem ser implementadas, como usar uma tabela para implementar uma pilha. Porém, o SQL Server não garante a ordem em que as linhas são processadas e retornadas por instruções DML que usam a cláusula OUTPUT. Cabe ao aplicativo incluir uma cláusula WHERE apropriada que possa garantir a semântica desejada ou entender que, quando várias linhas puderem se qualificar para a operação DML, não haverá nenhuma garantia de ordem. O exemplo a seguir usa uma subconsulta e presume que exclusividade seja uma característica da coluna DatabaseLogID para implementar a semântica de ordenação desejada.

USE tempdb;
GO

CREATE TABLE dbo.table1  
(  
    id INT,  
    employee VARCHAR(32)  
);  
GO  
  
INSERT INTO dbo.table1 VALUES   
      (1, 'Fred')  
     ,(2, 'Tom')  
     ,(3, 'Sally')  
     ,(4, 'Alice');  
GO  
  
DECLARE @MyTableVar TABLE  
(  
    id INT,  
    employee VARCHAR(32)  
);  
  
PRINT 'table1, before delete'   
SELECT * FROM dbo.table1;  
  
DELETE FROM dbo.table1  
OUTPUT DELETED.* INTO @MyTableVar  
WHERE id = 4 OR id = 2;  
  
PRINT 'table1, after delete'  
SELECT * FROM dbo.table1;  
  
PRINT '@MyTableVar, after delete'  
SELECT * FROM @MyTableVar;  
  
DROP TABLE dbo.table1;  
--Results  
--table1, before delete  
--id          employee  
------------- ------------------------------  
--1           Fred  
--2           Tom  
--3           Sally  
--4           Alice  
--  
--table1, after delete  
--id          employee  
------------- ------------------------------  
--1           Fred  
--3           Sally  
--@MyTableVar, after delete  
--id          employee  
------------- ------------------------------  
--2           Tom  
--4           Alice

Observação

Use a dica de tabela READPAST nas instruções UPDATE e DELETE, se o cenário permitir que vários aplicativos executem uma leitura destrutiva de uma tabela. Isso impedirá que venham a acontecer problemas de bloqueios, caso outro aplicativo já esteja lendo o primeiro registro de qualificação na tabela.

Permissões

São necessárias permissões SELECT nas colunas recuperadas por meio de <dml_select_list> ou usadas em <scalar_expression>.

São necessárias permissões INSERT nas tabelas especificadas em <output_table>.

Exemplos

a. Usando OUTPUT INTO com uma instrução INSERT simples

O exemplo a seguir insere uma linha na tabela ScrapReason e usa a cláusula OUTPUT para retornar os resultados da instrução para a variável de tabela @MyTableVar. Como a coluna ScrapReasonID está definida com uma propriedade IDENTITY, não é especificado um valor na instrução INSERT dessa coluna. Porém, note que o valor gerado pelo Mecanismo de Banco de Dados para a coluna é retornado na cláusula OUTPUT na coluna inserted.ScrapReasonID.

USE AdventureWorks2012;
GO

DECLARE @MyTableVar TABLE (
    NewScrapReasonID SMALLINT,  
    Name VARCHAR(50),  
    ModifiedDate DATETIME); 
    
INSERT Production.ScrapReason  
    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate  
        INTO @MyTableVar  
VALUES (N'Operator error', GETDATE());  
  
--Display the result set of the table variable.  
SELECT NewScrapReasonID, Name, ModifiedDate FROM @MyTableVar;  
--Display the result set of the table.  
SELECT ScrapReasonID, Name, ModifiedDate   
FROM Production.ScrapReason;  
GO

B. Usando OUTPUT com uma instrução DELETE

O exemplo a seguir exclui todas as linhas da tabela ShoppingCartItem. A cláusula OUTPUT deleted.* especifica que os resultados da instrução DELETE, que são todas as colunas nas linhas excluídas, sejam retornados para o aplicativo de chamada. A instrução SELECT que segue verifica os resultados da operação de exclusão na tabela ShoppingCartItem.

USE AdventureWorks2012;
GO

DELETE Sales.ShoppingCartItem  
OUTPUT DELETED.*   
WHERE ShoppingCartID = 20621;  
  
--Verify the rows in the table matching the WHERE clause have been deleted.  
SELECT COUNT(*) AS [Rows in Table] FROM Sales.ShoppingCartItem WHERE ShoppingCartID = 20621;  
GO

C. Usando OUTPUT INTO com uma instrução UPDATE

O exemplo a seguir atualiza a coluna VacationHours na tabela Employee em 25% nas primeiras 10 linhas. A cláusula OUTPUT retorna o valor de VacationHours existente antes da aplicação da instrução UPDATE na coluna deleted.VacationHours e o valor atualizado na coluna inserted.VacationHours para a variável de tabela @MyTableVar.

Seguem duas instruções SELECT que retornam os valores em @MyTableVar e os resultados da operação de atualização na tabela Employee.

USE AdventureWorks2012;
GO
  
DECLARE @MyTableVar TABLE (  
    EmpID INT NOT NULL,  
    OldVacationHours INT,  
    NewVacationHours INT,  
    ModifiedDate DATETIME);  
  
UPDATE TOP (10) HumanResources.Employee  
SET VacationHours = VacationHours * 1.25,  
    ModifiedDate = GETDATE()   
OUTPUT inserted.BusinessEntityID,  
       deleted.VacationHours,  
       inserted.VacationHours,  
       inserted.ModifiedDate  
INTO @MyTableVar;  
  
--Display the result set of the table variable.  
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate  
FROM @MyTableVar;  
GO  
--Display the result set of the table.  
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate  
FROM HumanResources.Employee;  
GO  

D. Usando OUTPUT INTO para retornar uma expressão

O exemplo a seguir se baseia no exemplo C, definindo uma expressão na cláusula OUTPUT como diferença entre o valor VacationHours atualizado e o valor VacationHours antes de a atualização ser aplicada. O valor dessa expressão é retornado para a variável de tabela @MyTableVar na coluna VacationHoursDifference.

USE AdventureWorks2012;  
GO

DECLARE @MyTableVar TABLE (  
    EmpID INT NOT NULL,  
    OldVacationHours INT,  
    NewVacationHours INT,  
    VacationHoursDifference INT,  
    ModifiedDate DATETIME);  
  
UPDATE TOP (10) HumanResources.Employee  
SET VacationHours = VacationHours * 1.25,  
    ModifiedDate = GETDATE()  
OUTPUT inserted.BusinessEntityID,  
       deleted.VacationHours,  
       inserted.VacationHours,  
       inserted.VacationHours - deleted.VacationHours,  
       inserted.ModifiedDate  
INTO @MyTableVar;  
  
--Display the result set of the table variable.  
SELECT EmpID, OldVacationHours, NewVacationHours,   
    VacationHoursDifference, ModifiedDate  
FROM @MyTableVar;  
GO  
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate  
FROM HumanResources.Employee;  
GO

E. Usando OUTPUT INTO com from_table_name em uma instrução UPDATE

A exemplo a seguir atualiza a coluna ScrapReasonID na tabela WorkOrder para todas as ordens de serviço com uma ProductID e uma ScrapReasonID especificadas. A cláusula OUTPUT INTO retorna valores da tabela que está sendo atualizada (WorkOrder) e também da tabela Product. A tabela Product é usada na cláusula FROM para especificar as linhas a serem atualizadas. Como a tabela WorkOrder tem um gatilho AFTER UPDATE definido, é necessária a palavra-chave INTO.

USE AdventureWorks2012;
GO

DECLARE @MyTestVar TABLE (  
    OldScrapReasonID INT NOT NULL,   
    NewScrapReasonID INT NOT NULL,   
    WorkOrderID INT NOT NULL,  
    ProductID INT NOT NULL,  
    ProductName NVARCHAR(50)NOT NULL);  
  
UPDATE Production.WorkOrder  
SET ScrapReasonID = 4  
OUTPUT deleted.ScrapReasonID,  
       inserted.ScrapReasonID,   
       inserted.WorkOrderID,  
       inserted.ProductID,  
       p.Name  
    INTO @MyTestVar  
FROM Production.WorkOrder AS wo  
    INNER JOIN Production.Product AS p   
    ON wo.ProductID = p.ProductID   
    AND wo.ScrapReasonID= 16  
    AND p.ProductID = 733;  
  
SELECT OldScrapReasonID, NewScrapReasonID, WorkOrderID,   
    ProductID, ProductName   
FROM @MyTestVar;  
GO

F. Usando OUTPUT INTO com from_table_name em uma instrução DELETE

O exemplo a seguir exclui linhas da tabela ProductProductPhoto com base em critérios de pesquisa definidos na cláusula FROM da instrução DELETE. A cláusula OUTPUT retorna colunas da tabela que está sendo excluída (deleted.ProductID, deleted.ProductPhotoID) e colunas da tabela Product. Essa tabela é usada na cláusula FROM para especificar as linhas a serem excluídas.

USE AdventureWorks2012;
GO

DECLARE @MyTableVar TABLE (  
    ProductID INT NOT NULL,   
    ProductName NVARCHAR(50)NOT NULL,  
    ProductModelID INT NOT NULL,   
    PhotoID INT NOT NULL);  
  
DELETE Production.ProductProductPhoto  
OUTPUT DELETED.ProductID,  
       p.Name,  
       p.ProductModelID,  
       DELETED.ProductPhotoID  
    INTO @MyTableVar  
FROM Production.ProductProductPhoto AS ph  
JOIN Production.Product as p   
    ON ph.ProductID = p.ProductID   
    WHERE p.ProductModelID BETWEEN 120 and 130;  
  
--Display the results of the table variable.  
SELECT ProductID, ProductName, ProductModelID, PhotoID   
FROM @MyTableVar  
ORDER BY ProductModelID;  
GO

G. Usando OUTPUT INTO com um tipo de dados de objeto grande

O exemplo a seguir atualiza um valor parcial em DocumentSummary e uma coluna nvarchar(max) na tabela Production.Document usando a cláusula .WRITE. A palavra components é substituída pela palavra features especificando a palavra de substituição, o local de início (deslocamento) da palavra a ser substituída nos dados existentes e o número de caracteres a serem substituídos (comprimento). O exemplo usa a cláusula OUTPUT para retornar as imagens de antes e depois da coluna DocumentSummary para a variável de tabela @MyTableVar. Observe que são retornadas as imagens completas de antes e depois da coluna DocumentSummary.

USE AdventureWorks2012;
GO

DECLARE @MyTableVar TABLE (  
    SummaryBefore NVARCHAR(max),  
    SummaryAfter NVARCHAR(max));  
  
UPDATE Production.Document  
SET DocumentSummary .WRITE (N'features',28,10)  
OUTPUT deleted.DocumentSummary,   
       inserted.DocumentSummary   
    INTO @MyTableVar  
WHERE Title = N'Front Reflector Bracket Installation';  
  
SELECT SummaryBefore, SummaryAfter   
FROM @MyTableVar;  
GO

H. Usando OUTPUT em um gatilho INSTEAD OF

O exemplo a seguir usa a cláusula OUTPUT em um gatilho para retornar os resultados da operação do gatilho. Primeiro, uma exibição é criada na tabela ScrapReason e, em seguida, um gatilho INSTEAD OF INSERT é definido na exibição, permitindo que apenas a coluna Name da tabela base seja modificada pelo usuário. Como a coluna ScrapReasonID é uma coluna IDENTITY na tabela base, o gatilho ignora o valor fornecido pelo usuário. Isso permite ao Mecanismo de Banco de Dados gerar o valor correto automaticamente. O valor fornecido pelo usuário para ModifiedDate também é ignorado, sendo definido como a data atual. A cláusula OUTPUT retorna os valores inseridos de fato na tabela ScrapReason.

USE AdventureWorks2012;
GO

IF OBJECT_ID('dbo.vw_ScrapReason','V') IS NOT NULL  
    DROP VIEW dbo.vw_ScrapReason;  
GO  
CREATE VIEW dbo.vw_ScrapReason  
AS (SELECT ScrapReasonID, Name, ModifiedDate  
    FROM Production.ScrapReason);  
GO  
CREATE TRIGGER dbo.io_ScrapReason   
    ON dbo.vw_ScrapReason  
INSTEAD OF INSERT  
AS  
BEGIN  
--ScrapReasonID is not specified in the list of columns to be inserted   
--because it is an IDENTITY column.  
    INSERT INTO Production.ScrapReason (Name, ModifiedDate)  
        OUTPUT INSERTED.ScrapReasonID, INSERTED.Name,   
               INSERTED.ModifiedDate  
    SELECT Name, getdate()  
    FROM inserted;  
END  
GO  
INSERT vw_ScrapReason (ScrapReasonID, Name, ModifiedDate)  
VALUES (99, N'My scrap reason','20030404');  
GO

Eis o conjunto de resultados gerado no dia 12 de abril de 2004 ('2004-04-12'). Observe que as colunas ScrapReasonIDActual e ModifiedDate refletem os valores gerados pela operação do gatilho, no lugar dos valores fornecidos na instrução INSERT.

ScrapReasonID  Name             ModifiedDate  
-------------  ---------------- -----------------------  
17             My scrap reason  2004-04-12 16:23:33.050

I. Usando OUTPUT INTO com colunas de identidade e colunas computadas

O exemplo a seguir cria a tabela EmployeeSales e, em seguida, insere várias linhas nela por meio de uma instrução INSERT com uma instrução SELECT, para recuperar dados das tabelas de origem. A tabela EmployeeSales contém uma coluna de identidade (EmployeeID) e uma coluna computada (ProjectedSales).

USE AdventureWorks2012;
GO

IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL  
    DROP TABLE dbo.EmployeeSales;  
GO  
CREATE TABLE dbo.EmployeeSales  
( EmployeeID   INT IDENTITY (1,5) NOT NULL,  
  LastName     NVARCHAR(20) NOT NULL,  
  FirstName    NVARCHAR(20) NOT NULL,  
  CurrentSales MONEY NOT NULL,  
  ProjectedSales AS CurrentSales * 1.10   
);  
GO  
DECLARE @MyTableVar TABLE (  
  EmployeeID   INT NOT NULL,  
  LastName     NVARCHAR(20) NOT NULL,  
  FirstName    NVARCHAR(20) NOT NULL,  
  CurrentSales MONEY NOT NULL,  
  ProjectedSales MONEY NOT NULL  
  );  
  
INSERT INTO dbo.EmployeeSales (LastName, FirstName, CurrentSales)  
  OUTPUT INSERTED.EmployeeID,
         INSERTED.LastName,   
         INSERTED.FirstName,   
         INSERTED.CurrentSales,
         INSERTED.ProjectedSales
  INTO @MyTableVar  
    SELECT c.LastName, c.FirstName, sp.SalesYTD  
    FROM Sales.SalesPerson AS sp  
    INNER JOIN Person.Person AS c  
        ON sp.BusinessEntityID = c.BusinessEntityID  
    WHERE sp.BusinessEntityID LIKE '2%'  
    ORDER BY c.LastName, c.FirstName;  
  
SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales  
FROM @MyTableVar;  
GO  
SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales  
FROM dbo.EmployeeSales;  
GO

J. Usando OUTPUT e OUTPUT INTO em uma única instrução

O exemplo a seguir exclui linhas da tabela ProductProductPhoto com base em critérios de pesquisa definidos na cláusula FROM da instrução DELETE. A cláusula OUTPUT INTO retorna as colunas da tabela que está sendo excluída (deleted.ProductID, deleted.ProductPhotoID) e as colunas da tabela Product para a variável de tabela @MyTableVar. A tabela Product é usada na cláusula FROM para especificar as linhas a serem excluídas. A cláusula OUTPUT retorna as colunas deleted.ProductID e deleted.ProductPhotoID e a data e a hora em que a linha foi excluída da tabela ProductProductPhoto ao aplicativo de chamada.

USE AdventureWorks2012;
GO

DECLARE @MyTableVar TABLE (  
    ProductID INT NOT NULL,   
    ProductName NVARCHAR(50)NOT NULL,  
    ProductModelID INT NOT NULL,   
    PhotoID INT NOT NULL);  
  
DELETE Production.ProductProductPhoto  
OUTPUT DELETED.ProductID,  
       p.Name,  
       p.ProductModelID,  
       DELETED.ProductPhotoID  
    INTO @MyTableVar  
OUTPUT DELETED.ProductID, DELETED.ProductPhotoID, GETDATE() AS DeletedDate   
FROM Production.ProductProductPhoto AS ph  
JOIN Production.Product as p   
    ON ph.ProductID = p.ProductID   
WHERE p.ProductID BETWEEN 800 and 810;  
  
--Display the results of the table variable.  
SELECT ProductID, ProductName, PhotoID, ProductModelID   
FROM @MyTableVar;  
GO

K. Inserindo dados retornados de uma cláusula OUTPUT

O exemplo a seguir captura dados retornados da cláusula OUTPUT de uma instrução MERGE e insere esses dados em outra tabela. A instrução MERGE atualiza a coluna Quantity da tabela ProductInventory diariamente, com base nos pedidos processados na tabela SalesOrderDetail. Ela também exclui linhas de produtos cujos inventários caem para 0 ou menos. O exemplo captura as linhas excluídas e as insere em outra tabela, ZeroInventory, que rastreia produtos sem-estoque.

USE AdventureWorks2012;
GO

IF OBJECT_ID(N'Production.ZeroInventory', N'U') IS NOT NULL  
    DROP TABLE Production.ZeroInventory;  
GO  
--Create ZeroInventory table.  
CREATE TABLE Production.ZeroInventory (DeletedProductID int, RemovedOnDate DateTime);  
GO  
  
INSERT INTO Production.ZeroInventory (DeletedProductID, RemovedOnDate)  
SELECT ProductID, GETDATE()  
FROM  
(   MERGE Production.ProductInventory AS pi  
    USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod  
           JOIN Sales.SalesOrderHeader AS soh  
           ON sod.SalesOrderID = soh.SalesOrderID  
           AND soh.OrderDate = '20070401'  
           GROUP BY ProductID) AS src (ProductID, OrderQty)  
    ON (pi.ProductID = src.ProductID)  
    WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0  
        THEN DELETE  
    WHEN MATCHED  
        THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty  
    OUTPUT $action, deleted.ProductID) AS Changes (Action, ProductID)  
WHERE Action = 'DELETE';  
IF @@ROWCOUNT = 0  
PRINT 'Warning: No rows were inserted';  
GO  
SELECT DeletedProductID, RemovedOnDate
FROM Production.ZeroInventory;
GO

Consulte Também

DELETE (Transact-SQL)
INSERT (Transact-SQL)
UPDATE (Transact-SQL)
table (Transact-SQL)
CREATE TRIGGER (Transact-SQL)
sp_configure (Transact-SQL)