Skip site navigation (1) Skip section navigation (2)

Re: trigger that needs a PK

From: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: trigger that needs a PK
Date: 2008-02-13 07:32:24
Message-ID: 20080213073224.GD24109@a-kretschmer.de (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

Responses

pgsql-novice by date

Next:From: johnfDate: 2008-02-13 07:46:41
Subject: Re: trigger that needs a PK
Previous:From: johnfDate: 2008-02-13 07:20:46
Subject: trigger that needs a PK

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group