Entering generated unique value.

I have a table, and upon any INSERT to the table, I need to generate a value to be inserted with it. The value is generated separately from the INSERTed row, but must be unique to the table.

Until now I've been gerating this outside the database, testing it's uniqueness (I.E. WHERE Generated_Id = <new_value> and looping until nothing is returned) and then inserting it. This has worked fine so far, but I am concerned as the table gets INSERTs more and more frequently, the time inbetween the generation and the insert may allow for another insert to use that value, causing this INSERT to fail.

So, I've been thinking of creating a PL/SQL function to do the job, but how would I INSERT it? Through a stored procedure? A trigger? How do I guarantee it's uniqueness between the check and the INSERT? Do I need to just catch a failed INSERT and loop iuntil it gets doine correctly?

Please note, that a sequential number is not useful here, I already have an a sequence generating sequential Ids for the row. This generated id, is of fixed length, and is used specifically for obfuscation.

Brian Received on Sun Feb 04 2001 - 12:08:38 CST