
-- SI-T 05/02/2024 - ESE3

-- 3.2)

create table MEDICI_BASE (
     CodM char(5) not null PRIMARY KEY,
     Nome varchar(30) not null UNIQUE		);

create table PM (
    CF char(16) not null PRIMARY KEY,
	CodM char(5) not null REFERENCES MEDICI_BASE,
    DataScelta date not NULL				);
     
create table ARCHIVIO (
     CF char(16) not null,
     CodM char(5) not null REFERENCES MEDICI_BASE,
     DataScelta date not null,
     constraint ID_ARCHIVIO_ID primary key (CF,CodM)  );

-- INSERT per testare il funzionamento

INSERT INTO MEDICI_BASE
VALUES 	('M1234','Paperoga'),
		('M5678','Archimede');
	
INSERT INTO PM
VALUES ('ABCDEF','M1234','23.11.2023');

CREATE OR REPLACE TRIGGER CAMBIO_MEDICO
AFTER UPDATE ON PM
REFERENCING OLD AS O
FOR EACH ROW
INSERT INTO ARCHIVIO
VALUES (O.CF,O.CodM,O.DataScelta);


UPDATE 	PM
SET 	CodM = (SELECT 	M.CodM
				FROM 	MEDICI_BASE M
				WHERE	Nome = 'Archimede'),
		DataScelta = CURRENT DATE
WHERE 	CF = 'ABCDEF';








