Clausola OUTPUT (Transact-SQL)
Si applica a: SQL Server database SQL di Azure Istanza gestita di SQL di Azure
Restituisce le informazioni da (o le espressioni basate su) ogni riga interessata da un'istruzione INSERT, UPDATE, DELETE o MERGE. Questi risultati possono essere restituiti all'applicazione di elaborazione per l'utilizzo nei messaggi di errore, l'archiviazione e altri scopi simili dell'applicazione. I risultati possono anche essere inseriti in una tabella o in una variabile di tabella. Inoltre, è possibile acquisire i risultati di una OUTPUT
clausola in un'istruzione INSERT, UPDATE, DELETE o MERGE annidata e inserire tali risultati in una tabella o vista di destinazione.
Nota
Un'istruzione UPDATE, INSERT o DELETE con una OUTPUT
clausola restituirà righe al client anche se l'istruzione rileva errori e viene eseguito il rollback. Il risultato non deve essere usato se si verifica un errore quando si esegue l'istruzione .
Usata in:
Convenzioni relative alla sintassi Transact-SQL
Sintassi
<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
Nota
Per visualizzare la sintassi Transact-SQL per SQL Server 2014 (12.x) e versioni precedenti, vedere Documentazione delle versioni precedenti.
Argomenti
@table_variable
Specifica una variabile table in cui vengono inserite le righe restituite invece di essere restituite al chiamante. @table_variable deve essere dichiarato prima dell'istruzione INSERT, UPDATE, DELETE o MERGE.
Se non viene specificato column_list , la variabile di tabella deve avere lo stesso numero di colonne del OUTPUT
set di risultati. Le eccezioni sono le colonne calcolate e Identity, le quali devono essere ignorate. Se column_list viene specificato, le colonne omesse devono consentire i valori Null oppure devono avere valori predefiniti assegnati.
Per altre informazioni sulle variabili table, vedere table (Transact-SQL).
output_table
Specifica una tabella in cui vengono inserite le righe restituite invece di essere restituite al chiamante. output_table può essere una tabella temporanea.
Se non viene specificato column_list , la tabella deve avere lo stesso numero di colonne del OUTPUT
set di risultati. Le eccezioni sono le colonne calcolate e Identity, che devono essere ignorate. Se column_list viene specificato, le colonne omesse devono consentire i valori Null oppure devono avere valori predefiniti assegnati.
output_table non è possibile:
- avere trigger abilitati definiti
- Partecipare in un vincolo di chiave esterna
- avere regole abilitate o vincoli CHECK.
column_list
Elenco facoltativo di nomi di colonna nella tabella di destinazione della clausola INTO. È analogo all'elenco delle colonne consentite nell'istruzione INSERT.
scalar_expression
Qualsiasi combinazione di simboli e operatori che restituisce un singolo valore. Le funzioni di aggregazione non sono consentite in scalar_expression.
Qualsiasi riferimento alle colonne della tabella che viene modificata deve essere qualificato con il prefisso INSERTED o DELETED.
column_alias_identifier
Nome alternativo utilizzato per fare riferimento al nome della colonna.
DELETED
Prefisso di colonna che specifica il valore eliminato dall'operazione di aggiornamento o eliminazione. Le colonne con prefisso DELETED riflettono il valore prima del completamento dell'istruzione UPDATE, DELETE o MERGE.
L'istruzione DELETED non può essere usata con la OUTPUT
clausola nell'istruzione INSERT.
INSERTED
Prefisso di colonna che specifica il valore aggiunto dall'operazione di inserimento o aggiornamento. Le colonne con prefisso INSERTED riflettono il valore dopo il completamento dell'istruzione UPDATE, INSERT o MERGE ma prima dell'esecuzione dei trigger.
INSERTED non può essere usato con la OUTPUT
clausola nell'istruzione DELETE.
from_table_name
Prefisso di colonna che specifica una tabella inclusa nella clausola FROM di un'istruzione DELETE, UPDATE o MERGE utilizzata per specificare le righe da aggiornare o eliminare.
Se anche la tabella che viene modificata è specificata nella clausola FROM, qualsiasi riferimento alle colonne della tabella deve essere qualificato con il prefisso INSERTED o DELETED.
*
*
Specifica che tutte le colonne interessate dall'azione di eliminazione, inserimento o aggiornamento vengono restituite nell'ordine in cui sono presenti nella tabella.
Ad esempio, OUTPUT DELETED.*
nell'istruzione DELETE seguente restituisce tutte le colonne eliminate dalla tabella ShoppingCartItem
:
DELETE Sales.ShoppingCartItem
OUTPUT DELETED.*;
column_name
Riferimento di colonna esplicito. Qualsiasi riferimento alla tabella che viene modificata deve essere qualificato correttamente tramite il prefisso INSERTED o DELETED in base alle esigenze, ad esempio: INSERTED.column_name.
$action
Disponibile solo per l'istruzione MERGE. Specifica una colonna di tipo nvarchar(10) nella OUTPUT
clausola in un'istruzione MERGE che restituisce uno dei tre valori per ogni riga: 'INSERT', 'UPDATE' o 'DELETE', in base all'azione eseguita su tale riga.
Osservazioni:
La OUTPUT <dml_select_list>
clausola e la OUTPUT <dml_select_list> INTO { @table_variable | output_table }
clausola possono essere definite in una singola istruzione INSERT, UPDATE, DELETE o MERGE.
Nota
Se non specificato diversamente, i riferimenti alla OUTPUT
clausola fanno riferimento sia alla OUTPUT
clausola che alla OUTPUT INTO
clausola .
La OUTPUT
clausola può essere utile per recuperare il valore delle colonne identity o calcolate dopo un'operazione INSERT o UPDATE.
Quando una colonna calcolata è inclusa in <dml_select_list>
, la colonna corrispondente nella tabella di output o nella variabile di tabella non è una colonna calcolata. I valori della nuova colonna corrispondono ai valori calcolati quando è stata eseguita l'istruzione.
Non esiste alcuna garanzia che l'ordine in cui le modifiche vengono applicate alla tabella e l'ordine in cui le righe vengono inserite nella tabella di output o nella variabile di tabella, corrisponderanno.
Se i parametri o le variabili vengono modificati come parte di un'istruzione UPDATE, la OUTPUT
clausola restituisce sempre il valore del parametro o della variabile come prima dell'esecuzione dell'istruzione anziché del valore modificato.
È possibile usare OUTPUT
con un'istruzione UPDATE o DELETE posizionata su un cursore che usa la sintassi WHERE CURRENT OF.
La OUTPUT
clausola non è supportata nelle istruzioni seguenti:
istruzioni DML che fanno riferimento a viste partizionate locali, viste partizionate distribuite o tabelle remote
istruzioni INSERT che contengono un'istruzione EXECUTE.
I predicati full-text non sono consentiti nella
OUTPUT
clausola quando il livello di compatibilità del database è impostato su 100.La
OUTPUT INTO
clausola non può essere usata per inserire in una vista o una funzione del set di righe.Non è possibile creare una funzione definita dall'utente se contiene una
OUTPUT
clausola INTO con una tabella come destinazione.
Per evitare un comportamento non deterministico, la OUTPUT
clausola non può contenere i riferimenti seguenti:
Sottoquery o funzioni definite dall'utente che eseguono l'accesso ai dati dell'utente o di sistema o che si presume eseguano tale accesso. Si presuppone che le funzioni definite dall'utente eseguano l'accesso ai dati se non sono associate allo schema.
Colonna di una vista o di una funzione inline con valori di tabella se tale colonna viene definita mediante uno dei metodi seguenti:
Sottoquery.
Funzione definita dall'utente che esegue, o si presume esegua, l'accesso ai dati dell'utente o di sistema.
Colonna calcolata che contiene una funzione definita dall'utente che esegue l'accesso ai dati dell'utente o di sistema nella relativa definizione.
Quando SQL Server rileva una colonna di questo tipo nella clausola , viene generato l'errore
OUTPUT
4186.
Inserire i dati restituiti da una clausola OUTPUT in una tabella
Quando si acquisiscono i risultati di una OUTPUT
clausola in un'istruzione INSERT, UPDATE, DELETE o MERGE annidata e si inseriscono tali risultati in una tabella di destinazione, tenere presenti le informazioni seguenti:
L'intera operazione è di tipo atomico. Sia l'istruzione INSERT che l'istruzione DML nidificata che contiene la
OUTPUT
clausola execute oppure l'intera istruzione ha esito negativo.Alla destinazione dell'istruzione INSERT esterna si applicano le restrizioni seguenti:
La destinazione non può essere un'espressione di tabella, vista o tabella comune remota.
La destinazione non può avere un vincolo FOREIGN KEY o fare riferimento a un vincolo FOREIGN KEY.
I trigger non possono essere definiti nella destinazione.
La destinazione non può partecipare alla replica di tipo merge o alle sottoscrizioni aggiornabili per la replica transazionale.
All'istruzione DML nidificata si applicano le restrizioni seguenti:
La destinazione non può essere una tabella remota o una vista partizionata.
L'origine stessa non può contenere una
<dml_table_source>
clausola .
La
OUTPUT INTO
clausola non è supportata nelle istruzioni INSERT che contengono una<dml_table_source>
clausola .@@ROWCOUNT
restituisce le righe inserite solo dall'istruzione INSERT esterna.@@IDENTITY
,SCOPE_IDENTITY
eIDENT_CURRENT
restituiscono valori Identity generati solo dall'istruzione DML annidata e non da quelli generati dall'istruzione INSERT esterna.Le notifiche delle query considerano l'istruzione come entità singola e il tipo di qualsiasi messaggio creato sarà il tipo dell'istruzione DML nidificata, anche se la modifica significativa proviene dall'istruzione INSERT esterna stessa.
<dml_table_source>
Nella clausola le clausole SELECT e WHERE non possono includere sottoquery, funzioni di aggregazione, funzioni di classificazione, predicati full-text, funzioni definite dall'utente che eseguono l'accesso ai dati o laTEXTPTR()
funzione.
Parallelism
Una OUTPUT
clausola che restituisce i risultati al client o alla variabile di tabella userà sempre un piano seriale.
Nel contesto di un database impostato sul livello di compatibilità 130 o superiore, se un'operazione INSERT...SELECT
usa un WITH (TABLOCK)
hint per l'istruzione SELECT e usa OUTPUT...INTO
anche per inserire in una tabella temporanea o utente, la tabella di destinazione per l'oggetto INSERT...SELECT
sarà idonea per il parallelismo a seconda del costo del sottoalbero. La tabella di destinazione a cui si fa riferimento nella OUTPUT INTO
clausola non sarà idonea per il parallelismo.
Trigger
Le colonne restituite da OUTPUT
riflettono i dati così come dopo il completamento dell'istruzione INSERT, UPDATE o DELETE, ma prima dell'esecuzione dei trigger.
Per i trigger INSTEAD OF, i risultati restituiti vengono generati come se le istruzioni INSERT, UPDATE o DELETE siano state effettivamente completate, anche se non si verifica alcuna modifica come risultato dell'operazione trigger. Se un'istruzione che include una OUTPUT
clausola viene utilizzata all'interno del corpo di un trigger, gli alias di tabella devono essere utilizzati per fare riferimento alle tabelle inserite ed eliminate del trigger per evitare la duplicazione di riferimenti a colonne con le tabelle INSERTED e DELETED associate a OUTPUT
.
Se la OUTPUT
clausola viene specificata senza specificare anche la parola chiave INTO, la destinazione dell'operazione DML non può avere alcun trigger abilitato definito per l'azione DML specificata. Ad esempio, se la OUTPUT
clausola è definita in un'istruzione UPDATE, la tabella di destinazione non può avere trigger UPDATE abilitati.
Se l'opzione sp_configure
non consente risultati dai trigger è impostata, una OUTPUT
clausola senza una clausola INTO causa l'esito negativo dell'istruzione quando viene richiamata dall'interno di un trigger.
Tipo di dati
La OUTPUT
clausola supporta i tipi di dati per oggetti di grandi dimensioni: nvarchar(max), varchar(max), varbinary(max), text, ntext, image e xml. Quando si usa la .WRITE
clausola nell'istruzione UPDATE per modificare una colonna nvarchar(max), varchar(max)o varbinary(max), il valore completo prima e dopo le immagini dei valori viene restituito se vi si fa riferimento. La TEXTPTR()
funzione non può essere visualizzata come parte di un'espressione in una colonna text, ntext o image nella OUTPUT
clausola .
Code
È possibile usare OUTPUT
nelle applicazioni che usano tabelle come code o per contenere set di risultati intermedi. In altre parole, l'applicazione aggiunge o rimuove costantemente le righe dalla tabella. Nell'esempio seguente viene utilizzata la OUTPUT
clausola in un'istruzione DELETE per restituire la riga eliminata all'applicazione chiamante.
USE AdventureWorks2022;
GO
DELETE TOP(1) dbo.DatabaseLog WITH (READPAST)
OUTPUT DELETED.*
WHERE DatabaseLogID = 7;
GO
In questo esempio viene rimossa una riga da una tabella utilizzata come coda e i valori eliminati vengono restituiti all'applicazione di elaborazione in una singola azione. È possibile implementare anche altri tipi di semantica, ad esempio l'utilizzo di una tabella per implementare uno stack. SQL Server, tuttavia, non garantisce l'ordine in cui le righe vengono elaborate e restituite dalle istruzioni DML usando la OUTPUT
clausola . Spetta all'applicazione includere una clausola WHERE appropriata per garantire la semantica desiderata oppure tenere in considerazione che quando più righe possono essere incluse nell'operazione DML, non viene garantito alcun ordine particolare. Nell'esempio seguente viene utilizzata una sottoquery e viene presupposto che l'unicità sia una caratteristica della colonna DatabaseLogID
per implementare la semantica di ordinamento desiderata.
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;
Ecco i risultati:
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
Nota
Utilizzare l'hint di tabella READPAST nelle istruzioni UPDATE e DELETE se lo scenario consente a più applicazioni di eseguire una operazione di lettura distruttiva da una tabella. Ciò evita i problemi relativi ai blocchi che possono verificarsi se un'altra applicazione sta già leggendo il primo record qualificato nella tabella.
Autorizzazioni
Le autorizzazioni SELECT sono necessarie in ogni colonna recuperata tramite <dml_select_list>
o usata in <scalar_expression>
.
Le autorizzazioni INSERT sono necessarie in ogni tabella specificata in <output_table>
.
Esempi
R. Usare OUTPUT INTO con un'istruzione INSERT
Nell'esempio seguente viene inserita una riga nella tabella ScrapReason
e viene utilizzata la clausola OUTPUT
per restituire i risultati dell'istruzione alla variabile di tabella @MyTableVar
. Poiché la ScrapReasonID
colonna è definita con una proprietà IDENTITY, un valore non viene specificato nell'istruzione INSERT
per tale colonna. Tuttavia, il valore generato dal motore di database per tale colonna viene restituito nella OUTPUT
clausola nella colonna 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. Usare OUTPUT con un'istruzione DELETE
Nell'esempio seguente vengono eliminate tutte le righe nella tabella ShoppingCartItem
. La clausola OUTPUT DELETED.*
specifica che i risultati dell'istruzione DELETE
, ovvero tutte le colonne nelle righe eliminate, vengono restituiti all'applicazione chiamante. L'istruzione SELECT
che segue verifica i risultati dell'operazione di eliminazione nella tabella 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. Usare OUTPUT INTO con un'istruzione UPDATE
Nell'esempio seguente viene aggiornata la colonna VacationHours
nella tabella Employee
del 25% per le prime 10 righe. La clausola OUTPUT
restituisce il valore VacationHours
esistente prima di applicare l'istruzione UPDATE
nella colonna DELETED.VacationHours
e il valore aggiornato nella colonna INSERTED.VacationHours
alla variabile di tabella @MyTableVar
.
Seguono due SELECT
istruzioni, che restituiscono i valori in @MyTableVar
e i risultati dell'operazione di aggiornamento nella Employee
tabella.
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. Usare OUTPUT INTO per restituire un'espressione
Nell'esempio seguente viene utilizzato come base l'esempio C definendo un'espressione nella clausola OUTPUT
come differenza tra il valore VacationHours
aggiornato e il valore VacationHours
prima dell'applicazione dell'aggiornamento. Il valore di questa espressione viene restituito alla variabile di tabella @MyTableVar
nella colonna 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. Usare OUTPUT INTO con from_table_name in un'istruzione UPDATE
Nell'esempio seguente viene aggiornata la colonna ScrapReasonID
nella tabella WorkOrder
per tutti gli ordini di lavoro con valori ProductID
e ScrapReasonID
specificati. La clausola OUTPUT INTO
restituisce i valori dalla tabella in fase di aggiornamento (WorkOrder
) e anche dalla tabella Product
. La tabella Product
viene utilizzata nella clausola FROM
per specificare le righe da aggiornare. Poiché per la tabella WorkOrder
è stato definito un trigger AFTER UPDATE
, è necessaria la parola chiave 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. Usare OUTPUT INTO con from_table_name in un'istruzione DELETE
Nell'esempio seguente vengono eliminate le righe nella tabella ProductProductPhoto
in base ai criteri di ricerca definiti nella clausola FROM
dell'istruzione DELETE
. La clausola OUTPUT
restituisce le colonne dalla tabella che viene eliminata (DELETED.ProductID
, DELETED.ProductPhotoID
) e dalla tabella Product
. Questa tabella viene utilizzata nella clausola FROM
per specificare le righe da eliminare.
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. Usare OUTPUT INTO con un tipo di dati di oggetti di grandi dimensioni
Nell'esempio seguente viene aggiornato un valore parziale in DocumentSummary
, una colonna di tipo nvarchar(max)
della tabella Production.Document
, tramite la clausola .WRITE
. La parola components
viene sostituta con la parola features
specificando la parola sostitutiva, la posizione iniziale (offset) della parola da sostituire nei dati esistenti e il numero di caratteri da sostituire (lunghezza). Nell'esempio viene usata la clausola OUTPUT
per restituire le immagini precedenti e successive della colonna DocumentSummary
alla variabile di tabella @MyTableVar
. Vengono restituiti l'intero oggetto prima e dopo le immagini della DocumentSummary
colonna.
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. Usare OUTPUT in un trigger INSTEAD OF
Nell'esempio seguente viene utilizzata la clausola OUTPUT
in un trigger per restituire i risultati dell'operazione trigger. Viene prima creata una vista nella tabella ScrapReason
e quindi definito un trigger INSTEAD OF INSERT
nella vista che consente all'utente di modificare esclusivamente la colonna Name
della tabella di base. Poiché la colonna ScrapReasonID
è una colonna IDENTITY
nella tabella di base, il trigger ignora il valore specificato dall'utente, consentendo al motore di database di generare automaticamente il valore corretto. Inoltre, il valore specificato dall'utente per ModifiedDate
viene ignorato e impostato sulla data corrente. La clausola OUTPUT
restituisce i valori di fatto inseriti nella tabella 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
Di seguito è riportato il set di risultati generato il 12 aprile 2004 ('2004-04-12'
). Si noti che le colonne ScrapReasonIDActual
e ModifiedDate
riflettono i valori generati dall'operazione trigger invece dei valori specificati nell'istruzione INSERT
.
ScrapReasonID Name ModifiedDate
------------- ---------------- -----------------------
17 My scrap reason 2004-04-12 16:23:33.050
I. Usare OUTPUT INTO con le colonne identity e calcolate
Nell'esempio seguente viene creata la tabella EmployeeSales
, in cui vengono quindi inserite diverse righe tramite un'istruzione INSERT
con un'istruzione SELECT
per il recupero dei dati dalle tabelle di origine. La tabella EmployeeSales
include una colonna Identity (EmployeeID
) e una colonna calcolata (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. Usare OUTPUT e OUTPUT INTO in una singola istruzione
Nell'esempio seguente vengono eliminate le righe nella tabella ProductProductPhoto
in base ai criteri di ricerca definiti nella clausola FROM
dell'istruzione DELETE
. La clausola OUTPUT INTO
restituisce le colonne dalla tabella che viene eliminata (DELETED.ProductID
, DELETED.ProductPhotoID
) e dalla tabella Product
alla variabile di tabella @MyTableVar
. La tabella Product
viene utilizzata nella clausola FROM
per specificare le righe da eliminare. La clausola OUTPUT
restituisce all'applicazione chiamante le colonne DELETED.ProductID
, DELETED.ProductPhotoID
e la data e l'ora in cui la riga è stata eliminata dalla tabella ProductProductPhoto
.
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. Inserire i dati restituiti da una clausola OUTPUT
Nell'esempio seguente vengono acquisiti i dati restituiti dalla clausola OUTPUT
di un'istruzione MERGE
e tali dati vengono inseriti in un'altra tabella. L'istruzione MERGE
aggiorna la colonna Quantity
della tabella ProductInventory
su base giornaliera, in base agli ordini elaborati nella tabella SalesOrderDetail
. Elimina anche le righe per i prodotti i cui inventari scendono a 0
o meno. In questo esempio vengono acquisite le righe eliminate, che vengono inserite in un'altra tabella, ZeroInventory
, in cui viene tenuta traccia dei prodotti senza scorte.
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