[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [GENERAL] Referential integrity
From:       José Soares <jose () sferacarta ! com>
Date:       1999-08-31 12:10:42
[Download RAW message or body]

Questo è un messaggio multi-parte scritto in formato MIME.

Try using triggers: (see attached example)
José


Pablo Sentis ha scritto:

>    Part 1.1    Type: Plain Text (text/plain)
>            Encoding: quoted-printable

["trigger.sql" (text/plain)]

--Referential integrity:
--1. Don't allow to add a detail without header
--2. Delete all details in cascade if one decide to delete the header
--3. Update details' key in cascade if header's key is changed

DROP TABLE header;
CREATE TABLE header (
	year			integer NOT NULL,
	number			INTEGER  NOT NULL,
	date			DATE 	 NOT NULL,
	cod_client 		CHAR(4)  NOT NULL,
        CONSTRAINT k_header PRIMARY KEY (year,number)
	);

DROP TABLE detail;
CREATE TABLE detail (
	year			integer NOT NULL,
	number			INTEGER  NOT NULL,
	cod_product      	CHAR(05) NOT NULL,
        qty			INTEGER  NOT NULL,
	cost			DECIMAL(8,2),
        CONSTRAINT k_detail PRIMARY KEY (year,number,cod_product),
        CONSTRAINT k_extern FOREIGN KEY(year,number) 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 number into tot from header
        where year = new.year and number = new.number;
	if not found then
		raise notice ''I cannot add a detail without header!'';
		return NULL;
	else
    		return new;
	end if;
end;
' language 'plpgsql';

drop function f_del_detail();
create function f_del_detail() returns opaque as '
begin
	-- Delete details in cascade...
	delete from detail where 
	detail.year = old.year
	and detail.number = old.number;
    	return new;
end;
' language 'plpgsql';

drop function f_upd_detail();
create function f_upd_detail() returns opaque as '
begin
	-- Updates details keys in cascade...
	update detail set year = new.year, number = new.number
	where detail.year = old.year
	and detail.number = old.number;
    	return new;
end;
' language 'plpgsql';


create trigger t_ins_after before INSERT
    on detail for each row execute procedure f_not_add_detail();

create trigger t_del_after after DELETE
    on header for each row execute procedure f_del_detail();

create trigger t_upd_after after UPDATE
    on header for each row execute procedure f_upd_detail();

insert into header values(1999,321,current_date,'C128');
insert into detail values(1999,321,'B139',2,200.35);
insert into header values(1997,132,current_date,'C500');
insert into detail values(1997,132,'B166',3,120.50);
select * from header;
select * from detail;
update header set year=1998 where year=1999;
select * from header;
select * from detail;

delete from header where year=1998;
select * from header;
select * from detail;

insert into detail values(1999,321,'B139',2,200.35);

************


[prev in list] [next in list] [prev in thread] [next in thread]