Menu

Triggers PostgreSQL : CREATE TRIGGER avec exemples

Un trigger exécute une fonction automatiquement quand des lignes sont insérées, mises à jour ou supprimées. Dans PostgreSQL, vous écrivez une fonction trigger qui renvoie trigger, puis vous l'attachez à une table avec CREATE TRIGGER.

Cette page contient des éditeurs exécutables. Modifiez, exécutez et voyez la sortie instantanément.

Un trigger fait appeler une fonction par PostgreSQL automatiquement quand une table change. Écrivez d'abord la fonction trigger, déclarée RETURNS trigger, puis attachez-la : CREATE TRIGGER name BEFORE UPDATE ON table FOR EACH ROW EXECUTE FUNCTION fn();. L'usage classique consiste à tenir à jour une colonne updated_at :

Seul le post 1 a été mis à jour, donc lui seul a un horodatage. NEW est la ligne sur le point d'être écrite ; un trigger BEFORE peut la modifier et doit la renvoyer. Vous pouvez attacher la même fonction à toutes les tables qui ont une colonne updated_at.

BEFORE, AFTER ou INSTEAD OF

MomentS'exécuteUsage typique
BEFOREavant l'écriture de la lignemodifier ou valider NEW, ou ignorer la ligne
AFTERaprès l'écriture de la ligneécrire dans d'autres tables : journaux d'audit, compteurs
INSTEAD OFà la place de la modification, sur les vues uniquementrendre une vue modifiable

FOR EACH ROW exécute la fonction une fois par ligne touchée. FOR EACH STATEMENT (par défaut) l'exécute une fois par instruction, même si aucune ligne n'a changé. Dans un trigger de ligne, NEW contient la nouvelle ligne (INSERT, UPDATE) et OLD la précédente (UPDATE, DELETE). TG_OP donne l'opération qui l'a déclenché et TG_TABLE_NAME la table.

La valeur renvoyée par un trigger de ligne BEFORE décide de la suite : renvoyez NEW (modifiée ou non) pour continuer, ou NULL pour ignorer la ligne sans erreur. La valeur renvoyée par un trigger AFTER est ignorée, donc par convention il renvoie NULL.

Écrire un journal d'audit avec un trigger AFTER

La table d'audit enregistre deux INSERT, un UPDATE de 10 à 12 et un DELETE du produit à 20. OLD vaut NULL lors d'une insertion et NEW vaut NULL lors d'une suppression, d'où le coalesce. Pour stocker des lignes entières, placez to_jsonb(OLD) et to_jsonb(NEW) dans une colonne jsonb.

Bloquer ou ignorer une modification

Un trigger BEFORE peut refuser une modification avec RAISE EXCEPTION, qui interrompt l'instruction, ou ignorer une ligne en renvoyant NULL :

Le DELETE a supprimé le brouillon et gardé sans rien dire la facture payée. Un trigger BEFORE DELETE renvoie OLD pour laisser la suppression se faire. Pour faire échouer toute l'instruction à la place, remplacez RETURN NULL par RAISE EXCEPTION 'invoice % is paid', OLD.id;. Une erreur se débogue plus facilement qu'une ligne qui reste discrètement en place.

Se déclencher seulement quand une colonne change

UPDATE OF column limite un trigger aux mises à jour qui modifient cette colonne. Une condition WHEN évite l'appel de la fonction quand elle est fausse. IS DISTINCT FROM traite NULL comme une valeur, donc il détecte aussi les passages vers ou depuis NULL :

La première mise à jour remet le même prix, donc le WHEN est faux et le compteur reste à 0. La seconde le change, et price_changed finit à 1.

Lister, désactiver et supprimer des triggers

-- triggers you created (excludes the ones behind foreign keys)
SELECT tgrelid::regclass AS table_name, tgname
FROM pg_trigger WHERE NOT tgisinternal;

ALTER TABLE products DISABLE TRIGGER products_price;
ALTER TABLE products ENABLE TRIGGER products_price;
DROP TRIGGER products_price ON products;

Dans psql, \d products liste les triggers d'une table. CREATE OR REPLACE TRIGGER existe depuis PostgreSQL 14, et EXECUTE FUNCTION depuis PostgreSQL 11 ; le code plus ancien écrit EXECUTE PROCEDURE, qui fonctionne toujours. Un trigger qui écrit dans sa propre table peut se redéclencher lui-même, donc protégez-vous des boucles (pg_trigger_depth() renvoie le niveau d'imbrication). Le chapitre sur les fonctions trigger liste toutes les variables TG_, et fonctions et procédures couvre PL/pgSQL lui-même.

Questions fréquentes

Un trigger PostgreSQL peut-il fonctionner sans fonction ?

Non. CREATE TRIGGER appelle toujours une fonction déclarée RETURNS trigger. Écrivez d'abord la fonction, en général en PL/pgSQL, puis attachez-la.

Dans quel ordre se déclenchent les triggers d'une même table ?

Les triggers du même type sur le même événement se déclenchent dans l'ordre alphabétique de leurs noms. Préfixez les noms par des chiffres ou des lettres si l'ordre compte.

Une erreur dans un trigger annule-t-elle l'instruction ?

Oui. Une erreur levée dans un trigger, par exemple avec RAISE EXCEPTION, interrompt l'instruction qui l'a déclenché, et toute la transaction sauf si vous l'interceptez avec un savepoint.

Les triggers se déclenchent-ils sur TRUNCATE ?

Les triggers DELETE au niveau ligne, non. Créez un trigger au niveau instruction avec BEFORE TRUNCATE ou AFTER TRUNCATE pour y réagir.

Illustration des langages de programmation de Coddy

Apprendre à coder avec Coddy

Commencer