Re: SQL INSERT/TRIGGER Help

Aww man thank you so much! It worked like a charm! Have a smashing day :D

On Dec 10, 2007 9:43 AM, A. Kretschmer <andreas(dot)kretschmer(at)schollglas(dot)com>
wrote:

> am Mon, dem 10.12.2007, um 9:27:58 +0200 mailte Poovendran Moodley
> folgendes:
> > I'm not really sure how to the currval() method. I've read up on it and
> I
> > noticed it works with nextval() and setval(). The parameter for
> currval() is a
> > regex - is there a regex to represent the most recently automatically
> generated
> > number ( i.e. a serial field)? If there isn't, I was thinking that a
> trigger
> > could be used so that when an INSERT is executed against the
> Observation_Value
>
>
> Okay, i explain this a little bit more:
>
> first, i create two tables, master and slave. master contains a
> serial-field, this field is referenced by the slave table:
>
> test=# create table master (id serial primary key, name text);
> 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(master_id int references master, val text);
> CREATE TABLE
>
>
> Now i have 2 tables and a sequence named 'master_id_seq', and now the
> insert's:
>
>
> test=*# insert into master (name) values ('test');
> INSERT 0 1
> test=*# insert into slave (master_id, val) values
> (currval('master_id_seq'), 'value');
> INSERT 0 1
>
>
>
> The parameter for currval() is the name of the sequence. Note: you have to
> insert in the master table first, this calls the nextval() for the
> sequence. After, within this database session, you can use currval() to
> obtain the actual value for this sequence. And yes, this way is safe
> also for concurrency.
>
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>