Verwenden der Tabellen inserted und deletedUse the inserted and deleted Tables

Gilt für: JaSQL Server JaAzure SQL-Datenbank NeinAzure Synapse Analytics (SQL DW) NeinParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

DML-Triggeranweisungen verwenden zwei besondere Tabellen: die inserted-Tabelle und die deleted-Tabelle.DML trigger statements use two special tables: the deleted table and the inserted tables. SQL ServerSQL Server erstellt und verwendet diese Tabellen automatisch.automatically creates and manages these tables. Sie können diese temporären, speicherresidenten Tabellen verwenden, um die Auswirkungen bestimmter Datenänderungen zu testen und Bedingungen für DML-Triggeraktionen festzulegen.You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for DML trigger actions. Das direkte Ändern der Daten in den Tabellen bzw. das Ausführen von Data Definition Language-(DDL-)Vorgängen für die Tabellen, beispielsweise CREATE INDEX, ist nicht möglich.You cannot directly modify the data in the tables or perform data definition language (DDL) operations on the tables, such as CREATE INDEX.

In DML-Triggern werden die Tabellen inserted und deleted hauptsächlich für folgende Vorgänge verwendet:In DML triggers, the inserted and deleted tables are primarily used to perform the following:

  • Erweitern der referenziellen Integrität zwischen Tabellen.Extend referential integrity between tables.

  • Einfügen oder Aktualisieren von Daten in Basistabellen, die einer Sicht zugrunde liegen.Insert or update data in base tables underlying a view.

  • Testen im Hinblick auf Fehler und Ausführen der entsprechenden Vorgänge.Test for errors and take action based on the error.

  • Ermitteln des Unterschieds zwischen dem Tabellenstatus vor und nach einer Datenänderung und Ausführen von Aktionen, die auf diesem Unterschied basierenFind the difference between the state of a table before and after a data modification and take actions based on that difference.

In der deleted-Tabelle werden Kopien der von DELETE- und UPDATE-Anweisungen betroffenen Zeilen gespeichert.The deleted table stores copies of the affected rows during DELETE and UPDATE statements. Während der Ausführung einer DELETE- oder UPDATE-Anweisung werden Zeilen aus der Triggertabelle gelöscht und in die deleted-Tabelle übertragen.During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table. Die deleted-Tabelle und die Triggertabelle enthalten normalerweise nicht die gleichen Zeilen.The deleted table and the trigger table ordinarily have no rows in common.

In der inserted-Tabelle werden Kopien der durch INSERT- und UPDATE-Anweisungen betroffenen Zeilen gespeichert.The inserted table stores copies of the affected rows during INSERT and UPDATE statements. Während einer Einfügungs- oder Updatetransaktion werden neue Zeilen sowohl der inserted-Tabelle als auch der Triggertabelle hinzugefügt.During an insert or update transaction, new rows are added to both the inserted table and the trigger table. Die Zeilen in der inserted-Tabelle sind Kopien der neuen Zeilen in der Triggertabelle.The rows in the inserted table are copies of the new rows in the trigger table.

Eine Updatetransaktion entspricht einem Löschvorgang mit anschließender Einfügung; die alten Zeilen werden zunächst in die deleted-Tabelle kopiert, und anschließend werden die neuen Zeilen in die Triggertabelle und die inserted-Tabelle kopiert.An update transaction is similar to a delete operation followed by an insert operation; the old rows are copied to the deleted table first, and then the new rows are copied to the trigger table and to the inserted table.

Wenn Sie Triggerbedingungen festlegen, sollten Sie die inserted- und die deleted-Tabelle entsprechend der Aktion verwenden, die den Trigger ausgelöst hat.When you set trigger conditions, use the inserted and deleted tables appropriately for the action that fired the trigger. Es wird zwar kein Fehler ausgegeben, wenn Sie beim Testen einer INSERT-Anweisung auf die deleted-Tabelle oder beim Testen einer DELETE-Anweisung auf die inserted-Tabelle verweisen, diese Triggertesttabellen enthalten jedoch in diesen Fällen keine Zeilen.Although referencing the deleted table when testing an INSERT or the inserted table when testing a DELETE does not cause any errors, these trigger test tables do not contain any rows in these cases.

Hinweis

Wenn Triggeraktionen von der Anzahl der Zeilen abhängen, die von einer Datenänderung betroffen sind, sollten Sie Tests verwenden (z.B. die Untersuchung von @@ROWCOUNT), die mehrzeilige Datenänderungen (eine INSERT-, DELETE- oder UPDATE-Anweisung, die auf einer SELECT-Anweisung basiert) überprüfen und die entsprechenden Vorgänge ausführen.If trigger actions depend on the number of rows a data modification effects, use tests (such as an examination of @@ROWCOUNT) for multirow data modifications (an INSERT, DELETE, or UPDATE based on a SELECT statement), and take appropriate actions.

SQL ServerSQL Server lässt keine Spaltenverweise vom Typ text, ntextoder image in der inserted- und deleted-Tabelle für AFTER-Trigger zu.does not allow for text, ntext, or image column references in the inserted and deleted tables for AFTER triggers. Diese Datentypen sind jedoch nur aus Gründen der Abwärtskompatibilität eingeschlossen.However, these data types are included for backward compatibility purposes only. Speichern Sie umfangreiche Daten bevorzugt mit den Datentypen varchar(max), nvarchar(max) und varbinary(max) .The preferred storage for large data is to use the varchar(max), nvarchar(max), and varbinary(max) data types. Sowohl AFTER- als auch INSTEAD OF-Trigger unterstützen varchar(max)-, nvarchar(max)- und varbinary(max)-Daten in der inserted- und deleted-Tabelle.Both AFTER and INSTEAD OF triggers support varchar(max), nvarchar(max), and varbinary(max) data in the inserted and deleted tables. Weitere Informationen finden Sie unter CREATE TRIGGER (Transact-SQL).For more information, see CREATE TRIGGER (Transact-SQL).

Beispiel für die Verwendung der inserted-Tabelle in einem Trigger zum Erzwingen einer GeschäftsregelAn Example of Using the inserted Table in a Trigger to Enforce Business Rules

Da sich CHECK-Einschränkungen nur auf Spalten beziehen können, für die die Einschränkung auf Spalten- oder Tabellenebene definiert wurde, müssen tabellenübergreifende Einschränkungen (in diesem Fall Geschäftsregeln) als Trigger definiert werden.Because CHECK constraints can reference only the columns on which the column-level or table-level constraint is defined, any cross-table constraints (in this case, business rules) must be defined as triggers.

Im folgenden Beispiel wird ein DML-Trigger erstellt.The following example creates a DML trigger. Der Trigger überprüft die Bonität eines Herstellers, wenn versucht wird, eine neue Bestellung in die PurchaseOrderHeader -Tabelle einzufügen.This trigger checks to make sure the credit rating for the vendor is good when an attempt is made to insert a new purchase order into the PurchaseOrderHeader table. Zum Ermitteln der Bonität des Herstellers im Zusammenhang mit der gerade eingefügten Bestellung muss auf die Vendor -Tabelle verwiesen und diese mit der inserted-Tabelle verknüpft werden.To obtain the credit rating of the vendor corresponding to the purchase order that was just inserted, the Vendor table must be referenced and joined with the inserted table. Ist die Bonität zu niedrig, wird eine Meldung angezeigt, und die Bestellung wird nicht eingefügt.If the credit rating is too low, a message is displayed and the insertion does not execute. Bitte beachten Sie, dass in diesem Beispiel multirow-Datenänderungen nicht berücksichtigt werden.Note that this example does not allow for multirow data modifications. Weitere Informationen finden Sie unter Create DML Triggers to Handle Multiple Rows of Data.For more information, see Create DML Triggers to Handle Multiple Rows of Data.

USE AdventureWorks2012;
GO
IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL
   DROP TRIGGER Purchasing.LowCredit;
GO
-- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table
-- when the credit rating of the specified vendor is set to 5 (below average).

CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader
AFTER INSERT
AS
IF EXISTS (SELECT *
           FROM Purchasing.PurchaseOrderHeader p 
           JOIN inserted AS i 
           ON p.PurchaseOrderID = i.PurchaseOrderID 
           JOIN Purchasing.Vendor AS v 
           ON v.BusinessEntityID = p.VendorID
           WHERE v.CreditRating = 5
          )
BEGIN
RAISERROR ('A vendor''s credit rating is too low to accept new
purchase orders.', 16, 1);
ROLLBACK TRANSACTION;
RETURN 
END;
GO

-- This statement attempts to insert a row into the PurchaseOrderHeader table
-- for a vendor that has a below average credit rating.
-- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back.

INSERT INTO Purchasing.PurchaseOrderHeader (RevisionNumber, Status, EmployeeID,
VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight)
VALUES (
2
,3
,261	
,1652	
,4	
,GETDATE()
,GETDATE()
,44594.55	
,3567.564	
,1114.8638 );
GO

Verwenden der inserted- und deleted-Tabellen in INSTEAD OF-TriggernUsing the inserted and deleted Tables in INSTEAD OF Triggers

Für die inserted- und deleted-Tabellen, die an für Tabellen definierte INSTEAD OF-Trigger übergeben werden, gelten dieselben Regeln wie für die inserted- und deleted-Tabellen, die an AFTER-Trigger übergeben werden.The inserted and deleted tables passed to INSTEAD OF triggers defined on tables follow the same rules as the inserted and deleted tables passed to AFTER triggers. Das Format der inserted- und deleted-Tabellen ist mit dem Format der Tabelle identisch, für die der INSTEAD OF-Trigger definiert wurde.The format of the inserted and deleted tables is the same as the format of the table on which the INSTEAD OF trigger is defined. Jede Spalte in den inserted- und deleted-Tabellen ist direkt einer Spalte in der Basistabelle zugeordnet.Each column in the inserted and deleted tables maps directly to a column in the base table.

Für die Frage, wann eine INSERT- oder UPDATE-Anweisung, die auf eine Tabelle mit einem INSTEAD OF-Trigger verweist, Werte für Spalten angeben muss, gelten dieselben Regeln, wie wenn in der Tabelle kein INSTEAD OF-Trigger vorhanden wäre:The following rules regarding when an INSERT or UPDATE statement referencing a table with an INSTEAD OF trigger must supply values for columns are the same as if the table did not have an INSTEAD OF trigger:

  • Für berechnete Spalten oder Spalten mit einem timestamp -Datentyp können keine Werte angegeben werden.Values cannot be specified for computed columns or columns with a timestamp data type.

  • Für Spalten mit einer IDENTITY-Eigenschaft können nur Werte angegeben werden, wenn IDENTITY_INSERT für diese Tabelle auf ON festgelegt ist.Values cannot be specified for columns with an IDENTITY property, unless IDENTITY_INSERT is ON for that table. Wenn IDENTITY_INSERT auf ON festgelegt ist, müssen INSERT-Anweisungen einen Wert angeben.When IDENTITY_INSERT is ON, INSERT statements must supply a value.

  • INSERT-Anweisungen müssen Werte für alle NOT NULL-Spalten angeben, die keine DEFAULT-Einschränkungen aufweisen.INSERT statements must supply values for all NOT NULL columns that do not have DEFAULT constraints.

  • Außer in berechneten Spalten, Identitätsspalten oder timestamp -Spalten sind Werte für alle Spalten, die NULL-Werte zulassen, oder für alle NOT NULL-Spalten optional, die eine DEFAULT-Definition aufweisen.For any columns except computed, identity, or timestamp columns, values are optional for any column that allows nulls, or any NOT NULL column that has a DEFAULT definition.

Wenn eine INSERT-, UPDATE- oder DELETE-Anweisung auf eine Sicht verweist, die einen INSTEAD OF-Trigger aufweist, ruft Datenbank-EngineDatabase Engine den Trigger auf, anstatt direkte Aktionen für Tabellen auszuführen.When an INSERT, UPDATE, or DELETE statement references a view that has an INSTEAD OF trigger, the Datenbank-EngineDatabase Engine calls the trigger instead of taking any direct action against any table. Der Trigger muss anhand der in den inserted- und deleted-Tabellen dargestellten Informationen Anweisungen erstellen, die zum Implementieren der angeforderten Aktion in den Basistabellen erforderlich sind, selbst wenn das Format der für die Sicht erstellten Informationen in den inserted- und deleted-Tabellen nicht mit dem Format der Daten in den Basistabellen identisch ist.The trigger must use the information presented in the inserted and deleted tables to build any statements required to implement the requested action in the base tables, even when the format of the information in the inserted and deleted tables built for the view is different from the format of the data in the base tables.

Das Format der inserted- und deleted-Tabellen, das an einen in einer Sicht definierten INSTEAD OF-Trigger übergeben wird, stimmt mit der Auswahlliste der SELECT-Anweisung überein, die für die Sicht definiert wurde.The format of the inserted and deleted tables passed to an INSTEAD OF trigger defined on a view matches the select list of the SELECT statement defined for the view. Beispiel:For example:

USE AdventureWorks2012;  
GO  
CREATE VIEW dbo.EmployeeNames (BusinessEntityID, LName, FName)  
AS  
SELECT e.BusinessEntityID, p.LastName, p.FirstName  
FROM HumanResources.Employee AS e   
JOIN Person.Person AS p  
ON e.BusinessEntityID = p.BusinessEntityID;  

Das Resultset für diese Sicht weist drei Spalten auf: eine int -Spalte und zwei nvarchar -Spalten.The result set for this view has three columns: an int column and two nvarchar columns. Die inserted-Tabelle und die deleted-Tabelle, die an einen für die Sicht definierten INSTEAD OF-Trigger übergeben werden, enthalten ebenfalls eine int -Spalte mit der Bezeichnung BusinessEntityID, eine nvarchar -Spalte mit der Bezeichnung LNameund eine nvarchar -Spalte mit der Bezeichnung FName.The inserted and deleted tables passed to an INSTEAD OF trigger defined on the view also have an int column named BusinessEntityID, an nvarchar column named LName, and an nvarchar column named FName.

Die Auswahlliste einer Sicht kann auch Ausdrücke aufweisen, die nicht direkt einer einzigen Basistabellenspalte zugeordnet sind.The select list of a view can also contain expressions that do not directly map to a single base-table column. Einige Sichtausdrücke, z. B. Konstanten- oder Funktionsaufrufe, verweisen möglicherweise nicht auf Spalten und können deshalb ignoriert werden.Some view expressions, such as a constant or function invocation, may not reference any columns and can be ignored. Komplexe Ausdrücke können auf mehrere Spalten verweisen, aber die inserted- und deleted-Tabellen enthalten für jede eingefügte Zeile nur einen einzigen Wert.Complex expressions can reference multiple columns, yet the inserted and deleted tables have only one value for each inserted row. Dasselbe gilt für einfache Ausdrücke in einer Sicht, wenn sie auf eine berechnete Spalte mit einem komplexen Ausdruck verweisen.The same issues apply to simple expressions in a view if they reference a computed column that has a complex expression. Ein INSTEAD OF-Trigger in der Sicht muss diese Ausdruckstypen verarbeiten.An INSTEAD OF trigger on the view must handle these types of expressions.