Cláusula OUTPUT (Transact-SQL)

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Banco de Dados SQL no Microsoft Fabric

Retorna informações ou expressões baseadas em cada linha afetada por uma INSERTinstrução , 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 OUTPUT cláusula em uma instrução aninhada INSERT, UPDATE, DELETE, ou MERGE e inserir esses resultados em uma tabela ou exibição de destino.

Observação

Uma UPDATEinstrução , INSERT, or DELETE que tem uma OUTPUT cláusula retornará linhas para o 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

Convenções de sintaxe do 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

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 deve ser declarado antes da INSERTinstrução , UPDATE, DELETE, orMERGE.

Se column_list não for especificada, a variável table precisará ter o mesmo número de colunas que o conjunto de resultados 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 variáveis de tabela , consulte tabela.

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 variável table precisará ter o mesmo número de colunas que o conjunto de resultados 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.

output_table não pode:

• Ter gatilhos habilitados definidos.
• Participe de ambos os lados de uma FOREIGN KEY restrição.
• Ter CHECK restrições ou regras habilitadas.

column_list

Uma lista opcional de nomes de coluna na tabela de destino da INTO cláusula. É análogo à lista de colunas permitida na instrução INSERT .

scalar_expression

Qualquer combinação de símbolos e operadores que são avaliados como um valor único. 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 ser qualificada com o prefixo INSERTED or 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 e todos os valores existentes que não são alterados com a operação atual. As colunas prefixadas com DELETED refletem o valor antes que a UPDATEinstrução , DELETE, or MERGE seja concluída.

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

INSERTED

Um prefixo de coluna que especifica o valor adicionado pela operação de inserção ou atualização e todos os valores existentes que não são alterados com a operação atual. As colunas prefixadas com INSERTED refletem o valor após a conclusão da instrução , INSERT, orMERGE, UPDATEmas antes que os gatilhos sejam executados.

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

from_table_name

Um prefixo FROM de coluna que especifica uma tabela incluída na cláusula de uma DELETEinstrução , UPDATE, ou MERGE que é usada para especificar as linhas a serem atualizadas ou excluídas.

Se a tabela que está sendo modificada também for especificada na FROM cláusula, qualquer referência a colunas nessa tabela deverá ser qualificada com o prefixo INSERTED or DELETED .

*

O asterisco (*) especifica que todas as colunas afetadas pela ação de exclusão, inserção ou atualização são retornadas na ordem em que existem na tabela.

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

DELETE Sales.ShoppingCartItem
    OUTPUT DELETED.*;

column_name

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

$action

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

Comentários

A OUTPUT <dml_select_list> cláusula e a OUTPUT <dml_select_list> INTO { @table_variable | output_table } cláusula podem ser definidas em uma única INSERTinstrução , UPDATE, DELETE, ou MERGE .

Observação

A menos que haja outra especificação, as referências à cláusula OUTPUT se referem tanto à cláusula OUTPUT quanto à OUTPUT INTO.

A OUTPUT cláusula pode ser útil para recuperar o valor de identity ou colunas computadas após uma INSERT operação or 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á garantia de que a ordem em que as alterações são aplicadas à tabela e a ordem em que as linhas são inseridas na tabela de saída ou na variável de tabela.

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

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

A cláusula OUTPUT não é compatível com as seguintes instruções:

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

• INSERT declarações que contêm uma EXECUTE declaração.

• Não são permitidos predicados de texto completo na cláusula OUTPUT quando o nível de compatibilidade do 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.

• Uma função definida pelo usuário não poderá ser criada se contiver uma OUTPUT INTO cláusula que tenha uma tabela como destino.

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

• 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. Considera-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 o SQL Server detecta essa coluna na cláusula OUTPUT, ocorre o erro 4186.

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

Ao capturar os resultados de uma OUTPUT cláusula em uma instrução aninhada INSERT, UPDATE, DELETE, ou MERGE e inserir esses resultados em uma tabela de destino, lembre-se das seguintes informações:

• Toda a operação é atômica. A instrução e a INSERT instrução DML aninhada que contém a OUTPUT cláusula são executadas ou toda a instrução falha.

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

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

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

  • 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 OUTPUT INTO cláusula não tem suporte em INSERT instruções que contêm uma <dml_table_source> cláusula.

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

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

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

• <dml_table_source> Na cláusula, as SELECT cláusulas and WHERE não podem incluir 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 ou a TEXTPTR() função.

Paralelismo

Uma OUTPUT cláusula que retorna resultados para o cliente, ou variável de tabela, sempre usa um plano serial.

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

Gatilhos

As colunas retornadas refletem OUTPUT os dados como estão após a conclusão da INSERTinstrução , UPDATE, or DELETE , mas antes que os gatilhos sejam executados.

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

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

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

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ê usa a .WRITE cláusula na UPDATE instrução para modificar uma coluna nvarchar(max), varchar(max) ou varbinary(max), as imagens completas antes e depois dos valores sã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 OUTPUT cláusula em uma DELETE instrução para retornar a linha excluída para o aplicativo de chamada.

USE AdventureWorks2022;
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 apropriada WHERE que possa garantir a semântica desejada ou entender que, quando várias linhas podem se qualificar para a operação DML, não há ordem garantida. 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;

Estes são os resultados:

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 e UPDATE DELETE instruções READPAST 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

SELECT permissões são necessárias em todas as colunas recuperadas <dml_select_list> ou usadas no <scalar_expression>.

INSERT permissões são necessárias em todas as tabelas especificadas em <output_table>.

Exemplos

Os exemplos de código do Transact-SQL deste artigo usa o banco de dados de exemplo AdventureWorks2022 ou AdventureWorksDW2022, que pode ser baixado da home page Microsoft SQL Server Samples and Community Projects.

R. Usar OUTPUT INTO com uma instrução INSERT

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, o valor gerado pelo Mecanismo de Banco de Dados para essa coluna é retornado na cláusula OUTPUT na coluna INSERTED.ScrapReasonID.

USE AdventureWorks2022;
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. Usar 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 AdventureWorks2022;
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. Usar 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 AdventureWorks2022;
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. Usar 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 AdventureWorks2022;
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. Usar 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 AdventureWorks2022;
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. Usar 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 AdventureWorks2022;
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. Usar OUTPUT INTO com um tipo de dados de objeto grande

O exemplo a seguir atualiza um valor parcial em DocumentSummary, uma coluna nvarchar(max) na Production.Document tabela, usando a .WRITE cláusula. 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. As imagens completas da coluna DocumentSummary anteriores e posteriores são retornadas.

USE AdventureWorks2022;
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. Usar 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 AdventureWorks2022;
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'). As ScrapReasonIDActual colunas e ModifiedDate refletem os valores gerados pela operação de gatilho em vez dos valores fornecidos na INSERT instrução.

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

I. Usar 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 AdventureWorks2022;
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. Usar OUTPUT e OUTPUT INTO em uma só 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 OUTPUT cláusula retorna as DELETED.ProductIDcolunas , DELETED.ProductPhotoID e a data e hora em que a linha foi excluída da tabela para o ProductProductPhoto aplicativo de chamada.

USE AdventureWorks2022;
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
INNER 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. Inserir 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 AdventureWorks2022;
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
        INNER 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

Conteúdo relacionado

• DELETE (Transact-SQL)
• INSERT (Transact-SQL)
• UPDATE (Transact-SQL)
• tabela (Transact-SQL)
• CREATE TRIGGER (Transact-SQL)
• sp_configure (Transact-SQL)