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.

[TL2]

I am still unable to use an "AFTER UPDATE" sql trigger on the table of a survey123 feature class.  I am able to use "AFTER INSERT".  "AFTER INSERT" does do one insert and I am able to pull data from the submission (before after insert would only work with Attachments disabled. 

"AFTER UPDATE" still causes the survey submission to hang.  

Sure would be nice to to use "AFTER UPDATE" on a survey123 feature table...

[JamesTedrick]

Hi,

What DBMS are you using? Which version of ArcGIS Enterprise?

Could I also ask that you try a direct REST submission request on the feature service's ApplyEdits call (Apply Edits (Feature Service)—ArcGIS REST API: Services Directory | ArcGIS for Developers ) and see if you get the same results?

[TL2]

James,

Error: Underlying DBMS error [HY000:[Microsoft][ODBC Driver 13 for SQL Server]Connection is busy with results for another command] [GISDB.DBO.FC_S123_TABLE].

I believe this to be the one error in my way.  Here are the scenarios that work and fail.

This seems to not be a S123 issue so maybe you can point me to someone else.

After Insert

• a. The post is successful and I am able to use data from inserted and update tables in the geodatabase with inserted data.
• b. The post is successful when I use an exec store procedure and do not use any data from inserted in the SP.
• c. The post fails when I use an exec store procedure and try to capture any data from inserted in the SP.
  • example: exec sp_send_dbmail fails with inserted data in the email body/subject...
    • This is the one I want and this is the one that gives me the error above.

After Update

• d. The post is successful and I am able to use data from inserted and update tables in the geodatabase with inserted data.

• e. The post fails when I use an exec store procedure and do not use any data from inserted in the SP.
• f. The post fails when I use an exec store procedure and try to capture any data from inserted in the SP.
  • example: exec sp_send_dbmail fails with inserted data in the email body/subject...
    • This is the one I want and this is the one that gives me the error above.

I do receive these same results when applying edits at the rest end point as mentioned above. 

I am using SS2016 and 10.7.  Data is Archived.

When applying the edits directly in ArcMap I do not have any issues.

Thanks.

[JamesTedrick]

Hi,

Yes, this appears to be out of scope for Survey123 - I would suggest opening an Esri technical support ticket focusing on the DB behavior.

[vijaybadugu]

I was facing same issue 

[TL2]

Its actually not a S123 issue but the way the rest endpoint posts to the database using Apply Edits at the feature service level.  From my testing it does two transactions, an insert and then an update.  Any executions fail in the trigger because they are called on the insert but the update has not occurred yet.  Hence the warning.  I have not found a work around, but that is whats happening.

Additionally it is by design so basically not an ESRI problem.

I'm not sure how Collector posts but this is not an issue with Collector.

[vijaybadugu]

Yes .. The problem with Insert when we append update to it AFTER INSERT, UPDATE . Whenever Inserting records , Internally update is also firing . If you write Trigger in Attachment table . it should work . it is not working on Base table 

[vijaybadugu]

The problem is when i did not pass attachment is also. it is firing twice on insert trigger