Clausola OUTPUT (Transact-SQL)

  • Articolo

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 IN edizione Standard RT annidata, UPDATE, DELETE o MERGE e inserire tali risultati in una tabella o vista di destinazione.

Nota

Un'istruzione UPDATE, IN edizione Standard RT 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 di 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 la 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 IN edizione Standard RT, 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 IN edizione Standard RT.

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.

IN edizione Standard RTED 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: 'IN edizione Standard RT', '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 IN edizione Standard RT, 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 IN edizione Standard RT 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 IN edizione Standard RT annidata, UPDATE, DELETE o MERGE e si inseriscono tali risultati in una tabella di destinazione, tenere presenti le informazioni seguenti:

  • L'intera operazione è di tipo atomico. Sia l'istruzione IN edizione Standard RT 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 IN edizione Standard RT che contengono una <dml_table_source> clausola .

  • @@ROWCOUNTrestituisce le righe inserite solo dall'istruzione IN edizione Standard RT esterna.

  • @@IDENTITY, SCOPE_IDENTITYe IDENT_CURRENT restituiscono valori Identity generati solo dall'istruzione DML annidata e non da quelli generati dall'istruzione IN edizione Standard RT 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 edizione Standard LECT 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 la TEXTPTR() 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 edizione Standard LECT e usa OUTPUT...INTO anche per inserire in una tabella temporanea o utente, la tabella di destinazione per l'oggetto sarà idonea per il INSERT...SELECT 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 riflettono OUTPUT i dati così come sono dopo il completamento dell'istruzione IN edizione Standard RT, 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 di colonna con le tabelle IN edizione Standard RTED 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 IN edizione Standard RT

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

Passaggi successivi