ArcGIS Online Editing form Overwrites Database Insert Trigger

[JakeMatthys]

I will attempt to describe a simple example that illustrates the issue that I am experiencing.

I have a line feature class with a database insert trigger that populates an ID column from a sequence.  In AGO, when a user double clicks to finish creating a feature AND then proceeds to update another attribute using the AGO attribute form, the ID column will be overwritten with a null.  Behind the scenes, the double click in AGO does the insert.  The insert trigger fires in the database and the row gets the ID from the sequence (can see this in sql-plus).  The issue is that the AGO attribute window does not get updated with the ID, so when the user updates a different attribute, and update takes place against the feature class and each row in the AGO attribute form that does not contain data (which includes the ID attribute) gets overwritten with a null (can see this in sql-plus). 

This issue does not exist in ArcMap.  When a user double clicks to create a feature in ArcMap, the attribute window immediately refreshes to show the result (new ID) of the insert trigger. 

My current work around is to add additional UPDATE logic to the database trigger, but I am searching for a more elegant solution.  Is there a way to get the AGO attribute window to refresh so that it will display the values that are currently in the database?  Something similar to the disableClientCache (from silverlight) might be available in AGO or can be turned on from a feature service perspective?

I searched GeoNet and did not find other posts concerning this issue.  How have others gotten around this issue? 

Thanks

- Jake

[TobiasFimpel1]

Hi Jake,

I had the same issue. I solved it by enabling Editor Tracking and then on-insert-and-update comparing the row's TimeCreated vs. TimeLastEdited values to determine whether the edit is a new feature or an update. Only if TimeCreated=TimeLastedited (i.e. a newly created feature) should the ID be calculated based on the sequence. I can send you my sql code tomorrow or so - remind me if I forget.

Thanks,

Tobias

[TobiasFimpel1 · Accepted solution]

Jake, upon re-reading your post I realize that my answer is not really solve the real issue you are referring to. It solves the "overwrite with null" issue that you seem to have solved already with "additional UPDATE logic". Anyways, here's my code, perhaps it's useful for someone else...

CREATE OR REPLACE TRIGGER sDE.FEATURECLASS_ID_BEF_INS

BEFORE INSERT OR UPDATE ON sDE.FEATURECLASS

FOR EACH ROW

BEGIN

IF :NEW.CREATED_DATE = :NEW.LAST_EDITED_DATE THEN

      SELECT FEATURECLASS_S.NEXTVAL

      INTO :NEW.IDFIELD

      FROM DUAL;

   END IF;

   IF :NEW.CREATED_DATE <> :NEW.LAST_EDITED_DATE THEN

--this is an edit to an existing point. here we need to block the incoming null value for IDFIELD

      SELECT :OLD.IDFIELD

      INTO :NEW.IDFIELD

      FROM DUAL;

   END IF;

END;

/

[JakeMatthys]

Hi Tobias,

Thanks for your response.  I have essentially used the logic that you provided above (IF :NEW.FIELD IS NULL AND :OLD.FIELD IS NOT NULL THEN SET :NEW.FIELD := :OLD.FIELD) in order to prevent the 'overwrite with nulls' issue.  I guess my overall concern is that this is 'extra' logic that must be evaluated by the database simply because the form does not update with values that are currently in the database.  I'm sure that there are technological obstacles that ESRI must overcome in order to tightly couple the web-form with the database.  Hopefully this is something that is on their radar.  Thanks for the confirmation that the approach that I am taking is similar to the work around that you have used.

Thanks,

- Jake

[JakeMatthys]

I should add that the logic that I am using will NOT allow a user to overwrite an existing value in the database with a NULL value, even if they wanted to.  For my specific application, this is an acceptable consequence.