Remember all oracle, adjust as necessary
So we create a sequence as the data owner (MCGIS). It will be shared (this is for fittings)
CREATE SEQUENCE MCGIS.FIT_SEQUENCE
START WITH 159082
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER;
Then we create triggers (in this case, for water, reclaimed, and sewer fittings). Showing one of them (reclaimed) below. The MCGIS.A2436 is the add table of the Feature Class, determined from the SDE.Table_Registry Registration_ID column
TABLE_NAME of feature class = Reclaimed_Fittings, Registration_ID = 2436
CREATE OR REPLACE TRIGGER MCGIS."UWOW_RFIT_TRIGGER" BEFORE INSERT ON MCGIS.A2436 FOR EACH ROW
WHEN (
NEW.UWOW_ID IS NULL
)
DECLARE V_UWOW_ID NUMBER(8); BEGIN
SELECT FIT_SEQUENCE.NEXTVAL INTO V_UWOW_ID FROM DUAL;
:NEW.UWOW_ID := V_UWOW_ID; END;
/
and the next Feature class that shared it would just use the same sequence, for example our sewer one that shares it
CREATE OR REPLACE TRIGGER MCGIS."UWOW_SFIT_TRIGGER" BEFORE INSERT ON MCGIS.A2532 FOR EACH ROW
WHEN (
NEW.UWOW_ID IS NULL
)
DECLARE V_UWOW_ID NUMBER(8); BEGIN
SELECT FIT_SEQUENCE.NEXTVAL INTO V_UWOW_ID FROM DUAL;
:NEW.UWOW_ID := V_UWOW_ID; END;
/
These triggers populate the UWOW_ID field of the feature class with the next available number if null (as to not overwrite if exists).
A few concerns to note that are solved with workflow issues
1. Since versioning is short transactions, this doesnt populate until save edits, when it hits the database
2. It does not prevent the user from manually hosing the ID (it remains editable)
3. If you reload the Feature Class, it breaks the trigger as it gets a new registration ID number.
Does that help?
