Re: trigger that needs a PK

am Tue, dem 12.02.2008, um 23:20:46 -0800 mailte johnf folgendes:
> Hi,
> I'm sure this question has been ask before but I could not find anything on
> google. I most likely did not enter the right text into the google search.
>
> I have a parent table that requires that an insert into a child table happen.
> The problem is I can not determine what the parent pk is for the insert into
> the child because it hasn't happen yet - if I set the trigger to before
> insert. So I guess I need something that works with after insert into the
> parent so the pkid can be created.

You don't need a TRIGGER, you need currval(). I will explain with an
example:

test=# create table master (id serial primary key);
NOTICE: CREATE TABLE will create implicit sequence "master_id_seq" for
serial column "master.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"master_pkey" for table "master"
CREATE TABLE
test=*# create table slave (fk int references master);
CREATE TABLE
test=*# insert into master (id) values(default);
INSERT 0 1
test=*# insert into master (id) values(default);
INSERT 0 1
test=*# insert into master (id) values(default);
INSERT 0 1
test=*# insert into slave (fk) values(currval('master_id_seq'));
INSERT 0 1
test=*# select * from master;
id
----
1
2
3
(3 rows)

test=*# select * from slave;
fk
----
3
(1 row)

http://www.postgresql.org/docs/current/static/functions-sequence.html

HTH, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net