Triggers SQL : création et cas d'usage pratiques expliqués

Les triggers SQL sont des procédures stockées qui s'exécutent automatiquement en réponse à des événements spécifiques sur une table ou une vue. Ces mécanismes puissants permettent d'automatiser des tâches comme l'audit, la validation de données ou la synchronisation entre tables. Contrairement aux fonctions fenêtre SQL qui traitent les données lors de la lecture, les triggers interviennent lors des modifications.

Un trigger se déclenche lors d'opérations INSERT, UPDATE ou DELETE et peut s'exécuter avant (BEFORE) ou après (AFTER) l'événement déclencheur. Cette capacité d'intervention automatique en fait un outil incontournable pour maintenir l'intégrité des données et implémenter une logique métier complexe au niveau de la base de données.

Types de triggers SQL et leurs utilisations

Les triggers SQL se classent principalement selon leur moment d'exécution et l'événement déclencheur. Les triggers BEFORE s'exécutent avant l'opération principale, permettant de valider ou modifier les données entrantes. Les triggers AFTER interviennent après l'opération, parfaits pour l'audit ou la notification.

Chaque type répond à des besoins spécifiques. Les triggers BEFORE sont essentiels pour la validation de données en temps réel, tandis que les triggers AFTER excellent dans la journalisation et la synchronisation. Les triggers INSTEAD OF, disponibles sur certains SGBD, remplacent complètement l'opération originale.

La granularité d'exécution varie également : les triggers FOR EACH ROW s'exécutent pour chaque ligne affectée, tandis que les triggers FOR EACH STATEMENT ne s'exécutent qu'une fois par instruction, même si elle affecte plusieurs lignes.

Syntaxe de création d'un trigger

La syntaxe de base d'un trigger suit une structure standardisée, bien que les détails varient selon le SGBD utilisé. Voici la structure générale :

CREATE TRIGGER nom_trigger
    BEFORE|AFTER INSERT|UPDATE|DELETE
    ON nom_table
    FOR EACH ROW
BEGIN
    -- Logique du trigger
END;

Cette syntaxe s'adapte selon les besoins. Pour PostgreSQL, on utilisera une fonction PL/pgSQL séparée, tandis que MySQL accepte directement le code dans le trigger. La spécification du timing (BEFORE/AFTER) et de l'événement (INSERT/UPDATE/DELETE) détermine le comportement du trigger.

Les variables OLD et NEW permettent d'accéder aux valeurs avant et après modification. OLD contient les anciennes valeurs (disponible pour UPDATE et DELETE), NEW les nouvelles valeurs (disponible pour INSERT et UPDATE).

Trigger BEFORE : validation et préparation

Les triggers BEFORE sont parfaits pour valider les données avant leur insertion en base. Ils permettent de modifier les valeurs entrantes ou d'empêcher l'opération si les critères ne sont pas respectés.

Voici un exemple de validation d'email avant insertion :

CREATE TRIGGER validate_email_before_insert
    BEFORE INSERT ON users
    FOR EACH ROW
BEGIN
    IF NEW.email NOT REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Format email invalide';
    END IF;
    
    -- Normalisation automatique
    SET NEW.email = LOWER(NEW.email);
    SET NEW.created_at = NOW();
END;

Ce trigger vérifie le format de l'email, normalise sa casse et définit automatiquement la date de création. L'opération est annulée si l'email est invalide, garantissant la qualité des données stockées.

Trigger AFTER : audit et journalisation

Les triggers AFTER sont essentiels pour l'audit et la traçabilité. Ils s'exécutent après l'opération réussie, permettant de journaliser les modifications sans risquer d'interférer avec l'opération principale.

Exemple d'audit complet sur une table utilisateurs :

CREATE TRIGGER audit_users_changes
    AFTER UPDATE ON users
    FOR EACH ROW
BEGIN
    INSERT INTO users_audit (
        user_id,
        operation,
        old_email,
        new_email,
        changed_by,
        changed_at
    ) VALUES (
        NEW.id,
        'UPDATE',
        OLD.email,
        NEW.email,
        USER(),
        NOW()
    );
END;

Ce trigger capture automatiquement toutes les modifications sur la table users, créant une piste d'audit complète. L'historique des changements reste intact même si l'enregistrement original est supprimé.

Cas d'usage pratique : calcul automatique

Les triggers excellent pour maintenir des données dérivées à jour. Considérons une table de commandes où le total doit être recalculé automatiquement :

CREATE TRIGGER update_order_total
    BEFORE INSERT ON order_items
    FOR EACH ROW
BEGIN
    DECLARE order_total DECIMAL(10,2);
    
    -- Calcul du nouveau total
    SELECT SUM(quantity * price) INTO order_total
    FROM order_items 
    WHERE order_id = NEW.order_id;
    
    -- Mise à jour du total commande
    UPDATE orders 
    SET total_amount = COALESCE(order_total, 0) + (NEW.quantity * NEW.price)
    WHERE id = NEW.order_id;
END;

Ce trigger maintient automatiquement la cohérence entre les lignes de commande et le total général, éliminant les risques d'incohérence manuelle.

Gestion des erreurs dans les triggers

La gestion d'erreur robuste est cruciale dans les triggers. Une erreur non gérée peut bloquer toute l'application. Voici les bonnes pratiques :

CREATE TRIGGER safe_price_update
    BEFORE UPDATE ON products
    FOR EACH ROW
BEGIN
    DECLARE error_message VARCHAR(255);
    
    -- Validation métier
    IF NEW.price < 0 THEN
        SET error_message = CONCAT('Prix négatif interdit pour produit ', NEW.id);
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = error_message;
    END IF;
    
    -- Log des modifications importantes
    IF ABS(NEW.price - OLD.price) / OLD.price > 0.5 THEN
        INSERT INTO price_alerts (product_id, old_price, new_price, alert_date)
        VALUES (NEW.id, OLD.price, NEW.price, NOW());
    END IF;
END;

Cette approche combine validation stricte et alertes métier, assurant la qualité des données tout en informant les équipes des changements significatifs.

Comparaison des types de triggers