Select depois de insert
Pedro Marques
---------- Forwarded message ----------
From: Pedro Marques <thund...@oi.com.br>
Date: Mon, 8 Nov 2010 14:17:18 -0200
Subject: OUTPUT (Transact-SQL)
To: engsoft...@gmail.com
cláusula OUTPUT (Transact-SQL)
SQL Server 2008 R2
<javascript:;> Outras versões
http://i3.msdn.microsoft.com/Hash/8841a3a4fc3a5f17f74da3b076fe248a.png
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.
ms177564.note(pt-br,SQL.105).gifObservaçã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:
<http://msdn.microsoft.com/pt-br/library/ms189835.aspx> DELETE
<http://msdn.microsoft.com/pt-br/library/ms174335.aspx> INSERT
<http://msdn.microsoft.com/pt-br/library/ms177523.aspx> UPDATE
<http://msdn.microsoft.com/pt-br/library/bb510625.aspx> MERGE
Ícone de vínculo de tópico
<http://msdn.microsoft.com/pt-br/library/ms177563.aspx> Convenções de
sintaxe Transact-SQL
Sintaxe
_____
<javascript:CodeSnippet_CopyCode('CodeSnippetContainerCode0');> Copiar
<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 declarada
antes da instrução INSERT, UPDATE, DELETE ou MERGE.
Se column_list não for especificado, a variável table deverá 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 especificado, as colunas omitidas deverão permitir valores
nulos ou ter valores padrão atribuídos a elas.
Para obter mais informações sobre variáveis table, consulte
<http://msdn.microsoft.com/pt-br/library/ms175010.aspx> tabela
(Transact-SQL).
output_table
Especifica uma tabela na qual são inseridas as linhas retornadas, em vez de
serem retornadas ao chamador. output_table pode ser uma tabela temporária.
Se column_list não for especificado, a tabela deverá 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. Elas devem ser ignoradas. Se column_list
for especificado, as colunas omitidas deverão permitir valores nulos ou ter
valores padrão atribuídos a elas.
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. É análoga à lista de colunas permitida na instrução
<http://msdn.microsoft.com/pt-br/library/ms174335.aspx> INSERT.
scalar_expression
É qualquer combinação de símbolos e operadores que avalia um mesmo valor.
Funções de agregação não são permitidas em 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:
<javascript:CodeSnippet_CopyCode('CodeSnippetContainerCode1');> Copiar
DELETE Sales.ShoppingCartItem
OUTPUT DELETED.*;
column_name
É uma referência de coluna explícita. Toda referência à tabela que está
sendo modificada deve estar 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 dentre
três valores para cada linha: 'INSERT', 'UPDATE' ou 'DELETE', de acordo com
a ação que foi executada na linha.
Comentários
_____
As cláusulas OUTPUT <dml_select_list> e OUTPUT <dml_select_list> INTO {
@table_variable | output_table } podem ser definidas em uma mesma instrução
INSERT, UPDATE, DELETE ou MERGE.
ms177564.note(pt-br,SQL.105).gifObservaçã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 em <dml_select_list>, a coluna
correspondente na tabela de saída ou 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. Para obter mais informações, consulte
<http://msdn.microsoft.com/pt-br/library/cc645595.aspx> MSSQLSERVER_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 própria origem não pode conter uma cláusula <dml_table_source>.
· A cláusula OUTPUT INTO não é suportada em instruções INSERT que
contenham 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 agregadas, 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.
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 oferece suporte a tipos de dados de objeto grande:
nvarchar(max), varchar(max), varbinary(max), text, ntext, image e xml.
Quando a cláusula .WRITE é utilizada na instrução UPDATE para modificar uma
coluna nvarchar(max), varchar(max) ou varbinary(max), as imagens completas
dos valores de antes e depois são retornadas, caso sejam consultadas. A
função TEXTPTR ( ) não pode aparecer como 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.
<javascript:CodeSnippet_CopyCode('CodeSnippetContainerCode2');> Copiar
USE AdventureWorks2008R2;
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.
<javascript:CodeSnippet_CopyCode('CodeSnippetContainerCode3');> Copiar
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
ms177564.note(pt-br,SQL.105).gifObservaçã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
_____
Permissões de SELECT são necessárias nas colunas recuperadas por
<dml_select_list> ou usadas em <scalar_expression>.
Permissões de INSERT são necessárias 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
@MyTableVartable. Como a colunaScrapReasonID 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.
<javascript:CodeSnippet_CopyCode('CodeSnippetContainerCode4');> Copiar
USE AdventureWorks2008R2;
GO
DECLARE @MyTableVar table( ScrapReasonID 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 ScrapReasonID, 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.
<javascript:CodeSnippet_CopyCode('CodeSnippetContainerCode5');> Copiar
USE AdventureWorks2008R2;
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 VacationHours que
existe antes da aplicação da instrução UPDATE na coluna
DELETED.VacationHours e o valor atualizado na coluna INSERTED.VacationHours
para a variável @MyTableVartable.
Seguem duas instruções SELECT que retornam os valores em @MyTableVar e os
resultados da operação de atualização na tabela Employee. Note que os
resultados na colunaINSERTED.ModifiedDate não são iguais aos valores na
coluna ModifiedDate da tabela Employee. Isso porque um gatilho AFTER UPDATE,
que atualiza o valor de ModifiedDate com a data atual, está definido na
tabela Employee. Porém, as colunas retornadas de OUTPUT refletem os dados
antes da ação do gatilho.
<javascript:CodeSnippet_CopyCode('CodeSnippetContainerCode6');> Copiar
USE AdventureWorks2008R2;
GO
DECLARE @MyTableVar table(
EmpID int NOT NULL,
OldVacationHours int,
NewVacationHours int,
ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25
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.
--Note that ModifiedDate reflects the value generated by an
--AFTER UPDATE trigger.
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 @MyTableVartable na coluna
VacationHoursDifference.
<javascript:CodeSnippet_CopyCode('CodeSnippetContainerCode7');> Copiar
USE AdventureWorks2008R2;
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
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
O exemplo a seguir atualiza a coluna ScrapReasonID da tabela WorkOrder em
todas as ordens de trabalho com um ProductID e um ScrapReasonID
especificados. A cláusula OUTPUT INTOretorna 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.
<javascript:CodeSnippet_CopyCode('CodeSnippetContainerCode8');> Copiar
USE AdventureWorks2008R2;
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.
<javascript:CodeSnippet_CopyCode('CodeSnippetContainerCode9');> Copiar
USE AdventureWorks2008R2;
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-se 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 da coluna DocumentSummary de antes e depois para a variável
@MyTableVartable. Note que são retornadas as imagens completas de antes e
depois da coluna DocumentSummary.
<javascript:CodeSnippet_CopyCode('CodeSnippetContainerCode10');> Copiar
USE AdventureWorks2008R2;
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 colunaIDENTITY 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.
<javascript:CodeSnippet_CopyCode('CodeSnippetContainerCode11');> Copiar
USE AdventureWorks2008R2;
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'). Note que as colunas ScrapReasonIDActual e ModifiedDate
refletem os valores gerados pela operação do gatilho, em vez 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 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).
Como esses valores são gerados pelo Mecanismo de banco de dados do SQL
Server durante a operação de inserção, nenhuma dessas colunas pode ser
definida em @MyTableVar.
<javascript:CodeSnippet_CopyCode('CodeSnippetContainerCode12');> Copiar
USE AdventureWorks2008R2 ;
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(
LastName nvarchar(20) NOT NULL,
FirstName nvarchar(20) NOT NULL,
CurrentSales money NOT NULL
);
INSERT INTO dbo.EmployeeSales (LastName, FirstName, CurrentSales)
OUTPUT INSERTED.LastName,
INSERTED.FirstName,
INSERTED.CurrentSales
INTO @MyTableVar
SELECT c.LastName, c.FirstName, sp.SalesYTD
FROM HumanResources.Employee AS e
INNER JOIN Sales.SalesPerson AS sp
ON e.BusinessEntityID = sp.BusinessEntityID
INNER JOIN Person.Person AS c
ON e.BusinessEntityID = c.BusinessEntityID
WHERE e.BusinessEntityID LIKE '2%'
ORDER BY c.LastName, c.FirstName;
SELECT LastName, FirstName, CurrentSales
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 colunas da tabela que está sendo excluída
(DELETED.ProductID, DELETED.ProductPhotoID) e colunas da tabela Product para
a variável @MyTableVartable. A tabela Product é usada na cláusulaFROM 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 deProductProductPhoto para o aplicativo de chamada.
<javascript:CodeSnippet_CopyCode('CodeSnippetContainerCode13');> Copiar
USE AdventureWorks2008R2;
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 tabelaProductInventory 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.
<javascript:CodeSnippet_CopyCode('CodeSnippetContainerCode14');> Copiar
USE AdventureWorks2008R2;
GO
IF OBJECT_ID(N'Production.ZeroInventory', N'U') IS NOT NULL
DROP TABLE Production.ZeroInventory;
GO
--Create ZeroInventory table.
CREATE TABLE Production.ZeroInventory (ProductID int);
GO
INSERT INTO Production.ZeroInventory (ProductID)
SELECT ProductID
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 = '20030401'
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';
GO
SELECT ProductID FROM Production.ZeroInventory;
Consulte também
_____
Referência
<http://msdn.microsoft.com/pt-br/library/ms189835.aspx> DELETE
(Transact-SQL)
<http://msdn.microsoft.com/pt-br/library/ms174335.aspx> INSERT
(Transact-SQL)
<http://msdn.microsoft.com/pt-br/library/ms177523.aspx> UPDATE
(Transact-SQL)
<http://msdn.microsoft.com/pt-br/library/ms175010.aspx> tabela
(Transact-SQL)
<http://msdn.microsoft.com/pt-br/library/ms189799.aspx> CREATE TRIGGER
(Transact-SQL)
<http://msdn.microsoft.com/pt-br/library/ms188787.aspx> sp_configure
(Transact-SQL)
--
Att,
Pedro Marques M. Neto
MCTS Microsoft Windows Sharepoint Application Development