
-- SI-T 25/01/2024 - ESE3

-- 3.2)

create table PRODOTTI (
     PID char(5) not null primary key,
     Prezzo dec(6,2) not NULL,
	 NumCat int not null default 0			);

create table PC (
     PID char(5) not null references PRODOTTI,
     CatID char(5) not null,
     Pertinenza int not null check (Pertinenza BETWEEN 1 AND 10),
     OK int not null check (OK IN (0,1)),
     constraint ID_PC_ID primary key (CatID, PID)		);

-- 3.3)

-- INSERT per testare il funzionamento

INSERT INTO PRODOTTI(PID,Prezzo) 
VALUES ('P1234',45.27);

CREATE OR REPLACE TRIGGER NUOVA_CATEGORIA
BEFORE INSERT ON PC
REFERENCING NEW AS N
FOR EACH ROW
IF N.Pertinenza > 5 THEN SET N.OK = 1; ELSE SET N.OK = 0;
END IF

CREATE OR REPLACE TRIGGER UPDATE_NUMCAT
AFTER INSERT ON PC
REFERENCING NEW AS N
FOR EACH ROW
UPDATE PRODOTTI
SET NumCat = NumCat +1 
WHERE PID = N.PID;

INSERT INTO PC(PID,CatID,Pertinenza)	-- OK = 1
VALUES ('P1234','Cat15',6);

INSERT INTO PC(PID,CatID,Pertinenza)	-- OK = 0
VALUES ('P1234','Cat26',3);








