Home » SQL & PL/SQL » SQL & PL/SQL » order of geting value from sequence by trigger (Oracle 11g)
order of geting value from sequence by trigger [message #652099] Wed, 01 June 2016 13:23 Go to next message
miodas007
Messages: 2
Registered: June 2016
Junior Member
I have table where one column is auto incremented (sequence + before trigger).
When i have a couple of inserts to this table, close to each other (race case), and order is:
INSERT1
INSERT2
INSERT3
...

have i guarentee that values from sequence will be get with same order ?

In other words: after all inserts have i quarentee that last one have the growest value, and the first one have the lowest value?
In other words: In my case geting value from sequence and inserting it is transactional ?
Re: order of geting value from sequence by trigger [message #652101 is a reply to message #652099] Wed, 01 June 2016 13:27 Go to previous messageGo to next message
Michel Cadot
Messages: 67920
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
after all inserts have i quarentee that last one have the growest value, and the first one have the lowest value?


If all inserts are made in one session, yes.
Otherwise you first have to define "order", order by insert statement execution time or order by transaction start time or transaction end (that is commit) time.

Re: order of geting value from sequence by trigger [message #652103 is a reply to message #652099] Wed, 01 June 2016 13:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

SEQUENCE values only increase.
Rows in a table are like balls in a basket. Which ball is the last ball?
Re: order of geting value from sequence by trigger [message #652107 is a reply to message #652101] Wed, 01 June 2016 15:56 Go to previous messageGo to next message
miodas007
Messages: 2
Registered: June 2016
Junior Member
Quote:
If all inserts are made in one session, yes.

This inserts are made in diffrent sessions.
Quote:
Otherwise you first have to define "order", order by insert statement execution time or order by transaction start time or transaction end (that is commit) time.

Assume two scenario:
1. by execution time
2. by transaction end time
What answer is for this cases?

[Updated on: Wed, 01 June 2016 15:57]

Report message to a moderator

Re: order of geting value from sequence by trigger [message #652108 is a reply to message #652107] Wed, 01 June 2016 23:21 Go to previous messageGo to next message
Michel Cadot
Messages: 67920
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes (if you are not in RAC and assuming "execution time" means when the trigger gets the sequence value) and no.

[Updated on: Wed, 01 June 2016 23:22]

Report message to a moderator

Re: order of geting value from sequence by trigger [message #652554 is a reply to message #652108] Mon, 13 June 2016 09:28 Go to previous messageGo to next message
BillKoster
Messages: 7
Registered: June 2016
Location: Silver Spring, MD
Junior Member
Minor correction .. "SEQUENCE values only increase" is not correct. Sequences can be defined with a negative increment.
Re: order of geting value from sequence by trigger [message #652557 is a reply to message #652554] Mon, 13 June 2016 09:58 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Why do you care which one is the last row? If it really bothers you then add a column to the table to hold the session id and sort on both. However again, why do you care. A sequence only guarantees a unique Identifier for the row. Who cares otherwise.
Previous Topic: Extract the most data after joining 3 tables
Next Topic: External Table and Blank spaces
Goto Forum:
  


Current Time: Tue Jul 27 19:11:32 CDT 2021