At 10:58 PM 28/04/2003 +0000, you wrote:

>Hi Luc,
>
>Yep, understand about generators being outside transactions...but if
>I can guarantee the only place the generator is called is in the
>insert table trigger, was thinking that this would still work? The
>generator will only get incremented in my insert statement within my
>transaction?
>
>Or am I missing something?! (quite likely!! ;-) )

I think perhaps, yes, it comes maybe from your unfamiliarity with
transaction isolation and the architecture of multi-generational record
versions.

Your trigger to generate the unique ID is different from an autoinc type in
a few quite useful ways. The obvious one is that the generator is not
linked to the table (albeit the database will store the *dependency*
between your trigger and the generator and prevent you from dropping the
generator). The other is that generators spin off numbers independently of
any user transactions.

This means that the timing of the number-fetch is not dependent on the
committal of any DML statement. It means that you can pre-fetch the number
into your client application's current transaction context, confident that
the number is totally unique. You can apply it to both the master key and
the referencing key of a master-detail relationship set, without needing to
commit the master record first. This allows you to isolate all of the
current work on that structure inside the transaction and to commit it
entirely, or to roll back the entire structure without impacting the
database state. This is the prime objective of transaction isolation.

In effect, the trigger is a safeguard to guarantee that the column will get
a unique number if an insert is attempted by an application that doesn't
draw the number before submitting an update request. For example, when
inserting new rows via an interactive SQL tool.

The techniques described here, by Luc and others, specifically address
*your* requirement to know the new id before the new work is posted to the
database. If you don't need to know the id, then you can happily proceed
with letting your "auto-inc" trigger do its work. To achieve that, you
simply omit that column from your INSERT statement (or your UPDATE
statement, in the case where you have a BEFORE UPDATE trigger that draws on
a generator for some purpose).

Recalling an earlier posting by you, I'm wondering whether you still think
you need to capture the generated number because you have to explicitly
update the generator... if so, you can totally forget it. The database
engine itself takes care of maintaining the unique number
series. Transaction isolation protects the integrity of the database state
and in the multi-user environment. Using application code to maintain
unique values is - at best - unproductive and - at worst - dangerous to
data state integrity.

heLen