Verwenden der eingefügten und gelöschten Tabellen

DML-Triggeranweisungen verwenden zwei spezielle Tabellen: die gelöschte Tabelle und die eingefügten Tabellen. SQL Server erstellt und verwaltet diese Tabellen automatisch. Sie können diese temporären, speicherresresidierenden 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 ändern oder DDL-Vorgänge (Data Definition Language) für die Tabellen ausführen, z. B. CREATE INDEX.

In DML-Triggern werden die eingefügten und gelöschten Tabellen in erster Linie verwendet, um Folgendes auszuführen:

• Erweitern Sie die referenzielle Integrität zwischen Tabellen.

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

• Testen Sie auf Fehler, und ergreifen Sie basierend auf dem Fehler Maßnahmen.

• Suchen Sie den Unterschied zwischen dem Status einer Tabelle vor und nach einer Datenänderung, und ergreifen Sie Aktionen basierend auf diesem Unterschied.

In der gelöschten Tabelle werden Kopien der betroffenen Zeilen während delete- und UPDATE-Anweisungen gespeichert. Während der Ausführung einer DELETE- oder UPDATE-Anweisung werden Zeilen aus der Triggertabelle gelöscht und an die gelöschte Tabelle übertragen. Die gelöschte Tabelle und die Auslösertabelle weisen in der Regel keine gemeinsamen Zeilen auf.

Die eingefügte Tabelle speichert Kopien der betroffenen Zeilen während insert- und UPDATE-Anweisungen. Während einer Einfüge- oder Aktualisierungstransaktion werden neue Zeilen sowohl der eingefügten Tabelle als auch der Triggertabelle hinzugefügt. Die Zeilen in der eingefügten Tabelle sind Kopien der neuen Zeilen in der Triggertabelle.

Eine Aktualisierungstransaktion ähnelt einem Löschvorgang, gefolgt von einem Einfügevorgang; die alten Zeilen werden zuerst in die gelöschte Tabelle kopiert, und dann werden die neuen Zeilen in die Triggertabelle und in die eingefügte Tabelle kopiert.

Wenn Sie Triggerbedingungen festlegen, verwenden Sie die eingefügten und gelöschten Tabellen entsprechend für die Aktion, die den Trigger ausgelöst hat. Obwohl beim Testen eines INSERT auf die gelöschte Tabelle oder beim Testen eines DELETE auf die eingefügte Tabelle keine Fehler verursacht werden, enthalten diese Triggertesttabellen in diesen Fällen keine Zeilen.

SQL Server 2014 erlaubt keine text, ntext oder image-Spaltenverweise in den Tabellen "inserted" und "deleted" für AFTER-Trigger. Diese Datentypen sind jedoch nur aus Gründen der Abwärtskompatibilität enthalten. Der bevorzugte Speicher für große Daten besteht darin, die varchar(max)Datentypen und varbinary(max)nvarchar(max)Datentypen zu verwenden. Sowohl AFTER als auch INSTEAD OF triggers unterstützen varchar(max), nvarchar(max)und varbinary(max) Daten in den eingefügten und gelöschten Tabellen. Weitere Informationen finden Sie unter CREATE TRIGGER (Transact-SQL).

Beispiel für die Verwendung der eingefügten Tabelle in einem Trigger zum Erzwingen von Geschäftsregeln

Da CHECK-Einschränkungen nur auf die Spalten verweisen können, für die die Einschränkung auf Spaltenebene oder Tabellenebene definiert ist, müssen alle tabellenübergreifenden Einschränkungen (in diesem Fall Geschäftsregeln) als Trigger definiert werden.

Im folgenden Beispiel wird ein DML-Trigger erstellt. Dadurch werden Prüfungen ausgelöst, um sicherzustellen, dass die Kreditwürdigkeit für den Lieferanten gut ist, wenn versucht wird, eine neue Bestellung in die PurchaseOrderHeader Tabelle einzufügen. Um die Bonität des Lieferanten zu erhalten, der der soeben eingefügten Bestellung entspricht, muss auf die Vendor Tabelle verwiesen und mit der eingefügten Tabelle verknüpft werden. Wenn die Kreditwürdigkeit zu niedrig ist, wird eine Meldung angezeigt, und die Einfügung wird nicht ausgeführt. Beachten Sie, dass in diesem Beispiel keine Multirow-Datenänderungen zulässig sind. Weitere Informationen finden Sie unter Erstellen von DML-Triggern zum Behandeln mehrerer Datenzeilen.

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 eingefügten und gelöschten Tabellen in ANSTELLE VON Triggern

Die eingefügten und gelöschten Tabellen, die an ANSTELLE VON Triggern übergeben werden, die in Tabellen definiert sind, entsprechen den gleichen Regeln wie die eingefügten und gelöschten Tabellen, die an AFTER-Trigger übergeben werden. Das Format der eingefügten und gelöschten Tabellen entspricht dem Format der Tabelle, in der der INSTEAD OF-Trigger definiert ist. Jede Spalte in den eingefügten und gelöschten Tabellen wird einer Spalte in der Basistabelle direkt zugeordnet.

Die folgenden Regeln zur Festlegung, wann eine INSERT- oder UPDATE-Anweisung, die sich auf eine Tabelle mit einem INSTEAD OF-Trigger bezieht, Werte für Spalten liefern muss, sind die gleichen, wie wenn die Tabelle keinen INSTEAD OF-Trigger hätte:

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

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

• INSERT-Anweisungen müssen Werte für alle NOT NULL-Spalten bereitstellen, die keine DEFAULT-Einschränkungen aufweisen.

• Für alle Spalten mit Ausnahme von berechneten, Identitäts- oder timestamp-Spalten sind Werte in jeder Spalte optional, die NULL-Werte zulässt oder eine NOT NULL-Spalte mit einer DEFAULT-Definition ist.

Wenn eine INSERT-, UPDATE- oder DELETE-Anweisung auf eine Ansicht verweist, die über einen INSTEAD OF-Trigger verfügt, ruft das Datenbankmodul den Trigger auf, anstatt direkte Aktionen für eine beliebige Tabelle auszuführen. Der Trigger muss die in den eingefügten und gelöschten Tabellen dargestellten Informationen verwenden, um alle Anweisungen zu erstellen, die zum Implementieren der angeforderten Aktion in den Basistabellen erforderlich sind, auch wenn das Format der informationen in den eingefügten und gelöschten Tabellen, die für die Ansicht erstellt wurden, vom Format der Daten in den Basistabellen abweicht.

Das Format der in einen INSTEAD OF Trigger übergebenen eingefügten und gelöschten Tabellen, die in einer Ansicht definiert sind, entspricht der Auswahlliste der SELECT-Anweisung, die für die Ansicht definiert ist. Beispiel:

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 Ansicht weist drei Spalten auf: eine int Spalte und zwei nvarchar Spalten. Die eingefügten und gelöschten Tabellen, die an einen INSTEAD OF-Trigger übergeben werden, der in der Ansicht definiert ist, verfügen auch über eine int Spalte namens BusinessEntityID, eine nvarchar Spalte namens LName, und eine nvarchar Spalte namens FName.

Die Auswahlliste einer Ansicht kann auch Ausdrücke enthalten, die einer einzelnen Basistabellenspalte nicht direkt zugeordnet sind. Einige Ansichtsausdrücke, z. B. ein Konstanten- oder Funktionsaufruf, verweisen möglicherweise nicht auf Spalten und können ignoriert werden. Komplexe Ausdrücke können auf mehrere Spalten verweisen, doch die eingefügten und gelöschten Tabellen weisen nur einen Wert für jede eingefügte Zeile auf. Dieselben Probleme gelten für einfache Ausdrücke in einer Ansicht, wenn sie auf eine berechnete Spalte verweisen, die einen komplexen Ausdruck aufweist. Ein INSTEAD OF-Trigger auf einer Ansicht muss diese Arten von Ausdrücken behandeln.