
-- SI-T 13/02/2023 - ESE3

-- 3.2)

create table MAGAZZINI (
     CodM char(5) not null primary key,
     Indirizzo varchar(50) not null);

create table PRODOTTI (
     CodProd char(5) not null primary key,
     Prezzo dec(6,2) not null);

create table VENDITE (
     CodV char(5) not null primary key,
     Data date not null,
     CodM char(5) not null references MAGAZZINI);

create table PV (
     CodProd char(5) not null references PRODOTTI,
     CodV char(5) not null references VENDITE,
     Quantita int not null check(Quantita > 0),
     constraint ID_PV_ID primary key (CodV, CodProd));

create table PM (
     CodM char(5) not null references MAGAZZINI,
     CodProd char(5) not null references PRODOTTI,
     Scorta int not null,
     constraint ID_PM_ID primary key (CodM, CodProd));


-- 3.3)

-- INSERT per testare il funzionamento

INSERT INTO MAGAZZINI VALUES
('MAG01','Via Roma 15, Bologna');

INSERT INTO PRODOTTI VALUES
('PRO01',200.50);

INSERT INTO VENDITE VALUES
('VEN01','13.02.2013','MAG01');

INSERT INTO PM VALUES
('MAG01','PRO01',50);

CREATE OR REPLACE TRIGGER CHECK_SCORTA
BEFORE INSERT ON PV
REFERENCING NEW AS N
FOR EACH ROW
WHEN (N.Quantita > ( 	SELECT PM.Scorta 
				FROM	  PM, VENDITE V
				WHERE  PM.CodProd = N.CodProd
				AND    PM.CodM = V.CodM
				AND	  V.CodV = N.CodV  ))
SIGNAL SQLSTATE '70001' ('Scorta insufficiente!');

CREATE OR REPLACE TRIGGER AGGIORNA_SCORTA
AFTER INSERT ON PV
REFERENCING NEW AS N
FOR EACH ROW
UPDATE 	PM
SET		PM.Scorta = PM.Scorta - N.Quantita
WHERE		PM.CodProd = N.CodProd
AND		PM.CodM = ( SELECT V.CodM 
				 FROM	   VENDITE V
				  WHERE V.CodV = N.CodV  ) ;


INSERT INTO PV VALUES ('PRO01','VEN01',60); -- errore

INSERT INTO PV VALUES ('PRO01','VEN01',40); -- ok





