Accueil À propos Portfolio Stages Veille Contact

Axel Rayer

Web Developper

Projet scolaire : Utilisation des déclencheurs et procédures stockées


Lors de ma deuxième années de BTS SIO, nous avons du travaillez sur une activité portant sur l'utilisation des déclencheurs et procédures stockées sous PostgreSQL, pour cela nous devions répondre à une quelques questions et enfin synthétiser le tout sur notre Portfolio.

  • Une procédure stockée est en fait une série d'instructions SQL désignée par un nom. Lorsque l'on crée une procédure stockée, on l'enregistre dans la base de données que l'on utilise, au même titre qu'une table par exemple. Une fois la procédure créée, il est possible d'appeler celle-ci, par son nom. Les instructions de la procédure sont alors exécutées.
  • Les triggers (ou déclencheurs) sont des objets de la base de données. Attachés à une table, ils vont déclencher l'exécution d'une instruction, ou d'un bloc d'instructions, lorsqu'une, ou plusieurs lignes sont insérées, supprimées ou modifiées dans la table à laquelle ils sont attachés.


  • Contexte / Modèle conceptuel de données
    Il nous était fournit un modèle conceptuel, il nous à donx fallut en faire un modèle relationnel et ensuite créer la base de données avec les différentes requêtes SQL appropriées (CREATE TABLE, ALTER TABLE etc)

    Porcédure stockées

    Il m'a fallut ensuite créer plusieures procédures stockée pour traiter des données, j'ai utiliser le langage procédural PL/pqSQL :

  • inserer_produit qui prend en paramètres une désignation de produit, un prix unitaire, une quantité et qui insère un nouveau produit dans la base de données. Le numéro de produit sera auto-incrémenté, les quantités (sauf le stock) seront mises à zéro et l'alerte de stock sera initialisée à 1

  • CREATE FUNCTION inserer_produit(designation varcahr, pri_unitaire int, quantite int)
    RETURNS void AS $$
    DELCARE
    BEGIN
    INSERT INTO produit (designation, prix_unitaire, qte_sotck, qte_reserve, qte_commandee, alterte_stock) values (designation, prix_unitaire, quantite), 0,0,1);
    END


  • vider_c_a_passer qui vide la table c_a_passer;

  • CREATE FUNCTION vider_c_a_passer()
    RETURNS INT AS $$
    DELCARE
    BEGIN
    SELECT INTO lignes COUNT (*)
    FROM c_a_passer;
    RETURN lignes;
    TRUNCATE TABLE c_a_passer;
    END


  • extraire_commande qui renvoie toutes les commandes d'un client dont le numéro est passé en paramètre

  • CREATE FUNCTION extraire_commande(numcli int)
    RETURNS SET OFF commande AS
    $BODY$
    DECLARE
    com commande%rowtype%;
    BEGIN;
    FOR com IN SELECT * FORM commande;
    WHERE num_client = numcli
    LOOP
    com.num_com =com.num_com+1;
    RETURN NEXT com;
    END LOOP
    RETURN;
    END


  • reception_produit qui prend en paramètres un numéro de produit, un prix, et une quantité et qui met à jour le prix unitaire moyen de ce produit dans la table produit.

  • CREATE FUNCTION reception_produit(numprod int, prix2 float, qte int)
    RETURNS VOID AS
    DECLARE
    prix float;
    stock int;
    BEGIN
    SELECT prix_unitaire, qte_sotck INTO prix, stock
    FROM produit
    WHERE num_produit =numprod
    UPDATE produit
    SET pri_unitaire = (prix * stock + prix2 * qte) / (qte + stock);
    WHERE num_produit = numprod;
    END



    Triggers

    Il m'a fallut ensuite créer plusieurs déclencheurs pour traiter des données :

  • Lorsqu'un produit est réceptionné (insertion dans la table c_reception), il faut mettre a jour le prix unitaire, la quantité en stock et la quantité commandée (qui diminue si elle n'est pas a 0) dans la table Produit

  • CREATE FUNCTION mise_aJour_produit() RETURNS TRIGGER as $$
    DECLARE
    mon_produit produit%rowtype;
    modif_qte_demandee INT;
    BEGIN
    SELECT INTO mon_produit *
    FROM Produit WHERE num_produit = NEW.num produit;
    UPDATE produit
    SET
    prix_unitaire=((mon_produit.prix_unitaire*mon_produit.qte_stock)+(NEW.prix_unitaire*NEW.qte_recue))/(mon_produit.qte_stock+NEW.qte_recue),qte_stock=(mon produit.gte_stock+NEW.qte_recue) WHERE num_produit=NEW.num_produit;
    IF(mon_produit.qte_commandee - NEW.qte_recue) < 0 THEN modif_qte_demandee:=0;
    ELSE
    modif_qte_demandee:=(mon_produit.qte_commandee - NEW.qte_recue);
    END IF;
    UPDATE produit
    SET qte_commandee=modif_qte_demandee
    WHERE num_produit = NEW.num_produit;
    RETURN NEW;
    END;
    CREATE TRIGGER trigger_insertion_c_reception AFTER INSERT ON c_reception FOR EACH ROW EXECUTE PROCEDURE mise_a_jour_produit();


  • lorsqu'une ligne de commande est insérée, si la quantité en stock à laquelle on soustrait la quantité réservée est suffisante pour satisfaire le client alors on passe le champ possible a 1 dans la ligne de commande et on diminue le stock. Sinon possible reste à 0 et c est la quantité réservée du produit qui augmente. Il faut valoriser le prix de vente. Le prix auquel le client achète est égal au prix unitaire du produit commandé majoré de 10%. Ce prix est enregistré dans la ligne de commande

  • CREATE FUNCTION commande_possible RETURNS TRIGGER as $$
    DECLARE
    mon_produit produit%rowtype;
    bool boolean;
    BEGIN
    SELECT INTO mon_produit produit.*
    FROM produit, Iigne_commande WHERE ligne_commande.num_produit=NEW.num_produit
    AND produit.num_produit=NEW.num_produit;
    IF(mon_produit.qte_stock-mon_produit.qte_reservee)>=NEW.qte THEN
    bool:=1;
    ELSE
    bool:=0;
    END IF;
    UPDATE Iigne_commande
    SET possible=bool, prix_unitaire=mon_produit.pri_unitaire*110/ 100
    WHERE num_produit=NEW.num_produit
    AND num_com=NEW.num_oom;
    RETURN NEW;
    END;
    CREATE TRIGGER commande_possible AFTER INSERT ON Iigne_commande FOR EACH ROW EXECUTE PROCEDURE commande_possible();


  • quand le stock diminue, si la nouvelle quantité en stock + la quantité commandée - la quantité réservée atteint le stock d'alerte, il faut insérer une ligne dans la table des commandes a passer. La quantité commandée est celle qui est prévue par l'alerte de stock pour le produit concerné. La quantité commandée (dans produit) augmente alors d'autant

  • CREATE FUNCTION stock() RETURNS TRIGGER as $$
    DECLARE
    mon_produit produit%rowtype;
    BEGIN
    SELECT INTO mon_produit produit.*
    FROM produit
    WHERE qte_stock=NEW.qte_stock;
    IF(mon_produit.qte_stock+mon_produit.qte_commandee-mon_produit.qte_reservee) <=mon_produit.alerte_stock THEN
    INSERT INTO c_a_passer (qte_a_commander, num_produit)
    VALUES(mon_produit.alerte_stock, mon_produit.num_produit);
    UPDATE produit
    SET qte_commandee=gte_commandee+mon_produit.alerte_stock
    WHERE num_produit=NEW.num_produit;
    END IF;
    RETURN NEW;
    END;


  • quand le stock augmente, il faut traiter les lignes de commande impossible précédemment (attribut possible à 0) qui concernent le produit. La quantité réservée du produit diminue alors, et possible passe a 1, et la quantité en stock du produit diminue.

  • CREATE FUNCTION possible() RETURNS TRIGGER as $$
    DECLARE
    mon_produit produit%rowtype;
    ma_commande ligne_commande%rowtype;
    BEGIN
    SELECT INTO mon_produit.produit.*
    FROM produit
    WHERE qte_stock=new.qte_stock;
    SELECT INTO ma_commande ligne_commande.*
    FROM ligne_commande, produit
    WHERE ligne_commande.num_produit=mon_produit.num_produit
    AND possible = ‘false';
    IF(mon_produit.qte.stock-mon_produit.qte_reservee)>=ma_commande.qte THEN
    UPDATE Iigne_commande
    SET possible = 'true'
    WHERE num_produit=mon_produit.num_produit;
    UPDATE produit
    SET qte_stock=qte_stock-ma_commande.qte,qte_reservee=qte_reservee+ma_commande.qte
    WHERE num_produit=mon_produit.num_produit;
    END IF;
    RETURN NEW;
    END;
    CREATE TRIGGER commande_impossible AFTER UPDATE ON produit FOR EACH ROW
    EXECUTE PROCEDURE possible();