Vytvoření aktivačních událostí

Triggery jsou speciální uložené procedury, které se automaticky spouštějí, když v databázi dojde k určitým událostem. Definujete triggery pro zachování integrity dat, vynucení obchodních pravidel a automatizaci databázových operací bez nutnosti kódu na úrovni aplikace.

Pochopte základy triggerů

Trigger reaguje na změny dat nebo změny schématu v databázi. Při vytváření triggeru zadáte událost, která ji aktivuje, a akce, které provede.

Spouštěče se spouštějí automaticky. Na rozdíl od uložených procedur, které explicitně voláte, se triggery aktivují v reakci na příkazy INSERT, UPDATE, DELETE nebo DDL. Díky tomuto automatickému spuštění jsou výkonná pro vynucování pravidel, která se musí konzistentně vztahovat napříč všemi úpravami dat.

SQL Server podporuje dvě hlavní kategorie triggerů: triggery DML (Data Manipulation Language) a triggery DDL (Data Definition Language). Triggery DML reagují na změny v datech tabulky, zatímco triggery DDL reagují na změny schématu, jako je CREATE, ALTERnebo DROP příkazy.

Vytvoření triggerů DML pro úpravy dat

Triggery DML monitorují změny dat v tabulkách nebo zobrazeních a reagují na ně. Definujete je jako triggery typu AFTER nebo typu INSTEAD OF.

Spouštěče typu AFTER se spouštějí po dokončení spouštěcího příkazu. Databáze nejprve provede úpravu dat a pak spustí kód triggeru. Triggery AFTER slouží k ověření změn, aktualizaci souvisejících tabulek nebo úprav protokolu:

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 triggery nahradí původní příkaz pro úpravu dat. Spouštěcí kód se vykonává namísto operace INSERT, UPDATE nebo DELETE. Pomocí triggerů INSTEAD OF můžete modifikovat zobrazení, která by normálně nepřijímala přímé úpravy, nebo k implementaci složité obchodní logiky.

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;

S triggery DML získáte přístup k vloženým a odstraněným pseudo tabulkám. Tyto dočasné tabulky ukládají kopie ovlivněných řádků. INSERT operace naplní vloženou tabulku, DELETE operace naplní odstraněnou tabulku a UPDATE operace naplní obě tabulky starými hodnotami v odstraněných i nových hodnotách vložených.

Implementace triggerů pro konkrétní události

Určíte, které události změn dat aktivují váš spouštěč. Jeden trigger může reagovat na více událostí kombinováním INSERT, UPDATEa DELETE v definici triggeru.

Pro přesnou kontrolu vytvoříte samostatné aktivační události pro každou operaci. Tento přístup zjednodušuje váš kód a usnadňuje údržbu 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;

Současně můžete kombinovat události, pokud stejná logika platí pro více operací. Můžete například vytvořit jeden trigger auditu, který reaguje na INSERT, UPDATEa DELETE:

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;

Tato UPDATE() funkce vám pomůže určit, které sloupce se změnily. Zkontrolujete konkrétní sloupce, abyste se vyhnuli zbytečnému zpracování, pokud pro obchodní logiku záleží jenom na určitých polích.

Použití osvědčených postupů pro triggery

Triggery ovlivňují výkon databáze, protože se spouštějí s každou opravňující operací. Napíšete efektivní aktivační kód, který minimalizuje dopad na propustnost transakcí.

Udržujte logiku spouštěče zaměřenou a minimalizovanou. Proveďte pouze základní operace v těle triggeru. U složitých nebo časově náročných operací zvažte protokolování podrobností o událostech a jejich asynchronní zpracování prostřednictvím samostatné úlohy:

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;

Vyhněte se rekurzivním operacím, kdy změna triggeru způsobí opětovné aktivaci stejného triggeru. Nastavte možnost RECURSIVE_TRIGGERS databáze vhodně a navrhněte triggery tak, aby bylo zabráněno nekonečným smyčkám.

Správně zpracovávat chyby v rámci aktivačních událostí Chování transakcí závisí na zpracování chyb. Pokud trigger narazí na chybu a nezpracuje ji, SQL Server vrátí zpět aktivační událost i původní příkaz:

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;

Důkladně zdokumentujte triggery. Ostatní vývojáři potřebují pochopit, proč triggery existují a co dělají, protože se provádějí neviditelně během normálních databázových operací.

Teď, když rozumíte tomu, jak vytvářet a implementovat triggery, jste připraveni prozkoumat, jak se integrují s dalšími objekty programovatelnosti a vytvářet komplexní databázová řešení.