DROP TABLE header;
CREATE TABLE header (
        distretto		CHAR(4)  NOT NULL,
	anno			DECIMAL(4)  NOT NULL,
	numero			INTEGER  NOT NULL,
	data			DATE 	 NOT NULL,
	azienda			CHAR(11) NOT NULL,
        CONSTRAINT k_header PRIMARY KEY (distretto,anno,numero)
	);

DROP TABLE detail;
CREATE TABLE detail (
        distretto		CHAR(4)  NOT NULL,
	anno			DECIMAL(4) NOT NULL,
	numero			INTEGER NOT NULL,
	cod_prestazione		CHAR(05) NOT NULL,
        quantita		FLOAT(4) NOT NULL,
	importo			FLOAT(8),
        CONSTRAINT k_detail PRIMARY KEY (distretto,anno,numero,cod_prestazione),
        CONSTRAINT k_extern FOREIGN KEY(distretto,anno,numero) references HEADER
	);

drop function f_not_add_detail();
create function f_not_add_detail() returns opaque as '
declare
	/* to avoid insert detail if header doesn''t exist */
	tot int;
begin
	select numero into tot from header
        where anno = new.anno and numero = new.numero;
	if not found then
		raise notice ''Impossible add new detail!'';
		return NULL;
	else
    		return new;
	end if;
end;
' language 'plpgsql';

create trigger t_not_add_detail before insert
    on detail for each row execute procedure f_not_add_detail();


--EXAMPLE:
select * from header;
select * from detail;
INSERT INTO detail VALUES ('E14','1999',2,'IMPOSSIBLE',123,345.5); --impossible
INSERT INTO header VALUES ('E14','1999',2,current_date,1235);
INSERT INTO detail VALUES ('E14','1999',2,'AB',523,35.5);
INSERT INTO header VALUES ('E14','1999',1,current_date,1235);
INSERT INTO detail VALUES ('E14','1999',1,'A1',423,45.5);
INSERT INTO detail VALUES ('E14','1999',1,'AC',123,345.5);
select * from header;
select * from detail;

drop function f_upd_key_detail();
create function f_upd_key_detail() returns opaque as '
declare
	/* change in cascade the key of every detail if header key is changed */
	tot int;
begin
	update detail 
          set anno = new.anno, numero = new.numero
	  where anno = old.anno and numero = old.numero;
    	return NULL;
end;
' language 'plpgsql';

drop trigger t_upd_key_detail on header;
create trigger t_upd_key_detail after update
    on header for each row execute procedure f_upd_key_detail();

--EXAMPLE:
select * from header;
select * from detail;
update header set anno='1997', numero=33 where numero = 1 and anno='1999';
select * from header;
select * from detail;


drop function f_del_cascade();
create function f_del_cascade() returns opaque as '
declare
	/* cancel in cascade all details after header is deleted */
begin
	delete from detail
        where anno = old.anno and numero = old.numero;
    	return NULL;
end;
' language 'plpgsql';

drop trigger t_del_cascade on header;
create trigger t_del_cascade after delete
    on header for each row execute procedure f_del_cascade();

--EXAMPLE:
select * from header;
select * from detail;
delete from header where anno = 1997;
select * from header;
select * from detail;