PostgreSQL CREATE TRIGGER

Les déclencheurs dans PostgreSQL sont des fonctions de rappel de base de données qui s'exécutent automatiquement ou se déclenchent lorsqu'un événement de base de données spécifique se produit, comme une opération `INSERT`, `UPDATE`, `DELETE`, ou même `TRUNCATE`. Ils sont utilisés pour appliquer les règles commerciales, valider les données et maintenir l'intégrité des données dans la base de données.

Utilisation

Les déclencheurs sont utilisés pour automatiser les tâches qui doivent être exécutées lorsque des événements spécifiques se produisent dans la base de données, réduisant ainsi la nécessité d'une intervention manuelle ou d'une logique côté application. Elles sont définies à l'aide de l'instruction `CREATE TRIGGER`, en spécifiant l'événement et l'action à entreprendre.

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

Dans cette syntaxe, `CREATE TRIGGER` définit un nouveau trigger nommé `trigger_name` qui exécute une fonction spécifiée (`function_name`) soit avant, soit après, soit à la place de l'événement spécifié sur `table_name`. La clause `FOR EACH ROW` indique que le trigger s'exécute une fois pour chaque ligne affectée par l'événement, tandis que `FOR EACH STATEMENT` s'exécute une fois par déclaration déclenchante, quel que soit le nombre de lignes affectées.

Exemples

1. Déclencheur d'insertion de base

sql
CREATE FUNCTION log_insert() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (table_name, operation) VALUES ('employees', 'INSERT');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER log_employee_inserts
AFTER INSERT ON employees
FOR EACH ROW EXECUTE PROCEDURE log_insert();

Cet exemple crée un déclencheur qui enregistre chaque opération d'insertion sur la table `employees` dans un tableau `audit_log`.

2. Mise à jour du déclencheur avec la condition

sql
CREATE FUNCTION check_salary_update() 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 prevent_salary_decrease
BEFORE UPDATE ON employees
FOR EACH ROW EXECUTE PROCEDURE check_salary_update();

Ce déclencheur empêche toute diminution du salaire des employés, garantissant que les mises à jour salariales ne peuvent qu'augmenter ou rester identiques. Ici, `NEW` et `OLD` représentent respectivement le nouvel et l'ancien état de la ligne en cours d'exploitation.

3. Déclenchement de la suppression en cascade

sql
CREATE FUNCTION delete_related_records() RETURNS TRIGGER AS $$
BEGIN
  DELETE FROM orders WHERE customer_id = OLD.customer_id;
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER cascade_delete_orders
AFTER DELETE ON customers
FOR EACH ROW EXECUTE PROCEDURE delete_related_records();

Cet exemple met en place un déclencheur qui supprime automatiquement toutes les commandes associées à un client lorsque ce dernier est supprimé.

4. Exemple de déclencheur à la place d'un déclencheur

Les déclencheurs INSTEAD OF sont généralement utilisés avec les vues pour effectuer des actions au lieu de l'opération par défaut.

sql
CREATE FUNCTION log_view_update() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (table_name, operation) VALUES ('view_name', 'UPDATE');
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER log_view_updates
INSTEAD OF UPDATE ON my_view
FOR EACH ROW EXECUTE PROCEDURE log_view_update();

Ce déclencheur enregistre les tentatives de mise à jour d'une vue au lieu d'effectuer la mise à jour.

Conseils et bonnes pratiques

• Définissez clairement les conditions de déclenchement. Veillez à ce que les conditions dans lesquelles les déclencheurs se déclenchent soient bien définies afin d'éviter tout comportement inattendu.
• Utilisez les déclencheurs avec parcimonie. L'utilisation excessive de déclencheurs peut entraîner des interdépendances complexes et rendre le débogage difficile.
• Documentez soigneusement les déclencheurs. Fournissez une documentation claire sur le rôle de chaque déclencheur et la raison pour laquelle il est utilisé, afin de pouvoir vous y référer ultérieurement.
• Effectuez un test approfondi. Testez toujours les déclencheurs dans un environnement de développement pour vous assurer qu'ils fonctionnent comme prévu sans dégradation des performances.
• Tenez compte des implications en termes de performances. Les déclencheurs complexes peuvent avoir un impact sur les performances ; surveillez-les et optimisez-les si nécessaire.
• Empêchez l'invocation récursive. Utilisez des commandes comme `ALTER TABLE ... ENABLE TRIGGER ...` pour contrôler l'activation du déclencheur et éviter les boucles d'invocation récursives potentielles.