topic Re: SQL Trigger in Versioned SDE in ArcGIS Enterprise Questions

SQL Trigger in Versioned SDE

LisaDygert — Sat, 11 Dec 2021 20:23:53 GMT

Hello,

I have been researching creating triggers in SQL to update the edit date and acreage values when an edit is completed on an SDE versioned feature class. I have created the triggers and SQL says that the command is completed successfully, but when I make an edit to the feature class, those fields don't get updated.

I am using SQL Server 2008 R2 Management Studio Express and ArcGIS 10.2.1. The feature class is versioned and I am only using the default version. No other versions were created from that. So, I don't need to reconcile any versions back to the default version. The code I am using is below.

Use [DNR_SDE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [DNR_Edit].[trg_insert_acres_date_fields]
     ON [DNR_Edit].[TRIBAL_TRUST_LANDS]
     AFTER INSERT, UPDATE
AS
BEGIN
     SET NOCOUNT ON;

     UPDATE [DNR_Edit].[TRIBAL_TRUST_LANDS]
          SET edit_date = getdate()
          WHERE OBJECTID IN (SELECT OBJECTID FROM INSERTED)

     UPDATE [DNR_EDIT].[TRIBAL_TRUST_LANDS]
          SET Acres = shape.STArea() * 0.000247105
          WHERE OBJECTID IN (SELECT OBJECTID FROM INSERTED)

END‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Is there a potential delay with a versioned FC before the fields get populated? I know others have used this same coding for an editable GIS web application and it is immediate, but I cannot figure out why it won't work for me.

Also, I would like to have a trigger that would automatically populate the latitude and longitude coordinates for any point added to a feature class or existing point adjusted. Is there any way to do that?

Thanks in advance for any assistance.

Lisa

Re: SQL Trigger in Versioned SDE

RobertScheitlin__GISP — Tue, 16 Sep 2014 12:32:18 GMT

Lisa,

I have not actually worked with adding triggers in SQL Server SDE but I do have some advice about what your issue likely is. The thing to understand about a versioned FeatureClass in SDE is that once a FC is versioned it has three (important) tables that it will use when versioning. There is the Business table likely the one you are editing and the Adds table (a table that holds all the new and edited features that will get moved to the business table once the compress is completed) like the one you need to be editing and the Deletes table that contains a list of feature that are to be deleted when the next compress occurs. So in your workflow you are likely editing the business table and the compress has not occurred yet and when the feature is requested from SDE it is actually getting the Adds table row for that feature and thus you do not see your changes. You can look at the table registry (sde.SDE_Table_Registry) in your geodatbase to determine the name of the adds table for your particular FC (something like DBOwner.a52). The registration_id field will have the number of the a table based on the name of your FC in the table_name column. If you add the trigger to the a table then you will see the edit immediately in ArcGIS and it will be committed to the business table for the FC when the Geodatabase is compressed.

Re: SQL Trigger in Versioned SDE

JakeSkinner — Tue, 16 Sep 2014 12:40:03 GMT

Hi Lisa,

When dealing with versioned data, the delta tables (A & D) are updated when a feature is created/deleted/modified. What you will need to do is create two triggers. One on the A table for inserts (creating new features) and another trigger on the D table for updates (moving/modifying features/attributes).

To find the A and D table, you can query the table_registry table:

select registration_id from sde.sde_table_registry where table_name = 'TRIBAL_TRUST_LANDS'

Note: If you are using a DBO schema, change sde.sde_table_registry to dbo.sde_table_registry.

This query will return the registration_id that you can use to query the A & D tables. Ex:

DNR_Edit.a483

DNR_Edit.d483

You can then create the triggers on each of these tables:

CREATE TRIGGER [DNR_Edit].[trg_insert_acres_date_fields_insert]
     ON [DNR_Edit].[a483]
     FOR INSERT
AS
BEGIN
     SET NOCOUNT ON;
     UPDATE [DNR_Edit].[a483]
          SET edit_date = getdate()
          WHERE OBJECTID IN (SELECT OBJECTID FROM INSERTED)
     UPDATE [DNR_Edit].[TRIBAL_TRUST_LANDS]
          SET edit_date = getdate()
          WHERE OBJECTID IN (SELECT OBJECTID FROM INSERTED)
     
     UPDATE [DNR_Edit].[a483]
          SET Acres = shape.STArea() * 0.000247105
          WHERE OBJECTID IN (SELECT OBJECTID FROM INSERTED)
     UPDATE [DNR_Edit].[TRIBAL_TRUST_LANDS]
          SET Acres = shape.STArea() * 0.000247105
          WHERE OBJECTID IN (SELECT OBJECTID FROM INSERTED)
END

CREATE TRIGGER [DNR_Edit].[trg_insert_acres_date_fields_update]
     ON [DNR_Edit].[d483]
     FOR UPDATE
AS
BEGIN
     SET NOCOUNT ON;
     UPDATE [DNR_Edit].[a483]
          SET edit_date = getdate()
          WHERE OBJECTID IN (SELECT OBJECTID FROM INSERTED)
     UPDATE [DNR_Edit].[TRIBAL_TRUST_LANDS]
          SET edit_date = getdate()
          WHERE OBJECTID IN (SELECT OBJECTID FROM INSERTED)
     
     UPDATE [DNR_Edit].[a483]
          SET Acres = shape.STArea() * 0.000247105
          WHERE OBJECTID IN (SELECT OBJECTID FROM INSERTED)
     UPDATE [DNR_Edit].[TRIBAL_TRUST_LANDS]
          SET Acres = shape.STArea() * 0.000247105
          WHERE OBJECTID IN (SELECT OBJECTID FROM INSERTED)
END

After making your edits, click the Editor dropdown > Save and the feature classes' fields will update.

Re: SQL Trigger in Versioned SDE

RobertScheitlin__GISP — Tue, 16 Sep 2014 12:48:08 GMT

Jake,

The D table hold just the deletes for the versioned FC it only has three columns SDE_STATE_ID, SDE_DELETES_ROW_ID, and DELETED_AT columns. When and edit or update occurs the original is marked for deletion and a new row is added to the A Table.

Re: SQL Trigger in Versioned SDE

JakeSkinner — Tue, 16 Sep 2014 13:14:46 GMT

Hi Robert,

After an update, the original is not deleted. Here is a screen shot of the A & D table after the same feature has been updated 4 times:

I originally was receiving an error, "connection is busy from results for another command", when trying to perform the INSERT and UPDATE on the A table. That is why I suggested to create the UPDATE on the D table. A second test, and this appears to be working with just creating the INSERT, UPDATE trigger on the A table. Not sure why I was receiving this error before.

Re: SQL Trigger in Versioned SDE

RobertScheitlin__GISP — Tue, 16 Sep 2014 13:30:14 GMT

Jake,

Yep that is why I said it is marked for deletion. Anyway I thought that I should clarify your statement.

D table for updates (moving/modifying features/attributes)

But now I think I understand that you were saying that a trigger needs to be added to this table for when the featureclass has a feature modified in some way and then the trigger will update the "a" table.

Re: SQL Trigger in Versioned SDE

JoeBorgione — Tue, 16 Sep 2014 14:23:46 GMT

I've used the same trigger on th A table only for years. Never worried about the D table. If the record has been deleted, what's left to modify with a trigger?

Re: SQL Trigger in Versioned SDE

ThomasColson — Fri, 25 Mar 2016 19:13:30 GMT

Based on your statement that you're only using the default version, and, emphasis on, this is my personal opinion, register the FC with "Move Edits to Base", and don't worry about triggers on the A table at all, put the trigger directly on the FC table. During an edit session, you won't see the trigger fire 'till you hit "Save Edits". That's my work flow, at least. Most of my editing is distributed (via AGISSvr Feature Service local copy for editing), and any attributing that is done by triggers is done on the backend like this. Why I like this way, is, if I ever have to unregister the FC to not-versioned, when the A table get's deleted, I don't lose a trigger I spend 2 weeks trying to get to work.

Re: SQL Trigger in Versioned SDE

PriscillaThoopthong1 — Thu, 28 Jun 2018 13:30:20 GMT

Whenever something is updated, SDE inserts into the A table, so that means even if you're just updating an attribute it gets inserted into the A table. So if you trigger is FOR INSERT, it will fire the trigger even when updating.

I only want my trigger to fire when they add a feature. Is there a way to do this? I tried doing a count in the trigger to see if there were other rows in the table with the same OBJECTID and that works great until I try to reconcile and then I get a mutating error. Is there another way?

Re: SQL Trigger in Versioned SDE

ThomasColson — Thu, 28 Jun 2018 13:50:58 GMT

AFTER INSERT, UPDATE
AS

BEGIN
SET NOCOUNT ON;
IF UPDATE ([SHAPE])
BEGIN

will only fire your trigger if a new feature is added, or the shape is edited (e.g moved). It will not fire on just an attribute edit.

Re: SQL Trigger in Versioned SDE

PriscillaThoopthong1 — Thu, 28 Jun 2018 14:22:39 GMT

Thanks but I don't want it to fire if the shape is moved either. I only want it to fire when the feature is first created. Actually I only want it to fire AFTER they've added a new feature and AFTER they've saved their edits but I think I'm asking too much of ESRI now

And then I have another trigger that I want to fire ONLY the first time a particular attribute is changed and ONLY that particular attribute and ONLY AFTER they've saved their edits.

Re: SQL Trigger in Versioned SDE

ThomasColson — Thu, 28 Jun 2018 15:24:05 GMT

This is all possible with before begin statement, you just need to build the conditions that must be TRUE before BEGIN. I've done something like that before, I just can't seem to find it now....try just after insert, drop the update.

Re: SQL Trigger in Versioned SDE

MikeLachance1 — Sat, 11 Dec 2021 20:23:55 GMT

I have a similar question but it also veers a little bit away from what has been discussed here so far.

Using:

ArcGIS Pro
Versioned Database (I am working with and have access to a child checked out from DEFAULT as well as a grandchild (child of child of DEFAULT)
Oracle w/ SDE

My purpose is to design a trigger so that the creation of a new feature in a specified feature class is recorded and stored in a history_tracking_table. A unique ID, the user who made the change, and the change date are captured as well. I have done this and it's functional. See code below:

create or replace
trigger insert_obs_history_2
AFTER INSERT
  ON A261570
  FOR EACH ROW
  
DECLARE
username varchar(20);
prev_Match_Recs number;

BEGIN
  SELECT COUNT(*) INTO prev_Match_Recs
  FROM HISTORY_TRACKING_STATE
  WHERE HISTORY_ACTION = 'INSERTED' AND
  HISTORY_REPORTID = :new.O_REPORTID;

IF prev_Match_Recs = 0 AND
:new.O_REPORTID IS NOT NULL AND
:new.SUBMISSION_STATE_ID IS NULL AND
:old.SUBMISSION_STATE_ID IS NULL
  THEN
    SELECT USER INTO username FROM dual;
  
    INSERT INTO HISTORY_TRACKING_STATE VALUES (
    :new.O_REPORTID, :new.SUBMISSION_STATE_ID,
    username, SYSTIMESTAMP, 'INSERTED' );
  END IF;
END;‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

My question is: How do I create an 'update' trigger to capture when the 'SUBMISSION_STATE_ID' field changes if my data is versioned? I am not able to run an UPDATE trigger and grab ':new' and ':old' values because with versioning the change in the field value is just an addition to the A#### table. I would like to be able to compare the old and new value of this field when it is changed to make sure they are not the same.

Any ideas?

Re: SQL Trigger in Versioned SDE

AndresCastillo — Sat, 10 Oct 2020 18:14:23 GMT

Beautiful solution for those with workflows that allow registering the FC as versioned with the option "Move Edits to Base"

Re: SQL Trigger in Versioned SDE

AndresCastillo — Sat, 10 Oct 2020 20:16:01 GMT

There's this GIS Stackexchange thread talking about how instead of implementing a trigger on the A&D tables, do it on the versioned view (ie. <TABLE_NAME_EVW>):

editing - ArcSDE 10.1 Insert/Update Trigger on Adds (A) Table (Oracle 11g) and Feature Services on ArcGIS Server - Geogr…

I have yet to try this, but I will verify it because I'm surprised they were able to write to a view (I thought it was read-only, per Oracle):

CREATE TRIGGER

INSTEAD OF Triggers

INSTEAD OF triggers are valid only for views. You cannot specify an INSTEAD OF trigger on a table.
You can read both the :OLD and the :NEW value, but you cannot write either the :OLD or the :NEW value.

In that article, they have lots of good points, including:

Invoking the trigger via the Feature Service query endpoint (https://<machine.domain.com>/webadaptor/rest/services/<folder>/<serviceName>/FeatureServer/0/query) via a web edit post API call.

They mention that if the Geodatabase Version Name parameter is not specified, edits are made to published map's version.

They also mention a cool way to use python to generate unique GUID's similar to Global IDs:

How To: Calculate unique identifier values similar to Global IDs

I believe I ran into your same question on GIS StackEchange as well:

enterprise geodatabase - How to Create 'Update Triggers' to Track Field Values Changes When Data is Versioned? - Geograp…

I did find an Esri documentation saying its ok to grant privileges to a user both on the base table and directly on the versioned view of the table (ie. <TABLE_NAME_EVW>), but that is indeed different than writing to the view:

Privileges for geodatabases in Oracle—Help | ArcGIS Desktop

If the user will be editing versioned data through a versioned view, the user must also be granted SELECT, INSERT, UPDATE, and DELETE privileges on the versioned view.

When you use the Privileges dialog box in ArcGIS to grant the SELECT, INSERT, UPDATE, and DELETE privileges on a versioned feature class, those privileges are automatically granted on the associated versioned view.

Another Esri documentation explains editing versioned views, which I believe applies to update triggers which edit record columns as well:

An overview of editing versioned data using SQL—Help | ArcGIS Desktop

Edit versioned data in Oracle using SQL—Help | ArcGIS Desktop

To edit versioned data from a SQL client, you must edit a versioned view of the data, not the base (business) table itself.

Editing the base table directly could lead to orphaned records and data loss.

Re: SQL Trigger in Versioned SDE

vijaybadugu — Wed, 07 Aug 2024 13:38:03 GMT

Can we update directly an attribute on A table or Versioned View on A table trigger for insert? I could see globalids 000000-00000-00000-000000 in A Table and base table after editing any feature in arcgis pro.

What needs to be done to avoid 0's in Global Id field?

Re: SQL Trigger in Versioned SDE

yockee — Tue, 15 Apr 2025 08:51:06 GMT

What if the table is not versioned ? I tried to record changes to a non-versioned table by copying some attributes (non spatial, just ordinary attribute data) to another table using triger (after update) whenever there is update. The trigger works fine on non-spatial table.