Informatique · PL/pgSQL (PostgreSQL)

Déclencheurs et curseurs

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éclencheur INSERT users 3 · Noah trg_user_audit audit_log : INSERT, 3 curseur FETCH c INTO r Léa 2400 Lina 4800 Noah 5200 Zoé 6100 Déjà à l'aise ? Aller au quiz

Le 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.

programme appli, script users table audit_log INSERT, 3 trg_user_audit Une seule requête suffit : la base ajoute la ligne d'audit.

Le programme n'a rien demandé d'autre.

Après cette fiche, tu sais

  • écrire un déclencheur complet : la fonction RETURNS trigger, puis CREATE TRIGGER ;
  • choisir entre BEFORE et AFTER, et prévoir ce que contiennent OLD et NEW ;
  • dire combien de fois un déclencheur s'exécute, FOR EACH ROW ou FOR EACH STATEMENT ;
  • parcourir le résultat d'une requête avec un curseur : OPEN, FETCH, CLOSE, ou une boucle FOR.
Je te pose une question : un déclencheur AFTER peut-il encore corriger la ligne qu'on vient d'enregistrer, par exemple plafonner un salaire ?

Je dirais oui, il peut la corriger. Tu confirmes ?

Garde ta réponse en tête : on la vérifie dans la notion 3, avec du vrai code.

La base agit seule

Notion 1 · Le déclencheur

Une fonction que la base appelle toute seule

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 TRUNCATE table users fonction trigger

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

2. Moment

BEFORE (avant l'opération), AFTER (après), ou INSTEAD OF (à la place), ce dernier seulement sur une vue.3

3. Niveau

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

4. Fonction

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.

journal d'auditqui a changé quoi, quand règlerefuser, plafonner dateremplie toute seule autre tablerépercuter le changement

Scène 1 · Le réveil du déclencheur

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.

Scène 1 · Audit automatique

Trois requêtes, un déclencheur

users id nom 1 Marc 2 Léa 3 Noah trg_user_audit AFTER INSERT OR UPDATE FOR EACH ROW endormi audit_log action user_id changed_at aucune ligne INSERT 3 10:41:07 UPDATE 3 10:42:15

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

Deux instructions : la fonction, puis le trigger

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.

1. la fonctionRETURNS triggersans paramètre 2. le triggerCREATE TRIGGERtable, événement,moment, niveau La fonction doit exister avant le trigger.
Le déclencheur d'auditPL/pgSQL
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
RETURNS trigger

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

AFTER INSERT OR UPDATE ON users

Le moment, puis les événements surveillés, reliés par OR, puis la table.

FOR EACH ROW

Une exécution par ligne touchée. Sans cette clause, le trigger serait FOR EACH STATEMENT et NEW vaudrait NULL (notion 5).3

EXECUTE FUNCTION

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

VariableContenu
NEWla nouvelle ligne d'un INSERT ou d'un UPDATE ; vaut NULL dans un DELETE et dans un trigger STATEMENT
OLDl'ancienne ligne d'un UPDATE ou d'un DELETE ; vaut NULL dans un INSERT et dans un trigger STATEMENT
TG_OPl'opération : 'INSERT', 'UPDATE', 'DELETE' ou 'TRUNCATE'
TG_TABLE_NAMEle nom de la table qui a déclenché l'appel
TG_WHEN, TG_LEVELBEFORE, AFTER ou INSTEAD OF ; ROW ou STATEMENT
TG_NAMEle nom du trigger qui s'exécute
Une fonction trigger ne s'appelle pas à la mainSELECT 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;3

OLD et NEW

Ce que contiennent OLD et NEW dépend de l'opération qui déclenche le trigger.

INSERT UPDATE DELETE OLD NEW NULL insérée ancienne nouvelle supprimée NULL OLD est NULL à l'INSERT, NEW est NULL au DELETE.
Exercice · classer

Accepté ou refusé par PostgreSQL ?

Huit situations, toutes testées dans PostgreSQL 18. Range chacune.

Corriger avant, réagir après

Notion 3 · BEFORE ou AFTER

Avant l'écriture on corrige, après on réagit

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

BEFORE ROW peut changer NEW ou renvoyer NULL écriture ligne écrite AFTER ROW déjà écrite valeur ignorée Avant l'écriture, on corrige. Après, on réagit.

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

PostgreSQL Oracle NEW.salaire := 0; RETURN NEW; la ligne garde sa valeur :NEW.salaire := 0; ORA-04084 modification refusée

Deux déclencheurs sur employes : le premier plafonne toute hausse de salaire à 10 %, le second note chaque changement dans audit_salaires.

Plafonner, puis tracerPL/pgSQL
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();

Séquence · la chronologie d'un UPDATE

Fais défiler : l'UPDATE traverse les deux triggers, et tu suis OLD et NEW.

UPDATE employes SET salaire = 3000 WHERE id = 1 BEFORE trg_plafond en attente écriture employes en attente AFTER trg_trace en attente OLD Léa · 2400.00 lecture seule NEW Léa · 3000 ligne proposée employes nom salaire Léa 2400.00 audit_salaires ancien nouveau vide 2400.00 2640.00
Étape 1 sur 5 · la requête

On demande 3000 pour Léa. OLD contient la ligne actuelle (2400.00), NEW la ligne proposée (3000).

Étape 2 sur 5 · BEFORE

3000 dépasse 2400 × 1,10 : trg_plafond réécrit NEW.salaire := 2640.00, puis renvoie NEW.

Étape 3 sur 5 · l'écriture

La ligne enregistrée est celle que le BEFORE a renvoyée : Léa passe à 2640.00, pas à 3000.

Étape 4 sur 5 · AFTER

trg_trace lit les valeurs définitives et écrit 2400.00 → 2640.00. Changer NEW ici n'aurait aucun effet.

Étape 5 sur 5 · RETURN NULL

Avec un BEFORE qui refuse les baisses en renvoyant NULL : la ligne reste telle quelle, aucun AFTER n'est appelé, réponse UPDATE 0.

Tu te souviens de ma question de l'ouverture : un AFTER peut-il corriger la ligne ?

Donc non, rien à faire après coup ? Tu confirmes ?

Réponse : non. Je peux changer NEW dans un AFTER, mais PostgreSQL ignore la valeur renvoyée. Testé : la ligne garde sa valeur. En Oracle, c'est même une erreur.

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

BEFORE Amodifie NEW BEFORE Bmodifie NEW écritureligne AFTERvaleurfinale AFTER ne voit la valeur finale qu'après les BEFORE.

Sans raison particulière, BEFORE reste le plus efficace : PostgreSQL n'a rien à garder jusqu'à la fin de l'instruction.

BEFORE ROW sur INSERT ou UPDATE

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

BEFORE ROW sur DELETE

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

AFTER, ou FOR EACH STATEMENT

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 ROWEffet (testé sur un UPDATE de 4 lignes)
RETURN NULLignore cette ligne en silence ; les autres lignes de l'instruction continuent
RAISE EXCEPTIONannule toute l'instruction, même les lignes déjà traitées, avec un message d'erreur
ROLLBACKinterdit dans un trigger : invalid transaction termination5
Et en Oracle PL/SQL ?Dans le corps d'un trigger, on écrit :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.6
Exercice · choisir

BEFORE ou AFTER ?

Six 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

Une fois par ligne, ou une fois par instruction

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

UPDATE de 3 lignes ligne 1 · Léa ligne 2 · Lina ligne 3 · Noah FOR EACH ROW 3 appels FOR EACH STATEMENT 1 appel

Le calendrier

Un BEFORE ROW passe juste avant chaque ligne. Un AFTER ROW passe à la fin de l'instruction, avant les AFTER STATEMENT.1

BEFOREROW écriturede la ligne AFTERROW AFTERSTATEMENT pour chaque ligne fin de l'instruction
Une fonction, deux niveauxPL/pgSQL
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();

Scène 2 · Combien d'appels ?

Fais défiler : un UPDATE qui touche quatre lignes, puis un UPDATE qui n'en touche aucune. Compte les appels de chaque trigger.

Scène 2 · Niveau

Un UPDATE de quatre lignes

eleves id actif 1 2 3 4 false false false false t_row · ROW 0 appel t_stmt · STATEMENT 0 appel

Le piège classique : un trigger STATEMENT part même si l'instruction ne touche aucune ligne.

Savoir quelles lignes ont changé, dans un trigger STATEMENTUn trigger AFTER peut déclarer une table de transition : 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.
Exercice · compter

Combien d'appels ?

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

Trois déclencheurs utiles, et ce qui peut mal tourner

Un BEFORE pour remplir une donnée, un BEFORE qui bloque, un AFTER qui archive : les trois cas qu'on rencontre le plus.

remplirBEFORE UPDATEune date,updated_at bloquerBEFORE DELETErefuserla suppression archiverAFTERchaque changementdans un journal
1. Remplir updated_atPL/pgSQL
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();
2. Interdire les suppressionsPL/pgSQL
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 employes
3. Un audit complet en JSONPL/pgSQL
CREATE 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.

Dans un BEFORE DELETE, ce que renvoie la fonction RETURN NEW NULL DELETE 0 RETURN OLD la ligne DELETE 1 Renvoyer OLD : la suppression a lieu.

Piège 1 : oublier FOR EACH ROW

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.

Piège 2 : RETURN NEW dans un BEFORE DELETE

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

Piège 3 : la boucle infinie

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.

Piège 4 : compter sur l'ordre de création

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

Lire un résultat ligne après ligne

Un SELECT … INTO ne garde qu'une ligne : la première. Les suivantes sont jetées.4

Léa 2400 Lina 4800 Noah 5200 Zoé 6100 SELECT INTO r r = Léa 2400 trois lignes jetées (Lina, Noah, Zoé)

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

Léa 2400 Lina 4800 Noah 5200 Zoé 6100 r ligne courante OPEN, avant la 1re ligne FETCH, ligne suivante CLOSE, libère le curseur

DECLARE

c CURSOR FOR SELECT …; associe le curseur à sa requête. Rien n'est encore lu.

OPEN c

Ouvre le curseur : il est placé avant la première ligne du résultat.4

FETCH c INTO r

Copie la ligne suivante dans r. Si aucune ligne ne vient, r reçoit des NULL et FOUND passe à false.4

CLOSE c

Ferme le curseur et libère ses ressources ; on peut ensuite le rouvrir.4

La masse salariale, ligne par lignePL/pgSQL
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

Scène 3 · Le curseur avance

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.

Scène 3 · Curseur

OPEN, FETCH, CLOSE

résultat de c Léa 2400.00 Lina 4800.00 Noah 5200.00 Zoé 6100.00 r non lu FOUND non lu total 0 curseur fermé NOTICE: total = 18500.00

Je te rappelle l'ordre dans la boucle : FETCH d'abord, test ensuite.

Pourquoi pas le test avant ? Tu peux m'expliquer ?

C'est le FETCH raté qui met FOUND à false. Sans EXIT WHEN, la boucle ne s'arrêterait jamais.
Exercice · remettre dans l'ordre

Reconstruis la boucle

Les huit lignes de la boucle ont été mélangées. Touche une ligne, puis celle avec laquelle l'échanger.

Plus court : la boucle FOR

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

La même somme avec FORPL/pgSQL
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

Modifier la ligne sous le curseur

Quand le curseur est placé sur une ligne de table, UPDATE … WHERE CURRENT OF c modifie exactement cette ligne.4

+2 % sous 5000PL/pgSQL
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
Une seule requête, quand elle suffitUPDATE 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.
Piège : l'ordre OPEN, FETCH, CLOSE

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éclarerc CURSOR FOR SELECT …;CURSOR c IS SELECT …;
Fin du résultatEXIT 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

Même idée, autre écriture

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

PostgreSQL Oracle CREATE FUNCTIONRETURNS trigger CREATE TRIGGERrelie la fonction CREATE OR REPLACETRIGGERle corps est écritdirectement dedans:NEW et :OLD

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

INSERTING FALSE UPDATING FALSE UPDATING TRUE DELETING FALSE UPDATING('salaire') vaut TRUE si la colonne salaire est visée
Audit par prédicatsPL/SQL (Oracle)
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

WHEN (NEW.salaire > OLD.salaire * 1.10) vraile corps s'exécute fauxla ligne est ignorée
Plafonner avec WHENPL/SQL (Oracle)
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;
/

L'ordre de déclenchement

Pour une instruction, Oracle passe par quatre étapes : BEFORE STATEMENT, puis, pour chaque ligne, BEFORE EACH ROW, l'écriture et AFTER EACH ROW, et enfin AFTER STATEMENT.7

écriture de la ligne entre les deux BEFORESTATEMENT BEFOREEACH ROW AFTEREACH ROW AFTERSTATEMENT une fois pour chaque ligne touchée une fois Même étape : l'ordre se règle avec FOLLOWS

Sans FOLLOWS, aucun ordre n'est garanti entre les triggers d'une même étape.7

Habitudes et exercices

Habitudes de pro

Six bonnes pratiques

  • un trigger fait une seule chose, et son nom le dit : trg_audit_salaires, trg_updated_at ;
  • pour changer la ligne en cours, modifier NEW dans un BEFORE ; jamais d'UPDATE de la même table dans le trigger ;
  • écrire FOR EACH ROW dès que la fonction lit NEW ou OLD.

Habitudes de pro, suite

Trois autres pratiques

  • renvoyer NEW dans un BEFORE INSERT ou UPDATE, OLD dans un BEFORE DELETE, NULL dans un AFTER ;
  • documenter les triggers d'une base : ils agissent sans que le code de l'application le montre ;
  • préférer une requête qui traite tout l'ensemble à un curseur, et garder le curseur pour les décisions ligne à ligne.
Entraînement

Quatre exercices corrigés

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.

1Écris un trigger qui enregistre toujours l'adresse e-mail des clients en minuscules, à l'INSERT comme à l'UPDATE.Voir le corrigé
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.

2Écris un trigger qui refuse tout salaire négatif dans 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.

3Ce trigger d'archivage ne fait pas ce qu'on attend : l'archive reçoit une ligne vide, et la suppression n'a pas lieu. Pourquoi ?Voir le corrigé
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)
4Avec un curseur, affiche le nom des employés qui gagnent plus de 5000, du plus haut salaire au plus bas, puis leur nombre.Voir le corrigé
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.

À retenir

La fiche en huit lignes

Déclencheurune fonction RETURNS trigger, reliée à une table par CREATE TRIGGER, que la base appelle à chaque événement.
ÉvénementsINSERT, UPDATE (OF colonne), DELETE, TRUNCATE, combinables avec OR.
BEFOREpeut modifier NEW, ou renvoyer NULL pour ignorer la ligne.
AFTERvoit la ligne définitive ; sa valeur de retour est ignorée.
OLD et NEWOLD vaut NULL dans un INSERT, NEW dans un DELETE, les deux dans un trigger STATEMENT.
ROW ou STATEMENTune fois par ligne, ou une fois par instruction même sans ligne ; défaut : STATEMENT.
CurseurDECLARE, OPEN, FETCH puis EXIT WHEN NOT FOUND, CLOSE ; ou FOR r IN c LOOP, qui fait tout.
Piègesoublier FOR EACH ROW, RETURN NEW dans un BEFORE DELETE, UPDATE de sa propre table.

Dernière étape : le quiz. Douze questions tirées au hasard, différentes à chaque partie. Si tu te trompes, lis bien l'explication.

Quiz final

Teste-toi

D'où viennent les faits

Sources

  1. PostgreSQL 18 Documentation, ch. 37.1 : Overview of Trigger Behavior (moments, niveaux ROW et STATEMENT, valeur de retour, usages de BEFORE et AFTER, ordre alphabétique, déclencheurs en cascade). postgresql.org
  2. PostgreSQL 18 Documentation, PL/pgSQL 41.10 : Trigger Functions (NEW, OLD, TG_OP et les autres variables, valeur de retour, RETURN OLD dans un DELETE, tables de transition). postgresql.org
  3. PostgreSQL 18 Documentation : CREATE TRIGGER (syntaxe, FOR EACH STATEMENT par défaut, INSTEAD OF sur les vues, UPDATE OF, EXECUTE PROCEDURE dépréciée, CREATE OR REPLACE TRIGGER, ordre des noms face à la norme SQL). postgresql.org
  4. PostgreSQL 18 Documentation, PL/pgSQL 41.7 : Cursors (déclaration, OPEN, FETCH et FOUND, CLOSE, boucle FOR, WHERE CURRENT OF) et 41.5.3 : Executing a Command with a Single-Row Result (SELECT INTO garde la première ligne). curseurs, SELECT INTO (postgresql.org)
  5. PostgreSQL 18 Documentation, PL/pgSQL 41.8 : Transaction Management (COMMIT et ROLLBACK seulement dans un CALL ou un DO). postgresql.org
  6. Oracle, Database PL/SQL Language Reference 19c : « DML Triggers », « Correlation Names and Pseudorecords » (:OLD, :NEW, ORA-04084), « Explicit Cursor Declaration and Definition », « Named Cursor Attribute ». pseudo-enregistrements, attributs de curseur (docs.oracle.com)
  7. Oracle, Database PL/SQL Language Reference : « Order in Which Triggers Fire » (quatre étapes, puis FOLLOWS pour l'ordre entre les triggers d'une même étape). docs.oracle.com
  8. Oracle, Database PL/SQL Language Reference : « CREATE TRIGGER Statement » (syntaxe, FOR EACH ROW, clause WHEN). docs.oracle.com

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.

Glisser pour continuer vers Exercices sur les curseurs
Université