Verwenden der Tabellen „inserted“ und „deleted“

Gilt für:SQL ServerAzure SQL-DatenbankVerwaltete Azure SQL-InstanzSQL-Datenbank in Microsoft Fabric

DML-Triggeranweisungen verwenden zwei besondere Tabellen: deleted und inserted. SQL Server erstellt und verwendet diese Tabellen automatisch. Sie können diese temporären, speicherresidenten Tabellen verwenden, um die Auswirkungen bestimmter Datenänderungen zu testen und Bedingungen für DML-Triggeraktionen festzulegen. Sie können die Daten in den Tabellen nicht direkt verändern oder Datendefinitionssprache (DDL)-Operationen auf den Tabellen durchführen, wie zum Beispiel CREATE INDEX.

Grundlegendes zu den Tabellen „inserted“ und „deleted“

In DML-Triggern werden die Tabellen inserted und deleted hauptsächlich für folgende Vorgänge verwendet:

• Erweitern der referenziellen Integrität zwischen Tabellen.

• Einfügen oder Aktualisieren von Daten in Basistabellen, die einer Sicht zugrunde liegen.

• Testen im Hinblick auf Fehler und Ausführen der entsprechenden Vorgänge.

• Ermitteln des Unterschieds zwischen dem Tabellenstatus vor und nach einer Datenänderung und Ausführen von Aktionen, die auf diesem Unterschied basieren

Die gelöschte Tabelle speichert Kopien der betroffenen Zeilen in der Trigger-Tabelle, bevor sie durch eine DELETE oder-Anweisung UPDATE geändert wurden (die Trigger-Tabelle ist die Tabelle, auf der der DML-Trigger läuft). Während der Ausführung einer DELETE oder-Anweisung UPDATE werden die betroffenen Zeilen zunächst aus der Trigger-Tabelle kopiert und in die gelöschte Tabelle übertragen.

Die eingefügte Tabelle speichert Kopien der neuen oder geänderten Zeilen nach einer INSERT oder-Anweisung UPDATE . Während der Ausführung einer INSERT oder-Anweisung UPDATE werden die neuen oder geänderten Zeilen in der Trigger-Tabelle in die eingefügte Tabelle kopiert. Die Zeilen in der Tabelle „inserted“ sind Kopien der neuen oder aktualisierten Zeilen in der Triggertabelle.

Eine Updatetransaktion ähnelt einem Löschvorgang, dem ein Einfügevorgang folgt. Während der Ausführung einer UPDATE Anweisung tritt folgende Abfolge von Ereignissen auf:

1. Die ursprüngliche Zeile wird aus der Triggertabelle in die Tabelle „deleted“ kopiert.
2. Die Trigger-Tabelle wird mit den neuen Werten aus der UPDATE Anweisung aktualisiert.
3. Die aktualisierte Zeile in der Triggertabelle wird in die Tabelle „inserted“ kopiert.

Dadurch können Sie den Inhalt der Zeile vor dem Update (in der Tabelle „deleted“) mit den neuen Zeilenwerten nach dem Update (in der Tabelle „inserted“) vergleichen.

Wenn Sie Triggerbedingungen festlegen, sollten Sie die inserted- und die deleted-Tabelle entsprechend der Aktion verwenden, die den Trigger ausgelöst hat. Obwohl das Verweisen auf die gelöschte Tabelle beim Testen INSERT einer oder die eingefügte Tabelle beim Testen von a DELETE keine Fehler verursacht, enthalten diese Trigger-Testtabellen in diesen Fällen keine Zeilen.

SQL Server lässt keine Spaltenverweise vom Typ text, ntextoder image in der inserted- und deleted-Tabelle für AFTER-Trigger zu. Diese Datentypen sind jedoch nur aus Gründen der Abwärtskompatibilität eingeschlossen. Speichern Sie umfangreiche Daten bevorzugt mit den Datentypen varchar(max), nvarchar(max)und varbinary(max) . Sowohl AFTER- als auch INSTEAD OF-Trigger unterstützen varchar(max)-, nvarchar(max)- und varbinary(max) -Daten in der inserted- und deleted-Tabelle. Weitere Informationen finden Sie unter CREATE TRIGGER (Transact-SQL).

Beispiel: Verwenden der Tabelle „inserted“ in einem Trigger zum Erzwingen von Geschäftsregeln

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.

Im folgenden Beispiel wird ein DML-Trigger erstellt. Der Trigger überprüft die Bonität eines Herstellers, wenn versucht wird, eine neue Bestellung in die PurchaseOrderHeader -Tabelle einzufügen. 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. Ist die Bonität zu niedrig, wird eine Meldung angezeigt, und die Bestellung wird nicht eingefügt.

USE AdventureWorks2022;
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 (ROWCOUNT_BIG() = 0)
    RETURN;
    IF EXISTS (SELECT 1
        FROM inserted AS i
        INNER JOIN Purchasing.Vendor AS v
            ON v.BusinessEntityID = i.VendorID
            WHERE v.CreditRating = 5)
BEGIN
    RAISERROR ('A vendor''s credit rating is too low to accept new purchase orders.', 16, 1);
    ROLLBACK;
    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 Tabellen „inserted“ und „deleted“ in INSTEAD OF-Triggern

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. Das Format der inserted- und deleted-Tabellen ist mit dem Format der Tabelle identisch, für die der INSTEAD OF-Trigger definiert wurde. Jede Spalte in den inserted- und deleted-Tabellen ist direkt einer Spalte in der Basistabelle zugeordnet.

Die folgenden Regeln darüber, wann eine INSERT ODER-Anweisung UPDATE , die auf eine Tabelle mit einem INSTEAD OF-Trigger verweist, Werte für Spalten liefern muss, sind dieselben, als hätte die Tabelle keinen INSTEAD OF-Trigger:

• Für berechnete Spalten oder Spalten mit einem timestamp -Datentyp können keine Werte angegeben werden.

• Werte können für Spalten mit einer Eigenschaft IDENTITY nicht angegeben werden, es sei denn IDENTITY_INSERT , für diese Tabelle ist ON. Wenn IDENTITY_INSERT ON ist, INSERT müssen Aussagen einen Wert liefern.

• INSERT Anweisungen müssen Werte für alle NICHT NULL-Spalten liefern, die keine Einschränkungen besitzen DEFAULT .

• Für alle Spalten außer berechneten, Identitäts- oder Zeitstempelspalten sind Werte optional für jede Spalte, die Nullpunkte erlaubt, oder für jede NICHT-NULL-Spalte, die eine Definition DEFAULT hat.

Wenn eine INSERT, , oder-Anweisung UPDATEDELETE auf eine Ansicht verweist, die einen INSTEAD-OF-Trigger hat, ruft die Datenbank-Engine den Trigger auf, anstatt eine direkte Aktion gegen eine beliebige Tabelle auszuführen. 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.

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. Zum Beispiel:

USE AdventureWorks2022;  
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. 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.

Die Auswahlliste einer Sicht kann auch Ausdrücke aufweisen, die nicht direkt einer einzigen Basistabellenspalte zugeordnet sind. Einige Sichtausdrücke, z. B. Konstanten- oder Funktionsaufrufe, verweisen möglicherweise nicht auf Spalten und können deshalb ignoriert werden. 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. Dasselbe gilt für einfache Ausdrücke in einer Sicht, wenn sie auf eine berechnete Spalte mit einem komplexen Ausdruck verweisen. Ein INSTEAD OF-Trigger in der Sicht muss diese Ausdruckstypen verarbeiten.

Überlegungen zur Leistung

Da es sich bei eingefügten und gelöschten Tabellen um virtuelle, speicherresidente Tabellen handelt, sind Eigenschaften wie Statistiken oder Indizes nicht verfügbar. Obwohl einige Kardinalitätsinformationen aus diesen Tabellen verfügbar gemacht werden, sollten Sie bei Überlegungen zur Anzahl von Zeilen, die vorübergehend dort gespeichert werden sollen, vorsichtig sein. Das Einfügen einer großen Anzahl von Zeilen in diese Tabellen und das Abfragen oder Verknüpfen mit anderen Tabellen kann zu suboptimalen Abfrageplänen und langsamen Abfrageausführungen führen. Entwerfen und testen Sie Ihre Anwendung sehr sorgfältig, um Ihre Anforderungen an die Abfrageleistung zu erfüllen.

Nächste Schritte

Weitere Informationen finden Sie in der Übersicht über DML-Trigger.