Has anyone successfully implemented a way of auto-incrementing an integer field within a SDE (Sql Server) geodatabase? If so, would you be willing to share your method with examples?
I'd like to have an integer field that increments anytime a feature is created (inserted) and not have to rely on scripts afterwards to populate the field.
Our current system is:
ArcSDE: 10.1 sp1
RDBMS: Sql Server 2008 R2
Thanks in advance!
We use a custom trigger and sequence in oracle. Would that work? If so maybe you can adapt it, happy to share. We have been using it for years on SDE to autopopulate sequential fields in a versioned database
I've heard using a sequence and trigger has worked. Unfortunately, I'm not a DBA and don't really understand creating a sequence or how the trigger would have to be written.
Questions:
1) Do you think if I explained this to our Sql DBA he'd understand what I'm after...?
2) Is the sequence stored as a table...?
3) Can the same sequence be used for multiple feature classes?...create one sequence that's used by multiple features to grab an ID?
Thanks!
1. I think so yes
2. Sequence is stored (at least in oracle) as a sequence, not a table.
3. Yes we share it across multiple FCs
Have you implemented this with versioned data or non-versioned data?
its versioned
How would something like this work if a user is adding a feature from a mobile device? Would the ID only be committed once the feature is posted back to the database or does it get generated on creation?
If there were multiple people editing the same feature class, since you have the trigger on the add table, does this 'support' versioning where duplicate numbers wouldn't get assigned?
Points 2 and 3 are understood.
I Believe since feature layers auto save it is immediately committed To the database. I will try and double check this Friday, I'm out the rest of the week for a medical thing, if you could remind me that would be great.
as for conflict resolution, we deal with the same issue. as long as your feature layer is published as a single version (and you are not changing it with setgdbversion) then as soon as it is set it will not be overwritten with a new number, as the next editor will see the previous editors "change" which includes the creation of the id
if everyone has their own version, since feature layers don't reconcile and post, it's last in wins And overwrites the others
i would be remiss without saying this, having field editors working on the same data, at the same time, is a workflow issue, not a technology issue. It should be handled as such
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?
Coming back to this topic...has anyone tried implementing a sequence to auto-increment a field within SQL Server...? Are sequences only available at SQL Server 2012 and above?...are they available within 2008 R2?