Interaction des triggers avec les fonctions PL/pgSQL : OLD, NEW, TG_OP

Les triggers dans PostgreSQL ne servent pas juste à lancer des fonctions en réponse à des actions, ils transmettent aussi des variables magiques à ces fonctions. Grâce à ces variables, tu peux savoir ce qu'il y avait dans la table avant l'opération, ce qu'il y a après, et même quelle opération a eu lieu.

• OLD — contient les anciennes données de la ligne de la table avant l'opération. On l'utilise dans les triggers pour UPDATE et DELETE, parce qu'avec INSERT il n'y a rien "d'ancien" à récupérer.
• NEW — contient les nouvelles données de la ligne de la table après l'opération. On l'utilise dans les triggers pour INSERT et UPDATE.
• TG_OP — contient une info textuelle sur l'opération en cours : INSERT, UPDATE ou DELETE.

Toutes ces variables sont automatiquement dispos dans la fonction liée au trigger.

La théorie sans la pratique, c'est comme du SQL sans index : lent et déprimant. Donc on va voir tout ça avec des exemples concrets.

Utiliser OLD pour accéder aux anciennes données

Imagine qu'on a une table students. Et dedans, quelqu'un modifie l'âge d'un étudiant (genre il s'est trompé, il pense que l'étudiant a encore 20 ans alors qu'il en a déjà 25).

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    age INT NOT NULL
);

Pour suivre les changements, on va créer une table de logs :

CREATE TABLE student_changes (
    change_id SERIAL PRIMARY KEY,
    student_id INT NOT NULL,
    old_value INT,
    new_value INT,
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

On continue : on crée une fonction qui va enregistrer les changements. C'est là que OLD devient utile :

CREATE OR REPLACE FUNCTION log_student_changes()
RETURNS TRIGGER AS $$
BEGIN
    -- On log les changements d'âge
    INSERT INTO student_changes (student_id, old_value, new_value)
    VALUES (OLD.id, OLD.age, NEW.age);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Et maintenant, on crée le trigger :

CREATE TRIGGER student_age_update
AFTER UPDATE OF age ON students
FOR EACH ROW
WHEN (OLD.age IS DISTINCT FROM NEW.age) -- On exécute seulement si l'âge a changé
EXECUTE FUNCTION log_student_changes();

Ajoutons un étudiant, puis modifions ses infos :

INSERT INTO students (name, age) VALUES ('Alice', 20);

UPDATE students
SET age = 25
WHERE name = 'Alice';

-- On vérifie le log des changements :
SELECT * FROM student_changes;

Tu verras que le changement d'âge de 20 à 25 a bien été enregistré dans la table de logs. Magique ? Non, c'est juste OLD.

Utiliser NEW pour les nouvelles données

Maintenant, imaginons qu'on veuille, à chaque ajout d'un nouvel étudiant, enregistrer automatiquement son ID et son nom dans la table de logs (ouais, c'est un peu parano, mais parfois ça sert) :

CREATE OR REPLACE FUNCTION log_new_student()
RETURNS TRIGGER AS $$
BEGIN
    -- On log les infos du nouvel étudiant
    INSERT INTO student_changes (student_id, old_value, new_value)
    VALUES (NEW.id, NULL, NEW.age); -- Pas d'ancienne valeur, vu que c'est un INSERT
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER student_insert_log
AFTER INSERT ON students
FOR EACH ROW
EXECUTE FUNCTION log_new_student();

Encore une fois, ajoutons un nouvel étudiant et vérifions :

INSERT INTO students (name, age) VALUES ('Bob', 22);

-- On vérifie le log :
SELECT * FROM student_changes;

Tu verras qu'un nouvel étudiant est apparu dans les logs. Voilà, c'est de la gestion de données niveau supérieur !

Utiliser TG_OP pour savoir quel type d'opération a eu lieu

Mais si on veut un trigger universel pour logger à la fois les INSERT, UPDATE et même DELETE ? C'est là que la variable TG_OP entre en jeu.

On crée une fonction universelle :

CREATE OR REPLACE FUNCTION log_all_operations()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO student_changes (student_id, old_value, new_value)
        VALUES (NEW.id, NULL, NEW.age);

    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO student_changes (student_id, old_value, new_value)
        VALUES (OLD.id, OLD.age, NEW.age);

    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO student_changes (student_id, old_value, new_value)
        VALUES (OLD.id, OLD.age, NULL);

    END IF;

    RETURN NULL; -- Pour un trigger AFTER sur DELETE, on retourne NULL
END;
$$ LANGUAGE plpgsql;

On crée un trigger qui va s'activer sur les trois opérations :

CREATE TRIGGER universal_student_log
AFTER INSERT OR UPDATE OR DELETE ON students
FOR EACH ROW
EXECUTE FUNCTION log_all_operations();

Ajoutons, modifions et supprimons un étudiant :

INSERT INTO students (name, age) VALUES ('Charlie', 30);
UPDATE students SET age = 31 WHERE name = 'Charlie';
DELETE FROM students WHERE name = 'Charlie';

-- On vérifie le log :
SELECT * FROM student_changes;

Tu pourras voir le log de toutes les opérations — un trigger pour tous les contrôler !

Erreurs classiques avec OLD, NEW, TG_OP

Quand tu bosses avec les triggers, tu peux tomber sur quelques pièges classiques :

"Pourquoi OLD ne marche pas lors d'un insert ?" C'est normal : pour INSERT il n'y a pas d'anciennes données. Utilise NEW.

"Que faire si NEW ne marche pas lors d'une suppression ?" Encore une fois, c'est logique : pour DELETE il n'y a pas de nouvelles données. Utilise OLD.

La logique du trigger provoque une récursion infinie. Vérifie que ton trigger ne s'appelle pas lui-même par accident. Pour ça, mets des conditions claires dans le bloc WHEN ou vérifie TG_OP.

1

Étude/Quiz

Introduction aux triggers, niveau 57, leçon 4

Indisponible

Introduction aux triggers

Introduction aux triggers

Ouvrir