1. Événement
INSERT, UPDATE, DELETE ou TRUNCATE, seuls ou combinés avec OR ; UPDATE OF salaire limite le trigger aux UPDATE qui visent cette colonne.3
Informatique · PL/pgSQL (PostgreSQL)
Noter chaque changement de salaire, remplir une date toute seule, refuser une suppression : un déclencheur fait ce travail sans qu'aucun programme n'y pense.
Déjà à l'aise ? Aller au quizLe principe
Un programme écrit dans la table users, et personne ne note ce qui a changé. La base peut le faire toute seule : elle appelle une fonction, le déclencheur, à chaque événement sur la table. Pour lire un résultat ligne après ligne, on prend un curseur.
Le programme n'a rien demandé d'autre.
Après cette fiche, tu sais
RETURNS trigger, puis CREATE TRIGGER ;Je dirais oui, il peut la corriger. Tu confirmes ?
La base agit seule
Notion 1 · Le déclencheur
Un déclencheur relie une fonction à un événement sur une table : INSERT, UPDATE, DELETE ou TRUNCATE. Quel que soit le programme qui envoie la requête, la fonction s'exécute. Quatre choix le définissent.1
INSERT, UPDATE, DELETE ou TRUNCATE, seuls ou combinés avec OR ; UPDATE OF salaire limite le trigger aux UPDATE qui visent cette colonne.3
BEFORE (avant l'opération), AFTER (après), ou INSTEAD OF (à la place), ce dernier seulement sur une vue.3
FOR EACH ROW : une fois par ligne touchée ; FOR EACH STATEMENT : une fois par instruction. Sans précision, c'est FOR EACH STATEMENT.3
Une fonction sans paramètre qui renvoie le type trigger, créée avant le trigger lui-même.1
Les usages
Tenir un journal d'audit, faire respecter une règle, remplir une donnée dérivée, répercuter un changement dans une autre table.
Fais défiler : un INSERT, un UPDATE puis un DELETE arrivent sur la table users. Regarde quand le déclencheur se réveille, et ce qui apparaît dans audit_log.
Trois requêtes, un déclencheur
Retiens l'idée : le programme n'a pas écrit les lignes d'audit. La base les a ajoutées toute seule.
Écrire le déclencheur
Notion 2 · Écrire un déclencheur
En PostgreSQL, un déclencheur se crée en deux temps. D'abord une fonction sans paramètre qui renvoie le type trigger ; ensuite CREATE TRIGGER, qui la relie à une table, à des événements, à un moment et à un niveau.1 Voici le code de la scène 1.
CREATE OR REPLACE FUNCTION log_user_changes()
RETURNS trigger AS $$
BEGIN
INSERT INTO audit_log (action, user_id, changed_at)
VALUES (TG_OP, NEW.id, now());
-- trigger AFTER : la valeur renvoyée est ignorée
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_user_audit
AFTER INSERT OR UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION log_user_changes();
INSERT INTO users (nom) VALUES ('Noah'); -- id 3
UPDATE users SET nom = 'Noé' WHERE id = 3;
DELETE FROM users WHERE id = 3; -- non surveillé
SELECT action, user_id FROM audit_log;
action | user_id --------+--------- INSERT | 3 UPDATE | 3
Obligatoire, et la fonction ne déclare aucun paramètre. Sinon PostgreSQL refuse : function … must return type trigger, ou trigger functions cannot have declared arguments.2
Le moment, puis les événements surveillés, reliés par OR, puis la table.
Une exécution par ligne touchée. Sans cette clause, le trigger serait FOR EACH STATEMENT et NEW vaudrait NULL (notion 5).3
La fonction à appeler. L'ancienne écriture EXECUTE PROCEDURE fonctionne encore, mais elle est dépréciée.3
Dans le corps, PL/pgSQL fournit des variables spéciales.2
| Variable | Contenu |
|---|---|
NEW | la nouvelle ligne d'un INSERT ou d'un UPDATE ; vaut NULL dans un DELETE et dans un trigger STATEMENT |
OLD | l'ancienne ligne d'un UPDATE ou d'un DELETE ; vaut NULL dans un INSERT et dans un trigger STATEMENT |
TG_OP | l'opération : 'INSERT', 'UPDATE', 'DELETE' ou 'TRUNCATE' |
TG_TABLE_NAME | le nom de la table qui a déclenché l'appel |
TG_WHEN, TG_LEVEL | BEFORE, AFTER ou INSTEAD OF ; ROW ou STATEMENT |
TG_NAME | le nom du trigger qui s'exécute |
SELECT log_user_changes(); répond trigger functions can only be called as triggers. Pour modifier un trigger existant : CREATE OR REPLACE TRIGGER ; pour le supprimer : DROP TRIGGER trg_user_audit ON users;3OLD et NEW
Ce que contiennent OLD et NEW dépend de l'opération qui déclenche le trigger.
Huit situations, toutes testées dans PostgreSQL 18. Range chacune.
Corriger avant, réagir après
Notion 3 · BEFORE ou AFTER
Un trigger BEFORE … FOR EACH ROW s'exécute juste avant que la ligne soit écrite. Il peut modifier NEW, la ligne qui va être enregistrée, ou renvoyer NULL pour ignorer cette ligne.1
Un trigger AFTER … FOR EACH ROW s'exécute à la fin de l'instruction. La ligne est déjà écrite, et sa valeur de retour est ignorée.1
Deux déclencheurs sur employes : le premier plafonne toute hausse de salaire à 10 %, le second note chaque changement dans audit_salaires.
CREATE OR REPLACE FUNCTION plafonner_hausse()
RETURNS trigger AS $$
BEGIN
IF NEW.salaire > OLD.salaire * 1.10 THEN
-- on corrige la ligne à écrire
NEW.salaire := OLD.salaire * 1.10;
END IF;
RETURN NEW; -- la ligne qui sera écrite
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_plafond
BEFORE UPDATE OF salaire ON employes
FOR EACH ROW
EXECUTE FUNCTION plafonner_hausse();
CREATE OR REPLACE FUNCTION tracer_salaire()
RETURNS trigger AS $$
BEGIN
INSERT INTO audit_salaires
(employe, ancien, nouveau)
VALUES (NEW.id, OLD.salaire, NEW.salaire);
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_trace
AFTER UPDATE OF salaire ON employes
FOR EACH ROW EXECUTE FUNCTION tracer_salaire();Fais défiler : l'UPDATE traverse les deux triggers, et tu suis OLD et NEW.
On demande 3000 pour Léa. OLD contient la ligne actuelle (2400.00), NEW la ligne proposée (3000).
3000 dépasse 2400 × 1,10 : trg_plafond réécrit NEW.salaire := 2640.00, puis renvoie NEW.
La ligne enregistrée est celle que le BEFORE a renvoyée : Léa passe à 2640.00, pas à 3000.
trg_trace lit les valeurs définitives et écrit 2400.00 → 2640.00. Changer NEW ici n'aurait aucun effet.
Avec un BEFORE qui refuse les baisses en renvoyant NULL : la ligne reste telle quelle, aucun AFTER n'est appelé, réponse UPDATE 0.
Donc non, rien à faire après coup ? Tu confirmes ?
Pourquoi l'audit en AFTER ?
Un AFTER voit la valeur finale de la ligne. Un BEFORE ne la voit pas encore, car d'autres BEFORE peuvent passer après lui.1
Sans raison particulière, BEFORE reste le plus efficace : PostgreSQL n'a rien à garder jusqu'à la fin de l'instruction.
Renvoyer NEW, éventuellement modifié : c'est cette ligne qui est écrite. Renvoyer NULL : la ligne est ignorée et les triggers suivants ne sont pas appelés.2
Renvoyer OLD. La valeur n'a pas d'effet direct mais doit être non nulle pour que la suppression ait lieu ; or NEW vaut NULL dans un DELETE.2
La valeur renvoyée est ignorée : on écrit RETURN NULL. Seule une erreur (RAISE EXCEPTION) peut encore annuler l'opération.2
| Dans un BEFORE ROW | Effet (testé sur un UPDATE de 4 lignes) |
|---|---|
RETURN NULL | ignore cette ligne en silence ; les autres lignes de l'instruction continuent |
RAISE EXCEPTION | annule toute l'instruction, même les lignes déjà traitées, avec un message d'erreur |
ROLLBACK | interdit dans un trigger : invalid transaction termination5 |
:NEW.salaire et :OLD.salaire, avec deux-points, mais sans les deux-points dans la clause WHEN. OLD vaut NULL dans un INSERT, NEW dans un DELETE, comme en PostgreSQL. Modifier :NEW dans un trigger AFTER provoque l'erreur ORA-04084, là où PostgreSQL ignore simplement la modification.6Six besoins. À chaque fois, demande-toi : faut-il changer la ligne avant qu'elle soit écrite, ou réagir à une ligne définitive ?
Par ligne ou par instruction
Notion 4 · ROW ou STATEMENT
Avec FOR EACH ROW, la fonction s'exécute pour chaque ligne touchée. Avec FOR EACH STATEMENT, elle ne s'exécute qu'une fois, même si l'instruction ne touche aucune ligne.1
Le calendrier
Un BEFORE ROW passe juste avant chaque ligne. Un AFTER ROW passe à la fin de l'instruction, avant les AFTER STATEMENT.1
CREATE OR REPLACE FUNCTION signaler()
RETURNS trigger AS $$
BEGIN
IF TG_LEVEL = 'ROW' THEN
RAISE NOTICE '% : NEW.id = %',
TG_NAME, NEW.id;
ELSE
RAISE NOTICE '% : NEW = %', TG_NAME, NEW;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER t_row AFTER UPDATE ON eleves
FOR EACH ROW
EXECUTE FUNCTION signaler();
CREATE TRIGGER t_stmt AFTER UPDATE ON eleves
FOR EACH STATEMENT EXECUTE FUNCTION signaler();Fais défiler : un UPDATE qui touche quatre lignes, puis un UPDATE qui n'en touche aucune. Compte les appels de chaque trigger.
Un UPDATE de quatre lignes
Le piège classique : un trigger STATEMENT part même si l'instruction ne touche aucune ligne.
REFERENCING NEW TABLE AS nouveaux. Dans la fonction, nouveaux se lit comme une table, en lecture seule, avec toutes les lignes modifiées par l'instruction.2 Testé : un UPDATE de trois lignes, et SELECT count(*) FROM nouveaux donne 3.Six instructions, toutes testées. Combien de fois la fonction du trigger s'exécute-t-elle ?
Trois usages, quatre pièges
Notion 5 · Usages et pièges
Un BEFORE pour remplir une donnée, un BEFORE qui bloque, un AFTER qui archive : les trois cas qu'on rencontre le plus.
CREATE OR REPLACE FUNCTION maj_updated_at()
RETURNS trigger AS $$
BEGIN
NEW.updated_at := now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_updated_at
BEFORE UPDATE ON produits
FOR EACH ROW EXECUTE FUNCTION maj_updated_at();CREATE OR REPLACE FUNCTION interdire_suppression()
RETURNS trigger AS $$
BEGIN
RAISE EXCEPTION 'Suppression interdite dans %',
TG_TABLE_NAME;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_pas_de_delete
BEFORE DELETE ON employes
FOR EACH ROW
EXECUTE FUNCTION interdire_suppression();
DELETE FROM employes WHERE id = 4;
-- ERREUR : Suppression interdite dans employesCREATE OR REPLACE FUNCTION auditer()
RETURNS trigger AS $$
BEGIN
INSERT INTO audit
(table_nom, operation, avant, apres,
par, quand)
VALUES
(TG_TABLE_NAME, TG_OP,
row_to_json(OLD), row_to_json(NEW),
current_user, now());
RETURN NULL; -- AFTER : valeur ignorée
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_audit_commandes
AFTER INSERT OR UPDATE OR DELETE ON commandes
FOR EACH ROW
EXECUTE FUNCTION auditer();
INSERT INTO commandes VALUES (1, 49.90);
UPDATE commandes SET total = 59.90 WHERE id = 1;
DELETE FROM commandes WHERE id = 1;
SELECT operation, avant, apres FROM audit;
operation | avant | apres
-----------+------------------------+------------------------
INSERT | | {"id":1,"total":49.90}
UPDATE | {"id":1,"total":49.90} | {"id":1,"total":59.90}
DELETE | {"id":1,"total":59.90} |row_to_json transforme toute la ligne en objet JSON. Comme OLD vaut NULL dans un INSERT et NEW dans un DELETE, les cases vides apparaissent toutes seules.
Le piège du DELETE
Dans un BEFORE DELETE, la valeur renvoyée décide de la suppression. Le schéma compare les deux retours possibles.
Le trigger devient FOR EACH STATEMENT, le niveau par défaut, et NEW vaut NULL.3 Testé : sans cette clause, l'audit n'écrit qu'une ligne pour un INSERT de trois lignes, avec user_id vide.
NEW vaut NULL dans un DELETE : renvoyer NEW annule la suppression, sans erreur. PostgreSQL répond DELETE 0. Dans un BEFORE DELETE, on renvoie OLD.2
Si la fonction modifie la table qui l'a déclenchée, elle la déclenche à nouveau. PostgreSQL ne l'empêche pas.1 Pour changer la ligne en cours, on modifie NEW dans un BEFORE.
Deux triggers sur le même événement s'exécutent par ordre alphabétique de leur nom.3 Testé : aa_b passe avant zz_b, même créé après lui.
Lire, ligne après ligne
Notion 6 · Les curseurs
Un SELECT … INTO ne garde qu'une ligne : la première. Les suivantes sont jetées.4
Pour traiter toutes les lignes une à une, on déclare un curseur : il encapsule la requête et livre son résultat quelques lignes à la fois. On évite ainsi de charger un très gros résultat en mémoire d'un coup.4
c CURSOR FOR SELECT …; associe le curseur à sa requête. Rien n'est encore lu.
Ouvre le curseur : il est placé avant la première ligne du résultat.4
Copie la ligne suivante dans r. Si aucune ligne ne vient, r reçoit des NULL et FOUND passe à false.4
Ferme le curseur et libère ses ressources ; on peut ensuite le rouvrir.4
DO $$
DECLARE
c CURSOR FOR SELECT nom, salaire FROM employes
ORDER BY salaire;
r RECORD;
total NUMERIC := 0;
BEGIN
OPEN c;
LOOP
FETCH c INTO r;
EXIT WHEN NOT FOUND;
total := total + r.salaire;
END LOOP;
CLOSE c;
RAISE NOTICE 'total = %', total;
END $$;
NOTICE: total = 18500.00
Fais défiler : le curseur s'ouvre, avance de ligne en ligne, puis tombe sur la fin du résultat. Regarde r, FOUND et total.
OPEN, FETCH, CLOSE
Pourquoi pas le test avant ? Tu peux m'expliquer ?
Les huit lignes de la boucle ont été mélangées. Touche une ligne, puis celle avec laquelle l'échanger.
FOR r IN c LOOP … END LOOP; ouvre le curseur, lit chaque ligne et le ferme à la sortie. Le curseur ne doit pas être déjà ouvert, et r, déclarée automatiquement, n'existe que dans la boucle.4 On peut même écrire la requête directement : FOR r IN SELECT … LOOP, qui utilise un curseur en interne.4
DO $$
DECLARE
c CURSOR FOR SELECT nom, salaire FROM employes
ORDER BY salaire;
total NUMERIC := 0;
BEGIN
-- OPEN, FETCH et CLOSE automatiques
FOR r IN c LOOP
total := total + r.salaire;
END LOOP;
RAISE NOTICE 'total = %', total;
END $$;
NOTICE: total = 18500.00
Quand le curseur est placé sur une ligne de table, UPDATE … WHERE CURRENT OF c modifie exactement cette ligne.4
DO $$
DECLARE
c CURSOR FOR SELECT nom, salaire FROM employes
ORDER BY salaire FOR UPDATE;
r RECORD;
BEGIN
OPEN c;
LOOP
FETCH c INTO r;
EXIT WHEN NOT FOUND;
IF r.salaire < 5000 THEN
UPDATE employes SET salaire = salaire * 1.02
WHERE CURRENT OF c;
END IF;
END LOOP;
CLOSE c;
END $$;
SELECT nom, salaire FROM employes ORDER BY salaire;
nom | salaire ------+--------- Léa | 2448.00 Lina | 4896.00 Noah | 5200.00 Zoé | 6100.00
UPDATE employes SET salaire = salaire * 1.02 WHERE salaire < 5000; donne exactement le même résultat (testé), en une instruction. On garde le curseur pour les traitements qui doivent vraiment décider ligne après ligne.Testé dans PostgreSQL 18 : FETCH sans OPEN répond cursor variable "c" is null ; FETCH après CLOSE, cursor … does not exist ; un second OPEN, ou une boucle FOR r IN c sur un curseur déjà ouvert, cursor … already in use.
| PL/pgSQL (PostgreSQL) | PL/SQL (Oracle) | |
|---|---|---|
| Déclarer | c CURSOR FOR SELECT …; | CURSOR c IS SELECT …; |
| Fin du résultat | EXIT WHEN NOT FOUND; | EXIT WHEN c%NOTFOUND; |
Oracle donne à chaque curseur nommé des attributs : %FOUND, %NOTFOUND, %ROWCOUNT (le nombre de lignes déjà lues) et %ISOPEN.6 La fiche d'exercices qui suit compare les deux écritures en détail.
Autre syntaxe, même idée
Et en Oracle PL/SQL
Oracle n'exige pas de fonction séparée : le corps du trigger s'écrit directement après la table et les événements. Sans FOR EACH ROW, il s'exécute une fois par instruction.8 Les lignes se lisent avec :NEW et :OLD, deux-points compris.6
Plusieurs événements
Un trigger qui surveille INSERT, UPDATE et DELETE teste lequel vient de se produire avec les prédicats INSERTING, UPDATING et DELETING. UPDATING('salaire') vérifie la colonne visée.6
CREATE OR REPLACE TRIGGER trg_user_audit
AFTER INSERT OR UPDATE ON users
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO audit_log (action, user_id, changed_at)
VALUES ('INSERT', :NEW.id, SYSDATE);
ELSIF UPDATING THEN
INSERT INTO audit_log (action, user_id, changed_at)
VALUES ('UPDATE', :NEW.id, SYSDATE);
END IF;
END;
/La clause WHEN
WHEN (NEW.salaire > OLD.salaire * 1.10) filtre les lignes : le corps ne s'exécute que si la condition est vraie. Dans WHEN, pas de deux-points devant NEW ni OLD.8
CREATE OR REPLACE TRIGGER trg_plafond
BEFORE UPDATE OF salaire ON employes
FOR EACH ROW
WHEN (NEW.salaire > OLD.salaire * 1.10)
BEGIN
:NEW.salaire := :OLD.salaire * 1.10;
END;
/Habitudes et exercices
Habitudes de pro
trg_audit_salaires, trg_updated_at ;NEW dans un BEFORE ; jamais d'UPDATE de la même table dans le trigger ;FOR EACH ROW dès que la fonction lit NEW ou OLD.Habitudes de pro, suite
NEW dans un BEFORE INSERT ou UPDATE, OLD dans un BEFORE DELETE, NULL dans un AFTER ;Tables utilisées : employes(id, nom, salaire) avec Léa 2400, Lina 4800, Noah 5200 et Zoé 6100, et clients(id, email). Tous les corrigés ont été exécutés dans PostgreSQL 18.
CREATE OR REPLACE FUNCTION email_minuscules()
RETURNS trigger AS $$
BEGIN
NEW.email := lower(NEW.email);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_email
BEFORE INSERT OR UPDATE ON clients
FOR EACH ROW
EXECUTE FUNCTION email_minuscules();
INSERT INTO clients VALUES (1, 'Lea.Martin@Exemple.CH');
SELECT email FROM clients;
email ----------------------- lea.martin@exemple.ch
BEFORE, parce qu'on modifie la ligne avant son écriture ; FOR EACH ROW, parce qu'on lit et modifie NEW.
employes, avec un message qui donne la valeur refusée.Voir le corrigéCREATE OR REPLACE FUNCTION verifier_salaire()
RETURNS trigger AS $$
BEGIN
IF NEW.salaire < 0 THEN
RAISE EXCEPTION 'Salaire négatif refusé : %',
NEW.salaire;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_salaire
BEFORE INSERT OR UPDATE ON employes
FOR EACH ROW
EXECUTE FUNCTION verifier_salaire();
INSERT INTO employes VALUES (5, 'Elias', -300);
-- ERREUR : Salaire négatif refusé : -300.00
RAISE EXCEPTION plutôt que RETURN NULL : le programme qui a envoyé la requête apprend qu'elle a échoué, et pourquoi. Une contrainte CHECK (salaire >= 0) ferait aussi l'affaire ; le trigger devient utile dès que la règle dépend d'autres données.
CREATE OR REPLACE FUNCTION archiver()
RETURNS trigger AS $$
BEGIN
INSERT INTO employes_archive SELECT NEW.*;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_archive
BEFORE DELETE ON employes
FOR EACH ROW
EXECUTE FUNCTION archiver();
DELETE FROM employes WHERE id = 4;
-- DELETE 0 ; l'archive reçoit une ligne de NULL
Dans un DELETE, NEW vaut NULL. L'archive reçoit donc une ligne vide, et la fonction renvoie NULL : PostgreSQL ignore la suppression. Il faut lire et renvoyer OLD :
CREATE OR REPLACE FUNCTION archiver()
RETURNS trigger AS $$
BEGIN
INSERT INTO employes_archive SELECT OLD.*;
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
DELETE FROM employes WHERE id = 4;
-- DELETE 1 ; archive : (4, Lina, 4800.00)DO $$
DECLARE
c CURSOR FOR SELECT nom FROM employes
WHERE salaire > 5000
ORDER BY salaire DESC;
r RECORD;
n INTEGER := 0;
BEGIN
OPEN c;
LOOP
FETCH c INTO r;
EXIT WHEN NOT FOUND;
RAISE NOTICE '%', r.nom;
n := n + 1;
END LOOP;
CLOSE c;
RAISE NOTICE '% employés', n;
END $$;
NOTICE: Zoé NOTICE: Noah NOTICE: 2 employés
Le filtre et le tri restent dans la requête du curseur : la boucle ne fait qu'afficher et compter.
Dernière étape : le quiz. Douze questions tirées au hasard, différentes à chaque partie. Si tu te trompes, lis bien l'explication.
Quiz finalD'où viennent les faits
Fiche relue et sourcée en septembre 2026, refaite en récit en octobre 2026. Tout le code a été exécuté dans PostgreSQL 18.