Re: Should/Can I use Sequence for this ?

"xtanto" <krislioe_at_gmail.com> wrote in message news:e1c9bd55.0409271911.5d3f28d4_at_posting.google.com...
| Hi,
| Thanks for the reply.
|
| OK, for table-based solution
| > the app would increment the value in the department row (which
unfortunately > serialized transactions at the department level)
|
| Is the serializable by default ?
| Or what command should I use to make sure that no two document use same
sequence
| number ?
|
| Thank you,
| tanto
|

(if you sw allows it, please post your replies at the bottom of the thread -- that seems to be the custom in this group)

the serialization is due to the lock oracle acquires on row being updated (department) until the transaction is committed or rolled back

regarding 'what command should I use to make sure that no two document use same sequence' -- not really clear about what you're asking, whether you're referring to two documents in the same department or just two documents in the same table. from a database standpoint, you will need a UNIQUE constraint on either the document id column, or a UNIQUE constraint on the combination of the department id and the document id.

how you assign the value depends on the application's businesses requirements -- the most important questions are:

[_] is it absolutely essential that documents are numbered sequentially, with no gaps in the sequence?
[_] is it absolutely essential that each department has its own series of document ids?

if you can answer no to these, just use one oracle sequence object and a before insert trigger to assign the document id if you answered yes to either of these questions, then you can't use sequence objects

if you can clarify your exact requirements, i'll try to post some examples

++ mcs Received on Tue Sep 28 2004 - 06:36:31 CDT