I've been developing an application against MySQL while a new development server has been slowly being set up. The new dev server will use oracle, and since we're getting close to it being ready, I want to remove the auto_increment attribute from my primary keys, and simulate Oracle's sequences. I haven't used oracle for several years.
Since every pre-insert trigger is going to do the same thing - get the next id for table foo out of *foo_sequence*, does Oracle make any allowances for implementing some form of dynamic/lexical triggering ?
Any tips, tricks or gotchas?
EDIT #1: 1550 - this is not a heavy-use app. a single use will cause 13 inserts of small, simple records in the biggest transaction. Concurrency is unknown, but the likely hood of one user causing lock starvation of any significance is estimates very low IMO.
EDIT #2 1612: the intent is to simulate oracle sequences in mysql, and then use oracle sequences on oracle.
- I'm implementing a class to act as my interface to the sequence.
- on mysql, I'm implementing a table to hold sequence values, and a stored procedure to get the next value and update the row. The class will call this stored proc and return the value.
- on oracle I'll just get the next value from an oracle sequence. The class will make this request. The Sequence class will request the next value from the sequence.
- the application code will use the Sequence class to obtain the next sequence value for the insert, abstracting the "nextval" implementation details away from the insert statement that consumes the value.