Auto-Increment Field within SDE
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
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.
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
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
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
