SQL Trigger in Versioned SDE

[LisaDygert]

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

[RobertScheitlin__GISP]

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.

[JakeSkinner · Accepted solution]

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.

[RobertScheitlin__GISP]

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.

[JakeSkinner]

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.

[RobertScheitlin__GISP]

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.

[JoeBorgione]

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?   

[ThomasColson]

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.

[AndresCastillo]

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

[PriscillaThoopthong1]

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?