Erstellen von Triggern

Abgeschlossen

Trigger sind spezielle gespeicherte Prozeduren, die automatisch ausgeführt werden, wenn bestimmte Ereignisse in Ihrer Datenbank auftreten. Sie definieren Trigger zum Verwalten der Datenintegrität, zum Erzwingen von Geschäftsregeln und zum Automatisieren von Datenbankvorgängen, ohne dass Code auf Anwendungsebene erforderlich ist.

Trigger-Grundlagen verstehen

Ein Trigger reagiert auf Datenänderungs- oder Schemaänderungen in Ihrer Datenbank. Wenn Sie einen Trigger erstellen, geben Sie das Ereignis an, das es aktiviert, und die ausgeführten Aktionen.

Trigger werden automatisch ausgeführt. Im Gegensatz zu gespeicherten Prozeduren, die Sie explizit aufrufen, werden Trigger als Reaktion auf INSERT, UPDATEDELETE oder DDL-Anweisungen ausgelöst. Diese automatische Ausführung macht sie leistungsfähig, um Regeln zu erzwingen, die für alle Datenänderungen konsistent gelten müssen.

SQL Server unterstützt zwei Hauptkategorien von Triggern: DML -Trigger (Data Manipulation Language) und DDL (Data Definition Language) Trigger. DML-Trigger reagieren auf Änderungen in Tabellendaten, während DDL-Trigger auf Schemaänderungen wie CREATE, ALTERoder DROP Anweisungen reagieren.

Erstellen von DML-Triggern für Datenänderungen

DML-Trigger überwachen und reagieren auf Datenänderungen in Tabellen oder Ansichten. Sie definieren sie entweder als AFTER-Trigger oder als INSTEAD OF-Trigger.

AFTER-Trigger werden ausgeführt, nachdem die auslösende Anweisung abgeschlossen wurde. Die Datenbank führt zuerst die Datenänderung aus und führt dann den Triggercode aus. Sie verwenden AFTER-Trigger, um Änderungen zu überprüfen, verwandte Tabellen zu aktualisieren oder Protokolländerungen zu protokollieren:

CREATE TRIGGER tr_UpdateInventory
ON Sales.OrderDetails
AFTER INSERT
AS
BEGIN
    UPDATE Inventory.Products
    SET QuantityInStock = QuantityInStock - i.Quantity
    FROM Inventory.Products p
    INNER JOIN inserted i ON p.ProductID = i.ProductID;
END;

INSTEAD OF-Trigger ersetzen die ursprüngliche Datenmodifikationsanweisung. Der Triggercode wird anstelle der INSERT, UPDATE oder DELETE-Operation ausgeführt. Anstelle von Triggern können Sie Ansichten ändern, die normalerweise keine direkten Änderungen akzeptieren oder komplexe Geschäftslogik implementieren:

CREATE TRIGGER tr_UpdateOrderView
ON Sales.OrderSummaryView
INSTEAD OF UPDATE
AS
BEGIN
    UPDATE Sales.Orders
    SET OrderStatus = i.OrderStatus,
        ModifiedDate = GETDATE()
    FROM Sales.Orders o
    INNER JOIN inserted i ON o.OrderID = i.OrderID;
END;

Mit DML-Triggern greifen Sie auf die eingefügten und gelöschten Pseudotabellen zu. Diese temporären Tabellen speichern Kopien der betroffenen Zeilen. INSERTVorgänge füllen die eingefügte Tabelle, DELETE Vorgänge füllen die gelöschte Tabelle auf, und UPDATE Vorgänge füllen beide Tabellen mit alten Werten in gelöschten und neuen Werten eingefügt.

Implementieren von Triggern für bestimmte Ereignisse

Sie geben an, welche Datenänderungsereignisse Ihren Trigger aktivieren. Ein einzelner Trigger kann auf mehrere Ereignisse reagieren, indem er INSERT, UPDATE und DELETE in der Triggerdefinition kombiniert.

Für eine präzise Steuerung erstellen Sie separate Trigger für jeden Vorgang. Dieser Ansatz vereinfacht Ihren Code und erleichtert die Verwaltung ihrer Trigger:

CREATE TRIGGER tr_LogPriceChanges
ON Products.Catalog
AFTER UPDATE
AS
BEGIN
    IF UPDATE(Price)
    BEGIN
        INSERT INTO Audit.PriceHistory (ProductID, OldPrice, NewPrice, ChangeDate)
        SELECT d.ProductID, d.Price, i.Price, GETDATE()
        FROM deleted d
        INNER JOIN inserted i ON d.ProductID = i.ProductID
        WHERE d.Price <> i.Price;
    END;
END;

Gleichzeitig können Sie Ereignisse kombinieren, wenn dieselbe Logik für mehrere Vorgänge gilt. Zum Beispiel könnten Sie einen einzelnen Überwachungstrigger erstellen, der auf INSERT, UPDATE und DELETE reagiert.

CREATE TRIGGER tr_AuditEmployeeChanges
ON HR.Employees
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @Operation NVARCHAR(10);
    
    IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
        SET @Operation = 'INSERT';
    ELSE IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
        SET @Operation = 'UPDATE';
    ELSE
        SET @Operation = 'DELETE';
    
    INSERT INTO Audit.EmployeeLog (EmployeeID, Operation, ChangeDate)
    SELECT COALESCE(i.EmployeeID, d.EmployeeID), @Operation, GETDATE()
    FROM inserted i
    FULL OUTER JOIN deleted d ON i.EmployeeID = d.EmployeeID;
END;

Mithilfe der UPDATE() Funktion können Sie ermitteln, welche Spalten geändert wurden. Sie überprüfen bestimmte Spalten, um unnötige Verarbeitung zu vermeiden, wenn nur bestimmte Felder für Ihre Geschäftslogik wichtig sind.

Anwenden bewährter Methoden für Trigger

Trigger wirken sich auf die Datenbankleistung aus, da sie mit jedem qualifizierenden Vorgang ausgeführt werden. Sie schreiben effizienten Triggercode, um die Auswirkungen auf den Transaktionsdurchsatz zu minimieren.

Halten Sie die Triggerlogik fokussiert und minimal. Führen Sie nur wesentliche Vorgänge innerhalb des Triggerkörpers aus. Bei komplexen oder zeitaufwendigen Vorgängen sollten Sie die Ereignisdetails protokollieren und asynchron über einen separaten Auftrag verarbeiten:

CREATE TRIGGER tr_QueueLargeOrders
ON Sales.Orders
AFTER INSERT
AS
BEGIN
    INSERT INTO Processing.OrderQueue (OrderID, TotalAmount, QueuedDate)
    SELECT OrderID, TotalAmount, GETDATE()
    FROM inserted
    WHERE TotalAmount > 10000;
END;

Vermeiden Sie rekursive Vorgänge, bei denen eine Triggeränderung dazu führt, dass derselbe Trigger erneut ausgelöst wird. Legen Sie die RECURSIVE_TRIGGERS Datenbankoption entsprechend fest, und entwerfen Sie Ihre Trigger, um endlose Schleifen zu verhindern.

Behandeln Sie Fehler richtig innerhalb von Triggern. Das Transaktionsverhalten hängt von der Fehlerbehandlung ab. Wenn ein Trigger auf einen Fehler stößt und Sie ihn nicht behandeln, setzt SQL Server sowohl den Trigger als auch die ursprüngliche Anweisung zurück:

CREATE TRIGGER tr_ValidateOrderDate
ON Sales.Orders
AFTER INSERT, UPDATE
AS
BEGIN
    IF EXISTS (SELECT * FROM inserted WHERE OrderDate > GETDATE())
    BEGIN
        THROW 50001, 'Order date cannot be in the future', 1;
    END;
END;

Dokumentieren Sie Ihre Trigger gründlich. Andere Entwickler müssen verstehen, warum Trigger vorhanden sind und was sie tun, da sie während normalen Datenbankvorgängen unsichtbar ausgeführt werden.

Nachdem Sie nun wissen, wie Sie Trigger erstellen und implementieren können, können Sie untersuchen, wie sie in andere Programmierbarkeitsobjekte integriert werden, um umfassende Datenbanklösungen zu erstellen.