
-- SI-T 19/01/2023 - ESE3

-- 3.2)
create table ACQUISTI (
     IdA char(5) not null primary key ,
     Importo DEC(6,2) not null,
     ImportoRata DEC(6,2) not null,
     ImportoResiduo DEC(6,2) not null,
     Pagato char(2) DEFAULT NULL CHECK (Pagato = 'SI') );

create table RATE (
     IdA char(5) not null references ACQUISTI,
     Data date not null,
     constraint ID_RATE_ID primary key (IdA, Data)  );


   
-- 3.3)

-- INSERT per testare il funzionamento

INSERT INTO ACQUISTI VALUES
('A0001',3000,1000,3000,DEFAULT);

CREATE OR REPLACE TRIGGER UPDATE_RESIDUO
AFTER INSERT ON RATE
REFERENCING NEW AS N
FOR EACH ROW
UPDATE 	ACQUISTI
SET		ImportoResiduo = ImportoResiduo - ImportoRata
WHERE	IdA = N.IdA ;

CREATE OR REPLACE TRIGGER PAGATO
AFTER UPDATE OF ImportoResiduo ON ACQUISTI
REFERENCING NEW AS N
FOR EACH ROW
WHEN (N.ImportoResiduo = 0)
UPDATE 	ACQUISTI
SET		Pagato = 'SI'
WHERE	IdA = N.IdA ;

INSERT INTO RATE VALUES
('A0001','02.12.2022');

INSERT INTO RATE VALUES
('A0001','02.01.2023');

INSERT INTO RATE VALUES
('A0001','12.01.2023');

