Message-ID: <3691EA80.B0B197B0@sferacarta.com>
Date: Tue, 05 Jan 1999 11:33:36 +0100
From: "Jose' Soares" <jose@sferacarta.com>
Reply-To: sferac@bo.nettuno.it
Organization: Sfera Carta Software
MIME-Version: 1.0
To: "Jackson, DeJuan" <djackson@cpsgroup.com>
CC: leif@danmos.dk, pgsql-interfaces@postgresql.org
Subject: Re: [INTERFACES] How to implement referential facilities in
 PostgreSQL
References: <F10BB1FAF801D111829B0060971D839F5B06E1@cpsmail>
Content-Type: multipart/mixed; boundary="------------C5F442DF21FF56C7B103A277"

This is a multi-part message in MIME format.
--------------C5F442DF21FF56C7B103A277
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

(see attached file)

I have two tables HEADER and DETAIL linked by an external key

I need to have the following integrity:

1. Don't allow to insert DETAILS if doesn't exist the HEADER with the
same
   key.

2. Delete in cascade all DETAILS if one cancel the HEADER having the
same key.

3. UPDATE in cascade the key of all DETAILS if one change the HEADER
key.

-Jose'-
--------------C5F442DF21FF56C7B103A277
Content-Type: text/plain; charset=us-ascii; name="trigger.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline; filename="trigger.sql"

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;

--------------C5F442DF21FF56C7B103A277--