I haven't tested this out, so it might not work, but this might do the
trick as long as the software program does not 1) log into the database
using the account which is the owner of the schema where the tables are
located. 2) Does not use owner.tablename to access the tables 3) Access the
tables via a synonym.
- Add the column for autoincrement to the table.
- Create a view for the table which includes the access of the scequence
to insert it into the new column.
- Create a view on top of the view created in step 2 which only shows the
columns other than the column which has the sequence.
- Drop any previous synonym which was used to reference the original
table.
- Create a new synonym using the same name as the synonym droped in step
four except now it will point to the step 3 view.
- Do a test insert to see if this works.
cspeer_at_my-deja.com wrote:
>
> Hello everyone,
>
> We purchased a software program that ties into an Oracle database.
> We noticed that there are no primary/unique keys on the tables. We are
> adding an auto-incrementing field... And herein the problem lies:
>
> In order to do an insert with an auto-incrementing field, you
> would normally create a sequence and then reference it in your INSERT
> INTO clause. However, we do not have that option since we cannot
> rewrite the code of the software that creates the statement. (We have
> dutifully cursed them and they are working on fixing it.)
>
> What alternatives do we have to do auto-incrementation. I heard
> from the software company, that you can create a field-type that
> increments in Oracle, but a) I don't belive it and b) I have not found
> any reference to it anywhere in documentation.
>
> So is there a field type in Oracle (even non-standard) that I can
> use to auto-increment?
>
> I am beginning to think that I will have to write a Trigger/Stored
> Procedure that will accomplish this. (ie, on insert, update the auto-
> incrementing field with max+1).
>
> References to articles would greatly help (especially if there is
> a reference on writing the trigger.)
Received on Tue Sep 28 1999 - 08:18:29 CDT
