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

[ThomasColson]

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. 

[PriscillaThoopthong1]

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.

[ThomasColson]

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. 

[MikeLachance1]

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?

[AndresCastillo]

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 A... 

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

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.