# Déclencheurs

Il est possible d'utiliser des fonctions pour des déclencheurs, ces fonctions peuvent alors s'exécuter lors d'opérations d'écriture sur des tables.
Par exemple, avec une table d'achats (achat d'un produit à un prix unitaire défini en une quantité donnée), on pourrait vouloir mettre à jour la quantité restante du produit et calculer automatiquement le prix total de l'achat.

On va réutiliser la table produit mais en ajoutant une colonne pour la quantite:

ALTER TABLE produit ADD COLUMN quantite_stock INT NOT NULL DEFAULT 0;

On ajoute un nouveau produit avec une quantite positive dont on va ensuite récupérer l'identifiant pour les requêtes suivantes:

INSERT INTO produit (id, nom, prix, date_creation, quantite_stock)
VALUES (nextval('seq_produit'), 'bloc-notes', 15, NOW(), 7);

-- ID: 5
SELECT * FROM produit ORDER BY id DESC LIMIT 1;

On crée ensuite la table des achats avec sa séquence:

CREATE SEQUENCE seq_achat_id INCREMENT BY 1 MINVALUE 1 START 1;
CREATE TABLE achat (
    id INT NOT NULL,
    produit_id INT NOT NULL,
    prix_unitaire INT NOT NULL,
    quantite INT NOT NULL,
    prix_total INT NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (produit_id) REFERENCES produit(id)
);

On crée enfin une fonction qui doit avoir un type de retour TRIGGER. La fonction pourra utiliser les variables OLD et NEW qui font référence aux anciennes et nouvelles valeurs de la ligne modifiée. Cette fonction va s'exécuter avant les opérations INSERT et UPDATE, les variables OLD et NEW seront toutes les deux disponibles lors d'un UPDATE mais seule OLD sera nulle en INSERT puisqu'il s'agira d'une nouvelle ligne.

CREATE OR REPLACE FUNCTION t_write_achat()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- Mise à jour du stock restant
    IF OLD IS NULL THEN
        UPDATE produit SET quantite_stock = quantite_stock - NEW.quantite WHERE id = NEW.produit_id;
    ELSIF OLD.quantite != NEW.quantite THEN
        UPDATE produit SET quantite_stock = quantite_stock - (NEW.quantite - OLD.quantite) WHERE id = NEW.produit_id;
    END IF;

    -- calcul automatique du prix total
    NEW.prix_total = NEW.prix_unitaire * NEW.quantite;
    RETURN NEW;
END;
$$;

Note: le calcul de la colonne prix_total sert ici de démonstration, l'utilisation d'une colonne générée serait probablement plus appropriée.

On utilise alors la fonction pour déclarer un déclencheur en précisant de l'exécuter avant l'opération (BEFORE), les opérations concernées (INSERT OR UPDATE), la table concernée (ON achat), et la fonction (EXECUTE FUNCTION t_write_achat()):

CREATE OR REPLACE TRIGGER write_achat
    BEFORE INSERT OR UPDATE ON achat
    FOR EACH ROW
    EXECUTE FUNCTION t_write_achat();

Note: l'utilisation de FOR EACH ROW indique que la fonction doit s'exécuter par ligne affectée et non pas par requête.

On peut maintenant tester le déclencheur avec l'insertion d'un premier achat en réutilisant l'identifiant de produit récupéré un peu plus tôt:

INSERT INTO achat (id, produit_id, prix_unitaire, quantite, prix_total)
VALUES (nextval('seq_achat_id'), 5, 15, 2, NULL);

La valeur de la colonne prix_total ne peut pas être NULL, mais c'est rendu possible grâce au déclencheur qui affecte automatiquement sa valeur.

Vérifiez que le stock et le prix soit mis à jour:

SELECT
    p.nom
    , p.quantite_stock
    , a.prix_unitaire
    , a.quantite
    , a.prix_total
FROM achat a
    INNER JOIN produit p on p.id = a.produit_id;

Effectuez une mise à jour de la quantité achetée:

UPDATE achat
SET quantite = 3
WHERE id = 1;

Réexécutez la requête précédente pour vérifier que les données sont à jour.