-- SI-T 15/02/2021 - ESE3

-- 3.2)

create table CONCORRENTI (
     IDConc char(5) not null,
     Nome varchar(20) not null,
     constraint ID_CONCORRENTI_ID primary key (IDConc));

create table PROVE (
     IDProva char(5) not null,
     Descrizione varchar(30) not null,
     TempoMax INT not null CHECK (TempoMax > 0),
     NumMaxTentativi INT not null CHECK (NumMaxTentativi > 0),
     constraint ID_PROVE_ID primary key (IDProva));

create table Tentativi (
     IDConc char(5) not null references CONCORRENTI,
     IDProva char(5) not null references PROVE,
     TentativiFatti INT not null DEFAULT 0,
     BestTime INT DEFAULT NULL CHECK (BestTime > 0),
     constraint ID_Tentativi_ID primary key (IDConc, IDProva));

-- 3.3)

UPDATE Tentativi
SET BestTime = MIN(:nuovotempo,COALESCE(BestTime,:nuovotempo)),
    TentativiFatti = TentativiFatti + 1
WHERE (IDConc,IDProva) = (:concorrente,:prova)

CREATE OR REPLACE TRIGGER TENTATIVO_INVALIDO
BEFORE UPDATE ON Tentativi
REFERENCING OLD AS O
FOR EACH ROW
WHEN ((O.TentativiFatti >= (SELECT NumMaxTentativi
					  FROM  Prove
					  WHERE IDProva = O.IDProva))
	OR
	 (O.BestTime <= (SELECT TempoMax
				  FROM  Prove
				  WHERE IDProva = O.IDProva)) )
SIGNAL SQLSTATE '70001' ('Prova superata o troppi tentativi!');





