Triggers SQLite : CREATE TRIGGER, BEFORE/AFTER, OLD/NEW

Un trigger SQLite exécute du SQL automatiquement

Un trigger SQLite, c'est un bloc de SQL stocké dans la base qui se déclenche dès qu'un événement précis survient sur une table donnée. Tu l'écris une seule fois, et c'est SQLite qui s'occupe du « quand ».

Voici la structure générale :

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL
);

CREATE TABLE price_history (
    product_id INTEGER,
    old_price REAL,
    new_price REAL,
    changed_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER log_price_change
AFTER UPDATE OF price ON products
BEGIN
    INSERT INTO price_history(product_id, old_price, new_price)
    VALUES (OLD.id, OLD.price, NEW.price);
END;

INSERT INTO products(name, price) VALUES ('Cahier', 5.00);
UPDATE products SET price = 6.50 WHERE name = 'Cahier';
SELECT * FROM price_history;

Nous n'avons jamais écrit explicitement d'INSERT dans price_history. C'est le trigger qui s'en est chargé. Toute mise à jour future du prix sera enregistrée de la même manière, qu'elle vienne du CLI, d'un script ou d'une appli.

Anatomie d'un CREATE TRIGGER en SQLite

Décortiquons la syntaxe morceau par morceau :

CREATE TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF } { INSERT | UPDATE [ OF column_list ] | DELETE }
ON table_name
[ FOR EACH ROW ]
[ WHEN condition ]
BEGIN
    -- une ou plusieurs instructions
END;

• Timing — BEFORE s'exécute avant la modification, AFTER s'exécute après, et INSTEAD OF remplace l'opération (uniquement sur les vues).
• Événement — l'opération qui déclenche le trigger. UPDATE OF col1, col2 restreint le déclenchement aux mises à jour de colonnes précises.
• Table — la table surveillée.
• FOR EACH ROW — SQLite ne gère que des triggers au niveau de la ligne, donc c'est implicite. Vous pouvez l'écrire pour la lisibilité, ça ne change rien.
• WHEN — une condition facultative. Le corps du trigger ne s'exécute que si elle est vraie.
• Corps — une ou plusieurs instructions entre BEGIN et END. Chacune doit se terminer par un point-virgule.

Voilà toute la syntaxe. En pratique, la plupart des triggers font cinq à dix lignes.

OLD et NEW : la ligne en cours de modification

Dans le corps du trigger, deux pseudo-lignes permettent d'accéder aux données :

• NEW — la nouvelle ligne. Disponible dans les triggers INSERT et UPDATE.
• OLD — la ligne existante. Disponible dans les triggers UPDATE et DELETE.

Un trigger DELETE ne dispose que de OLD. Un trigger INSERT ne dispose que de NEW. Un trigger UPDATE, lui, a accès aux deux.

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    name TEXT,
    balance REAL NOT NULL
);

CREATE TABLE deletions (
    table_name TEXT,
    row_id INTEGER,
    snapshot TEXT,
    deleted_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER archive_account
AFTER DELETE ON accounts
BEGIN
    INSERT INTO deletions(table_name, row_id, snapshot)
    VALUES ('accounts', OLD.id, OLD.name || ' / ' || OLD.balance);
END;

INSERT INTO accounts(name, balance) VALUES ('Rosa', 120.00);
DELETE FROM accounts WHERE name = 'Rosa';
SELECT * FROM deletions;

La ligne supprimée a bien disparu de accounts, mais ses données ont été capturées dans deletions juste avant.

BEFORE : valider ou ajuster la ligne

Les triggers BEFORE se déclenchent avant que la modification ne soit écrite sur disque. Pratique pour lever une erreur ou normaliser les données :

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    created_at TEXT
);

CREATE TRIGGER lowercase_email
BEFORE INSERT ON users
BEGIN
    UPDATE users SET email = email; -- noop, juste pour illustrer le contexte
END;

CREATE TRIGGER reject_empty_email
BEFORE INSERT ON users
WHEN NEW.email IS NULL OR trim(NEW.email) = ''
BEGIN
    SELECT RAISE(ABORT, 'l''email ne peut pas être vide');
END;

INSERT INTO users(email) VALUES ('ada@example.com');
INSERT INTO users(email) VALUES ('');
SELECT * FROM users;

Le second INSERT est interrompu avant qu'aucune ligne ne soit écrite. RAISE(ABORT, '...') annule l'instruction en cours et restaure l'état d'avant son exécution. Pour un contrôle plus fin, vous avez aussi RAISE(FAIL, ...), RAISE(ROLLBACK, ...) et RAISE(IGNORE).

Pour de la simple validation de données, privilégiez les contraintes CHECK : elles sont déclaratives et l'optimiseur en tient compte. Réservez le trigger BEFORE aux règles qui doivent consulter d'autres tables ou exprimer une logique qu'un CHECK ne peut pas couvrir.

La clause WHEN : des triggers conditionnels

La clause WHEN permet de filtrer quelles modifications déclenchent réellement le corps du trigger. Elle est évaluée ligne par ligne, une fois OLD et NEW liés :

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    total REAL NOT NULL,
    status TEXT NOT NULL
);

CREATE TABLE big_orders (
    order_id INTEGER,
    total REAL,
    flagged_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER flag_big_order
AFTER INSERT ON orders
WHEN NEW.total >= 1000
BEGIN
    INSERT INTO big_orders(order_id, total) VALUES (NEW.id, NEW.total);
END;

INSERT INTO orders(total, status) VALUES (50, 'paid');
INSERT INTO orders(total, status) VALUES (1500, 'paid');
INSERT INTO orders(total, status) VALUES (2200, 'paid');
SELECT * FROM big_orders;

La première commande ne passe pas le filtre. Les deux autres si. Sans la clause WHEN, chaque insertion irait dans big_orders et vous vous retrouverais à filtrer côté lecture.

INSTEAD OF : rendre une vue modifiable

Par défaut, les vues sont en lecture seule. Un trigger INSTEAD OF intercepte une écriture sur une vue et exécute votre SQL à la place — en général pour traduire l'opération en écritures sur la ou les tables sous-jacentes :

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    salary REAL
);

CREATE VIEW employee_summary AS
SELECT id, first_name || ' ' || last_name AS full_name, salary
FROM employees;

CREATE TRIGGER employee_summary_insert
INSTEAD OF INSERT ON employee_summary
BEGIN
    INSERT INTO employees(first_name, last_name, salary)
    VALUES (
        substr(NEW.full_name, 1, instr(NEW.full_name, ' ') - 1),
        substr(NEW.full_name, instr(NEW.full_name, ' ') + 1),
        NEW.salary
    );
END;

INSERT INTO employee_summary(full_name, salary) VALUES ('Boris Petrov', 60000);
SELECT * FROM employees;

L'application interagit avec la vue comme s'il s'agissait d'une vraie table. C'est le trigger qui se charge, en coulisses, de découper la valeur en first_name et last_name.

Lister et supprimer les triggers

Les triggers sont stockés dans sqlite_master, au même titre que les tables et les index :

CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);

CREATE TRIGGER touch_note
AFTER UPDATE ON notes
BEGIN
    SELECT 1; -- corps fictif
END;

SELECT name, tbl_name, sql
FROM sqlite_master
WHERE type = 'trigger';

DROP TRIGGER IF EXISTS touch_note;

SELECT count(*) AS remaining
FROM sqlite_master
WHERE type = 'trigger';

DROP TRIGGER IF EXISTS name; est la forme à privilégier. Et bonne nouvelle : si vous supprimez la table sur laquelle vit un trigger, celui-ci disparaît automatiquement — pas besoin de faire le ménage avant.

Pièges à connaître

Quelques détails qui surprennent toujours au premier essai :

• Un trigger se déclenche par ligne, pas par instruction. Un UPDATE qui touche 1 000 lignes va déclencher le trigger 1 000 fois. Si le corps du trigger est coûteux, l'addition grimpe vite.
• Les triggers s'exécutent dans la transaction englobante. Si l'instruction extérieure fait un rollback, les écritures du trigger sont annulées avec elle. C'est souvent ce qu'on veut, mais ça veut aussi dire qu'un trigger n'est pas une porte de sortie pour « logguer quoi qu'il arrive ».
• Les triggers récursifs sont désactivés par défaut. Un trigger qui modifie sa propre table ne se re-déclenchera pas tout seul, sauf si vous activez PRAGMA recursive_triggers = ON;. À laisser désactivé tant que vous n'avez pas une raison précise.
• Le code applicatif ne peut les contourner qu'en court-circuitant la base. Tant que toutes les écritures passent par SQLite, le trigger s'exécute. Même les ORM qui envoient du SQL brut en batch les déclenchent.
• N'éparpillez pas votre logique métier dans une multitude de triggers. Ils sont invisibles depuis le code appelant — la personne qui debugge « d'où sort cette ligne ? » devra fouiller sqlite_master au grep. Réservez-les aux préoccupations transverses (journaux d'audit, colonnes calculées, vues modifiables) et gardez le reste dans le code applicatif.

Un exemple concret de journal d'audit

Mettons toutes ces idées bout à bout — on va tracer chaque modification sur une table posts :

CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT,
    updated_at TEXT
);

CREATE TABLE post_audit (
    post_id INTEGER,
    action TEXT,
    old_title TEXT,
    new_title TEXT,
    at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER posts_audit_update
AFTER UPDATE OF title, body ON posts
BEGIN
    UPDATE posts SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
    INSERT INTO post_audit(post_id, action, old_title, new_title)
    VALUES (NEW.id, 'update', OLD.title, NEW.title);
END;

INSERT INTO posts(title, body) VALUES ('Brouillon', 'première version');
UPDATE posts SET title = 'Publié' WHERE id = 1;
SELECT id, title, updated_at FROM posts;
SELECT * FROM post_audit;

Un seul trigger garde updated_at à jour et écrit la ligne d'audit au même endroit. Le code applicatif qui fait l'UPDATE n'a besoin de connaître ni l'un ni l'autre.

La suite : le support JSON

Les triggers gèrent l'automatisation autour des événements de ligne. La prochaine brique avancée de SQLite, c'est ce que vous pouvez stocker dans une ligne : du JSON. SQLite propose tout un arsenal de fonctions JSON pour interroger et modifier des données structurées sans quitter le SQL, et c'est justement le sujet de la page suivante.