Clausola OUTPUT (Transact-SQL)

  • Articolo

Si applica a: SQL Server Database SQL di Azure Istanza gestita di SQL di Azure

Restituisce informazioni da espressioni o basate su ogni riga interessata da un'istruzione INSERT, UPDATEDELETE, 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 annidata INSERT, UPDATE, DELETE, o MERGE e inserire tali risultati in una tabella o vista di destinazione.

Nota

Un'istruzione UPDATE, INSERTo 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

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, UPDATEDELETE, 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 di tabella, vedere tabella.

output_table

Specifica una tabella in cui vengono inserite le righe restituite invece di essere restituite al chiamante. output_table potrebbe 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, le quali 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 su entrambi i lati di un FOREIGN KEY vincolo.
  • Avere CHECK vincoli o regole abilitate.

column_list

Elenco facoltativo di nomi di colonna nella tabella di destinazione della INTO clausola . È analogo all'elenco di 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 nella tabella da modificare deve essere qualificato con il INSERTED prefisso 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 e tutti i valori esistenti che non cambiano con l'operazione corrente. Le colonne con prefisso riflettono DELETED il valore prima del completamento dell'istruzione UPDATE, DELETEo MERGE .

DELETED non può essere usato con la OUTPUT clausola nell'istruzione INSERT .

INSERTED

Prefisso di colonna che specifica il valore aggiunto dall'operazione di inserimento o aggiornamento e i valori esistenti che non cambiano con l'operazione corrente. Le colonne con prefisso riflettono INSERTED il valore dopo il completamento dell'istruzione , INSERTo MERGE , ma prima dell'esecuzione UPDATEdei 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 FROM clausola di un'istruzione DELETE, UPDATEo MERGE utilizzata per specificare le righe da aggiornare o eliminare.

Se la tabella da modificare viene specificata anche nella FROM clausola , qualsiasi riferimento alle colonne della tabella deve essere qualificato con il INSERTED prefisso o DELETED .

*

L'asterisco (*) specifica che tutte le colonne interessate dall'azione di eliminazione, inserimento o aggiornamento vengono restituite nell'ordine in cui sono presenti nella tabella.

Nell'istruzione seguenteDELETE, ad esempio, OUTPUT DELETED.* vengono restituite tutte le colonne eliminate dalla ShoppingCartItem tabella:

DELETE Sales.ShoppingCartItem
    OUTPUT DELETED.*;

column_name

Riferimento di colonna esplicito. Qualsiasi riferimento alla tabella da modificare deve essere qualificato correttamente dal INSERTED prefisso o DELETED come appropriato, 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, UPDATEo 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 un'unica INSERTistruzione , UPDATE, DELETEo 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.

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 non sono garantite.

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 WHERE CURRENT OF sintassi .

La OUTPUT clausola non è supportata nelle istruzioni seguenti:

  • istruzioni DML che fanno riferimento a viste partizionate locali, viste partizionate distribuite o tabelle remote

  • INSERT istruzioni 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 INTO clausola 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 annidata INSERT, 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 che l'istruzione INSERT DML annidata che contiene la OUTPUT clausola execute oppure l'intera istruzione ha esito negativo.

  • Le restrizioni seguenti si applicano alla destinazione dell'istruzione esterna INSERT :

    • La destinazione non può essere un'espressione di tabella, vista o tabella comune remota.

    • La destinazione non può avere un FOREIGN KEY vincolo o fare riferimento a un FOREIGN KEY vincolo.

    • 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 INSERT istruzioni che contengono una <dml_table_source> clausola .

  • @@ROWCOUNT restituisce le righe inserite solo dall'istruzione esterna INSERT .

  • @@IDENTITY, SCOPE_IDENTITYe IDENT_CURRENT restituiscono valori Identity generati solo dall'istruzione DML annidata e non dai valori generati dall'istruzione esterna INSERT .

  • Le notifiche di query considerano l'istruzione come una singola entità e il tipo di qualsiasi messaggio creato è il tipo di DML annidato, anche se la modifica significativa proviene dall'istruzione esterna INSERT stessa.

  • <dml_table_source> Nella clausola le SELECT clausole 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 usa 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 è idonea per il parallelismo a seconda del costo del sottoalbero. La tabella di destinazione a cui si fa riferimento nella OUTPUT INTO clausola non è idonea per il parallelismo.

Trigger

Le colonne restituite da OUTPUT riflettono i dati così come sono al termine dell'istruzione , UPDATEo DELETE , ma prima dell'esecuzione INSERTdei trigger.

Per INSTEAD OF i trigger, i risultati restituiti vengono generati come se INSERTUPDATE, o DELETE si fosse effettivamente verificato, anche se non viene apportata alcuna modifica come risultato dell'operazione di 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 INSERTED tabelle e DELETED associate a OUTPUT.

Se la OUTPUT clausola viene specificata senza specificare anche la INTO parola chiave , 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 abilitati UPDATE .

Se l'opzione sp_configure non consente risultati dai trigger viene impostata, una OUTPUT clausola senza una INTO clausola 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 viene fatto 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. È anche possibile implementare altre semantiche, ad esempio l'uso 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 appropriata WHERE che possa garantire la semantica desiderata o comprendere che quando più righe potrebbero qualificarsi per l'operazione DML, non esiste un ordine garantito. 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

Usare l'hint READPAST di tabella nelle UPDATE istruzioni e DELETE se lo scenario consente a più applicazioni di eseguire una 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

SELECT Le autorizzazioni sono necessarie per tutte le colonne recuperate tramite <dml_select_list> o usate in <scalar_expression>.

INSERT Le autorizzazioni sono necessarie per tutte le tabelle specificate in <output_table>.

Esempi

Gli esempi di codice Transact-SQL in questo articolo utilizzano il database campione AdventureWorks2022 o AdventureWorksDW2022, che è possibile scaricare dalla home page di Esempi di Microsoft SQL Server e progetti collettivi.

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 nvarchar(max) nella Production.Document tabella usando la .WRITE clausola . 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'). Le ScrapReasonIDActual colonne e ModifiedDate riflettono i valori generati dall'operazione trigger anziché i 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 OUTPUT clausola restituisce le DELETED.ProductIDcolonne , DELETED.ProductPhotoID e la data e l'ora di eliminazione della ProductProductPhoto riga dalla tabella all'applicazione chiamante.

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

Contenuto correlato