Auto-Increment Field within SDE

15875
31
07-08-2014 01:13 PM
mpboyle
Occasional Contributor III

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!

31 Replies
mpboyle
Occasional Contributor III

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

CREATE TRIGGER [dbo].[TriggerName]
   ON  [DbName].[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
     @seq int,   -- sequence value
     @aOID int,  -- add delta table OID value
     @bOID int,  -- base table OID value
     @iOID int,  -- inserted OID value
     @iId int,   -- inserted ID value
     @newId int  -- flag for whether or not a new ID value is needed


     -- set inserted variables
     SET @iOID = (SELECT TOP(1) i.OBJECTID 
                  FROM [DbName].[SchemaName].[AddDeltaTable] a, inserted i 
                  WHERE a.OBJECTID = i.OBJECTID AND a.SDE_STATE_ID = i.SDE_STATE_ID)

     SET @iId = (SELECT TOP(1) i.ID
                 FROM [DbName].[SchemaName].[AddDeltaTable] a, inserted i 
                 WHERE a.OBJECTID = i.OBJECTID AND a.SDE_STATE_ID = i.SDE_STATE_ID)

     -- set base oid variable
     SET @bOID = (SELECT TOP(1) b.OBJECTID
                  FROM [DbName].[SchemaName].[BaseTable] b
                  WHERE b.ID = @iID)
     IF @bOID IS NULL
          BEGIN
               SET @bOID = -1
          END

     -- set add delta oid variable
     SET @aOID = (SELECT TOP(1) a.OBJECTID
                  FROM [DbName].[SchemaName].[AddDeltaTable] a
                  WHERE a.ID = @iID)
     IF @aOID IS NULL
          BEGIN
               SET @aOID = -1
          END

    -- ============================================================
    -- RUN CHECK OF INSERTED ID VALUE
    -- ============================================================

     -- set default value for new id flag
     SET @newId = 0

     -- if inserted ID value is NULL, update ID field
     IF @iId IS NULL
          BEGIN
               SET @newId = @newId + 1
          END

     -- check if inserted OID equals base OID
     IF @iOID <> @bOID AND @bOID <> -1
          BEGIN
               SET @newId = @newId + 1
          END

     -- check if inserted OID equals add delta OID
     IF @iOID <> @aOID AND @bOID <> -1
          BEGIN
               SET @newId = @newId + 1
          END

    -- ============================================================
    -- CHECK IF INSERTED ID VALUE SHOULD BE UPDATED
    -- ============================================================
     IF @newId > 0

          BEGIN

               -- get next sequence value
               SET @seq = NEXT VALUE FOR [DbName].[SchemaName].[Sequence]

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

          END

END
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
xander
by
New Contributor III

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.:)

0 Kudos
xander
by
New Contributor III

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

0 Kudos
RuchiraWelikala2
New Contributor III

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

0 Kudos
xander
by
New Contributor III

Thanks, I'll give it a try. One of my GDBs has several owners.

0 Kudos
KeithGerhartz1
Occasional Contributor II

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.

SET @maxbasetableid = (SELECT max(ADDRESSID)
    FROM MADB.DBO.MADB)
SET @maxdeltatableid = (SELECT max(ADDRESSID)
    FROM MADB.DBO.A28)
IF @maxdeltatableid > @maxbasetableid 
    BEGIN
       SET @maxid = @maxdeltatableid + 1;
       END
 ElSE 
    BEGIN
       SET @maxid = @maxbasetableid + 1;
    END
-- update sequence field
 UPDATE a
    SET a.ADDRESSID = @maxid 
    FROM MADB.DBO.A28 a, inserted i
    WHERE a.OBJECTID = i.OBJECTID AND a.SDE_STATE_ID = i.SDE_STATE_ID
0 Kudos
mpboyle
Occasional Contributor III

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).

0 Kudos
YungKaiChin
Occasional Contributor

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.

0 Kudos
PaulLohr
Occasional Contributor III

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.

0 Kudos

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.

0 Kudos