Select to view content in your preferred language

Sql Trigger Not Executing on Initial Insert

3380
5
03-05-2020 08:42 AM
mpboyle
Frequent Contributor

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‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
0 Kudos
5 Replies
HanliePetoors
Frequent Contributor

Hi,

I have come across this as well. ArcGIS seems to execute two db operations to create a spatial record:

  1. Create the geometry and OBJECTID.
  2. Update the record with the rest of the attribute values.

It looks like one operation to the front-end user, but in the background it is actually two. The solution is to put triggers for attribute values in an AFTER UPDATE trigger.

 

Regards

Hanlie

vijaybadugu
Frequent Contributor

Trigger with After insert does not work for sometimes . I’m getting “object with globalId missing error “. It was working fine and suddenly , I’m getting this error .

0 Kudos
HanliePetoors
Frequent Contributor

@vijaybadugu I'm not sure what is going on with your trigger, especially if it was working before. Can you post your SQL here?

Regards

Hanlie

0 Kudos
MarceloMarques
Esri Regular Contributor

FYI:

https://community.esri.com/t5/geodatabase-questions/sql-trigger-not-executing-on-initial-insert/td-p...

ArcGIS seems to execute two db operations to create a spatial record:

  1. Create the geometry and OBJECTID.
  2. Update the record with the rest of the attribute values.

It looks like one operation to the front-end user, but in the background it is actually two. The solution is to put triggers for attribute values in an AFTER UPDATE trigger.

| Marcelo Marques | Esri Principal Product Engineer | Cloud & Database Administrator | OCP - Oracle Database Certified Professional | "In 1992, I embarked on my journey with Esri Technology, and since 1997, I have been working with ArcSDE Geodatabases, right from its initial release. Over the past 32 years, my passion for GIS has only grown stronger." | “ I do not fear computers. I fear the lack of them." Isaac Isimov |
0 Kudos
MarceloMarques
Esri Regular Contributor

I am adding the link below from our conversation, so others are aware.

https://community.esri.com/t5/geodatabase-questions/sql-trigger-not-executing-on-initial-insert/td-p...

ArcGIS seems to execute two db operations to create a spatial record:

  1. Create the geometry and OBJECTID.
  2. Update the record with the rest of the attribute values.

It looks like one operation to the front-end user, but in the background it is actually two. The solution is to put triggers for attribute values in an AFTER UPDATE trigger.

| Marcelo Marques | Esri Principal Product Engineer | Cloud & Database Administrator | OCP - Oracle Database Certified Professional | "In 1992, I embarked on my journey with Esri Technology, and since 1997, I have been working with ArcSDE Geodatabases, right from its initial release. Over the past 32 years, my passion for GIS has only grown stronger." | “ I do not fear computers. I fear the lack of them." Isaac Isimov |
0 Kudos