OUTPUT – klauzule (Transact-SQL)

platí pro:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL databáze v Microsoft Fabric

Vrátí informace z nebo výrazů založených na každém řádku ovlivněném znakem INSERT, UPDATE, DELETEnebo MERGE příkazem. Tyto výsledky je možné vrátit do aplikace pro zpracování pro použití, jako jsou potvrzovací zprávy, archivace a další požadavky na aplikaci. Výsledky lze také vložit do tabulky nebo proměnné tabulky. Kromě toho můžete zachytit výsledky OUTPUT klauzule v vnořené INSERT, UPDATE, DELETE, nebo MERGE příkaz a vložit tyto výsledky do cílové tabulky nebo zobrazení.

Note

Příkaz UPDATE, INSERTnebo DELETE příkaz, který má OUTPUT klauzuli, vrátí řádky klientovi i v případě, že příkaz narazí na chyby a vrátí se zpět. Výsledek by se neměl používat, pokud při spuštění příkazu dojde k nějaké chybě.

Použito v:

• DELETE
• INSERT
• UPDATE
• MERGE

Transact-SQL konvence syntaxe

Syntax

<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

Arguments

@table_variable

Určuje proměnnou tabulky , do které se vrácené řádky vloží místo vrácení volajícímu. @table_variable musí být deklarovány před příkazem INSERT, UPDATE, DELETE, nebo MERGE příkazem.

Pokud není zadaný column_list , musí mít proměnná tabulky stejný počet sloupců jako OUTPUT sada výsledků. Výjimky jsou sloupce identity a počítané sloupce, které je potřeba přeskočit. Pokud je zadán column_list , všechny vynechané sloupce musí buď povolit hodnoty null, nebo mají přiřazené výchozí hodnoty.

Další informace o proměnných tabulky najdete v tabulce.

output_table

Určuje tabulku, do které se vrácené řádky vloží, místo aby se vrátily volajícímu. output_table může být dočasná tabulka.

Pokud není zadaný column_list , musí mít tabulka stejný počet sloupců jako OUTPUT sada výsledků. Výjimky jsou sloupce identity a počítané sloupce, které je potřeba přeskočit. Pokud je zadán column_list , všechny vynechané sloupce musí buď povolit hodnoty null, nebo mají přiřazené výchozí hodnoty.

output_table nemůže:

• Aktivovaly na něm definované triggery.
• Zúčastněte se na obou stranách FOREIGN KEY omezení.
• Máte CHECK omezení nebo povolená pravidla.

column_list

Volitelný seznam názvů sloupců v cílové tabulce INTO klauzule. Je to analogické se seznamem sloupců povoleným v příkazu INSERT .

scalar_expression

Libovolná kombinace symbolů a operátorů, které se vyhodnotí jako jedna hodnota. Agregační funkce nejsou v scalar_expression povolené.

Všechny odkazy na sloupce v upravené tabulce musí být kvalifikované s předponou nebo INSERTED předponouDELETED.

column_alias_identifier

Alternativní název použitý k odkazu na název sloupce.

DELETED

Předpona sloupce, která určuje hodnotu odstraněnou operací aktualizace nebo odstranění, a všechny existující hodnoty, které se v aktuální operaci nemění. Sloupce s předponou DELETED reflektují hodnotu před dokončením UPDATEpříkazu , DELETEnebo MERGE příkazu.

DELETED nelze použít s klauzulí OUTPUT v INSERT příkazu.

VLOŽENÝ

Předpona sloupce, která určuje hodnotu přidanou operací vložení nebo aktualizace a všechny existující hodnoty, které se v aktuální operaci nemění. Sloupce s INSERTED předponou odrážejí hodnotu za UPDATEINSERT, nebo MERGE příkaz je dokončen, ale před spuštěním triggerů.

INSERTED nelze použít s klauzulí OUTPUT v DELETE příkazu.

from_table_name

Předpona sloupce, která určuje tabulku obsaženou v FROM klauzuli DELETE, UPDATEnebo MERGE příkaz, který se používá k určení řádků pro aktualizaci nebo odstranění.

Pokud je v klauzuli zadána FROM také upravená tabulka, musí být všechny odkazy na sloupce v této tabulce kvalifikované s předponou nebo INSERTED předponouDELETED.

*

Hvězdička (*) určuje, že všechny sloupce ovlivněné akcí odstranění, vložení nebo aktualizace se vrátí v pořadí, ve kterém existují v tabulce.

Například OUTPUT DELETED.* v následujícím DELETE příkazu vrátí všechny sloupce odstraněné z ShoppingCartItem tabulky:

DELETE Sales.ShoppingCartItem
    OUTPUT DELETED.*;

column_name

Explicitní odkaz na sloupec. Jakýkoli odkaz na upravenou tabulku musí být správně kvalifikovaný buď INSERTEDDELETED podle předpony, nebo podle potřeby, například: INSERTED.<column_name>.

$action

K dispozici pouze pro MERGE příkaz. Určuje sloupec typu nvarchar(10) v klauzuli v OUTPUTMERGE příkazu, který vrátí jednu ze tří hodnot pro každý řádek: INSERT, UPDATEnebo DELETE, podle akce, která byla provedena na daném řádku.

Remarks

Klauzule OUTPUT <dml_select_list> a OUTPUT <dml_select_list> INTO { @table_variable | output_table } klauzule lze definovat v jediném INSERT, UPDATE, DELETE, nebo MERGE příkazu.

Note

Pokud není uvedeno jinak, odkazy na OUTPUT klauzuli odkazují jak OUTPUT na klauzuli, tak na klauzuli OUTPUT INTO .

Klauzule OUTPUT může být užitečná k načtení hodnoty identit nebo počítaných sloupců po operaci INSERTUPDATE .

Pokud je počítaný sloupec zahrnutý do <dml_select_list>tabulky , odpovídající sloupec ve výstupní tabulce nebo proměnné tabulky není počítaný sloupec. Hodnoty v novém sloupci jsou hodnoty vypočítané v okamžiku provedení příkazu.

Pořadí, ve kterém jsou změny použity v tabulce a pořadí, ve kterém jsou řádky vloženy do výstupní tabulky nebo proměnné tabulky, nejsou zaručeny, že odpovídají.

Pokud jsou parametry nebo proměnné upraveny jako součást UPDATE příkazu, OUTPUT klauzule vždy vrátí hodnotu parametru nebo proměnné, protože byla před provedením příkazu namísto změněné hodnoty.

Můžete použít OUTPUT s příkazem UPDATE umístěným DELETE na kurzoru, který používá WHERE CURRENT OF syntaxi.

Klauzule OUTPUT není podporována v následujících příkazech:

• Příkazy DML, které odkazují na místní rozdělená zobrazení, distribuovaná rozdělená zobrazení nebo vzdálené tabulky.

• INSERT příkazy, které obsahují EXECUTE příkaz.

• Predikáty fulltextu OUTPUT nejsou v klauzuli povoleny, pokud je úroveň kompatibility databáze nastavená na 100.

• Klauzuli OUTPUT INTO nelze použít k vložení do zobrazení nebo funkce sady řádků.

• Uživatelem definovanou funkci nelze vytvořit, pokud obsahuje OUTPUT INTO klauzuli, která má jako cíl tabulku.

Pokud chcete zabránit nedeterministickému chování, OUTPUT klauzule nemůže obsahovat následující odkazy:

• Poddotazy nebo uživatelem definované funkce, které provádějí přístup k datům uživatele nebo systému, nebo se předpokládá, že mají takový přístup provést. Uživatelem definované funkce se předpokládají, že mají přístup k datům, pokud nejsou vázané na schéma.

• Sloupec ze zobrazení nebo vložené funkce s hodnotou tabulky, pokud je tento sloupec definován některou z následujících metod:

  • Subquery.

  • Uživatelem definovaná funkce, která provádí přístup k datům uživatele nebo systému nebo se předpokládá, že takový přístup provádí.

  • Počítaný sloupec, který obsahuje uživatelem definovanou funkci, která v definici provádí přístup k datům uživatele nebo systému.

  Když SQL Server zjistí takový sloupec v OUTPUT klauzuli, vyvolá se chyba 4186.

Vložení dat vrácených z klauzule OUTPUT do tabulky

Při zachycení výsledků OUTPUT klauzule v vnořené INSERTUPDATE, , DELETEnebo MERGE příkazu a vložení těchto výsledků do cílové tabulky mějte na paměti následující informace:

• Celá operace je atomická. Příkaz i INSERT vnořený příkaz DML, který obsahuje OUTPUT klauzuli execute, nebo celý příkaz selže.

• Pro cíl vnějšího INSERT příkazu platí následující omezení:

  • Cílem nemůže být vzdálená tabulka, zobrazení ani běžný výraz tabulky.

  • Cíl nemůže mít FOREIGN KEY omezení nebo na ho FOREIGN KEY odkazovat.

  • Triggery nelze definovat v cíli.

  • Cíl se nemůže účastnit slučovací replikace ani aktualizovatelných předplatných pro transakční replikaci.

• Následující omezení platí pro vnořený příkaz DML:

  • Cílem nemůže být vzdálená tabulka ani rozdělené zobrazení.

  • Samotný zdroj nemůže obsahovat klauzuli <dml_table_source> .

• Klauzule OUTPUT INTO není podporována v INSERT příkazech, které obsahují <dml_table_source> klauzuli.

• @@ROWCOUNT vrátí řádky vložené pouze vnějším INSERT příkazem.

• @@IDENTITY, SCOPE_IDENTITYa IDENT_CURRENT návratové hodnoty identity vygenerované pouze vnořeným příkazem DML, a ne hodnotami generovanými vnějším INSERT příkazem.

• Oznámení dotazů považují příkaz za jedinou entitu a typ jakékoli vytvořené zprávy je typem vnořeného DML, i když je významná změna ze samotného vnějšího INSERT příkazu.

• <dml_table_source> V klauzuli SELECTWHERE nemohou klauzule zahrnovat poddotazy, agregační funkce, funkce řazení, predikáty fulltextu, uživatelem definované funkce, které provádějí přístup k datům nebo TEXTPTR() funkci.

Parallelism

Klauzule OUTPUT , která vrací výsledky klientovi nebo proměnné tabulky, vždy používá sériový plán.

V kontextu sady databáze na úroveň kompatibility 130 nebo vyšší, pokud INSERT...SELECT operace používá WITH (TABLOCK) nápovědu pro SELECT příkaz a používá OUTPUT...INTO také k vložení do dočasné nebo uživatelské tabulky, pak cílová tabulka pro tuto INSERT...SELECT úroveň je způsobilá pro paralelismus v závislosti na nákladech na podstrom. Cílová tabulka odkazovaná v OUTPUT INTO klauzuli nemá nárok na paralelismus.

Triggers

Sloupce vrácené z OUTPUT dat odrážejí data po INSERTdokončení příkazu , UPDATEnebo DELETE příkazu, ale před spuštěním triggerů.

U INSTEAD OF aktivačních událostí se vrácené výsledky vygenerují, jako by INSERTdošlo k , UPDATEnebo DELETE skutečně došlo, i když se v důsledku operace triggeru neprovedou žádné úpravy. Pokud se v textu triggeru používá příkaz, který obsahuje OUTPUT klauzuli, musí být aliasy tabulek použity k odkazování na vložené a odstraněné tabulky, aby nedocházelo k duplikování odkazů na sloupce s INSERTED tabulkami DELETED přidruženými OUTPUTk .

OUTPUT Pokud je klauzule zadána bez zadání klíčového INTO slova, cíl operace DML nemůže mít pro danou akci DML definovanou žádnou povolenou aktivační událost. Pokud OUTPUT je například klauzule definovaná v UPDATE příkazu, cílová tabulka nemůže mít žádné povolené UPDATE triggery.

sp_configure Pokud je nastavena možnost zakázat výsledky z triggerů, OUTPUT klauzule bez INTO klauzule způsobí selhání příkazu při vyvolání z triggeru.

Datové typy

Klauzule OUTPUT podporuje velké datové typy objektů: nvarchar(max), varchar(max), varbinary(max), text, ntext, obrázek a xml. Pokud použijete klauzuli .WRITE v UPDATE příkazu k úpravě sloupce nvarchar(max), varchar(max) nebo varbinary(max), vrátí se úplné před a za obrázky hodnot, pokud jsou odkazovány. Funkce TEXTPTR() se v klauzuli nemůže zobrazit jako součást výrazu ve sloupci textu, ntextu nebo obrázkuOUTPUT.

Queues

Můžete použít OUTPUT v aplikacích, které používají tabulky jako fronty, nebo k uložení průběžných sad výsledků. To znamená, že aplikace neustále přidává nebo odebírá řádky z tabulky. Následující příklad používá klauzuli OUTPUT v DELETE příkazu k vrácení odstraněného řádku do volající aplikace.

USE AdventureWorks2022;
GO

DELETE TOP(1) dbo.DatabaseLog WITH (READPAST)
OUTPUT DELETED.*
WHERE DatabaseLogID = 7;
GO

Tento příklad odebere řádek z tabulky používané jako fronta a vrátí odstraněné hodnoty do aplikace pro zpracování v jediné akci. Je také možné implementovat další sémantiku, například použití tabulky k implementaci zásobníku. SQL Server ale nezaručuje pořadí, ve kterém se řádky zpracovávají a vrací příkazy DML pomocí OUTPUT klauzule. Je na aplikaci, aby zahrnovala odpovídající WHERE klauzuli, která může zaručit požadovanou sémantiku, nebo pochopit, že pokud může více řádků kvalifikovat pro operaci DML, neexistuje žádné zaručené pořadí. Následující příklad používá poddotaz a předpokládá jedinečnost je charakteristické pro DatabaseLogID sloupec k implementaci požadované sémantiky řazení.

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;

Tady jsou výsledky:

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

Note

Nápovědu READPAST k UPDATE tabulce a DELETE příkazy použijte, pokud váš scénář umožňuje, aby více aplikací provádělo destruktivní čtení z jedné tabulky. To zabrání uzamčení problémů, které se můžou zobrazit, pokud jiná aplikace už čte první kvalifikační záznam v tabulce.

Permissions

SELECT oprávnění jsou vyžadována u všech sloupců načtených nebo <dml_select_list> použitých v <scalar_expression>.

INSERT oprávnění jsou vyžadována u všech tabulek zadaných v <output_table>.

Examples

Ukázky kódu v tomto článku používají ukázkovou databázi AdventureWorks2025 nebo AdventureWorksDW2025, kterou si můžete stáhnout z domovské stránky Microsoft SQL Serveru pro ukázky a komunitní projekty .

A. Použití příkazu OUTPUT INTO s příkazem INSERT

Následující příklad vloží řádek do ScrapReason tabulky a pomocí OUTPUT klauzule vrátí výsledky příkazu do @MyTableVar proměnné tabulky. Vzhledem k tomu, že ScrapReasonID sloupec je definován pomocí vlastnosti IDENTITY, hodnota není zadána INSERT v příkazu pro tento sloupec. Hodnota vygenerovaná databázovým strojem pro tento sloupec je však vrácena v OUTPUT klauzuli ve sloupci 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. Použití výstupu s příkazem DELETE

Následující příklad odstraní všechny řádky v ShoppingCartItem tabulce. Klauzule OUTPUT DELETED.* určuje, že výsledky DELETE příkazu, tj. všechny sloupce v odstraněných řádcích, se vrátí do volající aplikace. Následující SELECT příkaz ověří výsledky operace delete v ShoppingCartItem tabulce.

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. Použití VÝSTUPU INTO s příkazem UPDATE

Následující příklad aktualizuje VacationHours sloupec v Employee tabulce o 25 procent pro prvních 10 řádků. Klauzule OUTPUT vrátí VacationHours hodnotu, která existuje před použitím UPDATE příkazu ve sloupci DELETED.VacationHoursa aktualizovaná hodnota ve sloupci INSERTED.VacationHours na proměnnou @MyTableVar tabulky.

Následují dva SELECT příkazy, které vrátí hodnoty v @MyTableVar tabulce a výsledky operace aktualizace.Employee

USE AdventureWorks2022;
GO

DECLARE @MyTableVar TABLE (
    EmpID INT NOT NULL,
    OldVacationHours INT,
    NewVacationHours INT,
    ModifiedDate DATETIME);

UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
    ModifiedDate = GETDATE()
OUTPUT INSERTED.BusinessEntityID,
       DELETED.VacationHours,
       INSERTED.VacationHours,
       INSERTED.ModifiedDate
INTO @MyTableVar;

--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO
--Display the result set of the table.
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

D. Použití příkazu OUTPUT INTO k vrácení výrazu

Následující příklad vychází z příkladu C definováním výrazu OUTPUT v klauzuli jako rozdíl mezi aktualizovanou VacationHours hodnotou a VacationHours hodnotou před použitím aktualizace. Hodnota tohoto výrazu je vrácena do @MyTableVar proměnné tabulky ve sloupci 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. Použití příkazu OUTPUT INTO s from_table_name v příkazu UPDATE

Následující příklad aktualizuje ScrapReasonID sloupec v WorkOrder tabulce pro všechny pracovní objednávky se zadaným ProductID a ScrapReasonID. Klauzule OUTPUT INTO vrací hodnoty z aktualizované tabulky (WorkOrder) a také z Product tabulky. Tabulka Product se v klauzuli používá FROM k určení řádků, které se mají aktualizovat. Vzhledem k tomu, že v WorkOrder tabulce je AFTER UPDATE definovaný trigger, INTO je klíčové slovo povinné.

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. Použití příkazu OUTPUT INTO s from_table_name v příkazu DELETE

Následující příklad odstraní řádky v ProductProductPhoto tabulce na základě vyhledávacích kritérií definovaných FROM v DELETE klauzuli příkazu. Klauzule OUTPUT vrátí sloupce z odstraněné tabulky (DELETED.ProductID, DELETED.ProductPhotoID) a sloupců z Product tabulky. Tato tabulka se používá v FROM klauzuli k určení řádků, které se mají odstranit.

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. Použití výstupu INTO s velkým datovým typem objektu

Následující příklad aktualizuje částečnou hodnotu ve DocumentSummarysloupci nvarchar(max) v Production.Document tabulce pomocí .WRITE klauzule. Slovo components je nahrazeno slovem features zadáním náhradního slova, počátečním umístěním (posunem) slova, které má být nahrazeno v existujících datech, a počtem znaků, které se mají nahradit (délka). Příklad používá OUTPUT klauzuli k vrácení před a za obrázky DocumentSummary sloupce do @MyTableVar proměnné tabulky. Vrátí se úplné před a po obrázcích DocumentSummary sloupce.

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. Použití výstupu v triggeru MÍSTO triggeru

Následující příklad používá klauzuli OUTPUT v triggeru k vrácení výsledků operace triggeru. Nejprve se v tabulce vytvoří ScrapReason zobrazení a pak INSTEAD OF INSERT se v zobrazení definuje aktivační událost, která uživateli umožní upravit pouze Name sloupec základní tabulky. Vzhledem k tomu, že sloupec je ScrapReasonID sloupec IDENTITY v základní tabulce, trigger ignoruje zadanou hodnotu uživatelem. To umožňuje databázovému stroji automaticky vygenerovat správnou hodnotu. Hodnota zadaná uživatelem ModifiedDate je také ignorována a je nastavena na aktuální datum. Klauzule OUTPUT vrátí hodnoty skutečně vložené do ScrapReason tabulky.

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

Tady je sada výsledků vygenerovaná 12. dubna 2004 ('2004-04-12'). Sloupce ScrapReasonIDActual odrážejí ModifiedDate hodnoty vygenerované operací triggeru místo hodnot zadaných v INSERT příkazu.

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

I. Použití VÝSTUPU INTO s identitou a vypočítanými sloupci

Následující příklad vytvoří EmployeeSales tabulku a pak do ní vloží několik řádků pomocí INSERT příkazu s příkazem SELECT pro načtení dat ze zdrojových tabulek. Tabulka EmployeeSales obsahuje sloupec identity (EmployeeID) a vypočítaný sloupec (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. Použití výstupu a VÝSTUPU INTO v jednom příkazu

Následující příklad odstraní řádky v ProductProductPhoto tabulce na základě vyhledávacích kritérií definovaných FROM v DELETE klauzuli příkazu. Klauzule OUTPUT INTO vrátí sloupce z tabulky, která se odstraňuje (DELETED.ProductID, DELETED.ProductPhotoID) a sloupce z Product tabulky do @MyTableVar proměnné tabulky. Tabulka Product se v klauzuli používá FROM k určení řádků, které se mají odstranit. Klauzule OUTPUT vrátí DELETED.ProductIDsloupce, DELETED.ProductPhotoID sloupce a datum a čas odstranění řádku z ProductProductPhoto tabulky do volající aplikace.

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. Vložení dat vrácených z klauzule OUTPUT

Následující příklad zachycuje data vrácená z OUTPUT klauzule MERGE příkazu a vloží tato data do jiné tabulky. Příkaz MERGE aktualizuje Quantity sloupec ProductInventory tabulky denně na základě objednávek, které jsou zpracovány v SalesOrderDetail tabulce. Odstraní také řádky pro produkty, jejichž inventarizované zásoby klesne na 0 méně nebo méně. Příklad zachycuje řádky, které jsou odstraněny, a vloží je do jiné tabulky, která sleduje produkty bez ZeroInventoryinventáře.

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

Související obsah

• DELETE (Transact-SQL)
• INSERT (Transact-SQL)
• AKTUALIZACE (Transact-SQL)
• Tabulka (Transact-SQL)
• VYTVOŘIT SPOUŠŤ (Transact-SQL)
• sp_configure (Transact-SQL)