Using multiversion view trigger to maintain unique ID in ArcSDE for Oracle?

Question

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?

Answer 1

You put sequence value into :new.address_id (use the correct column name instead of address_id) and let the insert command do its work:

create trigger A746_insert_trigger
before insert on SDE.A746
for each row
begin
    select address_seq.nextval into :new.address_id from dual;
end;
/

Comment: your code is not complete so maybe you need something different.

Example1, Example2