BD_2FA/TP2/CG_TP2.txt

1. Implémenter les questions du TD
(Cela dépend des questions vues en TD. Tu peux exécuter ici celles qui sont pertinentes pour ton TP.)




2. Trigger trg_DeleteCommande pour gérer la suppression des commandes
Lorsqu’une commande est supprimée, toutes ses lignes de commande associées doivent être supprimées :


CREATE OR REPLACE TRIGGER trg_DeleteCommande
BEFORE DELETE ON Commande
FOR EACH ROW
BEGIN
    -- Supprimer les lignes de commande associées à la commande supprimée
    DELETE FROM ligne_commande WHERE commande_id = :OLD.id;
END;
/




3. Trigger trg_DeleteClient pour gérer la suppression d’un client
Lorsqu’un client est supprimé, toutes ses commandes et leurs lignes de commande doivent être supprimées :


CREATE OR REPLACE TRIGGER trg_DeleteClient
BEFORE DELETE ON Client
FOR EACH ROW
BEGIN
    -- Supprimer les lignes de commande associées aux commandes du client
    DELETE FROM ligne_commande 
    WHERE commande_id IN (SELECT id FROM Commande WHERE client_id = :OLD.id);

    -- Supprimer les commandes du client
    DELETE FROM Commande WHERE client_id = :OLD.id;
END;
/




4. Alternative aux triggers des questions 2 et 3
Plutôt que d’utiliser des triggers, on peut utiliser des contraintes de suppression en cascade (ON DELETE CASCADE) dans la création des tables. Cela garantit que la suppression d’un enregistrement entraîne automatiquement la suppression des enregistrements associés.
Exemple lors de la création des tables :


ALTER TABLE Commande
ADD CONSTRAINT fk_client_commande FOREIGN KEY (client_id) 
REFERENCES Client(id) ON DELETE CASCADE;

ALTER TABLE ligne_commande
ADD CONSTRAINT fk_commande_ligne FOREIGN KEY (commande_id) 
REFERENCES Commande(id) ON DELETE CASCADE;


✅ Avantage : Plus efficace que les triggers, car cela est géré automatiquement par le moteur de la base de données.




5. Historisation des clients supprimés


a) Création de la table Client_His pour stocker les anciens clients :


CREATE TABLE Client_His (
    id INT PRIMARY KEY,
    prenom VARCHAR2(50),
    nom VARCHAR2(50),
    email VARCHAR2(100),
    ville VARCHAR2(50),
    date_suppression DATE DEFAULT SYSDATE
);


b) Modification du trigger trg_DeleteClient pour enregistrer les clients supprimés :


CREATE OR REPLACE TRIGGER trg_DeleteClient
BEFORE DELETE ON Client
FOR EACH ROW
BEGIN
    -- Insérer les informations du client supprimé dans la table Client_His
    INSERT INTO Client_His (id, prenom, nom, email, ville, date_suppression)
    VALUES (:OLD.id, :OLD.prenom, :OLD.nom, :OLD.email, :OLD.ville, SYSDATE);

    -- Supprimer les lignes de commande associées
    DELETE FROM ligne_commande 
    WHERE commande_id IN (SELECT id FROM Commande WHERE client_id = :OLD.id);

    -- Supprimer les commandes du client
    DELETE FROM Commande WHERE client_id = :OLD.id;
END;
/


✅ Avantage : Permet de conserver un historique des clients supprimés.




6. Audit de la table Commande


a) Création de la table CommandeAuditLog :


CREATE TABLE CommandeAuditLog (
    Utilisateur VARCHAR2(100),
    ActionSQL VARCHAR2(3),
    DateMAJ DATE,
    ActCol VARCHAR2(200)
);


b) Trigger trg_AuditCommande pour enregistrer les actions sur Commande :


CREATE OR REPLACE TRIGGER trg_AuditCommande
AFTER INSERT OR DELETE OR UPDATE ON Commande
FOR EACH ROW
DECLARE
    v_action VARCHAR2(3);
    v_actCol VARCHAR2(200);
BEGIN
    -- Déterminer l'action réalisée (INS, DEL, UPD)
    IF INSERTING THEN
        v_action := 'INS';
    ELSIF DELETING THEN
        v_action := 'DEL';
    ELSIF UPDATING THEN
        v_action := 'UPD';

        -- Vérifier quelles colonnes ont été modifiées
        v_actCol := '';
        IF UPDATING('client_id') THEN v_actCol := v_actCol || 'client_id, '; END IF;
        IF UPDATING('date_achat') THEN v_actCol := v_actCol || 'date_achat, '; END IF;
        IF UPDATING('reference') THEN v_actCol := v_actCol || 'reference, '; END IF;

        -- Supprimer la dernière virgule et espace
        v_actCol := RTRIM(v_actCol, ', ');
    END IF;

    -- Insérer les informations dans la table d’audit
    INSERT INTO CommandeAuditLog (Utilisateur, ActionSQL, DateMAJ, ActCol)
    VALUES (USER, v_action, SYSDATE, v_actCol);
END;
/


✅ Avantage : Permet d’avoir un suivi des modifications sur la table Commande.




Résumé des solutions implémentées :

1. Trigger trg_DeleteCommande → Supprime les lignes de commande avant la suppression d'une commande.
2. Trigger trg_DeleteClient → Supprime les commandes et lignes de commande associées à un client.
3. Alternative aux triggers → Utilisation de ON DELETE CASCADE.
4. Historisation des clients → Création de Client_His pour stocker les clients supprimés.
5. Audit des modifications sur Commande → Création de CommandeAuditLog avec un trigger trg_AuditCommande.