Déclencheurs au niveau des lignes de PostgreSQL

Les déclencheurs dans PostgreSQL sont des procédures qui sont automatiquement exécutées en réponse à certains événements sur un tableau ou une vue particulière. Les déclencheurs de niveau ligne sont spécifiquement exécutés pour chaque ligne affectée par un événement tel que `INSERT`, `UPDATE`, ou `DELETE`.

Utilisation

Les déclencheurs au niveau des lignes sont utilisés pour appliquer les règles de gestion, valider les données ou maintenir une journalisation personnalisée au niveau des lignes. Ils sont définis pour effectuer des actions spécifiques chaque fois qu'une ligne est modifiée d'une manière précise.

sql
CREATE TRIGGER trigger_name
AFTER | BEFORE INSERT | UPDATE | DELETE
ON table_name
FOR EACH ROW
EXECUTE FUNCTION function_name();
Propulsé par

Dans cette syntaxe, `FOR EACH ROW` spécifie que le trigger se déclenchera pour chaque ligne affectée, et `EXECUTE FUNCTION` appelle la fonction qui contient la logique à exécuter.

Exemples

1. Déclencheur d'insertion de base

sql
CREATE OR REPLACE FUNCTION log_new_inserts()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (table_name, operation, timestamp)
  VALUES (TG_TABLE_NAME, 'INSERT', now());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER log_insert
AFTER INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION log_new_inserts();
Propulsé par

Cet exemple crée un trigger qui enregistre chaque nouvelle opération `INSERT` sur la table `employees` dans un tableau `audit_log`.

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

sql
CREATE OR REPLACE 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 salary_check
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION check_salary_update();
Propulsé par

Ici, un déclencheur empêche les réductions de salaire en soulevant une exception si le nouveau salaire est inférieur à l'ancien lors d'une `UPDATE`.

3. Supprimer un déclencheur avec journalisation

sql
CREATE OR REPLACE FUNCTION log_delete()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO delete_log (table_name, deleted_row_id, timestamp)
  VALUES (TG_TABLE_NAME, OLD.id, now());
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER log_delete
AFTER DELETE ON orders
FOR EACH ROW
EXECUTE FUNCTION log_delete();
Propulsé par

Cet exemple enregistre les détails des tableaux supprimés de la table `orders` dans un `delete_log` à des fins d'audit. Il suppose que `OLD.id` correspond à la clé primaire ou à un identifiant unique de la ligne.

Conseils et bonnes pratiques

• Utilisez les déclencheurs à bon escient. L'utilisation excessive des déclencheurs peut conduire à des systèmes complexes et difficiles à déboguer.
• Assurer l'atomicité. La fonction de déclenchement doit être atomique et idempotente pour maintenir l'intégrité des données.
• Tenez compte de l'impact sur les performances. Les déclencheurs ajoutent des frais généraux et sont exécutés de manière synchrone avec l'opération de déclenchement ; veillez à ce qu'ils soient optimisés pour les performances afin d'éviter de ralentir les opérations de la base de données.
• Testez soigneusement les déclencheurs. Effectuez des tests complets pour vous assurer que les déclencheurs se comportent correctement dans tous les cas de figure.
• Documenter la logique de déclenchement. Documentez clairement l'objectif et la logique de chaque déclencheur afin de pouvoir vous y référer ultérieurement et d'assurer la maintenance.

Informations complémentaires

Variables spéciales : Les triggers PostgreSQL utilisent des variables spéciales comme `TG_TABLE_NAME`, `NEW`, et `OLD` pour fournir un contexte. `TG_TABLE_NAME` fait référence au nom de la table à laquelle le trigger est associé, `NEW` contient les données de la nouvelle ligne pour les opérations `INSERT` ou `UPDATE`, et `OLD` contient les données de la ligne existante pour les opérations `UPDATE` ou `DELETE`.