Verwenden der Tabellen inserted und deleted

DML-Triggeranweisungen verwenden zwei spezielle Tabellen: die deleted-Tabelle und die inserted-Tabelle. SQL Server erstellt und verwaltet 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. 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.

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 Vorgängen, die auf diesem Unterschied basieren

In der deleted-Tabelle werden Kopien der von DELETE- und UPDATE-Anweisungen betroffenen Zeilen gespeichert. Während der Ausführung einer DELETE- oder UPDATE-Anweisung werden Zeilen aus der Triggertabelle gelöscht und in die deleted-Tabelle übertragen. Die deleted-Tabelle und die Triggertabelle enthalten normalerweise nicht die gleichen Zeilen.

In der inserted-Tabelle werden Kopien der durch INSERT- und UPDATE-Anweisungen betroffenen Zeilen gespeichert. Während einer Einfügungs- oder Aktualisierungstransaktion werden neue Zeilen sowohl der inserted-Tabelle als auch der Triggertabelle hinzugefügt. Die Zeilen in der inserted-Tabelle sind Kopien der neuen Zeilen in der Triggertabelle.

Eine Aktualisierungstransaktion 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.

Wenn Sie Triggerbedingungen festlegen, sollten Sie die inserted- und die deleted-Tabelle entsprechend der Aktion verwenden, die den Trigger ausgelöst hat. 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.

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.

In SQL Server 2008 sind Spaltenverweise vom Typ text, ntext oder image in der inserted-Tabelle und der deleted-Tabelle für AFTER-Trigger nicht zulässig. Diese Datentypen sind jedoch nur aus Gründen der Abwärtskompatibilität eingeschlossen. Die bevorzugte Speichermethode für große Datenmengen ist die Verwendung der varchar(max)-, nvarchar(max)- und varbinary(max)-Datentypen. Sowohl der AFTER-Trigger als auch der INSTEAD OF-Trigger unterstützen Daten vom Typ varchar(max), nvarchar(max) und varbinary(max) in der inserted-Tabelle und deleted-Tabelle. Weitere Informationen finden Sie unter CREATE TRIGGER (Transact-SQL).

Beispiel für die Verwendung der inserted-Tabelle in einem Trigger zum Erzwingen einer Geschäftsregel

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.

Hinweis
Beispiele für DML-AFTER-Trigger, die mehrere Zeilen aktualisieren, finden Sie unter Trigger bei mehrzeiligen Operationen.

USE AdventureWorks2008R2;
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