nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Auto-Increment Field within SDE

mpboyle

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!

Find more posts tagged with

Accepted answers

All comments

JeffPace1

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

mpboyle

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!

JeffPace1

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

mpboyle

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

JeffPace1

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?

JeffPace1

its versioned

mpboyle

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.

mpboyle

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?

JarrettGreen

Hi Matthew,

Have you had any luck doing this with SQL server? My trigger works as expected in ArcGIS Desktop, but does not work in ArcGIS Online. I've got SQL Server 2012 and AGS 10.3

I'm relying on the inserted table to count records.

mpboyle

Coming back to this thread nearly 2 years after original posting... finally had some time dive into Sql Server sequences, and they are surprisingly easy to understand, even for someone like me who is not a DBA, or Sql Server expert.

I have been testing out using a sequence within a Sql Server trigger and have found some success. Below are some template screenshots others may find useful.

Some quick notes about our environment:

ArcGIS version: 10.4.1
DBMS: Sql Server 2014 Standard

Step 1: create your sequence --- some background reading on their arguments is probably useful...

t-sql examples are provided in the above link
can also use SSMS --- expand database | Programmability | Sequences | New Sequence

Step 2: apply your sequence to a Sql trigger

Notes:
- The trigger template was provided to us by an Esri solution engineer; I'm not a DBA ... so the t-sql written may be improved upon without my knowing
- Line 14: if using a versioned dataset, your table name will need to be the associated 'add' (a) delta table
- Line 15: this trigger ONLY runs after an insert (create)
- Line 25: this trigger ONLY runs on the inserted record, not all records

Step 3: test Sql trigger

The highlighted fields in the screenshot below were all generated from Sql triggers
- Seq = next value within my sequence (sequence started at 1001) --- only on inserts
- Seq String = concatenation of 'WS-' and sequence value --- only on inserts
- Random = random 10-character string --- only on inserts
- Result Display = concatenation of Collection Date, Test, Result Value, and Result Unit --- on inserts and updates

JoeBorgione

Way to stick with it Matthew- this is really good stuff!

boyle.matt‌

NataliyaLys

This is great. We were looking for this solution for so long. I was able to implement this trigger. And it works great.

I do have one question, though: if I edit an existing record in my attribute table the trigger assigns a new sequential number to that record? I am probably missing something in the trigger?

Thanks,

Nataliya

mpboyle

I checked one of the triggers I'm using and noticed a slight variance than the above screenshot...which I probably noticed after trying to edit features!

The sample below should take care of handling features were a sequence has already been applied. The add (a) delta table for this test feature class has the trigger below applied to it, which is set to update the ID field on an insert/create IF the ID field is NULL. You can see on subsequent edits of the feature, the 'ID' field remains the same value because the field is not NULL, and thus the trigger is skipped.

Modified trigger to account for already-populated sequence value. The 'IF' statement on line 27 will check the inserted table to see if the sequence field (ID in this example) is already populated. If it's not, the next value within the sequence will be grabbed and populated into the 'ID' field as shown on line 35.

I blocked out our database and schema name. Simply replace with your values.

Add (a) delta table showing edits. ID field remains as-is on subsequent edits because it is already populated.

NataliyaLys

Matt,

Thank you very much. That is exactly what I needed. I am not DBA either, I just know enough to be dangerous .

Thanks,

Nataliya

RuchiraWelikala2

Hi Matthew,

Thank you so much for this solution. It works great, however, did you run into an error such as this when reconciling?

ESRI has a Tech Article on it.

I was wondering how you got around it if you experienced this sort of error.

Thanks

xander

Hi Mathew. Does this take in to account the feature copy/paste in an versioned edit session? Cheers and great work!

mpboyle

That's a good question! ... and something I hadn't really considered. Here is some testing I did this morning, and the trigger as above will NOT work with copying and pasting features ... the ID value will remain the same because the ID value of the inserted row is NOT NULL.

I altered the trigger to use the following logic, with sample testing shown below, and the trigger SQL at the very end.

As always, test on your end to make sure you're getting the desired outcome!!!

Get the inserted OID value
Get the inserted ID value
Get the corresponding base table OID value of inserted row based on inserted ID value, if any. If none, set variable to -1 (will not exist)
Get the corresponding add delta table OID value of inserted row based on inserted ID value, if any. If none, set variable to -1 (will not exist)
Create default 'flag' value of 0
If inserted ID value is NULL, add 1 to the existing flag value
If inserted OID value does NOT match corresponding base table OID value AND base table OID value does not equal -1 (i.e., it exists), add 1 to the existing flag value
If inserted OID value does NOT match corresponding add delta table OID value AND add delta table OID value does not equal -1 (i.e., it exists), add 1 to the existing flag value
Check if the existing flag value is greater than 0, indicating the ID value should be changed
- Grab the next sequence value
- Update the inserted ID value to the next sequence value

Start

Inserted 2 points new features --- features 1027, 1028 (red)

New features in add delta table

Copied, pasted, and moved (a few times to register delta table inserts) feature 1027 --- copied feature is 1029 (blue)

Add delta table after copy, paste, and move ---> new ID value (1029) for copied feature that existed in delta table

Copied, pasted, and moved (again, a few times) feature 1003 --- only existed in the base table --- copied feature is 1030 (blue)

Add delta table after copy, paste, and move --- new ID value for copied feature that only existed in the base table

Moved around existing features a few times to make sure their ID values didn't change

Add delta table after moving/editing existing features (1001, 1002, 1003)

Add delta table after saving edits --- leaving last edits (2 inserts, 2 copies, 3 modifies) --- new IDs for inserts and copies, IDs remained as-is for existing features

<SPAN class="keyword token">CREATE</SPAN> <SPAN class="keyword token">TRIGGER</SPAN> <SPAN class="punctuation token">[</SPAN>dbo<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">[</SPAN>TriggerName<SPAN class="punctuation token">]</SPAN>
&nbsp;&nbsp; <SPAN class="keyword token">ON</SPAN>&nbsp; <SPAN class="punctuation token">[</SPAN>DbName<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">[</SPAN>SchemaName<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">[</SPAN>AddDeltaTable<SPAN class="punctuation token">]</SPAN>&nbsp; <SPAN class="comment token">-- add delta table name</SPAN>
&nbsp;&nbsp; <SPAN class="keyword token">AFTER</SPAN> <SPAN class="keyword token">INSERT</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="keyword token">UPDATE</SPAN>
<SPAN class="keyword token">AS</SPAN> 
<SPAN class="keyword token">BEGIN</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="comment token">-- SET NOCOUNT ON added to prevent extra result sets from</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="comment token">-- interfering with SELECT statements.</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">SET</SPAN> NOCOUNT <SPAN class="keyword token">ON</SPAN><SPAN class="punctuation token">;</SPAN>

&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="comment token">-- delcare variables</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">DECLARE</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="variable token">@seq</SPAN> <SPAN class="keyword token">int</SPAN><SPAN class="punctuation token">,</SPAN>&nbsp;&nbsp; <SPAN class="comment token">-- sequence value</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="variable token">@aOID</SPAN> <SPAN class="keyword token">int</SPAN><SPAN class="punctuation token">,</SPAN>&nbsp; <SPAN class="comment token">-- add delta table OID value</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="variable token">@bOID</SPAN> <SPAN class="keyword token">int</SPAN><SPAN class="punctuation token">,</SPAN>&nbsp; <SPAN class="comment token">-- base table OID value</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="variable token">@iOID</SPAN> <SPAN class="keyword token">int</SPAN><SPAN class="punctuation token">,</SPAN>&nbsp; <SPAN class="comment token">-- inserted OID value</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="variable token">@iId</SPAN> <SPAN class="keyword token">int</SPAN><SPAN class="punctuation token">,</SPAN>&nbsp;&nbsp; <SPAN class="comment token">-- inserted ID value</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="variable token">@newId</SPAN> <SPAN class="keyword token">int</SPAN>&nbsp; <SPAN class="comment token">-- flag for whether or not a new ID value is needed</SPAN>


&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="comment token">-- set inserted variables</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">SET</SPAN> <SPAN class="variable token">@iOID</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="keyword token">SELECT</SPAN> <SPAN class="keyword token">TOP</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">)</SPAN> i<SPAN class="punctuation token">.</SPAN>OBJECTID 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">FROM</SPAN> <SPAN class="punctuation token">[</SPAN>DbName<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">[</SPAN>SchemaName<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">[</SPAN>AddDeltaTable<SPAN class="punctuation token">]</SPAN> <SPAN class="number token">a</SPAN><SPAN class="punctuation token">,</SPAN> inserted i 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">WHERE</SPAN> <SPAN class="number token">a</SPAN><SPAN class="punctuation token">.</SPAN>OBJECTID <SPAN class="operator token">=</SPAN> i<SPAN class="punctuation token">.</SPAN>OBJECTID <SPAN class="operator token">AND</SPAN> <SPAN class="number token">a</SPAN><SPAN class="punctuation token">.</SPAN>SDE_STATE_ID <SPAN class="operator token">=</SPAN> i<SPAN class="punctuation token">.</SPAN>SDE_STATE_ID<SPAN class="punctuation token">)</SPAN>

&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">SET</SPAN> <SPAN class="variable token">@iId</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="keyword token">SELECT</SPAN> <SPAN class="keyword token">TOP</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">)</SPAN> i<SPAN class="punctuation token">.</SPAN>ID
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">FROM</SPAN> <SPAN class="punctuation token">[</SPAN>DbName<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">[</SPAN>SchemaName<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">[</SPAN>AddDeltaTable<SPAN class="punctuation token">]</SPAN> <SPAN class="number token">a</SPAN><SPAN class="punctuation token">,</SPAN> inserted i 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">WHERE</SPAN> <SPAN class="number token">a</SPAN><SPAN class="punctuation token">.</SPAN>OBJECTID <SPAN class="operator token">=</SPAN> i<SPAN class="punctuation token">.</SPAN>OBJECTID <SPAN class="operator token">AND</SPAN> <SPAN class="number token">a</SPAN><SPAN class="punctuation token">.</SPAN>SDE_STATE_ID <SPAN class="operator token">=</SPAN> i<SPAN class="punctuation token">.</SPAN>SDE_STATE_ID<SPAN class="punctuation token">)</SPAN>

&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="comment token">-- set base oid variable</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">SET</SPAN> <SPAN class="variable token">@bOID</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="keyword token">SELECT</SPAN> <SPAN class="keyword token">TOP</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="number token">b</SPAN><SPAN class="punctuation token">.</SPAN>OBJECTID
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">FROM</SPAN> <SPAN class="punctuation token">[</SPAN>DbName<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">[</SPAN>SchemaName<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">[</SPAN>BaseTable<SPAN class="punctuation token">]</SPAN> <SPAN class="number token">b</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">WHERE</SPAN> <SPAN class="number token">b</SPAN><SPAN class="punctuation token">.</SPAN>ID <SPAN class="operator token">=</SPAN> <SPAN class="variable token">@iID</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">IF</SPAN> <SPAN class="variable token">@bOID</SPAN> <SPAN class="operator token">IS</SPAN> <SPAN class="token boolean">NULL</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">BEGIN</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">SET</SPAN> <SPAN class="variable token">@bOID</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="operator token">-</SPAN><SPAN class="number token">1</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">END</SPAN>

&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="comment token">-- set add delta oid variable</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">SET</SPAN> <SPAN class="variable token">@aOID</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="keyword token">SELECT</SPAN> <SPAN class="keyword token">TOP</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="number token">a</SPAN><SPAN class="punctuation token">.</SPAN>OBJECTID
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">FROM</SPAN> <SPAN class="punctuation token">[</SPAN>DbName<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">[</SPAN>SchemaName<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">[</SPAN>AddDeltaTable<SPAN class="punctuation token">]</SPAN> <SPAN class="number token">a</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">WHERE</SPAN> <SPAN class="number token">a</SPAN><SPAN class="punctuation token">.</SPAN>ID <SPAN class="operator token">=</SPAN> <SPAN class="variable token">@iID</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">IF</SPAN> <SPAN class="variable token">@aOID</SPAN> <SPAN class="operator token">IS</SPAN> <SPAN class="token boolean">NULL</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">BEGIN</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">SET</SPAN> <SPAN class="variable token">@aOID</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="operator token">-</SPAN><SPAN class="number token">1</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">END</SPAN>

&nbsp;&nbsp;&nbsp; <SPAN class="comment token">-- ============================================================</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="comment token">-- RUN CHECK OF INSERTED ID VALUE</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="comment token">-- ============================================================</SPAN>

&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="comment token">-- set default value for new id flag</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">SET</SPAN> <SPAN class="variable token">@newId</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="number token">0</SPAN>

&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="comment token">-- if inserted ID value is NULL, update ID field</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">IF</SPAN> <SPAN class="variable token">@iId</SPAN> <SPAN class="operator token">IS</SPAN> <SPAN class="token boolean">NULL</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">BEGIN</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">SET</SPAN> <SPAN class="variable token">@newId</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="variable token">@newId</SPAN> <SPAN class="operator token">+</SPAN> <SPAN class="number token">1</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">END</SPAN>

&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="comment token">-- check if inserted OID equals base OID</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">IF</SPAN> <SPAN class="variable token">@iOID</SPAN> <SPAN class="operator token"><></SPAN> <SPAN class="variable token">@bOID</SPAN> <SPAN class="operator token">AND</SPAN> <SPAN class="variable token">@bOID</SPAN> <SPAN class="operator token"><></SPAN> <SPAN class="operator token">-</SPAN><SPAN class="number token">1</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">BEGIN</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">SET</SPAN> <SPAN class="variable token">@newId</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="variable token">@newId</SPAN> <SPAN class="operator token">+</SPAN> <SPAN class="number token">1</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">END</SPAN>

&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="comment token">-- check if inserted OID equals add delta OID</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">IF</SPAN> <SPAN class="variable token">@iOID</SPAN> <SPAN class="operator token"><></SPAN> <SPAN class="variable token">@aOID</SPAN> <SPAN class="operator token">AND</SPAN> <SPAN class="variable token">@bOID</SPAN> <SPAN class="operator token"><></SPAN> <SPAN class="operator token">-</SPAN><SPAN class="number token">1</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">BEGIN</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">SET</SPAN> <SPAN class="variable token">@newId</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="variable token">@newId</SPAN> <SPAN class="operator token">+</SPAN> <SPAN class="number token">1</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">END</SPAN>

&nbsp;&nbsp;&nbsp; <SPAN class="comment token">-- ============================================================</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="comment token">-- CHECK IF INSERTED ID VALUE SHOULD BE UPDATED</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="comment token">-- ============================================================</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">IF</SPAN> <SPAN class="variable token">@newId</SPAN> <SPAN class="operator token">></SPAN> <SPAN class="number token">0</SPAN>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">BEGIN</SPAN>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="comment token">-- get next sequence value</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">SET</SPAN> <SPAN class="variable token">@seq</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="keyword token">NEXT</SPAN> <SPAN class="keyword token">VALUE</SPAN> <SPAN class="keyword token">FOR</SPAN> <SPAN class="punctuation token">[</SPAN>DbName<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">[</SPAN>SchemaName<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">[</SPAN>Sequence<SPAN class="punctuation token">]</SPAN>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="comment token">-- update sequence field</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">UPDATE</SPAN> <SPAN class="number token">a</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">SET</SPAN> <SPAN class="number token">a</SPAN><SPAN class="punctuation token">.</SPAN>ID <SPAN class="operator token">=</SPAN> <SPAN class="variable token">@seq</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">FROM</SPAN> <SPAN class="punctuation token">[</SPAN>DbName<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">[</SPAN>SchemaName<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">[</SPAN>AddDeltaTable<SPAN class="punctuation token">]</SPAN> <SPAN class="number token">a</SPAN><SPAN class="punctuation token">,</SPAN> inserted i
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">WHERE</SPAN> <SPAN class="number token">a</SPAN><SPAN class="punctuation token">.</SPAN>OBJECTID <SPAN class="operator token">=</SPAN> i<SPAN class="punctuation token">.</SPAN>OBJECTID <SPAN class="operator token">AND</SPAN> <SPAN class="number token">a</SPAN><SPAN class="punctuation token">.</SPAN>SDE_STATE_ID <SPAN class="operator token">=</SPAN> i<SPAN class="punctuation token">.</SPAN>SDE_STATE_ID

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">END</SPAN>

<SPAN class="keyword token">END</SPAN>
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

xander

Thanks, Matt. I am finally getting back to this and I'll let you know how things work out. I'm sure Ill have a few more questions.:)

xander

Does this trigger need to be against the dbo or owner schema?

RuchiraWelikala2

It has to be the owner schema, generally. I've set it up that way and it works like a charm.

KeithGerhartz1

Thank you for this. I modified slightly to increment from the largest value in the ID field as opposed to using a sequence. Largest value may exist in the base table or delta table so had to consider both.

<SPAN class="keyword token">SET</SPAN> <SPAN class="variable token">@maxbasetableid</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="keyword token">SELECT</SPAN> <SPAN class="token function">max</SPAN><SPAN class="punctuation token">(</SPAN>ADDRESSID<SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">FROM</SPAN> MADB<SPAN class="punctuation token">.</SPAN>DBO<SPAN class="punctuation token">.</SPAN>MADB<SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">SET</SPAN> <SPAN class="variable token">@maxdeltatableid</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="keyword token">SELECT</SPAN> <SPAN class="token function">max</SPAN><SPAN class="punctuation token">(</SPAN>ADDRESSID<SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">FROM</SPAN> MADB<SPAN class="punctuation token">.</SPAN>DBO<SPAN class="punctuation token">.</SPAN>A28<SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">IF</SPAN> <SPAN class="variable token">@maxdeltatableid</SPAN> <SPAN class="operator token">></SPAN> <SPAN class="variable token">@maxbasetableid</SPAN> 
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">BEGIN</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">SET</SPAN> <SPAN class="variable token">@maxid</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="variable token">@maxdeltatableid</SPAN> <SPAN class="operator token">+</SPAN> <SPAN class="number token">1</SPAN><SPAN class="punctuation token">;</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">END</SPAN>
 <SPAN class="keyword token">ElSE</SPAN> 
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">BEGIN</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">SET</SPAN> <SPAN class="variable token">@maxid</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="variable token">@maxbasetableid</SPAN> <SPAN class="operator token">+</SPAN> <SPAN class="number token">1</SPAN><SPAN class="punctuation token">;</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">END</SPAN>
<SPAN class="comment token">-- update sequence field</SPAN>
 <SPAN class="keyword token">UPDATE</SPAN> <SPAN class="number token">a</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">SET</SPAN> <SPAN class="number token">a</SPAN><SPAN class="punctuation token">.</SPAN>ADDRESSID <SPAN class="operator token">=</SPAN> <SPAN class="variable token">@maxid</SPAN> 
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">FROM</SPAN> MADB<SPAN class="punctuation token">.</SPAN>DBO<SPAN class="punctuation token">.</SPAN>A28 <SPAN class="number token">a</SPAN><SPAN class="punctuation token">,</SPAN> inserted i
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">WHERE</SPAN> <SPAN class="number token">a</SPAN><SPAN class="punctuation token">.</SPAN>OBJECTID <SPAN class="operator token">=</SPAN> i<SPAN class="punctuation token">.</SPAN>OBJECTID <SPAN class="operator token">AND</SPAN> <SPAN class="number token">a</SPAN><SPAN class="punctuation token">.</SPAN>SDE_STATE_ID <SPAN class="operator token">=</SPAN> i<SPAN class="punctuation token">.</SPAN>SDE_STATE_ID<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

mpboyle

You may want to check this. Any time you edit a feature, you're inserting a new row into the delta table. I think your ID value will increment on every edit (assuming this is the only bit of sql in your trigger).

YungKaiChin

Can you set it via SQL Server Management Studio (SSMS)?

In SSMS, right click Modify and under Column Properties you can find the setting under Identity Specification.

KyleGonterwitz

Very impressive solutions presented here! It definitely gets complicated when dealing with versioned data.

There is also this option, an arc tool to add an incrementing ID field in an enterprise geodatabase

Add Incrementing ID Field—Data Management toolbox | ArcGIS Desktop

In Oracle and SQL server this tool performs the functions of creating a sequence and a trigger for you.

mpboyle

This is a great tool, the only downside I see to it is that it can NOT be used on a table or feature classes registered with the geodatabase. If using Sql Server (which we do), I don't believe you can specify a field with an identity property if using a standard table or feature class.

PaulLohr

YungKai Chin,

I asked technical support this question. The feature class can't be edited if Identity Specification is set to 'Yes'. Here's the basis for this: FAQ: Is the Identity data type supported in versioned SQL Server geodatabases?

By the way, I don't think 'identity' is a data type. It is a property of a column. But we know what was meant.

MEDCGISAdmin

hello dear, I have the same task
I want to auto-update a field if any new feature added to the Editor version
for example :
I have a feature and I already update a field with certain code using SQL so I want to auto-complete the code updating in the same field once a new feature created

the code format to be OLU0001F --->> the only number to be incremented is 0001-0002-0003,...etc

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.