-- Cr�ation des tables TP producteur vin
-----------------------------------------
DROP table Recolte;
DROP table vin;
DROP table producteur;

CREATE TABLE vin(
numvin varchar(5) constraint pk_vin primary key,
cru varchar(10),
annee number(4),
degre number(4,1)
);
CREATE TABLE Producteur(
numprod varchar(5) constraint pk_prod primary key,
nom varchar(30),
prenom varchar(30),
region varchar(30),
qtetot number(10,2) default 0
);
CREATE TABLE Recolte(
numvin varchar(5),
numprod varchar(5),
quantite number(6,2),
constraint pk_recolte primary key(numvin,numprod)
);
-----------------------------------------
-- script de cr�ation des cl�s �trang�res
-----------------------------------------
ALTER table recolte add constraint fk_recolte1 foreign key(numvin)
references vin(numvin);
ALTER table recolte add constraint fk_recolte2 foreign key(numprod)
references producteur(numprod);

-----------------------------------
-- exemple de jeu d essai de d�part
-----------------------------------
insert into vin values ('v1','c1',1994,12);
insert into vin values ('v2','c1',1994,13);
insert into vin values ('v3','c2',1992,10);
insert into producteur values ('p1','Rotchild','','Beaujolais',0);
insert into producteur values ('p2','Chateau Louis','','Loire',0);
insert into producteur values ('p3','JC et Fils','','Beaujolais',0);
insert into producteur values ('p4','Domaine de la bourdonnaise','','Loire',0);
insert into recolte values ('v1','p1',100);
insert into recolte values ('v2','p1',200);
insert into recolte values ('v3','p1',100);
insert into recolte values ('v1','p2',100);
insert into recolte values ('v2','p3',200);
insert into recolte values ('v3','p4',100);

----------------------------------------------------
-- mise � jour explicite car trigger non encore cr�e
----------------------------------------------------
UPDATE producteur set qtetot = 400 where numprod = 'p1';
UPDATE producteur set qtetot = 100 where numprod = 'p2';
UPDATE producteur set qtetot = 200 where numprod = 'p3';
UPDATE producteur set qtetot = 100 where numprod = 'p4';

----------------------------------------------------
-- Trigger PL/SQL
----------------------------------------------------
DROP SEQUENCE seq_id_producteur;
CREATE SEQUENCE seq_id_producteur START WITH 5;
CREATE OR REPLACE TRIGGER trig_producteur BEFORE INSERT ON Producteur
FOR EACH ROW
DECLARE
v_numprod NUMBER;
BEGIN
SELECT seq_id_producteur.nextval INTO v_numprod FROM DUAL;
:new.numprod := 'p'||v_numprod;
IF :new.qtetot IS NULL THEN
  :new.qtetot := 0;
END IF;
END;
/

INSERT INTO Producteur(nom,prenom,region,qtetot) VALUES ('Dupont','Auguste','Bordelais',15000);

CREATE OR REPLACE TRIGGER trig_recolte AFTER INSERT ON Recole
FOR EACH ROW
BEGIN
UPDATE Producteur SET qtetot = qtetot + :new.quantite WHERE numprod = :new.numprod;
END;
/
