topic Re: Auto-Increment Field within SDE in ArcGIS Enterprise Questions

Auto-Increment Field within SDE

mpboyle — Tue, 08 Jul 2014 20:13:28 GMT

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!

Re: Auto-Increment Field within SDE

JeffPace — Tue, 08 Jul 2014 20:53:44 GMT

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

Re: Auto-Increment Field within SDE

mpboyle — Tue, 08 Jul 2014 21:00:31 GMT

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!

Re: Auto-Increment Field within SDE

JeffPace — Tue, 08 Jul 2014 21:03:53 GMT

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

Re: Auto-Increment Field within SDE

mpboyle — Tue, 08 Jul 2014 21:10:59 GMT

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

Re: Auto-Increment Field within SDE

JeffPace — Tue, 08 Jul 2014 21:18:10 GMT

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?

Re: Auto-Increment Field within SDE

JeffPace — Tue, 08 Jul 2014 21:18:41 GMT

its versioned

Re: Auto-Increment Field within SDE

mpboyle — Tue, 08 Jul 2014 21:26:05 GMT

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.

Re: Auto-Increment Field within SDE

JeffPace — Tue, 08 Jul 2014 23:49:00 GMT

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

Re: Auto-Increment Field within SDE

mpboyle — Thu, 18 Sep 2014 14:07:31 GMT

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?

Re: Auto-Increment Field within SDE

JarrettGreen — Mon, 18 Jan 2016 20:05:57 GMT

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.

Re: Auto-Increment Field within SDE

mpboyle — Mon, 13 Feb 2017 19:01:34 GMT

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

Re: Auto-Increment Field within SDE

JoeBorgione — Mon, 13 Feb 2017 19:27:03 GMT

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

boyle.matt‌

Re: Auto-Increment Field within SDE

NataliyaLys — Wed, 28 Jun 2017 19:03:15 GMT

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

Re: Auto-Increment Field within SDE

mpboyle — Wed, 28 Jun 2017 19:41:33 GMT

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.

Re: Auto-Increment Field within SDE

NataliyaLys — Thu, 29 Jun 2017 13:18:19 GMT

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

Re: Auto-Increment Field within SDE

RuchiraWelikala2 — Wed, 27 Jun 2018 14:43:23 GMT

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

Re: Auto-Increment Field within SDE

mpboyle — Sat, 11 Dec 2021 19:29:26 GMT

Ruchira Welikala‌,

I was reading through the SQL statement I posted above and noticed a couple things that are omitted in the triggers we use for versioned datasets.

I added the SDE_STATE_ID field in the where clauses because this is a key field in the add delta table. Simply using the OBJECTID field can return many records in the add delta table (because there could be multiple edits on a single feature), but using BOTH the OBJECTID field and the SDE_STATE_ID field should return a unique record in the add delta table.
I include TOP(1) in the SELECT statement to check whether or not the ID field is empty.

Below is an updated trigger t-SQL statement, as well as sample records in the add delta table. I have gotten the error you mention before, and I believe making these adjustments cleared things up.

Using this trigger I was able to insert 3 separate features and edit them several times.

CREATE TRIGGER [dbo].[TriggerName]
   ON  [DatabaseName].[SchemaName].[AddDeltaTable]  -- add delta table name
   AFTER INSERT, UPDATE
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Delcare variables
    DECLARE
    @seqId int


    -- =+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
    -- UPDATE ID FIELD BASED ON SEQUENCE VALUE
    -- =+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=

    -- check if inserted record has a NULL ID value
    IF (SELECT TOP(1) i.ID 
    FROM [DatabaseName].[SchemaName].[AddDeltaTable] a, inserted i 
    WHERE a.OBJECTID = i.OBJECTID AND a.SDE_STATE_ID = i.SDE_STATE_ID) IS NULL

    BEGIN

        -- get next sequence value
        SET @seqId = NEXT VALUE FOR [DatabaseName].[SchemaName].[SequenceName]

        -- update sequence field
        UPDATE a
        SET a.ID = @seqId
        FROM [DatabaseName].[SchemaName].[AddDeltaTable] a, inserted i
        WHERE a.OBJECTID = i.OBJECTID AND a.SDE_STATE_ID = i.SDE_STATE_ID

    END

END‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Auto-Increment Field within SDE

RuchiraWelikala2 — Wed, 27 Jun 2018 18:50:28 GMT

Ah, that makes perfect sense. I had created a trigger like this on Oracle (and leveraging the SDE_STATE_ID) but I didn't use it on this, which is on MSSQL. I updated the trigger and it seems to have resolved the issue. Thank you very much, Matthew.

Re: Auto-Increment Field within SDE

xander — Thu, 06 Sep 2018 11:04:13 GMT

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