Automatisation des tâches avec les triggers

Imagine que t’es admin d’une grosse base de données. D’un coup, quelqu’un supprime une info importante d’une table, et tout le monde gueule : "C’est qui qui a fait ça ?!". Pour éviter ce genre de galère, les bases de données permettent d’enregistrer les changements et de suivre ce qui s’est passé avec les données. Tout ça, c’est grâce à la journalisation (logging) et à l’audit.

• Journalisation des changements te permet de garder l’historique de ce qui s’est passé : quel enregistrement a changé, comment et quand.
• Audit des données sert à vérifier plus en profondeur, en enregistrant non seulement les changements, mais aussi des infos sur l’utilisateur qui les a lancés.

Maintenant que tu piges le "pourquoi", on va voir le "comment".

Création d’une table pour les logs

Avant de configurer les triggers, il nous faut une table pour stocker les logs des changements. Voilà un exemple :

-- On crée une table pour la journalisation des changements
CREATE TABLE change_logs (
    log_id SERIAL PRIMARY KEY,       -- Identifiant unique de l’enregistrement
    table_name TEXT NOT NULL,        -- Nom de la table où le changement a eu lieu
    operation TEXT NOT NULL,         -- Type d’opération : INSERT, UPDATE, DELETE
    change_time TIMESTAMP DEFAULT NOW(), -- Date et heure du changement
    old_data JSONB,                  -- Données avant le changement (pour UPDATE/DELETE)
    new_data JSONB                   -- Données après le changement (pour INSERT/UPDATE)
);

Qu’est-ce qui se passe ici ?

1. log_id — identifiant unique pour chaque log.
2. table_name — on va enregistrer quelle table a été modifiée.
3. operation — type d’opération : INSERT, UPDATE ou DELETE.
4. change_time — enregistre l’heure exacte du changement.
5. old_data et new_data — données avant et après le changement, au format JSON.

Journalisation des changements avec un trigger

Maintenant qu’on a une table pour les logs, on va créer un trigger pour une des tables, par exemple students. Il va enregistrer tous les changements : ajout de nouveaux étudiants, mise à jour ou suppression. Voilà le plan :

1. On écrit une fonction en PL/pgSQL qui va ajouter des logs dans la table.
2. On crée un trigger sur la table students.

La fonction va recevoir des infos sur l’opération (INSERT, UPDATE, DELETE), ainsi que les données qui ont changé (OLD et NEW).

-- Fonction pour enregistrer les changements dans la table de logs
CREATE OR REPLACE FUNCTION log_student_changes()
RETURNS TRIGGER AS $$
BEGIN
    -- Journalisation de l’opération INSERT
    IF TG_OP = 'INSERT' THEN
        INSERT INTO change_logs (table_name, operation, new_data)
        VALUES ('students', 'INSERT', row_to_json(NEW));

    -- Journalisation de l’opération DELETE
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO change_logs (table_name, operation, old_data)
        VALUES ('students', 'DELETE', row_to_json(OLD));

    -- Journalisation de l’opération UPDATE
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO change_logs (table_name, operation, old_data, new_data)
        VALUES ('students', 'UPDATE', row_to_json(OLD), row_to_json(NEW));
    END IF;

    RETURN NULL; -- On retourne NULL car c’est un trigger AFTER
END;
$$ LANGUAGE plpgsql;

Ici :

• TG_OP — variable spéciale qui contient l’opération en cours : INSERT, UPDATE, DELETE.
• row_to_json(OLD) et row_to_json(NEW) — transforment la ligne en JSON pour un stockage facile.
• RETURN NULL — comme c’est un trigger AFTER, il ne doit pas renvoyer de données modifiées.

Maintenant, on relie notre fonction à la table students.

-- On crée un trigger pour la journalisation des changements
CREATE TRIGGER students_log_trigger
AFTER INSERT OR UPDATE OR DELETE ON students
FOR EACH ROW
EXECUTE FUNCTION log_student_changes();

Qu’est-ce qui se passe ici ?

• AFTER INSERT OR UPDATE OR DELETE — le trigger se lance après les opérations indiquées sur la table students.
• FOR EACH ROW — le trigger s’exécute pour chaque ligne modifiée.
• EXECUTE FUNCTION log_student_changes() — appelle notre fonction de logging.

On teste le trigger

C’est le moment de vérifier si notre trigger fonctionne.

1. Insertion d’un nouvel enregistrement

INSERT INTO students (name, age, grade)
VALUES ('Otto Lin', 20, 'A');

Regardons ce qui a été enregistré dans la table de logs :

SELECT * FROM change_logs;

Exemple de résultat :

2. Mise à jour d’un enregistrement

UPDATE students
SET grade = 'B'
WHERE name = 'Otto Lin';

On vérifie encore la table de logs :

SELECT * FROM change_logs ORDER BY change_time DESC;

Résultat :

3. Suppression d’un enregistrement

DELETE FROM students
WHERE name = 'Otto Lin';

Et encore une fois, on check le log :

Exemples d’utilisation réelle

1. Journalisation des opérations sur les tables critiques : par exemple, une table de comptes bancaires doit enregistrer tous les changements pour éviter la fraude.
2. Audit du système : tu peux garder des logs pour la conformité réglementaire ou pour analyser l’activité des utilisateurs.
3. Assurer la restauration des données : si quelqu’un supprime des données par erreur, tu pourras les récupérer grâce à la table de logs.

Points particuliers et pièges à éviter

Quand tu mets en place la journalisation avec des triggers, faut faire gaffe à la perf. Si le trigger se déclenche trop souvent, ça peut charger la base. Donc :

• Utilise le logging seulement sur les tables vraiment critiques.
• Si le volume de logs devient trop gros, pense à des stratégies d’archivage.

Les triggers, c’est comme une corde de guitare bien tendue : faut bien les régler, mais après, ça sonne nickel, ça automatise les tâches chiantes et ça te donne un vrai contrôle sur tes données.