boyle.matt

Sql Trigger Not Executing on Initial Insert

Discussion created by boyle.matt on Mar 5, 2020

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

Outcomes