Entwerfen von INSTEAD OF-Triggern

Der hauptsächliche Vorteil von INSTEAD OF-Triggern ist, dass sie das Aktualisieren von Sichten ermöglichen, die normalerweise nicht aktualisiert werden könnten. Eine Sicht, die sich aus mehreren Basistabellen zusammensetzt, muss einen INSTEAD OF-Trigger verwenden, um INSERT-, UPDATE- und DELETE-Vorgänge zu unterstützen, die auf Daten in mehreren Tabellen verweisen. Ein weiterer Vorteil von INSTEAD OF-Triggern ist die Möglichkeit, Logik zu codieren, die Teile eines Batches zurückweist, während andere Teile zugelassen werden.

Ein INSTEAD OF-Trigger ermöglicht Folgendes:

• Ignorieren von Teilen eines Batches.
• Nichtverarbeitung eines Teiles eines Batches und Protokollieren der Problemzeilen.
• Ausführen einer alternativen Aktion, falls eine Fehlerbedingung festgestellt wurde.

  Hinweis:
  INSTEAD OF DELETE- und INSTEAD OF UPDATE-Trigger können nicht für Tabellen mit Fremdschlüsseln definiert werden, deren Fremdschlüssel mithilfe von kaskadierenden DELETE- oder UPDATE-Aktionen definiert wurden.

Die Codierung dieser Logik mit einem INSTEAD OF-Trigger verhindert, dass für alle Anwendungen, die auf die Daten zugreifen, diese Logik neu implementiert werden muss.

Beispiel

In der folgenden Transact-SQL-Anweisungssequenz aktualisiert ein INSTEAD OF-Trigger zwei Basistabellen einer Sicht. Außerdem werden zwei Ansätze zur Fehlerbehandlung aufgezeigt:

• Doppelte Einfügungen in der Person-Tabelle werden ignoriert, und die Informationen aus der Einfügung werden in der PersonDuplicates-Tabelle protokolliert.
• Einfügungen doppelter Daten in der EmployeeTable-Tabelle werden in eine UPDATE-Anweisung umgewandelt, die die aktuellen Informationen in die EmployeeTable-Tabelle einträgt, ohne eine Verletzung aufgrund doppelter Schlüssel zu generieren.

Die Transact-SQL-Anweisungen erstellen zwei Basistabellen, eine Sicht, eine Tabelle zum Aufzeichnen von Fehlern und den INSTEAD OF-Trigger für die Sicht. Die folgenden Tabellen trennen persönliche und geschäftliche Daten und bilden die Basistabellen für die Sicht.

CREATE TABLE Person
   (
    SSN         char(11) PRIMARY KEY,
    Name        nvarchar(100),
    Address     nvarchar(100),
    Birthdate   datetime
   )

CREATE TABLE EmployeeTable
   (
    EmployeeID       int PRIMARY KEY,
    SSN              char(11) UNIQUE,
    Department       nvarchar(10),
    Salary           money,
    CONSTRAINT FKEmpPer FOREIGN KEY (SSN)
    REFERENCES Person (SSN)
   )

Die folgende Sicht zeichnet alle relevanten Daten aus den beiden Tabellen für eine Person auf.

CREATE VIEW Employee AS
SELECT P.SSN as SSN, Name, Address,
       Birthdate, EmployeeID, Department, Salary
FROM Person P, EmployeeTable E
WHERE P.SSN = E.SSN

Sie können Versuche, Zeilen einzufügen, die doppelte Sozialversicherungsnummern enthalten, aufzeichnen. Die PersonDuplicates-Tabelle protokolliert die eingefügten Werte, den Namen des Benutzers, der die Einfügung ausführen wollte, sowie den Zeitpunkt der Einfügung.

CREATE TABLE PersonDuplicates
   (
    SSN           char(11),
    Name          nvarchar(100),
    Address       nvarchar(100),
    Birthdate     datetime,
    InsertSNAME   nchar(100),
    WhenInserted  datetime
   )

Der INSTEAD OF-Trigger fügt Zeilen aus einer einzigen Sicht in mehrere Basistabellen ein. Versuche, Zeilen mit doppelten Sozialversicherungsnummern einzufügen, werden in der PersonDuplicates-Tabelle aufgezeichnet. Doppelte Zeilen in der EmployeeTable-Tabelle werden in Aktualisierungsanweisungen geändert.

CREATE TRIGGER IO_Trig_INS_Employee ON Employee
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
-- Check for duplicate Person. If there is no duplicate, do an insert.
IF (NOT EXISTS (SELECT P.SSN
      FROM Person P, inserted I
      WHERE P.SSN = I.SSN))
   INSERT INTO Person
      SELECT SSN,Name,Address,Birthdate
      FROM inserted
ELSE
-- Log an attempt to insert duplicate Person row in PersonDuplicates table.
   INSERT INTO PersonDuplicates
      SELECT SSN,Name,Address,Birthdate,SUSER_SNAME(),GETDATE()
      FROM inserted
-- Check for duplicate Employee. If no there is duplicate, do an INSERT.
IF (NOT EXISTS (SELECT E.SSN
      FROM EmployeeTable E, inserted
      WHERE E.SSN = inserted.SSN))
   INSERT INTO EmployeeTable
      SELECT EmployeeID,SSN, Department, Salary
      FROM inserted
ELSE
--If there is a duplicate, change to UPDATE so that there will not
--be a duplicate key violation error.
   UPDATE EmployeeTable
      SET EmployeeID = I.EmployeeID,
          Department = I.Department,
          Salary = I.Salary
   FROM EmployeeTable E, inserted I
   WHERE E.SSN = I.SSN
END

Siehe auch

Konzepte

Entwerfen von DML-Triggern

Hilfe und Informationen

Informationsquellen für SQL Server 2005