Skip to Main Content
  • Questions
  • Obtaining sequence value inserted in a trigger

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Ken.

Asked: June 13, 2005 - 1:24 pm UTC

Last updated: February 09, 2006 - 4:09 am UTC

Version: 10g

Viewed 1000+ times

You Asked

Tom,

I read through all the articles you have on sequences. You typically recommend to embed the assignment of the sequence in the insert trigger. If I need the primary key that was assigned on insert to use as foriegn keys in related tables, how can I be sure I have the right value. I understand I can use the .curvalue to find out the currently assigned sequence number, but how can I be sure that is not the number for another transaction? Isn't it possible for another transaction to insert a record between my insert and the call for the current value?

I guess what I am asking is what is the best desgin principle when implementing a multiple table database with foriegn keys?

Thanks,


and Tom said...

currval is private to a session. currval always returns the last value from YOUR SESSIONS LAST nextval call. It is "session safe".

but, all you need to do is use the RETURNING clause if the trigger *must* be used.

ops$tkyte@ORA9IR2> create table t ( x int, y int );

Table created.

ops$tkyte@ORA9IR2> create sequence s;

Sequence created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace trigger t_trigger
2 before insert on t for each row
3 begin
4 select s.nextval into :new.x from dual;
5 end;
6 /

Trigger created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
2 l_x number;
3 begin
4 insert into t (y) values ( 42 ) returning x into l_x;
5 dbms_output.put_line( 'generated key = ' || l_x );
6 end;
7 /
generated key = 1

PL/SQL procedure successfully completed.



Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

chitra, February 08, 2006 - 11:48 am UTC

Can we use returning clause when doing a insert as select?
I am trying to do something like below, and I am hitting a
ORA-06550 error.
t is the table with two number columns x and y.

declare
l_x number;
begin
insert into t (x,y) ( select 42,32 from dual) returning x into l_x;
end;



Tom Kyte
February 09, 2006 - 4:09 am UTC

you cannot use the returning clause on an insert as select, only on an insert values statement.

it does not realize (it cannot realize, it might not be true) that you are only inserting a single record.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library