I'm trying to identify the best way to automatically maintain a unique id column in Oracle ArcSDE. I don't want to use a class extension, since I've multiple third party users of the data and it's too much of a headache. I thought I was being clever by editing the insert trigger created by the creation of a multiversion view for the feature class.
I created a sequence in Oracle, and then tried to use sequenceName.NEXTVAL to insert that into the appropriate column. The trigger compiles fine, and I can edit the feature class and insert new features just fine, but my sequence value is not getting inserted into the row. For example, I wrote
addNextVal := address_seq.nextval; INSERT INTO SDE.A746 VALUES (next_rowid, addNextVal,:new.SUITE, ...
but it didn't work.
Any thoughts on the approach, and what I might do to solve this problem?