Déclencheurs au niveau des déclarations PostgreSQL

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. Les déclencheurs au niveau des instructions sont exécutés une fois par instruction SQL, quel que soit le nombre de lignes affectées par l'instruction.

Utilisation
Les déclencheurs au niveau des instructions sont utilisés pour appliquer les règles de gestion, valider les entrées ou maintenir les journaux d'audit lorsqu'une instruction affecte un nombre quelconque de lignes. Ils sont définis pour agir avant ou après les opérations `INSERT`, `UPDATE`, ou `DELETE` sur un tableau.

sql
CREATE TRIGGER trigger_name
{ BEFORE | AFTER } { INSERT | UPDATE | DELETE }
ON table_name
FOR EACH STATEMENT
EXECUTE PROCEDURE function_name();
Propulsé par

Dans cette syntaxe, `FOR EACH STATEMENT` spécifie que le trigger doit se déclencher une fois par statement, et `EXECUTE PROCEDURE` définit la fonction à appeler. Notez que les déclencheurs de niveau déclaration n'ont pas accès aux valeurs de ligne `OLD` et `NEW`, car ils ne sont pas spécifiques à une ligne.

Exemples

1. Déclencheur de base au niveau de la déclaration

sql
-- This function logs an entry into audit_log whenever an INSERT, UPDATE, or DELETE operation is performed on the employees table.
CREATE FUNCTION log_total_changes() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log(event_type, event_time)
  VALUES (TG_OP, now());
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_changes
AFTER INSERT OR UPDATE OR DELETE
ON employees
FOR EACH STATEMENT
EXECUTE PROCEDURE log_total_changes();
Propulsé par

2. Déclenchement des opérations d'insertion

sql
-- This function logs every INSERT operation on the employees table into the insert_log table with the current timestamp.
CREATE FUNCTION log_inserts() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO insert_log(table_name, insert_time)
  VALUES (TG_TABLE_NAME, now());
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER log_employee_inserts
AFTER INSERT
ON employees
FOR EACH STATEMENT
EXECUTE PROCEDURE log_inserts();
Propulsé par

3. Exécution d'un déclencheur conditionnel

sql
-- This function conditionally calls a notification function only when a DELETE operation is performed on the orders table.
CREATE FUNCTION notify_admin() RETURNS TRIGGER AS $$
BEGIN
  IF (TG_OP = 'DELETE') THEN
    PERFORM notify_admin_function();
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER admin_notification
AFTER DELETE
ON orders
FOR EACH STATEMENT
EXECUTE PROCEDURE notify_admin();
Propulsé par

Dans ces exemples, `TG_OP` fait référence au type d'opération (`INSERT`, `UPDATE`, ou `DELETE`), et `TG_TABLE_NAME` fait référence au nom du tableau sur lequel le trigger est défini.

Conseils et bonnes pratiques

• Limitez la complexité des déclencheurs. Veillez à ce que la logique de déclenchement soit simple afin d'éviter les effets secondaires involontaires et de maintenir les performances.
• Utilisez les déclencheurs avec parcimonie. Une utilisation excessive peut entraîner des problèmes de maintenance et rendre le débogage difficile.
• Documentez clairement les éléments déclencheurs. Documentez toujours l'objectif et la fonction des déclencheurs dans votre base de données.
• Effectuez un test approfondi. Veillez à ce que les déclencheurs soient testés de manière approfondie afin de valider leur comportement dans différents scénarios.
• Tenez compte de l'impact sur les performances. Sachez que les déclencheurs peuvent affecter les performances, en particulier dans les environnements où les transactions sont nombreuses.
• Envisagez d'utiliser des déclencheurs au niveau de la ligne pour la logique spécifique à la ligne. Les déclencheurs au niveau de la déclaration ne conviennent pas aux opérations spécifiques aux lignes ; utilisez donc des déclencheurs au niveau des lignes si nécessaire.
• Comprendre les différences de déclenchement. Les déclencheurs au niveau de la déclaration s'exécutent une fois par déclaration sans accès spécifique à la ligne, tandis que les déclencheurs au niveau de la ligne s'exécutent pour chaque ligne affectée et peuvent accéder aux valeurs des lignes `OLD` et `NEW`.