topic Re: Sql Trigger Not Executing on Initial Insert in Geodatabase Questions

Sql Trigger Not Executing on Initial Insert

mpboyle — Sun, 12 Dec 2021 09:27:23 GMT

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‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Sql Trigger Not Executing on Initial Insert

HanliePetoors — Mon, 12 Apr 2021 11:06:56 GMT

Hi,

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

Create the geometry and OBJECTID.
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

Re: Sql Trigger Not Executing on Initial Insert

vijaybadugu — Tue, 04 Feb 2025 02:35:36 GMT

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 .

Re: Sql Trigger Not Executing on Initial Insert

HanliePetoors — Wed, 05 Feb 2025 06:44:49 GMT

@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

Re: Sql Trigger Not Executing on Initial Insert

MarceloMarques — Wed, 05 Feb 2025 17:10:28 GMT

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:

Create the geometry and OBJECTID.
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.

Re: Sql Trigger Not Executing on Initial Insert

MarceloMarques — Wed, 05 Feb 2025 17:10:51 GMT

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:

Create the geometry and OBJECTID.
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.