OUTPUT-Klausel (Transact-SQL)
Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance SQL-Datenbank in Microsoft Fabric
Gibt Informationen von oder Ausdrücken zurück, die auf jeder Zeile basieren, die von einem INSERT
, UPDATE
, DELETE
, oder MERGE
einer Anweisung betroffen ist. Diese Ergebnisse können an die verarbeitende Anwendung zurückgegeben werden, die sie z. B. für Bestätigungen, Archivierungen und andere Anwendungsanforderungen verwendet. Die Ergebnisse können auch in eine Tabelle oder Tabellenvariable eingefügt werden. Darüber hinaus können Sie die Ergebnisse einer Klausel in einer OUTPUT
geschachtelten INSERT
, UPDATE
, , DELETE
oder MERGE
Anweisung erfassen und diese Ergebnisse in eine Zieltabelle oder -ansicht einfügen.
Hinweis
Eine UPDATE
, INSERT
oder eine Anweisung, DELETE
die eine OUTPUT
Klausel enthält, gibt Zeilen an den Client zurück, auch wenn die Anweisung Fehler auftritt und zurückgesetzt wird. Wenn bei der Ausführung der Anweisung ein Fehler auftritt, sollte das Ergebnis nicht verwendet werden.
Verwendung in:
Transact-SQL-Syntaxkonventionen
<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
Gibt eine table-Variable an, in die die zurückgegebenen Zeilen eingefügt werden, statt an den Aufrufer zurückgegeben zu werden. @table_variable muss vor dem INSERT
, UPDATE
, oder DELETE
der MERGE
Anweisung deklariert werden.
Wenn column_list nicht angegeben wird, muss die table-Variable dieselbe Anzahl von Spalten wie das OUTPUT
-Resultset aufweisen. Ausnahmen bilden Identitätsspalten sowie berechnete Spalten, die ausgelassen werden müssen. Wenn column_list angegeben wird, müssen alle ausgelassenen Spalten entweder NULL-Werte zulassen oder über zugewiesene Standardwerte verfügen.
Weitere Informationen zu Tabellenvariablen finden Sie in der Tabelle.
Gibt eine Tabelle an, in die die zurückgegebenen Zeilen eingefügt werden, statt an den Aufrufer zurückgegeben zu werden. output_table kann eine temporäre Tabelle sein.
Wenn column_list nicht angegeben wird, muss die Tabelle dieselbe Anzahl von Spalten wie das OUTPUT
-Resultset aufweisen. Ausnahmen bilden Identitätsspalten sowie berechnete Spalten, die ausgelassen werden müssen. Wenn column_list angegeben wird, müssen alle ausgelassenen Spalten entweder NULL-Werte zulassen oder über zugewiesene Standardwerte verfügen.
Folgendes kann mit output_table nicht erzielt werden:
- Keine Definition von aktivierten Triggern.
- Nehmen Sie an beiden Seiten einer
FOREIGN KEY
Einschränkung teil. - Sie verfügen über
CHECK
Einschränkungen oder aktivierte Regeln.
Eine optionale Liste von Spaltennamen in der Zieltabelle der INTO
Klausel. Es entspricht der in der INSERT-Anweisung zulässigen Spaltenliste.
Eine beliebige Kombination von Symbolen und Operatoren, die zu genau einem Wert ausgewertet werden. Aggregatfunktionen sind in scalar_expression nicht zulässig.
Jeder Verweis auf Spalten in der zu ändernden Tabelle muss mit dem Präfix oder DELETED
dem INSERTED
Präfix qualifiziert werden.
Ein alternativer Name, über den auf den Spaltennamen verwiesen wird.
Ein Spaltenpräfix, das den vom Aktualisierungs- oder Löschvorgang gelöschten Wert angibt, und alle vorhandenen Werte, die sich nicht mit dem aktuellen Vorgang ändern. Spalten, die dem Wert vorangestellt DELETED
sind, bevor die UPDATE
, DELETE
oder MERGE
Anweisung abgeschlossen ist.
DELETED
kann nicht mit der Klausel in der OUTPUT
INSERT
Anweisung verwendet werden.
Ein Spaltenpräfix, das den vom Einfüge- oder Aktualisierungsvorgang hinzugefügten Wert angibt, und alle vorhandenen Werte, die sich nicht mit dem aktuellen Vorgang ändern. Spalten, die dem Wert vorangestellt INSERTED
sind, nachdem die UPDATE
Anweisung INSERT
abgeschlossen ist, MERGE
aber bevor Trigger ausgeführt werden.
INSERTED
kann nicht mit der Klausel in der OUTPUT
DELETE
Anweisung verwendet werden.
Ein Spaltenpräfix, das eine Tabelle angibt, die in der FROM
Klausel eines DELETE
, UPDATE
oder MERGE
einer Anweisung enthalten ist, die zum Angeben der zu aktualisierenden oder zu löschenden Zeilen verwendet wird.
Wenn die geänderte Tabelle auch in der FROM
Klausel angegeben ist, müssen alle Verweise auf Spalten in dieser Tabelle mit dem Präfix oder DELETED
dem INSERTED
Präfix qualifiziert werden.
Das Sternchen (*
) gibt an, dass alle Spalten, die von der Lösch-, Einfüge- oder Aktualisierungsaktion betroffen sind, in der Reihenfolge zurückgegeben werden, in der sie in der Tabelle vorhanden sind.
In der folgenden DELETE
Anweisung werden beispielsweise OUTPUT DELETED.*
alle Aus der ShoppingCartItem
Tabelle gelöschten Spalten zurückgegeben:
DELETE Sales.ShoppingCartItem
OUTPUT DELETED.*;
Ein expliziter Spaltenverweis. Jeder Verweis auf die geänderte Tabelle muss entweder durch das INSERTED
Präfix oder das DELETED
Präfix ordnungsgemäß qualifiziert werden, z. B.: INSERTED.<column_name>
.
Nur für die MERGE
Anweisung verfügbar. Gibt eine Spalte vom Typ nvarchar(10) in der Klausel in einer MERGE
Anweisung an, die OUTPUT
einen von drei Werten für jede Zeile zurückgibt: INSERT
, , UPDATE
oder DELETE
, entsprechend der Aktion, die für diese Zeile ausgeführt wurde.
Die OUTPUT <dml_select_list>
Klausel und die OUTPUT <dml_select_list> INTO { @table_variable | output_table }
Klausel können in einer einzelnen INSERT
, UPDATE
, , , DELETE
oder MERGE
Anweisung definiert werden.
Hinweis
Sofern nicht anderweitig angegeben, beziehen sich Verweise auf die OUTPUT
-Klausel sowohl auf die OUTPUT
- als auch auf die OUTPUT INTO
-Klausel.
Die OUTPUT
Klausel kann hilfreich sein, um den Wert von Identitäts- oder berechneten Spalten nach einem INSERT
Vorgang abzurufen UPDATE
.
Wenn eine berechnete Spalte in der <dml_select_list>
enthalten ist, handelt es sich bei der entsprechenden Spalte in der Ausgabetabelle oder Tabellenvariablen nicht um eine berechnete Spalte. Die Werte in der neuen Spalte entsprechen den Werten, die zum Zeitpunkt der Ausführung der Anweisung berechnet wurden.
Die Reihenfolge, in der die Änderungen auf die Tabelle angewendet werden, und die Reihenfolge, in der die Zeilen in die Ausgabetabelle oder Tabellenvariable eingefügt werden, sind nicht garantiert.
Wenn Parameter oder Variablen als Teil einer UPDATE
Anweisung geändert werden, gibt die OUTPUT
Klausel immer den Wert des Parameters oder der Variablen wie vor der Ausführung der Anweisung anstelle des geänderten Werts zurück.
Sie können eine oder eine UPDATE
Anweisung verwendenOUTPUT
, die auf einem Cursor positioniert ist, der eine Syntax verwendet WHERE CURRENT OF
DELETE
.
Die OUTPUT
-Klausel wird von den folgenden Anweisungen nicht unterstützt:
DML-Anweisungen, die auf lokale partitionierte Sichten, verteilte partitionierte Sichten oder Remotetabellen verweisen.
INSERT
Anweisungen, die eineEXECUTE
Anweisung enthalten.Volltextprädikate sind in der
OUTPUT
-Klausel nicht zulässig, wenn der Kompatibilitätsgrad der Datenbank auf 100 festgelegt ist.Die
OUTPUT INTO
-Klausel kann nicht zum Einfügen in eine Sicht oder eine Rowsetfunktion verwendet werden.Eine benutzerdefinierte Funktion kann nicht erstellt werden, wenn sie eine
OUTPUT INTO
Klausel enthält, die eine Tabelle als Ziel aufweist.
Um ein nicht deterministisches Verhalten zu verhindern, darf die OUTPUT
-Klausel nicht die folgenden Verweise enthalten:
Unterabfragen oder benutzerdefinierte Funktionen, die auf Benutzer- oder Systemdaten zugreifen bzw. bei denen davon ausgegangen wird, dass sie einen solchen Zugriff ausführen. Bei benutzerdefinierten Funktionen wird davon ausgegangen, dass sie auf Daten zugreifen, wenn sie nicht schemagebunden sind.
Eine Spalte aus einer Sicht oder Inline-Tabellenwertfunktion, wenn diese Spalte durch eine der folgenden Methoden definiert wird:
Eine Unterabfrage.
Eine benutzerdefinierte Funktion, die auf Benutzer- oder Systemdaten zugreift bzw. bei der davon ausgegangen wird, dass sie einen solchen Zugriff ausführt.
Eine berechnete Spalte, die eine benutzerdefinierte Funktion enthält, die in ihrer Definition auf Benutzer- oder Systemdaten zugreift.
Wenn SQL Server eine solche Spalte in der
OUTPUT
-Klausel erkennt, wird der Fehler 4186 ausgelöst.
Wenn Sie die Ergebnisse einer OUTPUT
Klausel in einem geschachtelten INSERT
, UPDATE
, DELETE
oder MERGE
einer Anweisung erfassen und diese Ergebnisse in eine Zieltabelle einfügen, beachten Sie die folgenden Informationen:
Der ganze Vorgang ist unteilbar. Entweder die
INSERT
Anweisung und die geschachtelte DML-Anweisung, die dieOUTPUT
Klausel enthält, oder die gesamte Anweisung schlägt fehl.Die folgenden Einschränkungen gelten für das Ziel der äußeren
INSERT
Anweisung:Das Ziel darf keine Remotetabelle, keine Remotesicht und kein allgemeiner Tabellenausdruck sein.
Das Ziel kann keine Einschränkung aufweisen
FOREIGN KEY
oder durch eineFOREIGN KEY
Einschränkung referenziert werden.Trigger können für das Ziel nicht definiert werden.
Das Ziel darf nicht Teil von Mergereplikationen oder aktualisierbaren Abonnements für Transaktionsreplikationen sein.
Die folgenden Einschränkungen gelten für die geschachtelte DML-Anweisung:
Das Ziel darf keine Remotetabelle und keine partitionierte Sicht sein.
Die Quelle selbst darf keine
<dml_table_source>
-Klausel enthalten.
Die
OUTPUT INTO
Klausel wird inINSERT
Anweisungen, die eine<dml_table_source>
Klausel enthalten, nicht unterstützt.@@ROWCOUNT
gibt die Zeilen zurück, die nur von der äußerenINSERT
Anweisung eingefügt wurden.@@IDENTITY
,SCOPE_IDENTITY
undIDENT_CURRENT
geben Sie Identitätswerte zurück, die nur von der geschachtelten DML-Anweisung und nicht von der äußerenINSERT
Anweisung generierten Werten generiert werden.Abfragebenachrichtigungen behandeln die Anweisung als einzelne Entität, und der Typ jeder erstellten Nachricht ist der Typ der geschachtelten DML, auch wenn die signifikante Änderung von der äußeren
INSERT
Anweisung selbst erfolgt.In der
<dml_table_source>
Klausel können dieSELECT
Und-KlauselnWHERE
keine Unterabfragen, Aggregatfunktionen, Rangfolgenfunktionen, Volltext-Prädikate, benutzerdefinierte Funktionen, die Datenzugriff ausführen, oder dieTEXTPTR()
Funktion enthalten.
Eine OUTPUT
Klausel, die Ergebnisse an den Client oder die Tabellenvariable zurückgibt, verwendet immer einen seriellen Plan.
Im Kontext einer Datenbank auf Kompatibilitätsebene 130 oder höher, wenn ein INSERT...SELECT
Vorgang einen WITH (TABLOCK)
Hinweis für die SELECT
Anweisung verwendet und auch zum Einfügen in eine temporäre oder Benutzertabelle verwendet OUTPUT...INTO
, ist die Zieltabelle für die INSERT...SELECT
Parallelität abhängig von den Unterstrukturkosten berechtigt. Die zieltabelle, auf die in der OUTPUT INTO
Klausel verwiesen wird, ist nicht für Parallelität geeignet.
Spalten, die von OUTPUT
den zurückgegebenen Daten zurückgegeben werden, während sie nach Abschluss der INSERT
, UPDATE
oder DELETE
Anweisung abgeschlossen sind, aber bevor Trigger ausgeführt werden.
Bei INSTEAD OF
Triggern werden die zurückgegebenen Ergebnisse generiert, als ob das INSERT
UPDATE
, , oder DELETE
tatsächlich aufgetreten wäre, auch wenn keine Änderungen als Ergebnis des Triggervorgangs stattfinden. Wenn eine Anweisung, die eine OUTPUT
Klausel enthält, im Textkörper eines Triggers verwendet wird, müssen Tabellenaliasen verwendet werden, um auf den eingefügten und gelöschten Auslöser zu verweisen, um zu vermeiden, dass Spaltenverweise mit den INSERTED
zugeordneten Tabellen DELETED
dupliziert werden OUTPUT
.
Wenn die OUTPUT
Klausel ohne Angabe des INTO
Schlüsselworts angegeben wird, kann das Ziel des DML-Vorgangs keinen aktivierten Trigger für die angegebene DML-Aktion definiert haben. Wenn die OUTPUT
Klausel beispielsweise in einer UPDATE
Anweisung definiert ist, kann die Zieltabelle keine aktivierten UPDATE
Trigger enthalten.
Wenn die sp_configure
Option die Ergebnisse von Triggern nicht zulassen, führt eine OUTPUT
Klausel ohne INTO
Klausel dazu, dass die Anweisung fehlschlägt, wenn sie aus einem Trigger aufgerufen wird.
Die OUTPUT
-Klausel unterstützt die LOB-Datentypen: nvarchar(max), varchar(max), varbinary(max), text, ntext, image und xml. Wenn Sie die Klausel in der .WRITE
UPDATE
Anweisung verwenden, um eine nvarchar(max)-, varchar(max)- oder varbinary(max) -Spalte zu ändern, werden die vollständigen Vor- und Nachbilder der Werte zurückgegeben, wenn auf sie verwiesen wird. Die TEXTPTR()
-Funktion kann nicht im Rahmen eines Ausdrucks in einer text-, ntext- oder image-Spalte in der OUTPUT
-Klausel angezeigt werden.
OUTPUT
kann in Anwendungen, die Tabellen als Warteschlangen verwenden, oder zum Aufbewahren von Zwischenresultsets verwendet werden. Die Anwendung fügt der Tabelle somit kontinuierlich Zeilen hinzu oder entfernt sie daraus. Im folgenden Beispiel wird die OUTPUT
Klausel in einer DELETE
Anweisung verwendet, um die gelöschte Zeile an die aufrufende Anwendung zurückzugeben.
USE AdventureWorks2022;
GO
DELETE TOP(1) dbo.DatabaseLog WITH (READPAST)
OUTPUT DELETED.*
WHERE DatabaseLogID = 7;
GO
In diesem Beispiel wird eine Zeile aus einer als Warteschlange verwendeten Tabelle entfernt und die gelöschten Werte in einer einzigen Aktion an die verarbeitende Anwendung zurückgegeben. Andere Semantik kann auch implementiert werden, z. B. die Verwendung einer Tabelle zum Implementieren eines Stapels. SQL Server garantiert jedoch nicht die Reihenfolge, in der Zeilen verarbeitet und von DML-Anweisungen über die OUTPUT
-Klausel zurückgegeben werden. Es liegt an der Anwendung, eine entsprechende WHERE
Klausel einzuschließen, die die gewünschte Semantik garantieren kann, oder verstehen, dass, wenn mehrere Zeilen für den DML-Vorgang berechtigt sind, keine garantierte Reihenfolge vorhanden ist. Im folgenden Beispiel wird eine Unterabfrage verwendet und davon ausgegangen, dass die DatabaseLogID
-Spalte eindeutig ist, um die gewünschte Reihenfolgensemantik zu implementieren.
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;
Dies sind die Ergebnisse:
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
Hinweis
Verwenden Sie den READPAST
Tabellenhinweis in UPDATE
und DELETE
Anweisungen, wenn Ihr Szenario es mehreren Anwendungen ermöglicht, einen destruktiven Lesevorgang aus einer Tabelle auszuführen. So werden Sperrkonflikte verhindert, die entstehen, wenn eine andere Anwendung bereits den ersten berechtigten Datensatz in der Tabelle liest.
SELECT
Berechtigungen sind für alle Spalten erforderlich, die durch <dml_select_list>
oder verwendet werden.<scalar_expression>
INSERT
Berechtigungen sind für alle tabellen erforderlich, die in <output_table>
.
Die Transact-SQL-Codebeispiele in diesem Artikel verwenden die AdventureWorks2022
- oder AdventureWorksDW2022
-Beispieldatenbank, die Sie von der Homepage Microsoft SQL Server Samples and Community Projects herunterladen können.
Im folgenden Beispiel wird eine Zeile in die ScrapReason
-Tabelle eingefügt, und die OUTPUT
-Klausel wird verwendet, um die Ergebnisse der Anweisung an die @MyTableVar
-Tabellenvariable zurückzugeben. Da die ScrapReasonID
-Spalte mit einer IDENTITY-Eigenschaft definiert wurde, wird kein Wert in der INSERT
-Anweisung für diese Spalte angegeben. Der von der Datenbank-Engine für diese Spalte generierte Wert wird jedoch in der OUTPUT
-Klausel in der INSERTED.ScrapReasonID
-Spalte zurückgegeben.
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
Im folgenden Beispiel werden alle Zeilen in der ShoppingCartItem
-Tabelle gelöscht. Die OUTPUT DELETED.*
-Klausel gibt an, dass die Ergebnisse der DELETE
-Anweisung, also alle Spalten in den gelöschten Zeilen, an die aufrufende Anweisung zurückgegeben werden. Die nachfolgende SELECT
-Anweisung überprüft die Ergebnisse des Löschvorgangs in der ShoppingCartItem
-Tabelle.
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
Im folgenden Beispiel werden die ersten zehn Zeilen der VacationHours
-Spalte in der Employee
-Tabelle um 25% aktualisiert. Die OUTPUT
-Klausel gibt an die VacationHours
-Tabellenvariable den Wert für UPDATE
zurück, der vor der Anwendung der DELETED.VacationHours
-Anweisung in der INSERTED.VacationHours
-Spalte vorhanden war, und den aktualisierten Wert in der @MyTableVar
-Spalte.
Es folgen zwei SELECT
-Anweisungen, die die Werte in @MyTableVar
und die Ergebnisse des Updatevorgangs in der Employee
-Tabelle zurückgeben.
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
Das folgende Beispiel baut auf Beispiel C auf, indem ein Ausdruck in der OUTPUT
-Klausel definiert wird, der die Differenz zwischen dem aktualisierten VacationHours
-Wert und dem VacationHours
-Wert vor dem Update beschreibt. Der Wert dieses Ausdrucks wird an die Taballenvariable @MyTableVar
in der Spalte VacationHoursDifference
zurückgegeben.
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
Im folgenden Beispiel werden alle Arbeitsaufträge in der ScrapReasonID
-Spalte der WorkOrder
-Tabelle aktualisiert, für die ProductID
und ScrapReasonID
angegeben wurde. Die OUTPUT INTO
-Klausel gibt Werte aus der Tabelle, die aktualisiert wird (WorkOrder
), sowie aus der Product
-Tabelle zurück. Die Product
-Tabelle wird in der FROM
-Klausel zur Angabe der zu aktualisierenden Zeilen verwendet. Da für die WorkOrder
-Tabelle ein AFTER UPDATE
-Trigger definiert wurde, wird das INTO
-Schlüsselwort benötigt.
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
Im folgenden Beispiel werden Zeilen in der ProductProductPhoto
-Tabelle auf der Grundlage von in der FROM
-Klausel der DELETE
-Anweisung definierten Suchkriterien gelöscht. Die OUTPUT
-Klausel gibt Spalten aus der Tabelle zurück, die gelöscht wird (DELETED.ProductID
, DELETED.ProductPhotoID
), sowie Spalten aus der Product
-Tabelle. Diese Tabelle wird in der FROM
-Klausel zur Angabe der zu löschenden Zeilen verwendet.
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
Im folgenden Beispiel wird ein Teilwert in DocumentSummary
der Spalte "nvarchar(max)" in der Production.Document
Tabelle mithilfe der .WRITE
Klausel aktualisiert. Der Begriff components
wird durch den Begriff features
ersetzt, indem der Ersatzbegriff, die Anfangsposition (offset) des zu ersetzenden Begriffs in den vorhandenen Daten und die Anzahl der zu ersetzenden Zeichen (length) angegeben werden. Im Beispiel wird die OUTPUT
-Klausel zur Rückgabe der Anfangs- und Endimages der Spalte DocumentSummary
an die Tabellenvariable @MyTableVar
verwendet. Die vollständigen Anfangs- und Endimages der DocumentSummary
-Spalte werden zurückgegeben.
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
Im folgenden Beispiel werden die Ergebnisse des Triggervorgangs mithilfe der OUTPUT
-Klausel in einem Trigger zurückgegeben. Zunächst wird eine Sicht in der ScrapReason
-Tabelle erstellt und anschließend ein INSTEAD OF INSERT
-Trigger für die Sicht definiert, der nur zulässt, dass Benutzer die Name
-Spalte der Basistabelle ändern können. Da die ScrapReasonID
-Spalte eine IDENTITY
-Spalte in der Basistabelle ist, ignoriert der Trigger den vom Benutzer bereitgestellten Wert. Dadurch kann Datenbank-Engine automatisch den richtigen Wert generieren. Darüber hinaus wird der vom Benutzer für ModifiedDate
bereitgestellte Wert ignoriert und auf das aktuelle Datum festgelegt. Die OUTPUT
-Klausel gibt die tatsächlich in die ScrapReason
-Tabelle eingefügten Werte zurück.
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
Dies ist das am 12. April 2004 ('2004-04-12'
) generierte Resultset. Die ScrapReasonIDActual
Und ModifiedDate
Spalten spiegeln die Werte wider, die vom Triggervorgang generiert werden, anstelle der in der INSERT
Anweisung angegebenen Werte.
ScrapReasonID Name ModifiedDate
------------- ---------------- -----------------------
17 My scrap reason 2004-04-12 16:23:33.050
Im folgenden Beispiel wird die EmployeeSales
-Tabelle erstellt, und anschließend werden mehrere Zeilen unter Verwendung einer INSERT
-Anweisung mit einer SELECT
-Anweisung in die Tabelle eingefügt, um Daten aus Quelltabellen abzurufen. Die EmployeeSales
-Tabelle enthält eine Identitätsspalte (EmployeeID
) und eine berechnete Spalte (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
Im folgenden Beispiel werden Zeilen in der ProductProductPhoto
-Tabelle auf der Grundlage von in der FROM
-Klausel der DELETE
-Anweisung definierten Suchkriterien gelöscht. Die OUTPUT INTO
-Klausel gibt Spalten aus der Tabelle, die gelöscht wird (DELETED.ProductID
, DELETED.ProductPhotoID
), sowie Spalten aus der Tabelle Product
an die Tabellenvariable @MyTableVar
zurück. Die Product
-Tabelle wird in der FROM
-Klausel zur Angabe der zu löschenden Zeilen verwendet. Die OUTPUT
Klausel gibt die DELETED.ProductID
Spalten DELETED.ProductPhotoID
und das Datum und die Uhrzeit zurück, zu dem die Zeile aus der ProductProductPhoto
Tabelle in die aufrufende Anwendung gelöscht wurde.
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
Im folgenden Beispiel werden von der OUTPUT
-Klausel einer MERGE
-Anweisung zurückgegebene Daten erfasst und in eine andere Tabelle eingefügt. Die MERGE
-Anweisung aktualisiert die Quantity
-Spalte der ProductInventory
-Tabelle täglich auf Grundlage der Bestellungen, die in der SalesOrderDetail
-Tabelle verarbeitet werden. Außerdem werden die Zeilen für Produkte gelöscht, deren Bestand auf 0
oder weniger zurückgeht. Das Beispiel erfasst die gelöschten Zeilen und fügt sie in einer anderen Tabelle (ZeroInventory
) ein, in der Produkte ohne Bestand gespeichert werden.
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