BUT2/BD/Lekpa/TP02/tp02_2.txt

execice 1

create or replace function fn_points_fidelite(Id_client Number)
return Number
IS
    V_pt_fidelite number;
Begin
    Select sum(L.prix_total) Into V_pt_fidelite
    from ligne_Commande L, Commande CO
    where L.commande_id = CO.id AND CO.client_id = Id_client;
    V_pt_fidelite := floor(V_pt_fidelite/10);
    return V_pt_fidelite;
Exception
    when No_data_found then
    return 0;
end fn_points_fidelite;

select fn_points_fidelite(16) from dual;


declare
    cursor C_client IS Select id, nom from client;
    V_points number;
Begin
    for v_client in c_client
    loop
        v_points := fn_points_fidelite(v_client.id);
        if (v_points < 2) then
            DBMS_OUTPUT.PUT_LINE(v_client.nom || 'à' || v_points || 'point de fidelité' );
        else
            DBMS_OUTPUT.PUT_LINE(v_client.nom || 'à' || v_points || 'points de fidelités' );
        end if;
    end loop;
end;



exercice 2

create or replace trigger trg_DeleteCommande
Before delete
on Commande
for each row
begin
    delete from ligne_commande L where L.commande_id = :old.id;
end trg_DeleteCommande;




exercice 3

create or replace trigger trg_DeleteClient
Before delete
on Client
for each row
begin
    delete from Commande CO where CO.client_id = :old.id;
end trg_DeleteCommande;


exercice 4

create table Client_His as select * from Client;
delete from Client_His;

create or replace trigger trg_DeleteClient
before delete
on Client
for each row
begin
    delete from Commande CO where CO.client_id = :old.id;
    insert into Client_His values (:old.id, :old.prenom, :old.nom, :old.email, :old.ville);
end trg_DeleteCommande;


exercice 5

la contrainte de suppression en cascade



exercice 6

// faire manuellement (car la suppression ne marche pas)
drop table Client cascade constraints;
drop table Commande cascade constraints;
drop table ligne_commande cascade constraints;

CREATE TABLE client (
    id INT PRIMARY KEY,
    prenom VARCHAR(255),
    nom VARCHAR(255),
    email VARCHAR(255),
    ville VARCHAR(255)
);

CREATE TABLE commande (
    id INT PRIMARY KEY,
    client_id INT,
    date_achat DATE,
    reference VARCHAR(255),
    FOREIGN KEY (client_id) REFERENCES client(id) ON DELETE CASCADE
);

CREATE TABLE ligne_commande (
    id INT PRIMARY KEY,
    commande_id INT,
    produit_id INT,
    quantite INT,
    prix_total DECIMAL(10, 2),
    FOREIGN KEY (commande_id) REFERENCES commande(id) ON DELETE CASCADE,
    FOREIGN KEY (produit_id) REFERENCES produit(id)
);



Exercice 7

create table CommandeAuditLog(
    Utilisateur VARCHAR(255),
    ActionSQL VARCHAR(3) CONSTRAINT type_enumere_requette CHECK (ActionSQL IN ('INS', 'DEL', 'UDP')),
    DateMAJ DATE,
    ActCol VARCHAR(255)
);

CREATE OR REPLACE TRIGGER insertionCommandeAuditLog
AFTER INSERT OR DELETE OR UPDATE
ON Commande
FOR EACH ROW
DECLARE 
    CURSOR c_attribut IS SELECT column_name FROM user_tab_columns WHERE table_name = 'COMMANDE';
    nom_colonne VARCHAR(255);
BEGIN
    IF INSERTING THEN
        INSERT INTO CommandeAuditLog VALUES (USER, 'INS', SYSDATE, null);
    ELSIF DELETING THEN
        INSERT INTO CommandeAuditLog VALUES (USER, 'DEL', SYSDATE, null);
    ELSIF UPDATING THEN
            OPEN c_attribut;
        LOOP
            FETCH c_attribut INTO nom_colonne ;
            IF UPDATING(nom_colonne) THEN
                INSERT INTO CommandeAuditLog VALUES (USER, 'UDP', SYSDATE, nom_colonne);
            END IF;          
            EXIT WHEN c_attribut%NOTFOUND;
        END LOOP;
        CLOSE c_attribut;
    END IF;
END combinedTrigger;