---- On détruit les tables
DROP TABLE produit cascade constraints;
DROP TABLE commande cascade constraints;
DROP TABLE ligne_commande cascade constraints;
DROP TABLE ligne_erreur cascade constraints;
-- on crée les tables
CREATE TABLE produit
-- Création de table
(
  idproduit varchar2(5),
  desprod varchar(50),
  prixprod number(8,2),
  stockprod number(4),
  constraint pk_produit primary key(idproduit)
);

CREATE TABLE commande
-- Création de table
(
  idcommande varchar2(5),
  datecde date,
  montanttotalcom number(10,2),
  constraint pk_commande primary key(idcommande)
);

CREATE TABLE ligne_commande
-- Création de table
(
  idlig varchar2(5),
  idcommande varchar2(5),
  quantiteprod number(3),
  idproduit varchar2(5),
  constraint pk_ligne_commande primary key (idlig,idcommande)
);

CREATE TABLE ligne_erreur
-- Création de table
(
  idcommande varchar2(5),
  idlig varchar2(5),
  idproduit varchar2(5),
  quantiteprod number(3),
  stockprod number(4)
);

ALTER TABLE ligne_commande add constraint fk_lig_com1 foreign key(idcommande)
REFERENCES commande(idcommande);
ALTER TABLE ligne_commande add constraint fk_lig_com2 foreign key(idproduit)
REFERENCES produit(idproduit);
INSERT INTO produit VALUES ('P1','desc P1',50,50);
INSERT INTO produit VALUES ('P2','des P2',500,0);
INSERT INTO commande VALUES ('cde1',to_date('20/12/2008','DD/MM/YYYY'),0);

CREATE OR REPLACE PROCEDURE notifyError(
  idcomm       varchar2,
  idligne      varchar2,
  idproduit    varchar2,
  quantiteprod number,
  stock        number
)
IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO 
    ligne_erreur 
  VALUES 
    (idcomm, idligne, idproduit, quantiteprod, stock);
  COMMIT;
END notifyError;
/

CREATE OR REPLACE TRIGGER update_command
BEFORE INSERT ON ligne_commande
FOR EACH ROW
DECLARE
  vprix          float;
  vstock         integer;
  STOCK_ERROR    EXCEPTION;
BEGIN
  --fetch the current product stock
  SELECT
    stockprod, prixprod
  INTO 
    vstock, vprix
  FROM
    produit
  WHERE
    idProduit = :new.idProduit;
    
  -- handle stock error
  IF ( :new.idProduit > vstock ) THEN
    RAISE STOCK_ERROR;
  END IF;
  
  -- ASSERT Stock enable is enough
  
  -- update the product's stock
  UPDATE 
    produit 
  SET 
    stockprod = stockprod - :new.quantiteprod
  WHERE 
    idproduit = :new.idproduit;
  
  -- update the commande
  UPDATE 
    commande 
  SET 
    montanttotalcom = montanttotalcom + :new.quantiteprod * vprix 
  WHERE 
    idcommande = :new.idcommande;
EXCEPTION
  WHEN STOCK_ERROR THEN
    notifyError(:new.idCommande, :new.idLig, :new.idProduit, :new.quantiteprod, vstock);
    raise_application_error(-20001,'Stock Error exception : not enough of this product available');
  WHEN NO_DATA_FOUND THEN
    notifyError(:new.idCommande, :new.idLig, :new.idProduit, :new.quantiteprod, NULL);
    raise_application_error(-20001,'No data found exception : some of your given ids were wrong');
END;
/
