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