Re: [GENERAL] Tr: DUMP database for sample

--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 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);

--results:
-----------------------------
insert into header values(1999,321,current_date,'C128');
INSERT 365317 1
insert into detail values(1999,321,'B139',2,200.35);
INSERT 365318 1
insert into header values(1997,132,current_date,'C500');
INSERT 365319 1
insert into detail values(1997,132,'B166',3,120.50);
INSERT 365320 1
select * from header;
year|number|date |cod_client
----+------+----------+----------
1999| 321|10/08/1999|C128
1997| 132|10/08/1999|C500
(2 rows)

select * from detail;
year|number|cod_product|qty| cost
----+------+-----------+---+------
1999| 321|B139 | 2|200.35
1997| 132|B166 | 3|120.50
(2 rows)

update header set year=1998 where year=1999;
UPDATE 1
select * from header;
year|number|date |cod_client
----+------+----------+----------
1997| 132|10/08/1999|C500
1998| 321|10/08/1999|C128
(2 rows)

select * from detail;
year|number|cod_product|qty| cost
----+------+-----------+---+------
1997| 132|B166 | 3|120.50
1998| 321|B139 | 2|200.35
(2 rows)

delete from header where year=1998;
DELETE 1
select * from header;
year|number|date |cod_client
----+------+----------+----------
1997| 132|10/08/1999|C500
(1 row)

select * from detail;
year|number|cod_product|qty| cost
----+------+-----------+---+------
1997| 132|B166 | 3|120.50
(1 row)

insert into detail values(1999,321,'B139',2,200.35);
NOTICE: I cannot add a detail without header!
INSERT 0 0

José

Stéphane FILLON ha scritto:

> -----Message d'origine-----
> De : Stéphane FILLON <fillons(at)offratel(dot)nc>
> À : pgsql-sql(at)postgresql(dot)org <pgsql-sql(at)postgresql(dot)org>;
> pgsql-admin(at)postgresql(dot)org <pgsql-admin(at)postgresql(dot)org>
> Date : dimanche 1 août 1999 09:13
> Objet : Tr: DUMP database for sample
>
> >
> >
> >>Hi !
> >>
> >>I am trying a big commercial database application with PostgreSQL and I
> >>encounter some problems seach as how to create a foreign key constraint..
> >>
> >>I would be very nice if someone could send me a dump of a real database
> >>application with TRIGGER / FUNCTION / TYPE / FOREIGN KEY AND PRIMARY KEY.
> >>
> >>I think that it would be nice if we could make a documentation with a real
> >>example. If you are interested I can make it during my build.
> >>
> >>
> >>Best Regards,
> >>
> >>A special thanks for the PostgreSQL's team,, KEEP THE GOOD JOB.
> >>
> >>Stephane.
> >>
> >