Vues et triggers

Show code cell source

Hide code cell source

import sqlite3
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.patches as mpatches
import matplotlib.lines as mlines
import numpy as np
import seaborn as sns

sns.set_theme(style="whitegrid", palette="muted", font_scale=1.1)

Vues : abstraire les requêtes

Une vue est une requête stockée sous un nom, que l’on peut ensuite utiliser comme une table ordinaire. Elle ne contient pas de données : chaque accès à la vue ré-exécute la requête sous-jacente. Les vues constituent l’un des mécanismes les plus utiles du SQL pour structurer un schéma, simplifier l’accès aux données et appliquer des règles de sécurité.

Définition 93

Une vue (view) est un objet du schéma relationnel défini par une requête SELECT. Elle se comporte comme une table virtuelle : on peut l’interroger avec SELECT, la joindre à d’autres tables, et, dans certaines conditions, y écrire des données. Elle ne stocke aucune ligne sur le disque.

Créer une vue simple

La syntaxe de base est :

CREATE VIEW nom_vue AS
SELECT ...;

On peut ensuite faire :

SELECT * FROM nom_vue WHERE condition;

Pour remplacer une vue existante sans la détruire d’abord :

CREATE OR REPLACE VIEW nom_vue AS
SELECT ...;

Pour la supprimer :

DROP VIEW nom_vue;
-- ou, plus prudemment :
DROP VIEW IF EXISTS nom_vue;

Supposons une table employes avec les colonnes id, nom, prenom, salaire, departement_id, et une table departements avec id et nom. On crée une vue employes_details qui expose la jointure :

conn = sqlite3.connect(":memory:")
cur = conn.cursor()

cur.executescript("""
CREATE TABLE departements (
    id   INTEGER PRIMARY KEY,
    nom  TEXT NOT NULL
);

CREATE TABLE employes (
    id              INTEGER PRIMARY KEY,
    nom             TEXT NOT NULL,
    prenom          TEXT NOT NULL,
    salaire         REAL,
    departement_id  INTEGER REFERENCES departements(id)
);

INSERT INTO departements VALUES (1,'Informatique'),(2,'Marketing'),(3,'RH');
INSERT INTO employes VALUES
  (1,'Dupont','Alice',  4200, 1),
  (2,'Martin','Bob',   3800, 2),
  (3,'Durand','Carole',4500, 1),
  (4,'Bernard','Denis',3200, 3),
  (5,'Petit','Eva',    4100, 2);

CREATE VIEW employes_details AS
    SELECT e.id,
           e.nom || ' ' || e.prenom  AS nom_complet,
           d.nom                     AS departement,
           e.salaire
    FROM   employes e
    JOIN   departements d ON d.id = e.departement_id;
""")
conn.commit()

pd.read_sql("SELECT * FROM employes_details", conn)

	id	nom_complet	departement	salaire
0	1	Dupont Alice	Informatique	4200.0
1	2	Martin Bob	Marketing	3800.0
2	3	Durand Carole	Informatique	4500.0
3	4	Bernard Denis	RH	3200.0
4	5	Petit Eva	Marketing	4100.0

Remarque 48

Les vues offrent trois avantages principaux :

• Simplification : une jointure complexe écrite une seule fois devient accessible par un nom court.

• Contrôle d’accès : on peut accorder à un utilisateur le droit de lire la vue sans lui donner accès aux tables sous-jacentes.

• Stabilité de l’interface : si le schéma évolue (ajout d’une colonne, renommage d’une table), on peut adapter la vue sans modifier le code applicatif qui l’utilise.

Limites des vues simples

Remarque 49

Les vues SQL présentent plusieurs limitations importantes :

• Pas de paramètres : une vue est une requête fixe. On ne peut pas passer d’arguments à une vue comme on le ferait avec une fonction. Pour ce besoin, on utilise des fonctions de table (table-valued functions) disponibles dans PostgreSQL ou SQL Server.

• Performance : chaque accès ré-exécute la requête. Sur des tables volumineuses avec des jointures coûteuses, cela peut être problématique (voir les vues matérialisées).

• Complexité des mises à jour : les vues sur plusieurs tables ou avec des agrégats ne sont généralement pas modifiables.

Vues mises à jour (updatable views)

Dans certaines conditions, une vue peut accepter des INSERT, UPDATE et DELETE qui se répercutent sur la table sous-jacente.

Définition 94

Une vue mise à jour (updatable view) est une vue sur laquelle des opérations DML (INSERT, UPDATE, DELETE) sont autorisées et se propagent à la table de base. Pour qu’une vue soit automatiquement mise à jour, elle doit satisfaire des conditions strictes : pas de DISTINCT, pas de fonctions d’agrégat, pas de GROUP BY, pas de HAVING, pas de UNION, et la requête doit porter sur une seule table de base.

Exemple : la vue suivante est mise à jour car elle ne porte que sur une table sans transformation :

CREATE VIEW employes_info AS
    SELECT id, nom, prenom, salaire
    FROM   employes;

-- Cette mise à jour se propage à la table employes :
UPDATE employes_info SET salaire = 4300 WHERE id = 2;

Remarque 50

La clause WITH CHECK OPTION garantit que les modifications effectuées via la vue respectent le filtre WHERE de celle-ci. Sans elle, on pourrait insérer une ligne qui ne serait plus visible dans la vue après insertion — ce qui est souvent indésirable.

CREATE VIEW employes_informatique AS
    SELECT * FROM employes WHERE departement_id = 1
    WITH CHECK OPTION;

-- Cette insertion échoue car departement_id = 2 ne satisfait pas le filtre :
-- INSERT INTO employes_informatique VALUES (6, 'Test', 'X', 3000, 2);

Vues matérialisées

Définition 95

Une vue matérialisée (materialized view) est une vue dont le résultat est physiquement stocké sur le disque. Elle se comporte comme une table : les requêtes la parcourent directement sans ré-exécuter la requête sous-jacente. En contrepartie, les données peuvent être décalées par rapport aux tables sources ; il faut rafraîchir explicitement la vue.

Les vues matérialisées sont propres à PostgreSQL (et à Oracle). SQLite n’en dispose pas. Les exemples ci-dessous sont illustratifs et supposent une connexion PostgreSQL.

-- Création (PostgreSQL) :
CREATE MATERIALIZED VIEW stats_departement AS
    SELECT   d.nom          AS departement,
             COUNT(e.id)    AS nb_employes,
             AVG(e.salaire) AS salaire_moyen
    FROM     employes e
    JOIN     departements d ON d.id = e.departement_id
    GROUP BY d.nom;

-- Rafraîchissement :
REFRESH MATERIALIZED VIEW stats_departement;

-- Rafraîchissement sans bloquer les lectures :
REFRESH MATERIALIZED VIEW CONCURRENTLY stats_departement;

Remarque 51

On peut créer des index sur une vue matérialisée, exactement comme sur une table ordinaire. C’est l’intérêt principal : une agrégation coûteuse est calculée une fois, indexée, et ensuite accessible en millisecondes.

CREATE INDEX ON stats_departement (departement);

Triggers : réagir aux événements

Un trigger est un programme stocké qui s’exécute automatiquement en réponse à un événement sur une table (INSERT, UPDATE, DELETE) ou, pour INSTEAD OF, sur une vue.

Définition 96

Un trigger (ou déclencheur) est une procédure stockée associée à une table ou une vue, qui s’exécute automatiquement lorsqu’un événement DML (INSERT, UPDATE, DELETE) se produit. Le trigger spécifie :

• Quand il s’exécute : BEFORE (avant l’opération), AFTER (après), ou INSTEAD OF (à la place — sur une vue).

• Sur quel événement : INSERT, UPDATE, DELETE, ou une combinaison.

• Granularité : FOR EACH ROW (trigger de ligne, s’exécute pour chaque ligne affectée) ou FOR EACH STATEMENT (trigger d’instruction, s’exécute une fois par opération).

Triggers BEFORE et AFTER

Exemple 34

Un trigger BEFORE INSERT peut valider ou modifier les données avant leur insertion. Un trigger AFTER INSERT peut journaliser l’opération.

-- PostgreSQL — validation BEFORE :
CREATE OR REPLACE FUNCTION valider_salaire()
RETURNS trigger AS $$
BEGIN
    IF NEW.salaire < 0 THEN
        RAISE EXCEPTION 'Le salaire ne peut pas être négatif';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trig_valider_salaire
BEFORE INSERT OR UPDATE ON employes
FOR EACH ROW EXECUTE FUNCTION valider_salaire();

Triggers INSTEAD OF

Définition 97

Un trigger INSTEAD OF s’applique uniquement aux vues (pas aux tables). Il remplace l’opération DML par la logique du trigger. C’est le mécanisme pour rendre une vue non-modifiable (portant sur plusieurs tables) capable d’accepter des insertions ou des mises à jour.

Exemple illustratif (PostgreSQL) :

CREATE OR REPLACE FUNCTION insert_employe_detail()
RETURNS trigger AS $$
BEGIN
    INSERT INTO employes (nom, prenom, salaire, departement_id)
    VALUES (NEW.nom, NEW.prenom, NEW.salaire,
            (SELECT id FROM departements WHERE nom = NEW.departement));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trig_insert_employe_detail
INSTEAD OF INSERT ON employes_details
FOR EACH ROW EXECUTE FUNCTION insert_employe_detail();

Triggers de ligne vs triggers d’instruction

Remarque 52

• Trigger de ligne (FOR EACH ROW) : exécuté une fois par ligne affectée par l’opération. Disponible dans PostgreSQL, Oracle, MySQL. Dans le corps du trigger PostgreSQL, on accède à NEW (nouvelle valeur) et OLD (ancienne valeur).

• Trigger d’instruction (FOR EACH STATEMENT) : exécuté une seule fois pour toute l’opération, quel que soit le nombre de lignes affectées. Utile pour des actions globales (journalisation du fait qu’une opération a eu lieu, sans détail par ligne).

SQLite ne supporte que les triggers de ligne (FOR EACH ROW), et sa syntaxe est plus simple (pas de fonctions séparées).

Cas d’usage pratiques

Audit log

L’un des cas d’usage les plus courants des triggers est la journalisation des modifications : chaque fois qu’une ligne est insérée, modifiée ou supprimée, on enregistre l’opération dans une table d’audit.

Exemple 35

On crée une table commandes et une table audit_commandes, puis un trigger SQLite qui trace chaque insertion et mise à jour.

cur.executescript("""
CREATE TABLE commandes (
    id         INTEGER PRIMARY KEY,
    client     TEXT NOT NULL,
    montant    REAL NOT NULL,
    statut     TEXT DEFAULT 'en_attente'
);

CREATE TABLE audit_commandes (
    id_audit    INTEGER PRIMARY KEY AUTOINCREMENT,
    operation   TEXT NOT NULL,       -- 'INSERT' ou 'UPDATE'
    id_commande INTEGER,
    ancien_statut TEXT,
    nouveau_statut TEXT,
    ts          TEXT DEFAULT (datetime('now'))
);

-- Trigger AFTER INSERT
CREATE TRIGGER trig_audit_insert
AFTER INSERT ON commandes
FOR EACH ROW
BEGIN
    INSERT INTO audit_commandes (operation, id_commande, nouveau_statut)
    VALUES ('INSERT', NEW.id, NEW.statut);
END;

-- Trigger AFTER UPDATE
CREATE TRIGGER trig_audit_update
AFTER UPDATE ON commandes
FOR EACH ROW
BEGIN
    INSERT INTO audit_commandes
        (operation, id_commande, ancien_statut, nouveau_statut)
    VALUES ('UPDATE', OLD.id, OLD.statut, NEW.statut);
END;
""")
conn.commit()

# Insertion de commandes
cur.executescript("""
INSERT INTO commandes (client, montant, statut) VALUES ('Alice', 150.00, 'en_attente');
INSERT INTO commandes (client, montant, statut) VALUES ('Bob',   320.50, 'en_attente');
UPDATE commandes SET statut = 'validee'  WHERE id = 1;
UPDATE commandes SET statut = 'expediee' WHERE id = 1;
UPDATE commandes SET statut = 'validee'  WHERE id = 2;
""")
conn.commit()

print("=== Table commandes ===")
display(pd.read_sql("SELECT * FROM commandes", conn))
print("\n=== Journal d'audit ===")
display(pd.read_sql("SELECT * FROM audit_commandes", conn))

=== Table commandes ===

	id	client	montant	statut
0	1	Alice	150.0	expediee
1	2	Bob	320.5	validee

=== Journal d'audit ===

	id_audit	operation	id_commande	ancien_statut	nouveau_statut	ts
0	1	INSERT	1	NaN	en_attente	2026-03-31 22:47:09
1	2	INSERT	2	NaN	en_attente	2026-03-31 22:47:09
2	3	UPDATE	1	en_attente	validee	2026-03-31 22:47:09
3	4	UPDATE	1	validee	expediee	2026-03-31 22:47:09
4	5	UPDATE	2	en_attente	validee	2026-03-31 22:47:09

Calcul automatique

Exemple 36

Un trigger BEFORE INSERT peut calculer automatiquement un champ dérivé. Par exemple, calculer un total TTC à partir du montant HT et d’un taux de TVA.

cur.executescript("""
CREATE TABLE factures (
    id       INTEGER PRIMARY KEY,
    montant_ht REAL NOT NULL,
    taux_tva   REAL NOT NULL DEFAULT 0.20,
    montant_ttc REAL
);

CREATE TRIGGER trig_calcul_ttc
BEFORE INSERT ON factures
FOR EACH ROW
BEGIN
    SELECT RAISE(ABORT, 'montant_ht doit être positif')
    WHERE NEW.montant_ht <= 0;
    UPDATE factures SET montant_ttc = NEW.montant_ht * (1 + NEW.taux_tva)
    WHERE id = NEW.id;
END;
""")
conn.commit()

# Dans SQLite, BEFORE INSERT ne peut pas modifier NEW directement,
# on simule le calcul lors de l'insertion :
cur.execute("""
    INSERT INTO factures (montant_ht, taux_tva, montant_ttc)
    VALUES (?, ?, ?)
""", (200.0, 0.20, 200.0 * 1.20))
cur.execute("""
    INSERT INTO factures (montant_ht, taux_tva, montant_ttc)
    VALUES (?, ?, ?)
""", (500.0, 0.055, 500.0 * 1.055))
conn.commit()

pd.read_sql("SELECT * FROM factures", conn)

	id	montant_ht	taux_tva	montant_ttc
0	1	200.0	0.200	240.0
1	2	500.0	0.055	527.5

Validation métier

Remarque 53

Les triggers permettent de centraliser des règles métier complexes que les contraintes CHECK ne peuvent pas exprimer : vérifications inter-tables, conditions dépendant d’agrégats, règles temporelles (plages horaires d’ouverture, séquences d’états autorisées). Dans SQLite, on utilise RAISE(ABORT, 'message') pour annuler l’opération.

# Vérification de la séquence d'états : une commande 'expediee' ne peut
# pas revenir à 'en_attente'
cur.executescript("""
CREATE TRIGGER trig_statut_valide
BEFORE UPDATE ON commandes
FOR EACH ROW
BEGIN
    SELECT RAISE(ABORT, 'Transition de statut interdite')
    WHERE OLD.statut = 'expediee' AND NEW.statut = 'en_attente';
END;
""")
conn.commit()

# Tenter une transition interdite
try:
    cur.execute("UPDATE commandes SET statut = 'en_attente' WHERE id = 1")
    conn.commit()
except (sqlite3.OperationalError, sqlite3.IntegrityError) as e:
    print(f"Erreur attendue : {e}")

# L'état n'a pas changé
pd.read_sql("SELECT id, client, statut FROM commandes", conn)

Erreur attendue : Transition de statut interdite

	id	client	statut
0	1	Alice	expediee
1	2	Bob	validee

Visualisation : flux d’un trigger

Show code cell source

Hide code cell source

fig, ax = plt.subplots(figsize=(12, 8))
ax.set_xlim(0, 12)
ax.set_ylim(0, 10)
ax.axis('off')
ax.set_title("Flux d'exécution d'un trigger AFTER INSERT", fontsize=14,
             fontweight='bold', pad=12)

palette = sns.color_palette("muted", 6)
c_blue   = palette[0]
c_green  = palette[2]
c_orange = palette[4]
c_red    = palette[3]
c_gray   = '#95a5a6'

def box(ax, x, y, w, h, color, text, fontsize=10):
    rect = mpatches.FancyBboxPatch(
        (x - w/2, y - h/2), w, h,
        boxstyle="round,pad=0.1", linewidth=1.5,
        edgecolor='white', facecolor=color, alpha=0.85, zorder=3
    )
    ax.add_patch(rect)
    ax.text(x, y, text, ha='center', va='center', fontsize=fontsize,
            color='white', fontweight='bold', zorder=4, wrap=True,
            multialignment='center')

def arrow(ax, x1, y1, x2, y2, label='', color='#555'):
    ax.annotate('', xy=(x2, y2), xytext=(x1, y1),
                arrowprops=dict(arrowstyle='->', color=color, lw=2), zorder=2)
    if label:
        mx, my = (x1+x2)/2, (y1+y2)/2
        ax.text(mx + 0.15, my, label, fontsize=8.5, color=color, va='center')

# Boîtes
box(ax,  6, 9.0, 3.2, 0.8, c_blue,   "Application envoie INSERT")
box(ax,  6, 7.5, 3.2, 0.8, c_blue,   "Moteur SQL reçoit la requête")
box(ax,  2, 5.8, 3.2, 0.8, c_orange, "Trigger BEFORE\n(si défini)")
box(ax,  6, 5.8, 3.2, 0.8, c_green,  "Insertion dans la table")
box(ax, 10, 5.8, 3.2, 0.8, c_orange, "Trigger AFTER\n(si défini)")
box(ax,  6, 4.0, 3.2, 0.8, c_green,  "Commit / Rollback")
box(ax, 10, 4.0, 3.2, 0.8, c_gray,   "Table d'audit\nmise à jour")
box(ax,  6, 2.2, 3.2, 0.8, c_blue,   "Réponse à l'application")

# Flèches
arrow(ax, 6, 8.6, 6, 7.9)
arrow(ax, 6, 7.1, 2, 6.2, "BEFORE ?")
arrow(ax, 2, 5.4, 6, 6.2)
arrow(ax, 6, 7.1, 6, 6.2, "Insertion")
arrow(ax, 6, 5.4, 6, 4.4)
arrow(ax, 6, 5.4, 10, 6.2, "AFTER ?")
arrow(ax, 10, 5.4, 10, 4.4)
arrow(ax, 10, 3.6, 6, 4.4)
arrow(ax, 6, 3.6, 6, 2.6)

# Légende
patches = [
    mpatches.Patch(color=c_blue,   label='Flux principal SQL'),
    mpatches.Patch(color=c_green,  label='Opération sur la table'),
    mpatches.Patch(color=c_orange, label='Trigger'),
    mpatches.Patch(color=c_gray,   label='Effet de bord (audit…)'),
]
ax.legend(handles=patches, loc='lower left', fontsize=9, framealpha=0.8)
plt.savefig("_build_trigger_flux.png", dpi=120, bbox_inches='tight')
plt.show()

Suppression et gestion des triggers

Remarque 54

Pour supprimer un trigger :

DROP TRIGGER IF EXISTS nom_trigger;

Dans SQLite, les triggers sont listés via :

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

Dans PostgreSQL :

SELECT trigger_name, event_manipulation, action_timing
FROM   information_schema.triggers
WHERE  event_object_table = 'commandes';

Il est conseillé de documenter précisément les triggers actifs sur un schéma : des triggers implicites peuvent être une source de bugs difficiles à diagnostiquer.

# Lister les triggers créés dans notre base SQLite
pd.read_sql(
    "SELECT name, tbl_name, sql FROM sqlite_master WHERE type='trigger'",
    conn
)[['name', 'tbl_name']]

	name	tbl_name
0	trig_audit_insert	commandes
1	trig_audit_update	commandes
2	trig_calcul_ttc	factures
3	trig_statut_valide	commandes

Résumé

Remarque 55

Ce chapitre a présenté deux mécanismes complémentaires pour structurer et automatiser la logique dans une base de données :

Vues :

• Une vue est une requête stockée sous un nom, accessible comme une table virtuelle.

• Elle simplifie les requêtes, sécurise l’accès et stabilise l’interface applicative.

• Une vue est mise à jour si elle porte sur une seule table sans agrégat ni DISTINCT.

• WITH CHECK OPTION garantit la cohérence lors des écritures via la vue.

• Les vues matérialisées (PostgreSQL) stockent physiquement le résultat et peuvent être indexées.

Triggers :

• Un trigger réagit à un événement (INSERT, UPDATE, DELETE) sur une table ou une vue.

• BEFORE : s’exécute avant l’opération — permet de valider ou modifier les données.

• AFTER : s’exécute après — idéal pour la journalisation et les effets de bord.

• INSTEAD OF : sur une vue — remplace l’opération standard.

• Les triggers de ligne (FOR EACH ROW) donnent accès à NEW et OLD.

• Cas d’usage typiques : audit log, calcul automatique de champs dérivés, validation de règles métier inter-tables, contrôle des séquences d’états.

conn.close()