drop table match;
drop table resultats;
drop table equipe;
drop table temps;
drop sequence seq_equipe;  
drop sequence seq_temps; 

create table match (
	equipe_locale varchar2(100),
	equipe_visiteur varchar2(100),
	nbButLocale integer,
	nbButVisiteur integer,
	dateMatch date,
	constraint pk_match primary key(equipe_locale, equipe_visiteur,dateMatch));

insert into Match values ('OM', 'Bordeaux', 2, 1, to_date('13/04/2019', 'DD/MM/YYYY'));
insert into Match values ('Paris', 'Marseille', 2, 1, to_date('12/11/2018', 'DD/MM/YYYY'));
insert into Match values ('Bordeaux', 'PSG', 3, 2, to_date('11/05/2019', 'DD/MM/YYYY'));
insert into Match values ('Bordeaux', 'OM', 2, 2, to_date('10/10/2018', 'DD/MM/YYYY'));
insert into Match values ('OM', 'PSG', 1, 5, to_date('15/11/2018', 'DD/MM/YYYY'));
insert into Match values ('PSG', 'Bordeaux', 3, 1, to_date('16/11/2018', 'DD/MM/YYYY'));

create table equipe (
  equipeid integer, 
  nom varchar2(100),
  constraint pk_equipe primary key(equipeid));
 
create table temps (
  tempsid integer, 
  mois integer, annee integer,
  constraint pk_temps primary key(tempsid));

 create table resultats(
   equipeid integer, 
   tempsid integer,
   nbVictoireDomicile integer,
   nbMoyenBut integer,
   constraint pk_resultats primary key(equipeid, tempsid),
   constraint fk_resultats_equipe foreign key (equipeid) references equipe(equipeid),
   constraint fk_resultats_temps foreign key (tempsid) references temps (tempsid));
   
create sequence seq_equipe;

create or replace
TRIGGER TRIGG_SEQ_equipe
BEFORE INSERT ON equipe FOR EACH ROW
BEGIN
SELECT SEQ_equipe.NEXTVAL INTO :NEW.equipeid FROM DUAL;
END;
/

create sequence seq_temps;

create or replace
TRIGGER TRIGG_SEQ_temps
BEFORE INSERT ON temps FOR EACH ROW
BEGIN
SELECT SEQ_temps.NEXTVAL INTO :NEW.tempsid FROM DUAL;
END;
/

