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!
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!!!
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
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.:)
Does this trigger need to be against the dbo or owner schema?
It has to be the owner schema, generally. I've set it up that way and it works like a charm.
Thanks, I'll give it a try. One of my GDBs has several owners.
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
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).
What about global id ? can we update globalid too ? I am getting 0's in global ids when i update other fields
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.
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.