
-- SI-T 30/01/2023 - ESE3

-- 3.2)

create table DOGANIERI (
     CodD char(5) not null primary key,
     Nome char(20) not null,
     NumVerifiche int not null default 0 check (NumVerifiche >= 0)	);

create table PS (
     CodSped char(5) not null,
     CodProd char(5) not null,
     Quantita int not null check (Quantita >= 0),
     primary key (CodSped, CodProd)	);

create table VERIFICHE (
     CodSped char(5) not null,
     CodProd char(5) not null,
     CodD char(5) not null references DOGANIERI,
     Esito char(2) default NULL check (Esito IN ('OK','KO')),
     primary key (CodSped, CodProd),
     foreign key (CodSped, CodProd) references PS		);

-- 3.3)

-- INSERT per testare il funzionamento

INSERT INTO DOGANIERI VALUES
('DOG01','Pippo',DEFAULT);

INSERT INTO PS VALUES
('SPE01','PRO01',100);

INSERT INTO VERIFICHE VALUES
('SPE01','PRO01','DOG01',DEFAULT);

CREATE OR REPLACE TRIGGER AGGIORNA_NUM_VERIFICHE
AFTER UPDATE OF Esito ON VERIFICHE
REFERENCING NEW AS N
FOR EACH ROW
WHEN (N.Esito IS NOT NULL)
UPDATE 	DOGANIERI
SET		NumVerifiche = NumVerifiche + 1
WHERE		CodD = N.CodD ;

CREATE OR REPLACE TRIGGER AZZERA_QUANTITA
AFTER UPDATE OF Esito ON VERIFICHE
REFERENCING NEW AS N
FOR EACH ROW
WHEN (N.Esito = 'KO')
UPDATE 	PS
SET		Quantita = 0
WHERE		(CodSped, CodProd) = (N.CodSped, N.CodProd);


UPDATE VERIFICHE
SET 	 Esito = :esito
WHERE (CodSped, CodProd) = ('SPE01','PRO01');





