PostgreSQL Introduction aux déclencheurs

Les déclencheurs dans PostgreSQL sont des procédures spéciales qui sont automatiquement exécutées ou déclenchées lorsque certains événements se produisent dans un tableau de la base de données. Ils sont utilisés pour maintenir l'intégrité des données, appliquer des règles commerciales et automatiser des contrôles ou des transformations complexes.

Utilisation

Les déclencheurs sont utilisés pour répondre automatiquement à des événements tels que les opérations `INSERT`, `UPDATE`, ou `DELETE` sur un tableau. Elles peuvent être définies pour s'exécuter "AVANT", "APRES" ou "EN DEHORS" de l'événement et peuvent optionnellement être conditionnées à la réussite de l'événement. Les déclencheurs peuvent être définis au niveau de la ligne, s'exécutant pour chaque ligne affectée, ou au niveau de l'instruction, s'exécutant une fois par instruction SQL.

CREATE TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF }
{ INSERT | UPDATE | DELETE }
ON table_name
FOR EACH { ROW | STATEMENT }
EXECUTE FUNCTION function_name();

Dans cette syntaxe, `CREATE TRIGGER` définit un nouveau trigger nommé `nom_trigger` qui appelle une fonction spécifiée `nom_fonction` avant, après ou à la place d'un événement modifiant les données sur `nom_table`.

Les déclencheurs interagissent avec les commandes de contrôle des transactions comme `COMMIT` et `ROLLBACK`. Si un déclencheur provoque une erreur, l'ensemble de la transaction est généralement annulée, sauf si elle est explicitement gérée.

Exemples

1. Déclencheur de base pour l'audit

CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    operation VARCHAR(10),
    operation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE OR REPLACE FUNCTION log_insert() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log(operation) VALUES ('INSERT');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER insert_audit
AFTER INSERT ON target_table
FOR EACH ROW
EXECUTE FUNCTION log_insert();

Cet exemple basique enregistre chaque opération `INSERT` sur `target_table` dans le tableau `audit_log`.

2. Déclenchement avec logique conditionnelle

CREATE OR REPLACE FUNCTION check_salary() RETURNS TRIGGER AS $$
BEGIN
    IF NEW.salary < OLD.salary THEN
        RAISE EXCEPTION 'Salary cannot be decreased!';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER salary_check
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION check_salary();

Ce déclencheur garantit que le salaire d'un employé ne peut pas être diminué au cours d'une opération de mise à jour.

3. Déclencheur en cascade

CREATE OR REPLACE FUNCTION delete_cascade() RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM orders WHERE customer_id = OLD.id;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER cascade_delete
AFTER DELETE ON customers
FOR EACH ROW
EXECUTE FUNCTION delete_cascade();

Cet exemple supprime automatiquement toutes les commandes associées à un client lorsque celui-ci est supprimé.

Gestion des déclencheurs

Pour désactiver ou supprimer un déclencheur lorsqu'il n'est plus nécessaire, vous pouvez utiliser les commandes suivantes :

ALTER TABLE table_name DISABLE TRIGGER trigger_name;
DROP TRIGGER trigger_name ON table_name;

Pour visualiser les déclencheurs existants dans une base de données, vous pouvez interroger le catalogue système `pg_trigger`.

Conseils et bonnes pratiques

• Veillez à ce que les déclencheurs soient simples. Une logique complexe peut entraîner des goulets d'étranglement au niveau des performances et des problèmes de maintenance.
• Effectuez un test approfondi. Assurez-vous que vos déclencheurs fonctionnent comme prévu en les testant dans différents scénarios.
• Déclencheurs de documents. Documentez clairement l'objectif et le fonctionnement de chaque déclencheur afin d'aider les futurs développeurs.
• Considérez les autorisations. Assurez-vous que les utilisateurs qui déclenchent les événements disposent des autorisations nécessaires pour exécuter les fonctions associées.
• Contrôler l'impact sur les performances. Sachez que l'utilisation intensive de déclencheurs peut nuire aux performances, en particulier pour les tableaux à fort volume de transactions.
• Évitez les déclencheurs récursifs. Les déclencheurs récursifs peuvent entraîner des boucles infinies et doivent être manipulés avec prudence.