
-- SI-T 30/01/2026 - ESE3

-- 3.2)
create table GIOCATORI (
     Nickname char(10) NOT NULL PRIMARY KEY,
     NumRecord char(1) NOT NULL DEFAULT 0		);

create table RISULTATI (
     Timestamp TIMESTAMP NOT NULL PRIMARY KEY,
     IDGioco char(5) NOT NULL,
     Nickname char(10) NOT NULL REFERENCES GIOCATORI,
     Punti INT NOT NULL CHECK (Punti > 0)		);

/* alter table GIOCATORI add constraint ID_GIOCATORI_CHK
     check(exists(select * from RISULTATI
                  where RISULTATI.Nickname = Nickname)); 
*/

-- 3.3)

-- INSERT per testare il funzionamento del trigger

INSERT INTO GIOCATORI
VALUES 	('SuperPippo',DEFAULT),
		('Topolino',DEFAULT);

CREATE OR REPLACE TRIGGER NUOVO_NUM_RECORD
AFTER INSERT ON RISULTATI
REFERENCING NEW AS N
FOR EACH ROW
WHEN (N.Punti >= 						   -- record
		(SELECT COALESCE(MAX(Punti),0)		
	  	 FROM   RISULTATI
	  	 WHERE  IDGioco = N.IDGioco) 
      AND N.Nickname NOT IN 			   
         	(SELECT NickName 			-- chi deteneva il record
	  	     FROM   RISULTATI
	  	     WHERE  IDGioco = N.IDGioco
	  	     AND    Timestamp < N.Timestamp -- esclude il nuovo risultato
   			 AND    Punti = 
   			   (SELECT MAX(Punti)			
	  	        FROM   RISULTATI
	  	        WHERE  IDGioco = N.IDGioco
      		    AND    Timestamp < N.Timestamp)  ) )
UPDATE GIOCATORI
SET	   NumRecord = NumRecord + 1
WHERE  Nickname = N.Nickname ;

INSERT INTO RISULTATI VALUES			-- incrementa NumRecord
(CURRENT TIMESTAMP,'G0001','SuperPippo',50);

INSERT INTO RISULTATI VALUES			-- incrementa NumRecord
(CURRENT TIMESTAMP,'G0001','Topolino',50);

INSERT INTO RISULTATI VALUES			-- NON incrementa NumRecord
(CURRENT TIMESTAMP,'G0001','SuperPippo',60);

INSERT INTO RISULTATI VALUES			-- incrementa NumRecord
(CURRENT TIMESTAMP,'G0001','Topolino',70);

DELETE FROM risultati;
DELETE FROM GIOCATORI;


