dietetique/sql/triggers&procedure.sql

create trigger tai_commandesproduit
  after insert
  on commandesproduits
  for each row
  begin

    declare v_nom varchar(255);
    declare v_description varchar(1024);
    declare v_marque varchar(255);
    declare v_achatmin varchar(255);
    declare v_economie varchar(255);
    declare v_unite varchar(255);
    declare v_format varchar(255);
    declare v_limite varchar(255);
    declare v_categorie varchar(255);


    select p.nom,
           p.description,
           p.marque,
           p.achat_minimum,
           p.economie,
           p.unite,
           f2.nom,
           p.limite_achat,
           c2.nom into v_nom, v_description, v_marque, v_achatmin, v_economie, v_unite, v_format, v_limite, v_categorie
    from produits p
           inner join format f2 on p.format = f2.id_format
           inner join categorie c2 on p.categorie = c2.id_categorie
    where p.id_produit = new.id_produit;

    insert into historiquecommandeproduit
    values ((select id_historique from historiquecommande hc where hc.id_commande = new.id_commande),
            new.id_produit,
            v_nom,
            v_description,
            v_marque,
            v_achatmin,
            v_limite,
            v_economie,
            v_unite,
            v_format,
            v_categorie,
            new.quantite);

  end;


create trigger tai_commandes
  after insert
  on commandes
  for each row
  begin

    DECLARE done INT DEFAULT FALSE;
    declare v_nom varchar(255);
    declare v_prenom varchar(255);
    declare v_courriel varchar(255);
    declare v_produit int;
    declare v_quantite decimal;
    declare c_panier cursor for select id_produit, quantite from panier where panier.id_compte = new.id_compte;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    select c.nom, c.prenom, c.courriel into v_nom, v_prenom, v_courriel
    from comptes c
    where c.id_compte = new.id_compte;

    insert into historiquecommande
    values (0,
            new.id_commande,
            new.id_compte,
            v_nom,
            v_prenom,
            v_courriel,
            new.date,
            new.status,
            new.prix_total,
            new.trace_paiement);

    #commandesproduit
    open c_panier;

    panier_loop: loop
      fetch c_panier into v_produit, v_quantite;

      IF done THEN
        LEAVE panier_loop;
      END IF;


      insert into commandesproduits values (new.id_commande, v_produit, v_quantite);
    end loop;

    close c_panier;

    delete from panier where panier.id_compte = new.id_compte;
  end;

create trigger tbd_commandes
  before delete
  on commandes
  for each row
  begin

    delete from commandesproduits where commandesproduits.id_commande = old.id_commande;

  end;

create trigger tbi_commandes
  before insert
  on commandes
  for each row
  begin

    declare num varchar(1024);

    set new.date = now();
    select CONCAT('DIET-', FLOOR(5 + RAND() * 60), '1', REPLACE(CURDATE(), '-', '')) into num from dual;
    set new.numero_commande = num;

  end;

create trigger tai_events
  after insert
  on events
  for each row
  begin

    if new.color = '#0071c5'
    then
      insert into datescommandes values (new.start, new.end);
    end if;

  end;

create trigger tau_events
  after UPDATE
  on events
  for each row
  begin
    declare nb int;

    if new.color = '#0071c5'
    then

      select count(*) into nb from datescommandes where date_debut = old.start
                                                    and date_fin = old.end;

      if nb > 0
      then
        update datescommandes
        set date_debut = new.start,
            date_fin   = new.end,
            nom        = new.title
        where date_debut = old.start
          and date_fin = old.end;
      else
        insert into datescommandes values (new.start, new.end, new.title);
      end if;

    end if;

  end;

create trigger tau_produits
  after update
  on produits
  for each row
  begin

    call updatelimiteproduit(new.id_produit, new.limite_achat);

  end;

create procedure updatelimiteproduit(in p_id int, in p_limite int) begin
  DECLARE done INT DEFAULT FALSE;
  declare v_compte int;
  declare v_quantite int;
  declare c_panier cursor for select id_compte, quantite from panier where id_produit = p_id;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  open c_panier;

  panier_loop: loop
    fetch c_panier into v_compte, v_quantite;

    IF done THEN
      LEAVE panier_loop;
    END IF;

    if v_quantite > p_limite then
      update panier set quantite = p_limite where id_compte = v_compte and id_produit = p_id;
    end if;
  end loop;

  close c_panier;

end;


create trigger tai_commandes2
  after insert
  on commandes
  for each row
  begin

    DECLARE done INT DEFAULT FALSE;
    declare v_produit int;
    declare v_quantite decimal;
    declare c_panier cursor for select id_produit, quantite from panier where panier.id_compte = new.id_compte;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    open c_panier;

    panier_loop: loop
      fetch c_panier into v_produit, v_quantite;

      IF done THEN
        LEAVE panier_loop;
      END IF;


      insert into commandesproduits values (new.id_commande, v_produit, v_quantite);
    end loop;

    close c_panier;

    delete from panier where panier.id_compte = new.id_compte;

  end;


create trigger tbd_historiquecommande
  before delete
  on historiquecommande
  for each row
  begin

    delete from historiquecommandeproduit where historiquecommandeproduit.id_historique = old.id_historique;

  end;