[Solar-talk] Oracle adapter

[Kilbride, James P.]

Folks,

I'm working on an Oracle adapter and trying to figure out the best
approach to 'describing' the columns autoincrementiness. For those of
you who aren't familiar with Oracle it doesn't have the pretty little
'autoincrement' descriptor for tables. To do autoincrementing tables
what you do is create a sequence and a trigger. The trigger replaces, on
insert, the column's value with the next value in the sequence. 

Like this:

BEGIN
        SELECT asset_sequence.NEXTVAL INTO :NEW.Asset_ID FROM dual;
    END;

So this might be in a trigger called 'TRG_INSERT_ASSET_PK'. You have to
write your own triggers and the sequences for it of course. So given
that fact I can do a select * from user_triggers to get the triggers and
do a match on table_name to find out if there is a trigger on the table
we are interested in, and we can look at the triggering_event to see if
it occurs on insert(which an autoincrement would) and could then pull
the TRIGGER_BODY to get that code. And I think w could then search for
:NEW.* to get the column name and match that against one of the columns
in the table. 

So that might all work to set the boolean for autoincrement, but is this
the right way to do it? This is the ugly pain of oracle 'autoincrement'.
It assumes that people don't muck with it and assumes that just because
there is a trigger that we must be using it for auto-increment. the
sequence name 'should' probably follow a convention like we see above
but it doesn't have to technically. 

Thoughts or ideas folks?

James Kilbride
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman-mail3.webfaction.com/pipermail/solar-talk/attachments/20080118/2e0b5a2c/attachment.html