/* Ceartion des tabkles  stocks et commande */

create table stocks ( id_prod number primary key,nom_prod varchar(15), Qtt_Prod_en_Stock  number )

create table commande ( 
                        id_cli number primary key , id_prod_cm number , Qtt_commandee number,
                        foreign key(id_prod_cm) references stocks(id_prod)
                        )

insertion de qlqs jeu de données

insert into stocks values( 111,'UC HP', 300);
insert into stocks values( 222,'Souris Bth', 400);
insert into stocks values( 333,'Moniteur HP 17', 500);
insert into stocks values( 444,'Moniteur HP 21', 500);
insert into stocks values( 555,'USB 64 GO', 100);

/* Quelques requetes utiles */

select * from stocks

select * from commande 

insert into commande values( 6 , 222, 200);

update commande set  Qtt_commandee= 100 where  id_cli=6 and id_prod_cm= 222;

delete from commande where  id_cli=6 and id_prod_cm= 222;


/* Trigger qui gere la mise à jour automatique de stocke une fois il y a des mise a jours au niveau de commande */

CREATE or replace  TRIGGER Ventes_trig before INSERT or update or delete ON commande
REFERENCING  NEW AS nouv OLD as encient 
FOR EACH ROW 
begin 
UPDATE Stocks 
SET Stocks.Qtt_Prod_en_Stock = Stocks.Qtt_Prod_en_Stock - ( nvl(:nouv.qtt_commandee,0) - nvl(:encient.qtt_commandee,0))
WHERE Stocks.ID_prod in ( :nouv.id_prod_cm , :encient.id_prod_cm );
end;

/* Trigger qui illustre la table en mutation */  */ il illustr aussi le filtre when */


CREATE or replace  TRIGGER more_than_4_USB AFTER INSERT ON commande
FOR EACH ROW 
WHEN (NEW.id_prod_cm = 555)
begin 
if :new.Qtt_commandee <=3  then 
delete from  commande  where id_prod_cm=:new.id_prod_cm;
dbms_output.put_line ( 'il faut commande au moins 4 articles de ce produit' ) ; 
end if;
end;


insert into commande values ( 12,555,2) ; -- cette commande va declencher le trigger 

/* une fois on commande les USB 16 Go (id_prod_cm = 555 ) 
avec une quantité moins de 4 on aura un probleme d'execution de trigger --> table en mutation */


/* la slution est  RAISE_APPLICATION_ERROR pour arreter  le traitement  --> le code ci-dessous*/

CREATE or replace  TRIGGER more_than_4_USB AFTER INSERT ON commande
FOR EACH ROW 
WHEN (NEW.id_prod_cm = 555)
begin 
if :new.Qtt_commandee <=3  then 
delete from commande where 
RAISE_APPLICATION_ERROR(-20550,'il faut commander au moins 4 articles de ce produit' ) ;
end if;
end;

  /* les tests */
insert into commande values ( 1,444,2)  
/*cette commande ne va pas declencher le trigger car la clause when à filter l' id_produit  concerné par le trigger */

insert into commande values ( 1,555,3)  -- cette commande va declencher le trigger 

insert into commande values ( 1,555,10) /*cette commande ne va pas declencher le trigger car la quantité commandée ( 10) >3   */