Triggers — Exemples

avancé

Schéma de référence

CREATE TABLE villes (id INTEGER PRIMARY KEY, nom TEXT);

CREATE TABLE etudiants (
  id INTEGER PRIMARY KEY,
  nom TEXT,
  age INTEGER,
  ville_id INTEGER REFERENCES villes(id),
  modifie_le TEXT
);

Horodatage automatique

CREATE TRIGGER trg_maj_date
AFTER UPDATE ON etudiants
FOR EACH ROW
WHEN NEW.modifie_le IS OLD.modifie_le
BEGIN
  UPDATE etudiants SET modifie_le = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

La clause WHEN évite la boucle si PRAGMA recursive_triggers est activé.

Historique / audit

CREATE TABLE etudiants_historique (
  id INTEGER, nom TEXT, action TEXT, le TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER trg_audit_suppression
AFTER DELETE ON etudiants
BEGIN
  INSERT INTO etudiants_historique (id, nom, action) VALUES (OLD.id, OLD.nom, 'delete');
END;

Validation métier

CREATE TRIGGER trg_verif_age
BEFORE INSERT ON etudiants
FOR EACH ROW
WHEN NEW.age < 0
BEGIN
  SELECT RAISE(ABORT, 'age invalide');
END;

Vue modifiable (INSTEAD OF)

CREATE VIEW v_etudiants_villes AS
SELECT e.id, e.nom, v.nom AS ville
FROM etudiants e JOIN villes v ON e.ville_id = v.id;

CREATE TRIGGER trg_insert_vue
INSTEAD OF INSERT ON v_etudiants_villes
BEGIN
  INSERT INTO etudiants (nom, ville_id)
  VALUES (NEW.nom, (SELECT id FROM villes WHERE nom = NEW.ville));
END;