SQL Trigger on table causes CODE 500 Error.

[TL2]

I have a trigger on the base table of one of my feature services.  After Insert seems to work fine as I have concluded that a Survey123 to FS transaction does a Input, then an Update.  

After Insert statements to not cause the Code 500 Error.

After Update does cause the Code 500 error, but not everytime, most of the time.

Is there a rational reason for this where I can code around it.  I need to pull data from insert after an update without the code 500.

I should add that the trigger performs fine and correctly when making the edits right in ArcMap, every time.  Also I have never had these issues with Collector (however most of my Collector FS is versioned, Survey123 is archive enabled.

[JohnathanHasthorpe]

Hi Tucker

Does you service have attachments? The reason I ask is that on submission, Survey123 will do an ApplyEdits and then an AddAttachments and this is why you would get two calls.

In the future we will change this into a single transaction using apply edits with UploadID, but we are still working on this implementation.

Thanks

John

[TL2]

I was skeptical on this response at first but apparently this is exactly what is going on. 

I realized that all of my After Insert triggers work when there are no attachments.

When there are attachments, my After Insert triggers fire, but the declared variables are always null. 

It seems to go something like this... Insert Row, Update Attachments, Update Row.  Unfortunately After Update does give the Code 500 error and freezes up the feature service as well.

Do you have any idea on the implementation of the apply edits with UploadID?  I would guess that is the method Collector uses.

Thanks.

[JohnathanHasthorpe]

We are aiming for 2.7 - there are just a couple of bugs that we need to iron out.

[TL2]

Was this able to be fixed in 2.7 (before I begin to test)?

 THANKS!!!!!!

[JamesTedrick]

Hi TL,

With 2.7, we have instituted a change in the submission process where supported. This reduces the number of calls to the database to 1.  Note that this is not supported with ArcGIS Enterprise hosted services (they currently do not advertise support for the submission procedure we are using) - it sounds as if you service is federated, correct (it should be using the new submission if that's the case).

[TL2]

James,

I am testing my triggers using 2.7 and while I do not receive the code 500 error when posting my surveys, the data still does not seem to post before the trigger is fired.

If I make edits or create a new feature within ArcMAP, my trigger contains the variables.  When I create a survey in Survey123 2.7, the trigger is fired but the variables are not passed. 

Any Suggestions?

Note: I do receive the error on "update" but not on "insert"

Thanks.

[JamesTedrick]

This is probably a bit complex to debug via GeoNet; it may make sense to try to do a webcast to interactively debug/understand the issue.  That being said, there are some areas to examine: 

• What is the database Survey123 is writing to- and enterprise GDB or the hosted feature store?  
• Can you attempt editing via the REST endpoint directly to see if the behaviors are consistent?
  • Survey123 uses the applyEdits call at the FeatureService endpoint (i.e., https://gis.server.com/arcgis/MyService/FeatureService/applyEdits)
  • Most other web editing techniques use applyEdits at the Feature Service Layer endpoint (i.e., https://gis.server.com/arcgis/MyService/FeatureService/applyEdits   

[TL2]

It seems to be behaving like version 2.6 and prior.  I have republished with 2.7 Connect, republished my service mxd and upgraded S123 to 2.7.  Is there a way to verify it is using the new method to post.  Portal federated with server 10.5.

[TL2]

I should say, posting with attachments disabled still works, just not with attachments.