Auto-Increment Field within SDE

12069
30
07-08-2014 01:13 PM
mpboyle
Regular Contributor

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!

30 Replies
JeffPace
MVP Regular Contributor

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

0 Kudos
mpboyle
Regular Contributor

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!

0 Kudos
JeffPace
MVP Regular Contributor

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

0 Kudos
mpboyle
Regular Contributor

Have you implemented this with versioned data or non-versioned data?

0 Kudos
JeffPace
MVP Regular Contributor

its versioned

0 Kudos
mpboyle
Regular Contributor

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.

0 Kudos
JeffPace
MVP Regular Contributor

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

0 Kudos
JeffPace
MVP Regular Contributor

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?

mpboyle
Regular Contributor

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?

0 Kudos