
-- SI-T 08/09/2023 - ESE3

-- 3.2)

create table BIRRERIE (
     BID char(5) not null primary key,
     Nome varchar(30) not null,
     NumRece integer not null default 0 check(NumRece >= 0) );

create table RECENSIONI (
     BID char(5) not null references BIRRERIE,
     CodCliente char(5) not null,
     Data date not null,
     Testo varchar(50) not null,
     primary key (BID, CodCliente) );

-- 3.3)

-- INSERT per testare il funzionamento

INSERT INTO BIRRERIE VALUES
('B0001','OktoberFest',DEFAULT);

CREATE OR REPLACE TRIGGER AGGIORNA_NUM_RECE
AFTER INSERT ON RECENSIONI
REFERENCING NEW AS N
FOR EACH ROW
UPDATE 	BIRRERIE
SET		NumRece = NumRece + 1
WHERE		BID = N.BID 				;

CREATE OR REPLACE TRIGGER MAX_3_RECE
AFTER INSERT ON RECENSIONI
REFERENCING NEW AS N
FOR EACH ROW
WHEN (3 < (SELECT COUNT(*) FROM RECENSIONI WHERE BID = N.BID))
DELETE FROM RECENSIONI
WHERE	  BID = N.BID
AND	  Data = ( SELECT MIN(Data)
			FROM   RECENSIONI
			WHERE  BID = N.BID ) 				;


INSERT INTO RECENSIONI VALUES 
('B0001','C1234','15.06.2023','Non male'); 

INSERT INTO RECENSIONI VALUES 
('B0001','C2345','18.06.2023','Ottima!'); 

INSERT INTO RECENSIONI VALUES 
('B0001','C3456','20.06.2023','Il top!');
 
INSERT INTO RECENSIONI VALUES 
('B0001','C4567','22.06.2023','Non fa per me'); 



