Feature service not executing DB triggers

[HanliePretorius]

Hi,

I'm working on ArcGIS 10.4.2 in Windows.

I have published a feature service to our ArcGIS Server and then added the service to our Portal from a feature class that has Editor Tracking and attachments enabled. In the database I created a sequence called TreeNumber, which I increment with a trigger on the table (feature class) after insert. It is seperate from the ObjectID.

If I add a feature in ArcMap, everything is fine - the attachment works and the TreeNumber is assigned and saved.

If I add a feature using JSON, talking directly to the REST endpoint, TreeNumber is assigned and saved. (I haven't worked out how  to add an attachment through the REST endpoint.)

If I add a feature in a Webmap, the attachment works but the TreeNumber is NULL after saving.

If I add a feature in a WebApp (built in WebApp Builder), the attachment works in IE but not in Chrome or Firefox. In all of them the TreeNumber remains NULL.

The Portal uses https and a web adaptor and is on the same server as the ArcGIS server.

The data is in a SQL Server 2012 Enterprise Geodatabase on a server separate from the ArcGIS server.

Can anyone help me with this?

Thanks

Hanlie

[HanliePretorius · Accepted solution]

I have found the answer about the attachments in this blog:
https://blogs.esri.com/esri/supportcenter/2016/09/20/announcement-errors-encountered-in-arcgis-for-s...

After installing the patch, the attachment upload now works in Chrome.

The TreeNumber created by the database trigger is only visible once the editing is disabled and the map view is refreshed so that the feature service is requeried.

[jonathanrobinson]

"The TreeNumber created by the database trigger is only visible once the editing is disabled and the map view is refreshed so that the feature service is requeried."

Hello, I'm experiencing a similar issue.  Can you please give me more detail about you statement above?  I don't understand.

[HanliePretorius]

Hi Jonathan,

Incrementing an ID field (TreeNumber) automatically from a feature service is problematic because of the way the REST service communicates with the database - it goes back and forth several times, possibly because ArcGIS first creates the feature with an ObjectID and a Shape and then updates this record with attributes. In the front end it all looks like one process, but in the background it's two. I have actually abandoned that idea and now generate TreeNumber values only in the desktop.

If you still want to try to auto increment an id field using a feature service, just make sure you refresh everything after you have saved before you decide if it worked or not. So, close the edit tool and zoom in and out, then you should see the changes. That was what I meant by the statement you highlighted.

Hope this helps

Hanlie

[Anonymous User]

Came across something very similar in the below situation and thought I'd share in case of use to future reader.

Mobile solution using Collector and Survey123: 

Users starts with Inspection sites in Collector (sites table) and from site pop up opens Survey123 survey for that site with custom url passing details from sites attributes (e.g. previous meter reading at site) into survey123 fields (survey table).

Survey has attachments (feature service attachments true) and db trigger on the survey table that executes when survey submitted. Trigger updates values in sites table e.g. site inspection status, last inspected date, previous meter reading values etc.

What I found was if the feature service had attachments then I needed to execute the trigger on update not insert. Executing it on insert resulted in the trigger firing before attributes in survey table were available to be populated into sites table. 

If the survey table does not have attachments then executing the db trigger on insert works just fine. 

[HanliePretorius]

Thanks for the information - looks like a very useful workflow.

2018-12-12 0:15 GMT+02:00, Michael Fletcher <geonet@esri.com>:

Michael Fletcher

replied to the discussion

"Re: Feature service not executing DB triggers"

To view the discussion, visit:

https://community.esri.com/message/818908-re-feature-service-not-executing-db-triggers?commentID=818908&et=watches.email.thread#comment-818908

>

[Adam_Commeford]

This seems to be the solution. Make sure your trigger is firing on updates and inserts as opposed to just inserts. 

CREATE OR REPLACE TRIGGER Trigger_Name 
BEFORE INSERT OR UPDATE ON "Table_Name"
FOR EACH ROW

BEGIN
IF :NEW.ID IS NULL THEN
:NEW.ID :=  :NEW.OBJECTID;
END IF;
END;

[HanliePretorius]

Thanks, this worked for me but changes are only visible after creating, saving, updating and saving.