Issue with SQL Trigger running After Delete

1158
1
02-21-2017 02:04 PM
ShawnaErnst
Occasional Contributor

I've created a set of triggers for our street centerline and address point feature classes.  The first trigger maintains a perpetual unique key, the second copies inserts or updates to a history table, and the third trigger runs after delete and copies deleted records to the same history table.

The first two triggers are working fine.  The third trigger (deletes) works just fine on address points, and on a test copy of street centerlines, but is failing on our production street centerlines with the error:  "Unable to save edits.  Underlying DBMS error [HY000:[Microsoft][SQL Server Native Client 11.0]Connection is busy with results for another command]".  All three feature classes are registered as versioned with edits moving to base.

Our environment is ArcGIS 10.3.1 running on top of SQL Server 2008R2.

I'm sure someone will point out that I could take advantage of the SDE Archiving functionality, but I decided against that approach because I like moving edits directly to base.  I've designed a couple of QA/QC views that are more useful if editors can immediately see their changes.  There are always pros and cons, right?  🙂

Has anyone had this same error?  Any ideas for troubleshooting?

0 Kudos
1 Reply
ThomasColson
MVP Frequent Contributor

You have a violation of trigger nesting levels. Wrap up the separate triggers for (inserts/updates) and (deletes) into a single trigger. You'd be better off using Change Data Capture though.