Triggers et Curseurs PL/pgSQL

Les Triggers (Déclencheurs)

Blocs PL/pgSQL qui s'exécutent automatiquement

Un trigger est un bloc PL/pgSQL qui s'exécute automatiquement en réponse à un événement (INSERT, UPDATE, DELETE) sur une table. Utile pour l'audit, la validation et la synchronisation.

1 Types de Triggers

🔵 BEFORE

S'exécute AVANT l'opération

✓ Peut modifier les données
✓ Validation des données
✓ Calculs automatiques

🟠 AFTER

S'exécute APRÈS l'opération

✓ Audit et historique
✓ Notifications
✓ Synchronisation

⚡ Trigger BEFORE

-- Trigger qui met à jour automatiquement la date de modification
CREATE OR REPLACE FUNCTION maj_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.last_modified := CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_update_timestamp
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION maj_timestamp();
-- Trigger de validation
CREATE OR REPLACE FUNCTION validate_salary()
RETURNS TRIGGER AS $$
BEGIN
    -- Vérifier que le salaire est positif
    IF NEW.salary < 0 THEN
        RAISE EXCEPTION 'Le salaire ne peut pas être négatif';
    END IF;
    -- Vérifier le salaire maximum
    IF NEW.salary > 50000 THEN
        RAISE EXCEPTION 'Le salaire ne peut pas dépasser 50000';
    END IF;
    -- Calculer une commission par défaut
    IF NEW.commission_pct IS NULL THEN
        NEW.commission_pct := 0.05;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_validate_salary
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION validate_salary();

📝 Trigger AFTER (Audit)

-- Créer une table d'audit
CREATE TABLE employee_audit (
    audit_id SERIAL PRIMARY KEY,
    employee_id INTEGER,
    action VARCHAR(10),
    old_salary NUMERIC,
    new_salary NUMERIC,
    changed_by VARCHAR(50),
    changed_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Trigger d'audit
CREATE OR REPLACE FUNCTION audit_salary_change()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO employee_audit (
        employee_id,
        action,
        old_salary,
        new_salary,
        changed_by,
        changed_date
    ) VALUES (
        NEW.employee_id,
        'UPDATE',
        OLD.salary,
        NEW.salary,
        CURRENT_USER,
        CURRENT_TIMESTAMP
    );
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_audit_salary
AFTER UPDATE OF salary ON employees
FOR EACH ROW
EXECUTE FUNCTION audit_salary_change();

💡 :NEW et :OLD

NEW - Les nouvelles valeurs (après INSERT/UPDATE)
OLD - Les anciennes valeurs (avant UPDATE/DELETE)
Dans un BEFORE trigger, vous pouvez modifier NEW
OLD est en lecture seule

2 Triggers au Niveau Table (Statement)

S'exécute une seule fois pour l'instruction complète, même si elle affecte plusieurs lignes.

-- Trigger au niveau table (sans FOR EACH ROW)
CREATE OR REPLACE FUNCTION log_operations()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO operation_log (
        table_name,
        operation,
        operation_date,
        user_name
    ) VALUES (
        'employees',
        TG_OP,
        CURRENT_TIMESTAMP,
        CURRENT_USER
    );
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_log_operations
AFTER INSERT OR UPDATE OR DELETE ON employees
EXECUTE FUNCTION log_operations();
-- Ce trigger s'exécute UNE SEULE FOIS même si on fait:
UPDATE employees SET salary = salary * 1.1
WHERE department_id = 50;  -- Affecte plusieurs lignes

🤔 Row vs Statement

FOR EACH ROW: S'exécute pour chaque ligne affectée
Sans FOR EACH ROW: S'exécute une seule fois par instruction

Les Curseurs

Parcourir plusieurs lignes de résultats

Un curseur est un pointeur vers un ensemble de résultats. Il permet de parcourir ligne par ligne les résultats d'une requête SELECT.

3 Curseurs Explicites

📋 Étapes d'utilisation

1. DECLARE

Déclarer

2. OPEN

Ouvrir

3. FETCH

Récupérer

4. CLOSE

Fermer

DECLARE
    -- 1. Déclarer le curseur
    cur_employees CURSOR FOR
        SELECT employee_id, first_name, last_name, salary
        FROM employees
        WHERE department_id = 50;
    -- Variables pour stocker les données
    v_emp_id employees.employee_id%TYPE;
    v_first_name employees.first_name%TYPE;
    v_last_name employees.last_name%TYPE;
    v_salary employees.salary%TYPE;
BEGIN
    -- 2. Ouvrir le curseur
    OPEN cur_employees;
    -- 3. Parcourir les résultats
    LOOP
        FETCH cur_employees INTO v_emp_id, v_first_name, v_last_name, v_salary;
        -- Sortir si plus de données
        EXIT WHEN NOT FOUND;
        -- Traiter chaque ligne
        RAISE NOTICE '% %: %', v_first_name, v_last_name, v_salary;
    END LOOP;
    -- 4. Fermer le curseur
    CLOSE cur_employees;
END;

🎯 Curseur avec FOR LOOP (plus simple)

DO $$
DECLARE
    cur_employees CURSOR FOR
        SELECT employee_id, first_name, last_name, salary
        FROM employees
        WHERE department_id = 50;
    emp_rec RECORD;
BEGIN
    -- FOR LOOP gère automatiquement OPEN, FETCH et CLOSE
    FOR emp_rec IN cur_employees LOOP
        RAISE NOTICE '% %: %', 
            emp_rec.first_name,
            emp_rec.last_name,
            emp_rec.salary;
        -- Exemple: augmenter le salaire
        UPDATE employees
        SET salary = salary * 1.10
        WHERE employee_id = emp_rec.employee_id;
    END LOOP;
    COMMIT;
END;
$$;

✨ Avantages du FOR LOOP

Plus simple et plus court
Gère automatiquement l'ouverture et la fermeture
Moins de risque d'erreur
Code plus lisible

4 Curseurs avec Paramètres

DO $$
DECLARE
    -- Curseur avec paramètre
    cur_dept_employees CURSOR(p_dept_id INTEGER) FOR
        SELECT first_name, last_name, salary
        FROM employees
        WHERE department_id = p_dept_id
        ORDER BY salary DESC;
    v_total_salary NUMERIC := 0;
    v_count INTEGER := 0;
    emp_rec RECORD;
BEGIN
    -- Utiliser le curseur avec différents départements
    RAISE NOTICE '=== Département 50 ===';
    FOR emp_rec IN cur_dept_employees(50) LOOP
        RAISE NOTICE '%: %', emp_rec.first_name, emp_rec.salary;
        v_total_salary := v_total_salary + emp_rec.salary;
        v_count := v_count + 1;
    END LOOP;
    RAISE NOTICE 'Moyenne: %', v_total_salary / v_count;
    -- Réinitialiser et utiliser avec un autre département
    v_total_salary := 0;
    v_count := 0;
    RAISE NOTICE E'\n=== Département 60 ===';
    FOR emp_rec IN cur_dept_employees(60) LOOP
        RAISE NOTICE '%: %', emp_rec.first_name, emp_rec.salary;
        v_total_salary := v_total_salary + emp_rec.salary;
        v_count := v_count + 1;
    END LOOP;
    RAISE NOTICE 'Moyenne: %', v_total_salary / v_count;
END;
$$;

5 Attributs de Curseur

FOUND

TRUE si FETCH a récupéré une ligne

NOT FOUND

TRUE si FETCH n'a pas récupéré de ligne

ISOPEN

TRUE si le curseur est ouvert (PostgreSQL gère automatiquement)

ROW_COUNT

Nombre de lignes récupérées (via GET DIAGNOSTICS)

DO $$
DECLARE
    cur_emp CURSOR FOR SELECT * FROM employees;
    v_emp employees%ROWTYPE;
    v_count INTEGER;
BEGIN
    OPEN cur_emp;
    RAISE NOTICE 'Curseur ouvert';
    LOOP
        FETCH cur_emp INTO v_emp;
        EXIT WHEN NOT FOUND;
        GET DIAGNOSTICS v_count = ROW_COUNT;
        RAISE NOTICE 'Ligne %: %', v_count, v_emp.last_name;
    END LOOP;
    GET DIAGNOSTICS v_count = ROW_COUNT;
    RAISE NOTICE 'Total: % lignes', v_count;
    CLOSE cur_emp;
END;
$$;

💪 Exercice Pratique

Créez un trigger et utilisez un curseur

Partie 1: Trigger

Créer un trigger qui empêche de supprimer un département ayant des employés
Utiliser RAISE_APPLICATION_ERROR si des employés existent

Partie 2: Curseur

Créer un curseur qui parcourt tous les départements
Pour chaque département, afficher le nombre d'employés et le salaire moyen

-- Partie 1: Trigger
CREATE OR REPLACE FUNCTION prevent_dept_delete()
RETURNS TRIGGER AS $$
DECLARE
    v_count INTEGER;
BEGIN
    SELECT COUNT(*)
    INTO v_count
    FROM employees
    WHERE department_id = OLD.department_id;
    IF v_count > 0 THEN
        RAISE EXCEPTION 'Impossible de supprimer le département: % employé(s) présent(s)', v_count;
    END IF;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_prevent_dept_delete
BEFORE DELETE ON departments
FOR EACH ROW
EXECUTE FUNCTION prevent_dept_delete();
-- Partie 2: Curseur
DO $$
DECLARE
    cur_departments CURSOR FOR
        SELECT department_id, department_name
        FROM departments;
    v_emp_count INTEGER;
    v_avg_salary NUMERIC;
    dept_rec RECORD;
BEGIN
    FOR dept_rec IN cur_departments LOOP
        SELECT COUNT(*), AVG(COALESCE(salary, 0))
        INTO v_emp_count, v_avg_salary
        FROM employees
        WHERE department_id = dept_rec.department_id;
        RAISE NOTICE '%: % employé(s), salaire moyen: %',
            dept_rec.department_name,
            v_emp_count,
            ROUND(v_avg_salary, 2);
    END LOOP;
END;
$$;