Sql Trigger Not Executing on Initial Insert
I have a fairly simple Sql trigger set to run on a point feature class. The trigger, however, doesn't seem to execute when the point is initially inserted. Any subsequent edit of the feature will seem to work as expected.
The trigger is on the add delta table of the feature class. The screenshot below is a query of the add delta table showing the edit history of a feature (most recent last). The trigger should update the XCoordinate, YCoordinate, Longitude, and Latitude fields after insert. You can see that on the initial insert of the feature, those fields remain NULL. Any edits after initial insert (rows 2-4) show those fields getting updated.
Is there some reason the trigger isn't working on the initial insert of the feature? I am testing by editing through a web map in ArcGIS Enterprise (10.7.1).
Below are some details of my feature class, database, etc... and the trigger statement.
RDBMS: Sql Server 2014 Standard
Geodatabase Version: 10.7.1
Feature Class is Registered as Versioned
Feature Class is Archived
ALTER TRIGGER [dbo].[triggerName]
ON [dbo].[a999]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- ==================================================
-- UPDATE COORDINATE FIELDS
-- ==================================================
-- update X coordinate field
update t
set t.XCoordinate = i.SHAPE.STX
from
EnterpriseDatabase.DBO.a999 as t
inner join
inserted as i on t.OBJECTID = i.OBJECTID and t.SDE_STATE_ID = i.SDE_STATE_ID
-- update Y coordinate field
update t
set t.YCoordinate = i.SHAPE.STY
from
EnterpriseDatabase.DBO.a999 as t
inner join
inserted as i on t.OBJECTID = i.OBJECTID and t.SDE_STATE_ID = i.SDE_STATE_ID
-- update Longitude field
update t
set t.Longitude = (select db1.dbo.get_longitude(i.SHAPE.STX, i.SHAPE.STY))
from
EnterpriseDatabase.DBO.a999 as t
inner join
inserted as i on t.OBJECTID = i.OBJECTID and t.SDE_STATE_ID = i.SDE_STATE_ID
-- update Latitude field
update t
set t.Latitude = (select db1.dbo.get_latitude(i.SHAPE.STX, i.SHAPE.STY))
from
EnterpriseDatabase.DBO.a999 as t
inner join
inserted as i on t.OBJECTID = i.OBJECTID and t.SDE_STATE_ID = i.SDE_STATE_ID
END
