Créer des déclencheurs

Les déclencheurs sont des procédures stockées spéciales qui s’exécutent automatiquement lorsque des événements spécifiques se produisent dans votre base de données. Vous définissez des déclencheurs pour maintenir l’intégrité des données, appliquer des règles métier et automatiser les opérations de base de données sans nécessiter de code au niveau de l’application.

Comprendre les principes fondamentaux des déclencheurs

Un déclencheur répond aux modifications de données ou aux modifications de schéma dans votre base de données. Lorsque vous créez un déclencheur, vous spécifiez l’événement qui l’active et les actions qu’il effectue.

Les déclencheurs s’exécutent automatiquement. Contrairement aux procédures stockées que vous appelez explicitement, les déclencheurs s'activent en réponse à INSERT, UPDATE, DELETE ou des instructions DDL. Cette exécution automatique les rend puissants pour appliquer des règles qui doivent s’appliquer de manière cohérente dans toutes les modifications de données.

SQL Server prend en charge deux catégories principales de déclencheurs : les déclencheurs DML (langage de manipulation de données) et les déclencheurs DDL (langage de définition de données). Les déclencheurs DML répondent aux modifications apportées aux données de table, tandis que les déclencheurs DDL répondent aux modifications de schéma telles que CREATE, ALTERou DROP instructions.

Créer des déclencheurs DML pour les modifications de données

Les déclencheurs DML surveillent et répondent aux modifications de données dans les tables ou les vues. Vous les définissez comme déclencheurs de type AFTER ou de type INSTEAD OF.

Les déclencheurs AFTER s’exécutent une fois l’instruction de déclenchement terminée. La base de données effectue d’abord la modification des données, puis exécute le code du déclencheur. Vous utilisez des déclencheurs AFTER pour valider les modifications, mettre à jour les tables associées ou les modifications de journal :

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;

Les déclencheurs INSTEAD OF remplacent l’instruction de modification de données d’origine. Le code de déclencheur s’exécute à la place de l’opération INSERT, UPDATEou DELETE . Vous utilisez des déclencheurs INSTEAD OF pour modifier les vues qui n’acceptent normalement pas de modifications directes ou pour implémenter une logique métier complexe :

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;

Avec les déclencheurs DML, vous accédez aux pseudo-tables insérées et supprimées . Ces tables temporaires stockent des copies des lignes affectées. INSERT les opérations remplissent la table insérée , DELETE les opérations remplissent la table supprimée et UPDATE les opérations remplissent les deux tables avec d’anciennes valeurs dans les valeurs supprimées et les nouvelles valeurs insérées.

Implémenter des déclencheurs pour des événements spécifiques

Vous spécifiez les événements de modification de données qui activent votre déclencheur. Un déclencheur unique peut répondre à plusieurs événements en combinant INSERT, UPDATEet DELETE dans la définition du déclencheur.

Pour un contrôle précis, vous créez des déclencheurs distincts pour chaque opération. Cette approche simplifie votre code et facilite la maintenance de vos déclencheurs :

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;

En même temps, vous pouvez combiner des événements lorsque la même logique s’applique à plusieurs opérations. Par exemple, vous pouvez créer un déclencheur d’audit unique qui répond à INSERT, UPDATEet 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;

La UPDATE() fonction vous aide à déterminer les colonnes modifiées. Vous vérifiez des colonnes spécifiques pour éviter le traitement inutile lorsque seuls certains champs importent pour votre logique métier.

Appliquer les meilleures pratiques des déclenchements

Les déclencheurs affectent les performances de la base de données, car ils s’exécutent avec chaque opération éligible. Vous écrivez du code de déclencheur efficace pour réduire l’impact sur le débit des transactions.

Gardez votre logique de déclencheur ciblée et minimale. Exécutez uniquement les opérations essentielles dans le corps du déclencheur. Pour les opérations complexes ou fastidieuses, envisagez de journaliser les détails de l’événement et de les traiter de manière asynchrone via un travail distinct :

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;

Évitez les opérations récursives où une modification de déclencheur entraîne le déclenchement du même déclencheur. Définissez l’option RECURSIVE_TRIGGERS de base de données de manière appropriée et concevez vos déclencheurs pour empêcher les boucles infinies.

Gérez correctement les erreurs dans les déclencheurs. Le comportement des transactions dépend de la gestion des erreurs. Si un déclencheur rencontre une erreur et que vous ne la gérez pas, SQL Server restaure le déclencheur et l’instruction d’origine :

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;

Documentez soigneusement vos déclencheurs. D’autres développeurs doivent comprendre pourquoi les déclencheurs existent et ce qu’ils font, car ils s’exécutent de manière invisible pendant les opérations normales de base de données.

Maintenant que vous comprenez comment créer et implémenter des déclencheurs, vous êtes prêt à explorer comment ils s’intègrent à d’autres objets de programmabilité pour créer des solutions de base de données complètes.