-- SI-T 03/02/2021 - ESE3

-- 3.2)

create table CONCORRENTI (
     IDConc char(5) not null,
     Nome varchar(20) not null,
     NumProve INT not null DEFAULT 0,
     TotSecs INT not null DEFAULT 0,
     AvgSecs DEC(8,2) DEFAULT NULL,
     constraint ID_CONCORRENTI_ID primary key (IDConc));

create table PC (
     IDConc char(5) not null references CONCORRENTI,
     IDProva char(5) not null,
     NumSecs INT not null CHECK (NumSecs > 0),
     constraint ID_PC_ID primary key (IDConc, IDProva));


-- 3.3)

INSERT INTO PC
VALUES (:concorrente,:prova,:numsecondi);

CREATE OR REPLACE TRIGGER UPDATE_DATI
AFTER INSERT ON PC
REFERENCING NEW AS N
FOR EACH ROW
UPDATE 	CONCORRENTI
SET		NUMPROVE = NUMPROVE + 1,
		TOTSECS = TOTSECS + N.NUMSECS,
		AVGSECS = 
		   DEC((TOTSECS + N.NUMSECS)*1.0/(NUMPROVE + 1),8,2)
WHERE		IDCONC = N.IDCONC ;

