Re: Insert and get sys generated PK?

There's an ugly way, but it works.

Create a PL/SQL package owned by the same user as the table, with a package-level variable of the same type as the table's primary key. Grant EXECUTE on this package to all users who have INSERT privileges on the table.

Modify the trigger so that whenever it writes a primary key value into a new record, it also stores that value into the package-level variable. Then, you can read that variable with:

  SELECT <user>.<package>.<variable> FROM DUAL;

and get the value that was just inserted. You can also create a function in that package that does nothing but return the current value of the variable. Notes:

1. This only works for inserting one row at a time.
2. This works for multiple users (sessions) because each session keeps its own copy of the package-level variables for any package they access.

KTL <KTL_at_retailsolutions.com> wrote in message news:375EA9B7.6C2F70B2_at_retailsolutions.com...
> I have a table that has a numberic primary key.
> I also have a sequence that populates the primary key via a Before
> Insert trigger.
>
> How can I insert a record, and GET the primary key assigned to that
> record all in one statement?
>
> The trigger just
>
> select filelist_seq.nextval
> into :new.filelistnbr
> from dual;
>
> that is all..
>
>
> Suggestions.. ?
>
> -K
>
>
>
Received on Wed Jun 09 1999 - 19:35:43 CDT