So I am looking for assistance working with triggers on a point feature class in a 10.7.1 Enterprise GDB on SQL Server 2016. The feature class is archived-enabled. The data is non-versioned.
We are migrating an old ArcGIS mobile project to an ArcGIS Runtime for .NET app for field use, data is taken offline and edits sync'd via a feature service on ArcGIS Server 10.7.1. The core of this feature class is a Crossing_ID which is our own unique ID that binds a larger solution together, we have always managed the Crossing_ID by using triggers on the business table to increment it (ie. MAX + 1) when a new feature was inserted. The feature class and triggers have been in place and working great for many years, since we enabled archiving a few weeks back, development / testing of the new solution has gone nowhere.
The trigger as we have it is working fine on the business table, we are using an AFTER INSERT so it increments the Crossing_ID as expected when new features are created; the issue lies on the device running the ArcGIS Runtime app that created the new feature. When created on the mobile device that new feature does not have a Crossing_ID, the edit is sent to the ArcGIS Server / DB and one is assigned. That new Crossing_ID is never synced back to the device that created it....unless that feature is edited elsewhere. It appears when we assign the Crossing ID in the trigger we are not performing a 'normal' edit which I assume would increment the GDB_ARCHIVE_OID, GDB_FROM_DATE, and GDB_TO_DATE; as a result that device does not see the addition of a new Crossing_ID as a change that needs to be pulled back to its Runtime Geodatabase. Also, if that same device makes an attribute change to that same feature it created, since the Crossing_ID is still NULL, when it sync's the new edit it wipes out the Crossing_ID in the database setting it to NULL which effectively breaks our entire solution.
So....is it possible to continue using our own triggers to accomplish tasks like this (maintain our own Unique ID) when using an archive-enabled feature class? If it is, how should this be handled in the database / feature class to force that new Crossing_ID back to the device that created it? Thanks in advance. Our current trigger is below:
CREATE TRIGGER [Schema].[RCCS_RoadCrossings_Inserted]
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
DECLARE @insertedID as int
SELECT @insertedID=ins.CROSSING_ID from inserted ins;
--Check for new crossing
IF @insertedID is NULL
--Get Largest Crossing ID
DECLARE @myMAX as int
SELECT @myMAX=MAX(CROSSING_ID) from Database.Schema.ROADCROSSINGS
--Update the table
SET CROSSING_ID = @myMAX+1
FROM Database.Schema OriginalRC
INNER JOIN INSERTED InsertedRC ON OriginalRC.OBJECTID = InsertedRC.OBJECTID;